Business Day as Partition Key
I was reviewing a model deal coverage FACT table today to investigate a performance issue while running an FSA report. Table is partitioned by business_date and data is also queried by business_date. Daily volume is ~500k rows. So why is it running so slowly?
It turned out that BUSINESS_DATE column is defined as TIMESTAMP , rather than simply a DATE column, which means that all queries against this table will use a function such as
WHERE trunc(BUSINESS_DATE)=’30-JUL-2010′
This does not encourage straightforward partition pruning to happen, as a result for every report against this table, Oracle was doing a full table scan, which is not a good thing considering that the table size is >8GB.
Lesson to be learned: choose your attribute wisely.