Re: Why the difference?

On 2 Apr 2002, jonwaterhouse_at_mail.gov.nf.ca wrote:
> 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.

Whew, even a correlated update on 800000 rows could probably perform better than 2.5 hours.

This can't be a logged operation problem.

How does:

    create table extractmonth as
    select column_names from fullfile where year = 1999 and month = 4;

    create table t1 as
    select column_names from extractmonth where 1=0;

    insert into t1
    (select * from extractmonth);

perform?

> create table extractmonth as
> select column_names from fullfile where year = 1999 and month = 4;
>
> runs at about 2.5 seconds.

Now, this sounds reasonable.

> There is an index on year and month on full file.

What does explain plan actually give you for each query?

> Any ideas on what causes the difference?

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.