Re: Why the difference?

In article <39206c8b.0204021314.6b8a94b4_at_posting.google.com>, jonwaterhouse_at_mail.gov.nf.ca says...
>
>I have a file with about 800,000 records from which I need to extract
>one month's worth of data (about 30,000) records.
>
>insert column_names into extractmonth
>(select column_names from fullfile where year = 1999 and month = 4);
>
>runs at about 2.5 hours.
>
>create table extractmonth as
>select column_names from fullfile where year = 1999 and month = 4;
>
>runs at about 2.5 seconds.
>
>There is an index on year and month on full file.
>
>Any ideas on what causes the difference?

what size are your logs? are you in archivelog mode?

insert into select -- logged.
CTAS -- not logged in noarchivelog mode.

look in your alert for "checkpoint not complete, cannot allocate new log"

is the insert inserting into an indexed table -- if so, the index maintainence will add tons too.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp