Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Global Temp Tables - lots of redo in 9i, why? how to fix?

Re: Global Temp Tables - lots of redo in 9i, why? how to fix?

From: <plm_at_gmx.li>
Date: 02 Mar 2003 09:50:37 +0100
Message-Id: <1046595037.69209.0@damia.uk.clara.net>


DA Morgan <damorgan_at_exesolutions.com> writes:

> I don't think there is anything to fix. And I highly doubt the
> impact is significant enough to matter much to anyone.
>
> What I'd like to know is what is anyone doing that requires GTTs
> anyway. I've been coding in Oracle for more than a decade and have
> yet to "need" one.

They are good to break down large and complex sql queries into smaller ones, and/or to combine smaller queries like modules into larger ones.

Since you cannot use cursors as subqueries in other cursors, I have never seen another way for functional decomposition and reuse of underlying smaller queries.

For me functional decomposition (no matter in what computer language) is a basic and essential way to approach complex problems. It is incomprehensible that SQL and PL/SQL never thought of this. At least with temporary tables you can.

Also it is useful for reporting applications: suppose you have a framework that can automatically display a resultset (cursor) in some table or chart, but some results that you want to display stem from different (and differently structured) tables etc. You can have the client framework call some pl/sql function that gathers data from several tables/sql-queries, puts it all together in a temporary table. Then the last statement opens a 'select * from temp_talbe' cursor and returns that to the caller.

I admit that the use of GTT in these cases is clumsy and a workaround. If anyone has any better ideas, please let me know :)

-- 
Peter Mutsaers, Dübendorf, Switzerland.
Received on Sun Mar 02 2003 - 02:50:37 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US