Home » SQL & PL/SQL » SQL & PL/SQL » Should I use global temporary table? (Oracle 10g)
Should I use global temporary table? [message #403905] Tue, 19 May 2009 08:39 Go to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Hello,

I have some PL/SQL code that:
1) reads really huge table
2) aggregate in some way the data that were read
3) inserts the result into an output table
And the same thing is executed several times.
After all the inserts are complete:
n-1) sorting is performed and one column is filled with the order position.
n) commit
Example:
INSERT INTO t_meta_dst SELECT col_1 FROM t_src;
INSERT INTO t_meta_dst SELECT col_1+1 FROM t_src;
INSERT INTO t_meta_dst SELECT col_1*5 FROM t_src;
INSERT INTO t_final_dst SELECT * FROM t_meta_data ORDER BY col_1;


My idea is to:
1) read huge table into GTT
2) aggregate using GTT
3) Insert results into another GTT
Repeat steps (2) and (3) required number of times
4) Insert sorted data into final table
5) commit (ON COMMIT DELETE ROWS)

However I have found some problems:
- Aggregation queries are dynamically generated
- I do not know how many columns should be in the GTT or final table before the PL/SQL code is executed
- Sometime a WHERE part could appear in the aggregation query (usually col_n>=:x) and index scan could be probably used


Similar result could be obtained by reading the data into memory - but then I could run out of it.

