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: create temporary table privs

Re: create temporary table privs

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 12 Jun 2002 07:48:14 +0100
Message-ID: <1023865065.19477.1.nnrp-01.9e984b29@news.demon.co.uk>

It's best to avoid being too dogmatic.
You never know what Oracle is going to
do behind the scenes that makes the
'official' story line look ridiculous.

Have you ever used the GROUPING SET
functionality, or subquery-factoring ?

If you do, you will find that an end-user SELECT can turn into recursive SQL like:

    create global temporary table SYS49038947665     storage (in_memory_metadata ....)

    insert into SYS49038947665 ....

    select from

        SYS49038947665
        SYS49038947664

    where ...

There are always options where the 'ideal' or 'pure' strategy has to give way to the more efficient strategy - although the trade-off between complexity/risk and performance also has to be considered, and that trade-off is often the better reason for discarding the use of any form of 'temporary storage'.

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

Next Seminars
        UK            June / July
        Australia      July / August
http://www.jlcomp.demon.co.uk/seminar.html

Daniel Morgan wrote in message <3D067414.8F4450E1_at_exesolutions.com>...

>
>Dynamic temporary tables are an idea born of problems and are best not
>used for anything in Oracle. And in Oracle there is really nothing that
>is a true temporary table though the global temporary tables come close
>they are truly permanent tables.
>
Received on Wed Jun 12 2002 - 01:48:14 CDT

Original text of this message

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