Newsgroup: it.comp.software.database
Titolo: table temporanea in oracle

Ho la necessità di creare una table in memoria da poter utilizzare in una
SELECT senza i limiti di 1000 elementi della funzione IN (xxx,yyy,zzz,...)
Mi dicono che in SYBASE è possibile.
Sapete se in Oracle si può fare e come si fa?
Grazie

Ciao mistico,
eccoti un pò di documentazione:
Introduction
~~~~~~~~~~~~ This is an overview of TEMPORARY TABLES introduced in Oracle8i.
This  new feature allows temporary tables to be created automatically in a
users temporary tablespace.  Syntax ~~~~~~   CREATE GLOBAL TEMPORARY TABLE
tablename ( columns )         [ ON COMMIT PRESERVE | DELETE ROWS ]    The
default option is to delete rows on commit.  What Happens ~~~~~~~~~~~~
When you create a GLOBAL TEMPORARY table a dictionary definition of   the
table is created. As soon as the table gets populated (on the first   INSERT
or at creation time for CTAS operations) a temporary segment is   created in
the users default TEMPORARY tablespace location. This temporary   segments
contents are just like a normal table.    Different sessions using the same
GLOBAL TEMPORARY table get allocated   different temporary segments. The
temporary segments are cleaned up   automatically at session end or
transaction end depending on the specified   duration (ON COMMIT PRESERVE
ROWS or ON COMMIT DELETE ROWS).     Apart from the data visibility temporary
tables can be used like ordinary   tables for most operations.
Characteristics ~~~~~~~~~~~~~~~   1. Data exists only for the duration of
either the session or     transaction.     This can be specified in the
create table command.    For example:         SQL> Create global temporary
table emp_temp(eno number)                on commit delete rows;       -
OR -         SQL> Create global temporary table emp_temp(eno number)
on commit preserve rows;      ON COMMIT DELETE ROWS indicates a transaction
level duration and    PRESERVE indicates a session level duration.      2.
Data is visible only at session or transaction level.  Multiple     users
using the same temporary table can see the definition    of the table and
their own data segment and nothing else.  3. Indexes, triggers and views can
be created on these tables.  4. If an Index is created on temporary tables
then it MUST be created     when the table is empty - ie: When there are NO
temporary segments for     incarnations of the table. Indexes are
implemented as separate     temporary segments.  5. No redo is generated for
operations on the temporary table itself BUT    undo is generated. Redo *IS*
generated for the undo so temporary tables    do indirectly generate redo.
6. The keyword GLOBAL indicates the table definition can be viewed    by
anybody with sufficient privileges - ie:using the same rules    that apply
to normal user tables. Currently only GLOBAL TEMPORARY    tables are
supported.  7. TRUNCATE operations truncate only the current session's
incarnation    of the table.  8. You can only export or import the
definition not the data.  9. Segments get created only on the first insert
(or CTAS) operation.   Drawbacks ~~~~~~~~~  1. The table definition is not
dropped automatically.   2. Only GLOBAL tables are supported right now, not
local ones.      3. Can perform DDL only when no session is bound to it.
4. There is no underlying support for STATISTICS on GLOBAL     TEMPORARY
tables so CBO (Cost Based Optimizer) has no statistical    information to
help determine an execution plan.    NB: "ANALYZE TABLE COMPUTE/ESTIMATE
STATISTICS" returns success         even though no statistics are gathered.
Constraints ~~~~~~~~~~~  Constraints can be implemented on the table either
at the session or  transaction level depending on the scope of the temporary
table and are not for the entire table even though the constraint is defined
for the entire table.  If there is a primary key or unique key constraint,
it is applicable only at either session or transaction leve  i.e. two users
can enter the same values into the table from different sessions even though
you have a primary / unique key in place for the entire table (if the scope
is the session )  In the case of a transaction level temporary table, the
same values  can be entered from different transactions.

 ha scritto nel messaggio
news:b06fpc$9d6$1...@grillo.cs.interbusiness.it...


Dal sito 
metalink.oracle.com
 ma devi essere partner Oracle per accedervi!
Ciaooo,
Simone