Re: Partitioned Table Problem

Hi,
is the index on the table equi-partitioned as the table?

Regards
matthias.penzlin_at_sdm.de

"Dymynix" <no_at_anon> wrote in message news:<1021546854.26780.0.nnrp-14.c2d92da9_at_news.demon.co.uk>...
> I have a table called transactions with 8 million rows of data.
>
> Currently on our customer site this table is not partitioned and is a cause
> of performance problems with several reports.
>
> For example one report which takes a start and end date takes 3min 30sec to
> execute with the parameters 15-APR-2002 and 22-APR-2002.
>
> I have decided to partition this table up into monthly partitions and each
> partition has approximately 1 million rows of data. e.g. JAN-2002, FEB-2002
> etc
>
> The report with the same parameters 15-APR-2002 and 22-APR-2002 now takes
> 1min to execute as partition elimination is taking place. This is good.
>
> Now the customer wants 3 years worth of data to be maintained so I have
> created a test database with 30 million rows of data in the transactions
> table spanning 3 years worth of data. The transactions table is again
> partitioned by month.
>
> The report this time takes 2min 45sec to execute.
>
> I don't understand why with this is so, the transactions table contains 30
> million transactions instead of 8 million transactions but the parameters to
> the report are 15-APR-2002 and 22-APR-2002 so Oracle should eliminate all
> partitions except the APR-2002 partition.
>
> Thus in the database with 8 million transactions and the database with 30
> million transactions Oracle should only look at the APR-2002 partition which
> has 1 million transactions in both cases. There should be no difference in
> the report time.
>
> Can anyone shed any light of this?
>
> Thanks
Received on Fri May 17 2002 - 03:33:04 CDT