Skip to Main Content
  • Questions
  • Temporary tables (comparision with SQLServer)

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Alfredo .

Asked: May 02, 2000 - 1:20 pm UTC

Last updated: May 16, 2019 - 3:24 pm UTC

Version:

Viewed 100K+ times! This question is

You Asked

We are migrating some Stored Procedures from Sybase 11
to Oracle 8i. Most of them use temporal tables like:

SELECT x, y, z INTO #Temp FROM some_table ...
-OR-
CREATE TABLE #Temp ( .... )

Where #Temp is a Temporal Table in a temporal area which
automaticaly drops down when the Stored Procedures ends or could
be erased using "Drop Table #Temp" and is an individual table for
each user.

We can not use the same type of Temporal Table in Oracle 8i. So I
wonder what is the equivalent of this type of table and how we
can create them and manage.

Thank a Lot for your help.

Alfredo Alzamora
Systems Analyst
CONASEV


and Chris said...


I believe you mean "temporary tables" -- temporal tables in a database are another thing entirely (there is actually such a thing -- temporal tables are tables that can return the answer that existed at a point in time -- you can ask the table to return the answer that existed at midnight last night, instead of the answer that exists right now)...

Oracle's temporary tables are similar to temp tables in those
other databases the main exception being that they are 'statically' defined. 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). 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>


Instead of executing "select x, y, z into #temp from some_table" you would:

o once per database create "TEMP" as a global temporary table.

o then in your procedures you would simply "insert into temp (x,y,z) select x,y,y from some_table"




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.

Bear in mind that both CREATE and DROP are DDL and will commit any outstanding transactions.


follow up to comment:

thats exactly what I mean. DDL is performed as:

commit;
ddl;
if success commit
else rollback;

see </code> http://asktom.oracle.com/~tkyte/autonomous/index.html <code>
for a paper on autonomous transactions which may be useful in such a case.


Rating

  (38 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

VJ, April 25, 2001 - 2:53 pm UTC

Can you explain in detail your last comment that create/drop are DDL and will commit out outstanding transactions? Does that mean that, in procedure there are some updates then an DDL is executed dynamically, then updates made earlier will get commited automatically?

Anil Kumar, September 22, 2001 - 4:16 pm UTC

Excellent,I like most of the articles here.

why temp table why not a regular table

Nag, November 21, 2001 - 9:07 am UTC

Tom , in the example you have given for

"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:
"

Why cant I use a regular table. What is the benefit of using a temporary table. Even if I use a regular table instead of a temp table, it is temporary in the sense that we are going to drop it after using it , as you have done in the program.

So how does using a temporary table here gives more benefit that using a regular table.

thanks

Tom Kyte
November 21, 2001 - 10:17 am UTC

Nag -- you have "the book" (mine ;) -- check out the chapter on tables. There i explain how temp tables generate almost no redo and rollback. How they automagically clean themselves out upon commit or session end. Those are their advantages.

nice feature

Rahul, September 04, 2002 - 7:42 am UTC

Hi Tom

When I'm trying to create the gtt with specified columns , it gives me error...

SQL> create global temporary table gtt
  2  on commit preserve rows
  3  (caseno number(9),permissions varchar2(5),iuid number(9),userid number(9));
(caseno number(9),permissions varchar2(5),iuid number(9),userid number(9))
*
ERROR at line 3:
ORA-00922: missing or invalid option

But when I give the command without "on commit preserve rows" , it creates the table without any error.

SQL> create global temporary table gtt
(caseno number(9),permissions varchar2(5),iuid number(9),userid number(9));

Table created.

So pls. let me know when I created the above table , in which mode it will work ..
i) on commit preserve rows
ii) on commit delete rows

Regards
Rahul

 

Tom Kyte
September 04, 2002 - 8:51 am UTC

ops$tkyte@ORA817DEV.US.ORACLE.COM> create global temporary table t
  2  on commit preserve rows
  3  ( x int )
  4  /
