Partitioned Table Problem

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 Thu May 16 2002 - 06:03:01 CDT