Is the exactmonth table indexed/index organized? That could be one problem.
Does it have any foreign keys?
Ashish
"Galen Boyer" <galendboyer_at_yahoo.com> wrote in message
news:u6639e3jx.fsf_at_rcn.com...
> 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.
Received on Wed Apr 03 2002 - 09:28:11 CST
