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

Jonathan Lewis wrote:

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

Thank you Jonathan, Howard, and everyone else that contributed. This has been very valuable.

Daniel Morgan Received on Sun Mar 02 2003 - 11:46:46 CST