Advice: Global Temporary Tables, Collections ... ?

Posted on 2007-12-07
Medium Priority
Last Modified: 2013-12-07

I need a little bit of advice.  Let me start by saying that I am fairly new to SQL and PL/SQL, so dont be too harsh ;)

I am going to provide a little bit of background here in case my approach could be improved on this.
The situation is that I have an app that allows users to create Processes as a sequence of steps from Start->Finish.  There may be dozens of steps with multiple paths between the start and finish steps of the process.  Each step has an estimated duration in # of days, so some Processes may take 10, 20 or 60 days to complete etc depending on the number of steps, their relative durations and the available paths between the first and last steps.

My objective has been to create a report to show the maximum delay from any step in the process to the finish step.

To do this, I have created a PL/SQL procedure that accepts three parameters  Process ID, Start Step ID and Finish Step ID.

The procedure is recursive  it starts with the Finish Step of the given process and progresses towards the Start step, calculating the maximum delay for all steps along the way.

Currently I use five DB tables (Oracle 10g R2).

Table 1:          End Result (three columns of Numbers)
Table2  5:      Temporary tables (all have between 3 and 6 columns of numbers)

This Procedure currently works fine for 1 process at a time.

However the report may be called concurrently by several users which would lead to inconsistent results.  Furthermore, each time I call this procedure I first truncate the temporary tables.

So this is where I need the advice&

I have been trying to read up on several possibilities including global temporary tables, collections, records and objects etc. and now that my mind feels saturated with this maze of information I would really appreciate a pointer in the right direction.

What makes it difficult for me is that the procedure is recursive, so any structure (GTT, array) needs to be accessible on each call.

Any (constructive) advice would be most welcome.

If possible, please give me some examples of the structure and syntax or point me to a URL.
Question by:cgosney42
  • 4
  • 2
  • 2
  • +1
LVL 36

Expert Comment

ID: 20427959
I think you are on the right track with the global temporary table.

As what you are doing is recursive, it is all within the same session.  Therefore, the global temporary tables should be visible to each call.  If your procedures do commits, they you will want to be sure to set on commint preserve rows on the tables.
LVL 36

Accepted Solution

Mark Geerlings earned 375 total points
ID: 20427960
I think global temporary tables will work very well for this.  (But I should admit, I am not a master of arrays or collections.)  GTTs will handle the multiple, concurrent users automatically with no problems.  Also the syntax for working with GTTs is exactly the same as the SQL syntax for standard tables.  You can: select, insert, update, delete, use cursors in PL\SQL, etc. with GTTs.  The main difference: most performance overhead is avoided.  You just have to remember that GTTs can never share data across multiple sessions (but I think you want that functionality, so multiple, concurrent users don't interfere with each other) and the contents of the GTTs will be cleared automatically either: when the session does a commit (this is the default) or when the session ends (if you add the clause "on committ preserve rows") when you create the global temporary tables.  One other note: when you create a global temporary table you *DO NOT* specify a tablespace or any storage parameters.
LVL 27

Expert Comment

ID: 20428318
You say any structure needs to be recursive; do you mean to say that every recursive call of your procedure has to have a "new" "structure". I.e. you do not want to share the same structure in the recursion?
If that is the case; GTT cannot be used here. Because the data within the GTT is valid until the end of the session.(Provided it is created with preserve rows on commit option).

You have to use a locally declared array to process the intermediate data. DONT use a global array declared in a package.

>> some examples of the structure and syntax
To provide you an example, we need at lease a pseudo code of what you are doing in the procedure.
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!


Author Comment

ID: 20428727
Thanks for your help guys.
@sujith80 - I think you misread what I wrote: "What makes it difficult for me is that the procedure is recursive, so any structure (GTT, array) needs to be accessible on each call."

What I meant was that the since the data of all five current tables needs to be available to each call of the procedure, it can't be declared within the procedure (local).

So if GTTs are the correct approach I would like to clarify something.
My report is a SQL query of the current resultant table (Table 1).

If I implement GTTs, will each initial call to the procedure from my SQL Code have its own memory space?  i.e. each time my SQL code calls PROC(PROC_ID, START_ID, FINISH_ID) with new values, is this regarded as a new session and thus all 5 tables will be effectively empty?

Futhermore, since PROC is recursive, is each recursive call within PROC regarded as the same session?

I hope this makes sense.  If not, I can give some pseudo code.
LVL 27

Expert Comment

ID: 20429119
>> this regarded as a new session
No, a session starts once you connect to oracle. Throughout the execution of the code you are connected to the same session.

Author Comment

ID: 20429292
">> this regarded as a new session
No, a session starts once you connect to oracle. Throughout the execution of the code you are connected to the same session."

Now I am confused.
My report is an SQL query which calls PROC.  PROC continues to call itself for all steps in the process, finally populates Table1 and control returns to the SQL query to complete the report.

User 1 and User 2 on my app may run this report at the same time for different processes.
or User 1 may run the report twice for different processes.

If I replace my current tables with GTT's and user 1 initiates a report, does start a session?  Does this finish when the SQL query is finished and the report is generated?
Or if User2 runs the report will the data be inconsistent?

If they share the same GTT, then this is not suitable for my needs.  Do you have a better suggestion?
LVL 36

Expert Comment

ID: 20429568
A temporary table is a distinct table for each session.  The name of the table is the same, however there is a separate copy of the table for each session.

In the case of user1 vs user2, then there will not be contention.  If user1 is connected twice and runs it from different sessions then again, there is not contention.

Within the process that populates the table, you will want to do a delete on the temporary table before doing anything.  This will clear the table if the report is run more than once in the same session.  If I recall correctly a truncate will not work if the table has not been used yet in the session, but the delete will.
LVL 36

Expert Comment

by:Mark Geerlings
ID: 20429658
Two different Oracle users will definitely *NOT* have any contention for data in global temporary tables.  Oracle will allow each session to see *ONLY* its own copy of the global temporary tables.

I agree with johnsone's recommendation that the first step of your procedure should be a delete of the temporary table(s) used in the procedure, especially if they were created with "on commit preserve rows".

Author Comment

ID: 20442856
I ended up using GTT's for the Temporary tables (2-5) explained in the question.
I needed to use static table for CP since the data in a GTT table is lost when the procedure finishes.  I added a column to CPP to mark the steps unique to each process per report, so that they could be deleted at the start of each call.  This also ensured that other data would not be corrupted.

Thanks again for all your help with this.

Author Closing Comment

ID: 31413379
Thanks :)

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

568 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question