Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
LVL 35

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 35

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.
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf


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 35

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 35

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

705 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