Re: MSSQL Table Variable Equivalent?

From: Thomas Kyte <thomas.kyte_at_oracle.com>
Date: 24 May 2005 04:26:07 -0700
Message-ID: <126933967.00006cb6.038@drn.newsguy.com>

In article <1116884005.477841.45400_at_g49g2000cwa.googlegroups.com>, LineVoltageHalogen says...
>
>Greetings All, I was hoping that someone might be to help me out. I
>was wondering if Oracle has a table data type like SQL server. This is
>just a variable that lives completely in memory and has many of the
>characteristics of a table. I need to be able to store a list of
>values in a table like format in memory. I say memory because each
>session logging into the server will have to perform this operation and
>I need it to be as fast as possible. I am aware of global temp tables
>and that they reside in the temp tablespace. However, the data in the
>temp table is written to the buffer cache which leads me to believe
>that there is a possibility that it could be written to disk too. This
>would be a huge drain on performance. So, I would appreciate any
>feedback.
>
>TFD
>

Funny thing about caches -- if you use it they keep the data in memory, if you do not use it, they page it out -- just like OS memory. You could well be solving a non-existent problem (we have session persistent global temporary tables, they may well be what you were looking for. I can say, due to the fact you can set statistics on them, you might find query optimization easier).

But, in memory collections can be queried as if they were a table, for example:

ops$tkyte_at_ORA9IR2> create or replace type myScalarType as object   2 ( x varchar2(30), y date, z number )   3 /  

Type created.  

ops$tkyte_at_ORA9IR2>
ops$tkyte_at_ORA9IR2> create or replace type myTableType as table of myScalarType   2 /  

Type created.  

ops$tkyte_at_ORA9IR2>
ops$tkyte_at_ORA9IR2>
ops$tkyte_at_ORA9IR2> create or replace package demo_pkg
  2  as
  3      g_data myTableType;

  4 end;
  5 /  

Package created.  

ops$tkyte_at_ORA9IR2>
ops$tkyte_at_ORA9IR2> create or replace package body demo_pkg   2 as
  3
  4
  5 begin

  6      select myscalarType( username, created, user_id )
  7        bulk collect into g_data
  8        from all_users
  9       where rownum <= 5;

 10 end;
 11 /  

Package body created.

Note: there are millions of ways to initialize the array, this is just an example.... first time we touch the package in our session, that elaboration code will run and fill up the collection for us, you could fill it anyway you want...  

ops$tkyte_at_ORA9IR2>
ops$tkyte_at_ORA9IR2> begin

  2      for x in ( select *
  3                   from all_users
  4                  where user_id in
  5                     ( select x.z
  6                         from table( cast(demo_pkg.g_data as
  7                                          myTabletype ) ) x )
  8               )
  9      loop
 10          dbms_output.put_line( x.username );
 11      end loop;

 12 end;
 13 /
SYS
SYSTEM
OUTLN
DBSNMP
WMSYS   PL/SQL procedure successfully completed.

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation

Received on Tue May 24 2005 - 06:26:07 CDT