( x int )
*
ERROR at line 3:
ORA-00922: missing or invalid option


ops$tkyte@ORA817DEV.US.ORACLE.COM> create global temporary table t
  2  ( x int )
  3  on commit preserve rows
  4  /

Table created.


Put it in the right place -- after the column list... 

Thanks But...

Rahul, September 05, 2002 - 2:17 am UTC

Hi Tom

Thanks for the help but you didn't tell me the mode of the table , if we create it with simple syntax as I send you .

Thanks

Tom Kyte
September 05, 2002 - 7:36 am UTC

well, it is documented:

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/statem3e.htm#2061631 <code>

and it can be queried out (duration column in user_tables)

It could also be discovered in a trivial fashion by

a) create global temporary table
b) insert a row
c) commit
d) select * from it -- if you get a row, it must be a SESSION based table, if you don't, it must be a TRANSACTION based table







Temporary tables (comparision with SQLServer)

Paul Wiselius, February 19, 2004 - 5:28 am UTC

This is the first time I asked Tom about temp-tables.
Always thought it wasnt possible in Oracle. Maybe you should advertize more with this feature!

Can SQL*Loader load data into a temp table

Jennfier Chen, May 20, 2004 - 1:48 pm UTC

Hi Tom,

Really thankful to be able to learn a lot from you. 

I have a batch job using regular table, which generated nearly 10GB redo logs.

I would like to use temp table, but found that I can't create indexes on it (the temp table needs to have indexes to speed up the join to another huge table and perform some complicated business rules):

SQL> create global temporary table alias.alias_snapshot
  2    (SID                                      VARCHAR2(8),
  3     dob                                      DATE,
  4     fbi                                      VARCHAR2(9),
  5     iffs                                     CHAR(1) NOT NULL,
  6     iii_status                               CHAR(3),
  7     nam                                      VARCHAR2(90),
  8     rac                                      CHAR(1) NOT NULL,
  9     sex                                      CHAR(1) NOT NULL)
 10    on commit preserve rows;

Table created.

SQL> CREATE UNIQUE INDEX alias.uk_alias_snapshot_sid_fbi
  2  ON alias.alias_snapshot (sid, fbi)
  3  TABLESPACE alias_mi;
ON alias.alias_snapshot (sid, fbi)
         *
ERROR at line 2:
ORA-14451: unsupported feature with temporary table

I need to be able to delete rows from the temp table in the middle of a transaction, is this feaciable?

Also, can SQL*Loader load data into a temp table?

Thank you. 

Tom Kyte
May 20, 2004 - 2:25 pm UTC

you would never specify a tablespace for anything to do with a temporary object -- they are "temporary", they go into TEMP.....

while sqlldr could load a temp table, it would be "moot" since temp tables are cleaned out upon commit or session end -- so, sqlldr would load it up but no one would ever see it!

Returning a cursor from a function

André, June 22, 2004 - 8:32 am UTC

Is there any way i can drop the temporary table and return the cursor (function) in the example you posted?

execute immediate 'drop table ' || l_tname;

return l_cursor;

I tried this but it shows a error because the table which the cursor was created no longer exists.

ORA-08103: object no longer exists

What is the best way to manipulate manipulate temporary data before returning a cursor from a function? I saw your comments about not using temporary table. Which one is better? Record, Table, Object, other?

Thanks in advance!

Tom Kyte
June 22, 2004 - 9:28 am UTC

you do not drop temporary tables in Oracle. it is not sqlserver.

do not do ddl in your stored procedures.


very horribly bad idea.

With clause ....

READER, July 20, 2004 - 9:42 pm UTC

Can you point me to some documentaion on the With CLAUSE
(which creates a temp table )
I tried my best but could not find anything much on metalink or your site.
which key word should I use.
Also,
Should I see a temp table transformation/creation in my explain plan ?

Thanks ,

Tom Kyte
July 20, 2004 - 10:05 pm UTC

you might see such a thing in the plan (a view_ step) but you might not.



it is called subquery factoring:

