Re: long "IN" list, tempo in-memory table

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 30 Jul 1999 12:25:59 GMT
Message-ID: <37b69993.177955957@newshost.us.oracle.com>

A copy of this was sent to Fei Sha <Fei.Sha_at_prism.uvsq.fr> (if that email address didn't require changing) On Fri, 30 Jul 1999 11:13:32 +0200, you wrote:

>
>
>David Sisk wrote:
>
>> Hi:
>>
>> If you're using Oracle8.1,you can create a temporary table and insert the
>
>> necessary values into it.
>
>Sorry I'm not too familiar with Oracle so need more precisions on your
>response.
>
>Why Oracle 8.1? Is the temporary table in 8.1 not written to disk?
>
>How to create a temporary table in 8.1?
>
>Thanks!
>
>Regards,
>
>Fei

Here is how temp tables in Oracle8i work.

You create them once per database, not once per stored procedure in the database. They always exist but appear empty until you put data in them. They may be SESSION based (data survives a commit but not a disconnect/reconnect). They may be TRANSACTION based (data disappears after a commit). They may be written to disk (there only exists so much memory) but would not be logged. Here is an example showing the behaviour of both. I used the scott.emp table as a template:

SQL> create global temporary table temp_table_session   2 on commit preserve rows
  3 as
  4 select * from scott.emp where 1=0
  5 /
Table created.

the ON COMMIT PRESERVE ROWS makes this a session based temporary table. rows will stay in this table until a logoff. Only I can see them though, no other session will ever see 'my' rows even after I commit

SQL> 
SQL> 
SQL> create global temporary table temp_table_transaction

  2 on commit delete rows
  3 as
  4 select * from scott.emp where 1=0
  5 /
Table created.

the ON COMMIT DELETE ROWS makes this a transaction based temp table. when you commit -- the rows disappear.

SQL> insert into temp_table_session select * from scott.emp; 14 rows created.

SQL> insert into temp_table_transaction select * from temp_table_session; 14 rows created.

we've just put 14 rows into each temp table and this shows we can 'see' them:

SQL> select count(*) from temp_table_session   2 /

  COUNT(*)

---

        14

SQL> select count(*) from temp_table_transaction   2 /

  COUNT(*)

---

        14

SQL> commit;
Commit complete.

since we've committed, we'll see the session based rows but not the transaction based rows:

SQL>
SQL> select count(*) from temp_table_session   2 /

  COUNT(*)

---

        14

SQL> select count(*) from temp_table_transaction   2 /

  COUNT(*)

---

         0

SQL> SQL> connect tkyte/tkyte
Connected.
SQL> since we've started a new session, we'll see no rows now:

SQL>
SQL> select count(*) from temp_table_session   2 /

  COUNT(*)

---

         0

SQL> select count(*) from temp_table_transaction   2 /

  COUNT(*)

---

         0

SQL> If you really need the temp table to be created in the procedure itself, Oracle8i release 8.1 makes this much easier to do as well. Consider the following example which uses plsql to create, insert into, fetch from and drop a temporary table -- whose name is not known until run time. Its almost as easy as static sql is:

SQL> declare
  2 type mycur is ref cursor;
  3

  4      l_tname     varchar2(30) default 'temp_table_' || userenv('sessionid');
  5      l_cursor    mycur;
  6      l_ename     scott.emp.ename%type;
  7  begin
  8      execute immediate 'create global temporary table ' ||
  9                         l_tname || ' on commit delete rows
 10                         as
 11                         select * from scott.emp where 1=0 ';
 12  
 13      execute immediate 'insert into ' || l_tname ||
 14                        ' select * from scott.emp';
 15  
 16      open l_cursor for
 17          'select ename from ' || l_tname || ' order by ename';
 18  
 19      loop
 20          fetch l_cursor into l_ename;
 21          exit when l_cursor%notfound;
 22          dbms_output.put_line( l_ename );
 23      end loop;
 24  
 25      close l_cursor;
 26      execute immediate 'drop table ' || l_tname;

 27 end;
 28 /
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN
MILLER
SCOTT
SMITH
TURNER
WARD PL/SQL procedure successfully completed.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jul 30 1999 - 07:25:59 CDT