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

To most people, functional decomposition goes hand in hand with "procedural" - which is contrary to the set-wise concept of SQL. This is why pl/sql is usually (a) easier for most people to understand and (b) the second best way of handling data manipulation in Oracle.

Functional decomposition has been though of in pl/sql - that's what blocks and procedures are for.

Functional decomposition has been thought of in SQL - that's what subqueries, set operators and (in-line) view are for.

Your requirement looks as if it could be handled with a simple UNION ALL of several sets of data, with any required grouping, filtering and ordering outside the UNION ALL. BTW - this doesn't mean that I disapprove of global temporary tables, or using pl/sql in the way you describe: but VERY selectively, and only with a relevant cost/risk/benefit justification.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____UK_______April 8th
____UK_______April 22nd

____USA_(FL)_May 2nd


Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


<plm_at_gmx.li> wrote in message
news:1046595037.69209.0_at_damia.uk.clara.net...


>

> 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.

>