</code> http://otn.oracle.com/pls/db92/db92.drilldown?remark=&word=subquery+factoring&book=&preference= <code>



Deleting Temporary Tables

Deborah, July 22, 2004 - 1:29 pm UTC

I created a temporary table but now I need to change it. How can I delete it and change it?

Tom Kyte
July 23, 2004 - 8:06 am UTC

what do you mean by "change"

 
ops$tkyte@ORA9IR2> alter table gtt add a number;
 
Table altered.


use alter as you would on a "real table" to modify the structure.

use insert/update/delete to modify it's contents.

use drop+create if you want.

it is not any different than a "real" table. 

SQL Loader and Temp tables

Mike, August 18, 2004 - 6:46 am UTC

for the record in 8i (8.1.7) at least you can not use SQL Loader to populate a temp table (SQL*Loader-280: table MY_TEMP is a temporary table). Nor do I think it is a moot point, Assuming I load a file with N data records and a trailing "these were the counts" record I can load that trailer into a view with an instead of trigger, and then start processing the temp table.

I was trying to do this (glad I tried it out first), but I guess I'll have to go to a normal table with the replace option... Shame but I can understand why...

Tom Kyte
August 18, 2004 - 8:36 am UTC

well, if you think about it -- temporary tables are either transaction based (poof goes the data when you commit) or session based (poof goes the data when you log off).

given that sqlldr

a) connects
b) loads
c) logs off

it is a good thing(tm) it won't load a temporary table -- it would just be wasting your time!

in 9i, you can use external tables to load a gtt in your session.

yes but no...

Mike, August 18, 2004 - 9:54 am UTC

I agree that in 99.9999% of occasions it is pointless to load into a temp table as by its definition it disappears when the load is finished.

However it looks like I wanted to do the opposite of the 9i external table... as I can't invoke sql Loader from PL/SQL (which is roughly what the external tab does?) I was trying to invoke PL/SQL from SQL Loader. so the Pl/SQL could process the contents of the temp table. this would be done inside the same session (the SQL Loaders one)

Still think it's valid, and a good idea(tm) too. but I can understand why you can't do it (the 99.99999999999% rule) and its not exactly a heartache to get around...

Tom Kyte
August 18, 2004 - 10:00 am UTC

it is exactly what an external table does (and more)....




true...

Mike, August 18, 2004 - 10:09 am UTC

but I'm 8i and I think you would be surprised how many 8i databases are out there.

My final word on the subject. I shall post no more!!

Tom Kyte
August 18, 2004 - 10:18 am UTC

i would not be surprised, i sort of have my finger on the pulse of that...

8i

A reader, August 18, 2004 - 10:26 am UTC

I wonder why this is. Customers rush to upgrade to Windows 2003, heck, even Windows 2005 Release Candidate 1 and are still on 8i which is years behind the latest release.

Is Oracle's marketing/support department doing something wrong?

Oracle should just start more aggresively desupporting older releases!

Dave, August 18, 2004 - 11:35 am UTC

why go to a newer version if the one you currently have works fine and you dont need the new features? Saves a lot of money on testing etc.

Reason people upgrade windows is simple - each release provides more stability than the last one wich is needed

Windows

A reader, August 18, 2004 - 2:29 pm UTC

"Reason people upgrade windows is simple - each release provides more stability than the last one wich is needed"

Well, thats not really true. Each newer Windows release fixes some old issues and gives rise to its own set of problems.

Overall stability, from the customers standpoint, is IMHO not really improved

Its all about marketing and perception. Windows is perceived as being so unstable, even today, that as soon as a newer version is released, customers jump to upgrade, silently hoping, praying that it will be more stable.

Heck, if you dont need the new features, even Oracle 7.3 is a solid piece of software!

Guess Oracle should employ the same marketing tactics as MS in promoting its releases.

Oh well, we are getting off-topic for this forum

A reader, August 20, 2004 - 9:00 am UTC

