Solved

Advice: Global Temporary Tables, Collections ... ?

Posted on 2007-12-07
10
1,695 Views
Last Modified: 2013-12-07
Hello,

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.
0
Comment
Question by:cgosney42
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 34

Expert Comment

by:johnsone
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.
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 125 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.
0
 
LVL 27

Expert Comment

by:sujith80
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.
0
 

Author Comment

by:cgosney42
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.
0
 
LVL 27

Expert Comment

by:sujith80
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.
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:cgosney42
ID: 20429292
@sujith80
">> 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?
0
 
LVL 34

Expert Comment

by:johnsone
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.
0
 
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".
0
 

Author Comment

by:cgosney42
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.
0
 

Author Closing Comment

by:cgosney42
ID: 31413379
Thanks :)
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

867 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now