Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: long "IN" list, tempo in-memory table
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_transaction2 on commit delete rows
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;
--
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