I think you are comparing apples and type writer's Oracle is not subject to the security attacks that Windows is. Nor is Apple Linux etc. etc. Besides which I was reading on the reg only today that Oracle has been in the bad books for holding up security patches for 10G DB, 10G apps server amongst other things. Can you imagine the bad press MS would/do get for doing that?

I guess the question should be how often is DB2 / SQL server released...

Oh and it's nice to see Oracle move to a monthly patch cycle... I need one for my Oracle Trousers 10G ;)

fantastic

Ram Kiran, July 05, 2006 - 11:55 pm UTC

this topic was really good, fetched me some good knowledge about gtt adn all...

"ORA-08103: object no longer exists" when using Transaction Based GTT's from VB.Net

Rohit, September 12, 2008 - 4:54 pm UTC

Hi Tom

i have developed a stoted procedure that uses Transaction Based GTT's

It all works nicely when i test in oracle environment (SQL*Plus)

But when this procedure is called from a VB.Net Module it raises ORA-08103: object no longer exists.

Any pointers ?
Tom Kyte
September 16, 2008 - 5:17 pm UTC

It is likely using XA or otherwise committing - as the VB.net guys how they deal transactionally with the database.

excellent article, but diffenence of session-specific and transaction specific

junwei, November 10, 2009 - 7:44 am UTC

excellent article,tom.
but one more puzzle: if in my java app, I have several business methods,each one does a oracle database query, what i want to know is whether each query will start a new seesion to oracle database? why i want to know this is because i want to use a session-specific temporary table to share data if it doesn't,does it feasible?
sorry for my poor english.


Tom Kyte
November 11, 2009 - 3:17 pm UTC

... what i want to know is whether each query will start a new seesion to oracle database?...

you tell me, you coded it.

If you grab a connection from a pool and then execute five queries on that connection and then give it back - all five queries will be in the same session.

If you grab a connection, execute a query, give the connection back and then grab a connection, execute a query and give the connection back - your queries could each run in DIFFERENT sessions.


You control this. You are in charge of it. When you grab a connection - it will be associated with a session, that is your session until you give the connection back to the pool, the next connection you grab might be a totally different session.

Temporary tables [ Inappropriate Solution ]

Suresh, April 06, 2010 - 6:29 am UTC

Tom,

You are giving an inappropriate solution for the question asked. If you create temp tables in MS SQL, for instance

SELECT * INTO #TMP FROM EMPLOYEE;

It doesn't create any physical tables in database. Its life time is procedures life time. Is there any such tables in oracle that i can make use of. These tables are required for holding dimentional datas during some report developments. I must be acle to create them on-the-fly and should not be available after that particular session.


Tom Kyte
April 12, 2010 - 7:06 pm UTC

Suresh,

you are in fact about as wrong as you can be.

Not about how it works in Microsoft, but how it should work in Oracle.

My answer totally stands, Oracle has ANSI standard global temporary tables, you create them ONCE and use them over and over - so you can use static sql in plsql. We do not do the "select * into #tmp" Microsoft form.


I must be acle to create them on-the-fly and should not be
available after that particular session.



You are wrong, you WILL create them once, they WILL persist, they WILL be part of your application. You can actually document them now, you can actually see what you are doing now.

You will discover that Oracle is different from Microsoft which is different from MySQL which is different from DB2 which is different from ... <and the list goes on>.....



So, you are wrong, the answer is completely appropriate, the sooner you realize that what you do in one database is not what you do in another the sooner you will be successful.

Temporary table usage

A reader, September 27, 2010 - 5:45 am UTC

Hi Tom,

I read some of these. I wonder what may be the use of session specific and transaction specific temporary tables. I heard that it can be used in some procedures. Can you please show me the same with examples(session and transaction specific temporary tables)?
Tom Kyte
September 27, 2010 - 12:12 pm UTC

read the original answer??? They were fully demonstrated.


create temp table in a block?

Luis, October 05, 2010 - 6:18 pm UTC

If temp table already exists, you may catch the exception when creating it.

