Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Global (and local) Temporary Tables & PL/SQL

Re: Global (and local) Temporary Tables & PL/SQL

From: Gints Plivna <>
Date: Wed, 4 Apr 2007 10:21:30 +0300
Message-ID: <>

> table, it won't compile as the table is created and populated in the
> same step and thus does not exist when trying to compile the
> procedure.

So from thuis text I understood that table is being created at runtime. I can only add my voice to the Tim's mail that GTT are not predicted to use in the same way as temporary tables are for example used in MS SQL Server. I won't explain much here why I don't like dynamic SQL - the usual performance and code maintainability problems apply.

> The facts known at this time are:
> 1. This table may be somewhat large (several hundred thousand rows
> with several VARCHAR 255 fields) at first, but later will be much more
> reasonable.

We have succesfully used both "explicit" GTTs (created manually) and "implicit" GTTs (created by with clause of Selects). The only problem I've encountered is quite obvious - if we create GTT using with clause for select and later it use in some nested loops operation in the select body, then if the GTT is big full scanning of it many times becomes quite a pain :) Of course full scan of GTT some fixed number of times (not dependant from number of rows) is absolutely normal.

> 2. Many users will be using this table at the same time with differing
> data and processing other data based on what they do with the data in
> these temp tables.

So this is somewhat contradictionary that you wrote before. If you you are creating this temp table on the fly then it is very doubtful that many users can use it - it is hard to imagine a scenario when you on the fly create this table and all users just like after command starts to use it and when it is dropped all of them stops to use it. Obviously these will be several tables and each user will have his own table.
Coincidentally just about a week ago I reviewed a code done by a developer (obviously with MS background), here is what I got: 1) he created the GTT called T2 but later in procedure used table called T1. So a wonderful example how it is hard to debug dynamic sql and how he obviously had created table T1 manually - and what is more important he succesfully used it! The create and drop scenario was absolutely redundant.
2) it was much harder for me to go through the code because it simply was dynamic sql.
3) if you create them on the fly depending on your user management (either Oracle or application server users) you have to think how to differentiate the names of them so that they are unique for a user or not. And going further what if the same user connects more than once in the same time? You need to differentiate it based even on some session info or using some unique identifier. I personally wouldn't like to think about such problems knowing I could easily avoid them just creating one table. Also if your session dies you have in next procedure run to check whether the table already is or isn't. 4) ddl on the fly will commit your transaction. It might be relevant or might not be relevant to your app, but one has to remember that.

> The questions are:
> 2. Is there a way to "force" PL/SQL to "trust me, the table will be
> there" to get the proc to compile.

Just precreating them if you haven't too much very important reasons not to do that, for example each time the structure of it may be different and is not known in advance.

> 3. What else should I be thinking about that I am not (since I just
> got blind-sided with this).

Generally in terms of redo inserts are very very cheap for GTTs, updates are much cheaper than for permanent tables, but deletes are almost in the same level.

Gints Plivna

Received on Wed Apr 04 2007 - 02:21:30 CDT

Original text of this message