Partition Pruning works!
In a recent post, David Aldridge discussed an approach for efficiently rebuilding a table so that partition key migrated from one column to another. This is a somewhat specialised operation and one that most people need not consider (well not unless you do data warehousing and need to revise a partitioning scheme for performance reasons)
In the follow-up postings I discussed with David the use of day level partitions as an alternative to month level partitions especially if you could eliminate the need for sub-partitions. In David’s case the use of LIST sub-partitions is relevant, but in a DW system we recently re-designed we gained query-time advantage by moving away from HASH sub-partitions. David did however ask if we still got partition elimination from month level queries on a day level table. The answer to this is “YES”
In our DW we have a base daily sales table BASE_S_DY (540,400,000 rows) with 9 dimension key columns (including actual_dt) and a variety of measures. Each key column is bitmap indexed and also has a RELY foreign key constraint to the dimension data tables. We also have defined Oracle dimension objects to support MV summaries.
For my simple test I am not using MV query rewrite. I have a simple dimension table DW_CAL_DAY that contains all available dates in our DW.
SQL> desc dw_cal_day Name Null? Type ----------------------------------------- -------- -------------- ACTUAL_DT NOT NULL DATE DAY_OF_WEEK NOT NULL VARCHAR2(10) WEEK_NO NOT NULL NUMBER(6) MONTH_NO NOT NULL NUMBER(6) CALENDAR_YEAR_NO NOT NULL NUMBER(4) CALENDAR_MONTH NOT NULL VARCHAR2(10) DATESTAMP DATE
There is a bitmap indexes on the MONTH_NO column of DW_CAL_DAY.
explain plan for select sum(SALES_VALUE_AT_SELL_LOC) from BASE_S_DY s, dw_cal_day d where s.actual_dt = d.actual_dt and month_no = 200507 and customer_no = 534879262Gives the following plan: (edited to fit page by removing parallel query columns
PLAN_TABLE_OUTPUT
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop
| 0 | SELECT STATEMENT | | 1 | 32 | 1158 (1)| | |
| 1 | SORT AGGREGATE | | 1 | 32 | | | | | | |
| 2 | SORT AGGREGATE | | 1 | 32 | | |
|* 3 | HASH JOIN | | 67 | 2144 | 1158 (1)| |
| 4 | TABLE ACCESS BY INDEX ROWID | DW_CAL_DAY | 30 | 390 | 5 (20)| |
| 5 | BITMAP CONVERSION TO ROWIDS | | | | | | | | | |
|* 6 | BITMAP INDEX SINGLE VALUE | PS_WK_MO | | | | | | | | |
| 7 | PARTITION RANGE ITERATOR | | | | | KEY | KEY | 75,01 | PCWP | |
| 8 | TABLE ACCESS BY LOCAL INDEX ROWID| BASE_S_DY | 1515 | 28785 | 1154 (1)| KEY|KEY
| 9 | BITMAP CONVERSION TO ROWIDS | | | | | |
|* 10 | BITMAP INDEX SINGLE VALUE | SSBASED_CU | | | | KEY | KEY
Predicate Information (identified by operation id):
3 - access("S"."ACTUAL_DT"="D"."ACTUAL_DT")
6 - access("D"."MONTH_NO"=200507)
10 - access("S"."CUSTOMER_NO"=534879262)
And the stats:
Statistics
54 recursive calls
4 db block gets
252 consistent gets
151 physical reads
1044 redo size
219 bytes sent via SQLNet to client
277 bytes received via SQLNet from client
2 SQL*Net roundtrips to/from client
36 sorts (memory)
0 sorts (disk)
1 rows processed
The key point here is that there is a partition range interator at id=7 the key value indicates that the partition key is identified at query time.
For my table S_BASE_DY contains 19,000,000 rows for the month being queried and we only need to access 86 rows to get the total sales for the customer in question. The total time to execute my query is around 2 seconds. I am happy with that!