(pseudo)
begin
create temp table
... ..
. . . .
exception
when other then raise some..
end

Am I right ???
Tom Kyte
October 06, 2010 - 4:46 am UTC

yes, you could, but you shouldn't.

for the simple reason that you shouldn't be creating in your code - since that would imply your code cannot direct reference it (since it would have to exist BEFORE your code was compiled!!! but if your code tries to create it - you have a chicken and egg problem - you've have to use 100% dynamic sql everywhere regarding that table but that would just be silly)


You create the table ONCE as part of the installation of your application.

table doesnot exists!!

Shubham Joshi, April 18, 2011 - 10:36 pm UTC

hi tom,

declare
type mycur is ref cursor;

l_tname varchar2(30) default 'temp_table_' || userenv('sessionid');
l_cursor mycur;
-- l_ename scott.emp.ename%type;
begin
execute immediate 'create global temporary table ' || l_tname ||
' on commit delete rows
as
select * from scott.emp where 1=0 ';

execute immediate 'insert into ' || l_tname || ' select * from scott.emp';

open l_cursor for 'select ename from ' || l_tname || ' order by ename';
/*
loop
fetch l_cursor
into l_ename;
exit when l_cursor%notfound;
dbms_output.put_line(l_ename);
end loop;*/

close l_cursor;
execute immediate 'drop table ' || l_tname;
end;

i tried to execute this code but its giving error table or table dosenot exist!!!!! at line number 8 please can you help

Regards,
Shubham
Tom Kyte
April 19, 2011 - 6:33 pm UTC

thanks goodness it did not work, that is an example of the worst sort of code on the planet. Here is how to code that:

for x in (select from scott.emp)
loop
....


in Oracle you DO NOT create tables on the fly, that is not the way we work (sql server is different from oracle, oracle is different from sql server, the way to do things in sql server is different from the way to do things in oracle, the way to do things in oracle is different from the way to do things in sql server)


i will NOT tell you how to make this code (above) work.

I will only, repeatedly, over and over again - tell you that it is WRONG.

Tell me what your goal is and I'll tell you how to do it in Oracle, but this is so wrong on so many levels.


DO NOT create tables on the fly, that is NOT the way to work in Oracle.

Narayanan, October 12, 2011 - 1:06 pm UTC

Interesting and Informative

temporary tables in Oracle

anthony, December 27, 2011 - 4:28 pm UTC

Great answer with definite precision.

Answer to Shabnam

Mathangi, February 06, 2012 - 10:13 am UTC

Hi Shabnam,

I know this is a pretty late response. I was just going over the thread. In your post, you are just trying to execute the pl/sql that Tom had provided earlier in his response. The error you get might be due to various reasons-

1) You have commented the following line : l_ename scott.emp.ename%type;
2) After commenting out that you are trying to create the temporary table with the data from scott.emp

You can either ensure that you uncomment the line or replace scott.emp with the table you have in your schema and use appropriate columns from the your table to test that piece of pl/sql.

Please ignore if you have already figured this out.

Thanks,
Mathangi

I misspelt you name Shubham

Mathangi, February 06, 2012 - 10:14 am UTC

Sorry

Oracle Jobs, Sessions and GTT

dlopez, April 18, 2012 - 9:19 am UTC

Hi Tom,
Wonderful information about GTT.

Can you please tell me what Jobs do in terms of Session?

I have a main procedure that populates a GTT defined with ON COMMIT PRESERVE ROWS with a list of IDs, then this procedure launches 11 more processes (1 SP per process). Each SP then updates and reads GTT.

Problem is that even the first SP appears not to be able to see data on GTT.

Thanks & Regards.

Tom Kyte
April 19, 2012 - 4:37 am UTC

global temporary table data is visible only in the session that put data in it. if you launch 11 other sessions, they will not be able to see it.

they do not work that way, if you want others to see it, you need to use a 'real' table.

Jess, October 05, 2016 - 10:46 pm UTC

