Advice: Global Temporary Tables, Collections ... ?
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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.
@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.
>> 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.
No, a session starts once you connect to oracle. Throughout the execution of the code you are connected to the same session.
ASKER
@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?
">> 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?
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.
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.
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".
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".
ASKER
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.
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.
ASKER
Thanks :)
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.