Oracle Database Discussions

We are seeing slower response times on SQL that run against a compressed table.

There are 3 tables in the SQL. The largest table has 750 million rows, range partitioned on a date column. The smaller tables have 600 and 1500 rows respectively. The large table was compressed back in February/March timeframe. The SQL performed well against the compressed table the first time. Query fetching ~72 million rows from a partition got processed in 2-3 min. Pretty good! Data is brought into newer partitions each month uncompressed. There are no updates or deletes on this data. The table is partitioned on snapshot_date, which is a date field.

In the subsequent month the data filter was changed to as what Oracle suggests for using proper partition pruning:-

Old

AND snapshot_date BETWEEN '2007-02-01' AND '2007-04-01'

New

AND snapshot_date BETWEEN TO_DATE ('2007-02-01', 'YYYY-MM-DD')
AND TO_DATE ('2007-04-01', 'YYYY-MM-DD')



With only the change mentioned above, currently we are seeing the response time increase from 2-3min to ~40min irrespective of data fetched from compressed on non-compressed partitions against the same table.

We have experimented quite a bit trying to find a rational explanation as to why this happened, but so far we are not successful.

Can anyone suggest possibly why the performance degradation happened for this scenario?

Thanks,

Joe