Re: why CTAS faster than Insert select?

"linda" <linglipeng_at_yahoo.com> wrote in message news:1112121974.648540.12120_at_l41g2000cwc.googlegroups.com...
> Hi,
>
> I've been testing the performance between Create Table As Select (CTAS)
> vs Insert Select for large volume of data (40 million records or so).
> Both in nologging mode, parallel, etc. It consistently shows that CTAS
> faster than Insert Select. My understanding is that, if you parallel
> both create/insert process and select process, then theoratically they
> should perform about the same (same amount of work, same number of
> parallel processes, minimum redo and undo...). Could anybody explain
> why this is the case?
>
> Thanks,
> Linda
>

Can you supply a few more details:

    Which version of Oracle
    What are typical times for the events.     How much redo is generated for the two cases     How much time is recorded in v$session_event     How much CPU time is recorded in v$sesstat     Framework of the two tests

        (but don't bother to show the full text of the
        select statement)

Can you check:

    For the CTAS you are doing:

        create table XXX nologging as select ...

    For the insert you are doing:

        create table XXX ( .. ) nologging  -- or similar
        insert /*+ append */ into XXX select ...

The insert is not 'nologging' unless the table has been declared as nologging. The append hint only stops the generation of undo.

Regards

Jonathan Lewis

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

http://www.jlcomp.demon.co.uk/seminar.html Public Appearances - schedule updated March 9th 2005 Received on Wed Mar 30 2005 - 16:24:53 CST