Hi Tom,
We're fixing some code for a project where we don't have control over much. The stored procedure I am writing is executed by a 3rd party application. The procedure is structured like so:
if (input param = X), select query 1, else select query 2.

Both queries produce mostly the same set of columns. For reasons beyond me, the 3rd party application breaks when calling a proc like that--I am told it has to do with it building output paths. We tried aliasing output colums in the two queries to different things, but it doesn't work--only 1 result must be returned across all conditionals.

Having tried a few things, our choices look to be limited to:
- break it up into 2 stored procs (which seems simple, but there are tens of these with multiple if/elses, so breaking them up looks like a recipe for support nightmare);
- build from and where clauses in the conditionals and then execute as dynamic sql (which tends to be error-prone in maintenance and support, among oher things);
- merge all if/elses in the proc into 1 query with a bunch of outer joins and then manage the right value in select based on input param (which seems rather wasteful, as tables are large, as the outer join would need to pull up the data at least half of which is a throw-away);
- create a temporary table/ref cursor in-memory (we aren't allowed to create a new gtt, as the project doesn't own the system that has the data), have each if/else output into the in-memory temp table and return the result that way (which also seems like it would have unnecessary overhead).

The constraints may seem a bit silly, but with no control of systems, it is what it is. What would be your recommendation in this case? In-memory-in-procedure temp table equivalent? Outer joins? Dynamic sql?

Thank you as always!
October 06, 2016 - 1:31 am UTC

Can you provide a little more input ? Are you returning *data* or are you returning a ref cursor ?

Jess, October 06, 2016 - 7:24 am UTC

I have to return data for the 3rd party app to consume as "select A, B, C from (resultset)", otherwise it doesn't work.
Logic is complex, so I have a few refactored subqueries and a main select selecting from it. If I put it into a temp table, the last statement in the proc would be selecting from it...

The project DBA comes from SQL Server background, so is pushing for in-proc temp table, but since, as this thread discusses, that's not commonly done in Oracle, I don't know much about comparative performance or other implications of those vs. dynamic sql vs. extra sets of outer joins (though suspect the latter would be the worst).

Thank you!
Connor McDonald
October 06, 2016 - 1:50 pm UTC

Well, I dont see the issue then. If you have

if x=1 then
  open rc for select a,b,c from ...
elsif x=2 then
  open rc for select x1 a, x2 b, x3 c from ...




then the calling environment will *never know* that you are getting different tables, columns etc. It will *only* see a,b,c coming back

Jess, October 06, 2016 - 3:13 pm UTC

So ref cursor is the way to go over dynamic sql then? Good to know. Thanks as always! :)

Chris Saxon
October 07, 2016 - 3:12 am UTC

Glad we could help

Always wondered about SYS_CONTEXT

Duke Ganote, October 07, 2016 - 4:47 pm UTC

It's always surprised me that something like this doesn't work in this situation:

SQL> create table av as select owner, view_name from all_views;

Table created.

SQL> create table ao as select owner, object_name from all_objects;

Table created.

SQL> exec dbms_stats.gather_table_stats(user,'AV');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'AO');

PL/SQL procedure successfully completed.

SQL> exec DBMS_APPLICATION_INFO.set_action(action_name => 'AV');

PL/SQL procedure successfully completed.

  WITH D AS (
  SELECT COUNT(*) cnt FROM AV
  where SYS_CONTEXT ('userenv','action') = 'AV'
  ),
  E AS (
  SELECT COUNT(*) cnt FROM AO
  where SYS_CONTEXT ('userenv','action') = 'AO'
  )
  select * from d
  union all
  select * from e
SQL> /

         CNT
------------
       2,056
           0

Execution Plan
----------------------------------------------------------
Plan hash value: 3618171386