Thus I'm asking - should I go into GTT or use a different solution?
Re: Should I use global temporary table? [message #403907 is a reply to message #403905] Tue, 19 May 2009 08:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do it in a single SQL statement.

Regards
Michel
Re: Should I use global temporary table? [message #403915 is a reply to message #403907] Tue, 19 May 2009 09:22 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Michel,
Currently this is done in a single statement. The problem is that we got to the limit: 32767 characters per query.
Re: Should I use global temporary table? [message #403918 is a reply to message #403915] Tue, 19 May 2009 09:29 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
wakula wrote on Tue, 19 May 2009 07:22
Michel,
Currently this is done in a single statement. The problem is that we got to the limit: 32767 characters per query.


use shorter column names
Re: Should I use global temporary table? [message #403921 is a reply to message #403918] Tue, 19 May 2009 09:34 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
BlackSwan wrote on Tue, 19 May 2009 16:29
use shorter column names


The query it already "cleaned out". All the comments are removed, all un-needed spaces are removed, all CRLF are converted into spaces, columns are aliased from
TREAT(DataColumn AS DataType).FullColumnName
into columns like c1.
Final table has about 70 columns.
Re: Should I use global temporary table? [message #403922 is a reply to message #403905] Tue, 19 May 2009 09:40 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
>- I do not know how many columns should be in the GTT or final table before the PL/SQL code is executed
Ready, Fire, Aim!
My initial reaction is that I doubt Third Normal Form is being followed.
You are not the 1st person doing data data aggregation & others do not find themselves in the same hole as you.
Re: Should I use global temporary table? [message #403925 is a reply to message #403905] Tue, 19 May 2009 09:51 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Output table has columns like:
KeyColumn1,KeyColumn2,KeyColumn3,Day1,Day2,Day3,...DayN

The data in columns "KeyColumn" are keys - thus must be within the table.
The data in columns "Day" are values and depends on the key.
The table is displayed in some external application as it is.
Re: Should I use global temporary table? [message #403930 is a reply to message #403905] Tue, 19 May 2009 10:18 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
>KeyColumn1,KeyColumn2,KeyColumn3,Day1,Day2,Day3,...DayN
As I suspected, not designed to Third Normal Form.
Almost as bad a the "flexible design" of name/value pair table.
Re: Should I use global temporary table? [message #403931 is a reply to message #403915] Tue, 19 May 2009 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
wakula wrote on Tue, 19 May 2009 16:22
Michel,
Currently this is done in a single statement. The problem is that we got to the limit: 32767 characters per query.

There is no such limit in SQL.

Regards
Michel
Re: Should I use global temporary table? [message #403958 is a reply to message #403931] Tue, 19 May 2009 18:05 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Michel Cadot wrote on Tue, 19 May 2009 17:20
wakula wrote on Tue, 19 May 2009 16:22
Michel,
Currently this is done in a single statement. The problem is that we got to the limit: 32767 characters per query.

There is no such limit in SQL.


Michel,
Could you, please, tell me how can I execute very long queries?
I'm currently using PL/SQL to create a long query that is kept in a VARCHAR2 variable (which is limited to 32767 characters). I guess that I would need to use CLOB.
Currently I'm using DBMS_SQL, and it is not supporting CLOBs. Execute immediate?

[Updated on: Tue, 19 May 2009 18:06]

Report message to a moderator

Re: Should I use global temporary table? [message #403970 is a reply to message #403958] Tue, 19 May 2009 22:48 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
32767 is the max varchar2 variable size in plsql.

dbms_sql won't use clob so I believe the largest query you can execute via dbms_sql is 32767.

maybe someone will show us different, I would like to see it.

Your best bet is to do as was suggested; take a look at the query again and try to shorten it.

Quote:
Try shortening columns names more: KeyColumn1 => k1, Day1 => d1.

Quote:
Try using WITH clause to create reusable pieces inside the query.

Quote:
Try constructing views on the real tables that contain only those columns you want, then use select * from <view> rather than select c1,c2,c3... from <table> when you dynamically construct your big query. Do not forget to rename columns in these helper views to really short columns names as defined above.


I would also suggest that GTT with hugh number of rows is a bad idea. GTTs work best when their contents stay in memory and thus you avoid lots of disk writes. If you exceed some memory limit that benefit is lost so why bother. You even can have other issues.

Good luck, Kevin
Re: Should I use global temporary table? [message #404001 is a reply to message #403970] Wed, 20 May 2009 00:54 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
DBMS_SQL.PARSE also has a variant that accepts a table of varchar2(256) strings. I doubt if that one too is restricted to 32767 chars in total.
Re: Should I use global temporary table? [message #404002 is a reply to message #403958] Wed, 20 May 2009 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
dbms_sql can take an array of strings.
Quote:
To parse SQL statements larger than 32 KB, DBMS_SQL makes use of PL/SQL tables to pass a table of strings to the PARSE procedure. These strings are concatenated and then passed on to the Oracle server.

Regards
Michel
icon14.gif  Re: Should I use global temporary table? [message #404019 is a reply to message #403905] Wed, 20 May 2009 02:03 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Since DBMS_SQL indeed has a version that accepts array of varchar's - I guess that the problem is solved.
However I would need to modify the code so that it uses DBMS_SQL (Some parts of the code might use EXECUTE IMMEDIATE).
I believe that in Oracle 11 query size should not be a problem.

Regards
Re: Should I use global temporary table? [message #404028 is a reply to message #404019] Wed, 20 May 2009 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, if you can upgrade in 11g you will not have this problem as both execute immediate and dbms_sql allows CLOB sql statement.

Regards
Michel
Re: Should I use global temporary table? [message #404109 is a reply to message #403958] Wed, 20 May 2009 07:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
Currently this is done in a single statement. The problem is that we got to the limit: 32767 characters per query.


It turns out that the answer is In the documentation
This took me about 90 seconds to find.
DBMS_SQL supports passing a statement in in a table of Varchar2(256) for an unlimited query size.

[Good - the OP has actually found this themselves]

[Updated on: Wed, 20 May 2009 08:00]

Report message to a moderator

Re: Should I use global temporary table? [message #404257 is a reply to message #403905] Thu, 21 May 2009 02:31 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Thanks for your replies.
Just to finish this topic:
DBMS_SQL has about 3 forms in Oracle 10g:
- First one that takes a single VARCHAR2 (it is limited to 32767 characters)
- Second one takes as input VARCHAR2S which is array of VARCHAR2(256)
- Third one takes as input VARCHAR2A which is array of VARCHAR2(32767)
The forms with arrays are just concatenating elements of the array. You can set whether CRLF should be inserted between the elements.
Example when you need CRFL: "SELECT * FROM","my_table"
Example when you don't want CRLF: "SELEC","T * FROM my_t","able"

When parsing SQL you should not add semicolon (Wink at the end of query.
When parsing PL/SQL you should add semicolon (Wink at the end of query.

Although the definitions of VARCHAR2A and VARCHAR2S are given in the manual - you should use DBMS_SQL.VARCHAR2A and DBMS_SQL.VARCHAR2S.
Reason 1: Your DBA could update DBMS_SQL synonym so that it points to something different than SYS.DBMS_SQL. In that case the VARCHAR2A and VARCHAR2S might change as well.
Reason 2: You should not narrow down the flexibility of your code by hard-coding the types in it.

As in the Oracle manual:
- Some exceptions could be thrown by DBMS_SQL (and should be handled by you)
- Usually EXECUTE IMMEDIATE is better for you - read manual for pros and cons of both EXECUTE IMMEDIATE and DBMS_SQL
- In DBMS_SQL you could bind & define variables by assigning a name to them
- EXECUTE IMMEDIATE is usually faster
- In Oracle 11 the query length should not be a problem
Re: Should I use global temporary table? [message #404269 is a reply to message #404257] Thu, 21 May 2009 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for this wrap-up.
The only point I see is that you should mention a link to the relevant part of the documentation (that you likely read) in the last pararagh.

Regards
Michel
Re: Should I use global temporary table? [message #404275 is a reply to message #404269] Thu, 21 May 2009 03:01 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Michel Cadot wrote on Thu, 21 May 2009 09:53
(...)you should mention a link to the relevant part of the documentation (that you likely read) in the last pararagh.


I have updated OraFAQ wiki. Additional resources are Oracle documentation for Oracle 11.1g and Oracle documentation for Oracle 10.2g
Re: Should I use global temporary table? [message #404413 is a reply to message #404275] Thu, 21 May 2009 13:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Great!
We really appreciate the extensive follow-up!

[Updated on: Thu, 21 May 2009 13:40]

Report message to a moderator

Re: Should I use global temporary table? [message #404415 is a reply to message #404413] Thu, 21 May 2009 13:49 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Frank wrote on Thu, 21 May 2009 20:40
Great!
We really appreciate the extensive follow-up!

Indeed!

Regards
Michel

Previous Topic: Fifo , Lifo and Average Method Inventory
Next Topic: Totalizing a column (merged)
Goto Forum:
  


Current Time: Mon Dec 05 23:53:18 CST 2016

Total time taken to generate the page: 0.09317 seconds