--------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |     2 |    26 |     8   (0)| 00:00:01 |
|   1 |  UNION-ALL                    |      |       |       |            |          |
|   2 |   VIEW                        |      |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE             |      |     1 |       |            |          |
|*  4 |     FILTER                    |      |       |       |            |          |
|   5 |      TABLE ACCESS STORAGE FULL| AV   |  2056 |       |     2   (0)| 00:00:01 |
|   6 |   VIEW                        |      |     1 |    13 |     6   (0)| 00:00:01 |
|   7 |    SORT AGGREGATE             |      |     1 |       |            |          |
|*  8 |     FILTER                    |      |       |       |            |          |
|   9 |      TABLE ACCESS STORAGE FULL| AO   | 74857 |       |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter(SYS_CONTEXT('userenv','action')='AV')
   8 - filter(SYS_CONTEXT('userenv','action')='AO')


I'm using 12c.
Connor McDonald
October 08, 2016 - 1:10 am UTC

What do you mean "doesnt work" ?

"doesn't work" -- hopefully clarified

Duke Ganote, October 10, 2016 - 4:05 pm UTC

Sorry, that was an unclear comment of mine. The query functions, but when I examine the execution plan, I don't see any "pruning". Looks like both tables gets queried, even though "I think" the optimizer has enough information to exclude one altogether.
Chris Saxon
October 11, 2016 - 12:42 am UTC

There is indeed pruning

See the FILTER lines, how they sit "above" the TABLE ACCESS FULL ones. So we will evaluate the filter and only proceed if its true.

Hope this helps.

Connor

Shazam!

Duke Ganote, October 11, 2016 - 4:56 pm UTC

When I try it with some rather large tables in production, it does seem to function as I'd expect. Thanks!

SQL> exec DBMS_APPLICATION_INFO.set_action(action_name => 'AV');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.11
SQL> /

         CNT ACTING
------------ --------------------
 524,384,424 AV
           0 AV

Elapsed: 00:00:14.95
SQL> exec DBMS_APPLICATION_INFO.set_action(action_name => 'AO');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.26
SQL> /

         CNT ACTING
------------ --------------------
           0 AO
############ AO

Elapsed: 00:02:31.04


Thanks!

The privilege to use gather_plan_statistics

Duke Ganote, October 12, 2016 - 6:55 pm UTC

I just got "better privileges" an hour or so ago. So I can use /*+ gather_plan_statistics */. Now that I can see the actuals as well as estimates, it's blindingly obvious that the optimizer is, well, optimizing.

1. I started with nothing "in action", and it's appropriately fast because both tables were ignored.
2. I set action = 'AO' and only one table was queried.
3. I set action = 'AV' and it queried the other table.

Reconfirms not rely exclusively on SET AUTOT ON... if you can get the privileges to use /* gather_plan_statistics */, which I learned of from the late Karen Morton's paper "Managing Statistics for Optimal Query Performance"
http://method-r.com/downloads/cat_view/38-papers-and-articles

My (condensed) results follow:
[the query is here...]

CNT 'A
--- --
  0 AV
  0 AO

Elapsed: 00:00:00.12

-----------------------------------------------------------------------------------------
| Id  | Operation                        | Name | Starts | E-Rows | A-Rows |   A-Time   |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |      |      1 |        |      2 |00:00:00.01 |
|   1 |  VIEW                            |      |      1 |      2 |      2 |00:00:00.01 |
|   2 |   UNION-ALL                      |      |      1 |        |      2 |00:00:00.01 |
|   3 |    SORT AGGREGATE                |      |      1 |      1 |      1 |00:00:00.01 |
|*  4 |     FILTER                       |      |      1 |        |      0 |00:00:00.01 |
|   5 |      INDEX STORAGE FAST FULL SCAN| AV   |      0 |   2503M|      0 |00:00:00.01 |
|   6 |    SORT AGGREGATE                |      |      1 |      1 |      1 |00:00:00.01 |
|*  7 |     FILTER                       |      |      1 |        |      0 |00:00:00.01 |
|   8 |      TABLE ACCESS STORAGE FULL   | AO   |      0 |   4370M|      0 |00:00:00.01 |
-----------------------------------------------------------------------------------------

exec DBMS_APPLICATION_INFO.set_action(action_name => 'AV');
[the query is here...]

         CNT 'A
------------ --
############ AV
           0 AO

Elapsed: 00:05:36.46

-----------------------------------------------------------------------------------------
| Id  | Operation                        | Name | Starts | E-Rows | A-Rows |   A-Time   |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |      |      1 |        |      2 |00:05:36.39 |
|   1 |  VIEW                            |      |      1 |      2 |      2 |00:05:36.39 |
|   2 |   UNION-ALL                      |      |      1 |        |      2 |00:05:36.39 |
|   3 |    SORT AGGREGATE                |      |      1 |      1 |      1 |00:05:36.39 |
|*  4 |     FILTER                       |      |      1 |        |   2622M|00:54:12.33 |
|   5 |      INDEX STORAGE FAST FULL SCAN| AV   |      1 |   2503M|   2622M|00:18:30.95 |
|   6 |    SORT AGGREGATE                |      |      1 |      1 |      1 |00:00:00.01 |
|*  7 |     FILTER                       |      |      1 |        |      0 |00:00:00.01 |
|   8 |      TABLE ACCESS STORAGE FULL   | AO   |      0 |   4370M|      0 |00:00:00.01 |
-----------------------------------------------------------------------------------------

exec DBMS_APPLICATION_INFO.set_action(action_name => 'AO');
[the query is here...]

         CNT 'A
------------ --
           0 AV
############ AO
Elapsed: 00:10:15.69

-----------------------------------------------------------------------------------------
| Id  | Operation                        | Name | Starts | E-Rows | A-Rows |   A-Time   |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |      |      1 |        |      2 |00:00:00.01 |
|   1 |  VIEW                            |      |      1 |      2 |      2 |00:00:00.01 |
|   2 |   UNION-ALL                      |      |      1 |        |      2 |00:00:00.01 |
|   3 |    SORT AGGREGATE                |      |      1 |      1 |      1 |00:00:00.01 |
|*  4 |     FILTER                       |      |      1 |        |      0 |00:00:00.01 |
|   5 |      INDEX STORAGE FAST FULL SCAN| AV   |      0 |   2503M|      0 |00:00:00.01 |
|   6 |    SORT AGGREGATE                |      |      1 |      1 |      1 |00:10:15.61 |
|*  7 |     FILTER                       |      |      1 |        |   4608M|01:34:55.20 |
|   8 |      TABLE ACCESS STORAGE FULL   | AO   |      1 |   4370M|   4608M|00:32:08.58 |
-----------------------------------------------------------------------------------------

Chris Saxon
October 13, 2016 - 9:08 am UTC

nice stuff

Seems like it's user specific if Oracle temp tables are useful

rolo3181, May 15, 2019 - 4:16 pm UTC

I read the comments, and all the talk about how to use Oracle temp tables is nonsense. I come from SQL Server, and on the fly I write heavy code to mine, join, and transform data. I don't know what I am going to write until I write it. SQL Server allows me to use #temp tables when I want, and then drop them a the end. I am new to Oracle, but I have yet to read anyone tell me how to do create my #temp tables that will give me the same flexibility, speed, and ease of use like in SQL Server. What am I missing? I could have 15 #temp tables to put together and aggregate data in SQL Server, then I put the results in a hard table. How does Oracle do that?
Chris Saxon
May 16, 2019 - 9:14 am UTC

Traditionally how Oracle Database deals with temporary tables is fundamentally different to SQL Server.

But starting in 18c you can create on-the-fly temp tables with private temporary tables:

https://blogs.oracle.com/sql/how-to-create-alter-and-drop-tables-in-sql#create-temporary

In the meantime, to explain an equivalent in Oracle Database we'll need an example of what you're doing in SQL Server.

I don't know what I am going to write until I write it

Not even the column names?

Chuck Jolley, May 16, 2019 - 2:46 pm UTC

Insert from select using CTEs is how I would normally approach problems like that.
Chris Saxon
May 16, 2019 - 3:24 pm UTC

Yeah, that's my first instinct too.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library