I'm using partitioning for the first time and I'm having trouble determining if I can partition my fact table in a way that will allow partition pruning to work with the queries that OBIEE generates. I've put together a simple example using queries that I wrote to illustrate my problem. In this example I have a star schema with a fact table and I'm joining in two dimensions. My fact table is LIST-RANGE partitioned on JOB_ID and TIME_ID and those are the keys that tie to the two dimensions I'm using in this query.
select sum(boxbase)
from TEST_RESPONSE_COE_JOB_QTR A
join DIM_STUDY C on A.job_id = C.job_id
join DIM_TIME B on A.response_time_id = B.time_id
where C.job_name = 'FY14 CSAT'
and B.fiscal_quarter_name = 'Quarter 1';
From what I can tell though, because the query is actually filtering on columns in the dimensions instead of they fact table columns, the pruning isn't actually taking place. I'm actually seeing slightly better performance from a non-partitioned table even though I wrote this query specifically targeted at the partitioning strategy that is in place now.
If I run the statement below, it runs much faster and an explain plan is very simple and looks to me like it is pruning down to one sub partition like I hoped. This isn't how any query generated by OBIEE is going to look though.
select sum(boxbase)
from TEST_RESPONSE_COE_JOB_QTR
where job_id = 101123480
and response_time_id < 20000000;
Any suggestions? I do get some benefit from partition exchange using this setup, but if I'm going to sacrifice reporting performance then that may not be worthwhile or at a minimum I'd need to get rid of my sub partitions if they aren't providing any benefit.
Here are the explain plans I got for the two queries in my original post:
Operation
Object Name
Rows
Bytes
Cost
Object Node
In/Out
PStart
PStop
SELECT STATEMENT Optimizer Mode=ALL_ROWS |
| 1 |
| 20960 |
|
|
|
|
SORT AGGREGATE |
| 1 | 13 |
|
|
|
|
|
VIEW | SYS.VW_ST_5BC3A99F | 101 K | 1 M | 20960 |
|
|
|
|
NESTED LOOPS |
| 101 K | 3 M | 20950 |
|
|
|
|
PARTITION LIST SUBQUERY |
| 101 K | 2 M | 1281 |
|
| KEY(SUBQUERY) | KEY(SUBQUERY) |
PARTITION RANGE SUBQUERY |
| 101 K | 2 M | 1281 |
|
| KEY(SUBQUERY) | KEY(SUBQUERY) |
BITMAP CONVERSION TO ROWIDS |
| 101 K | 2 M | 1281 |
|
|
|
|
BITMAP AND |
|
|
|
|
|
|
|
|
BITMAP MERGE |
|
|
|
|
|
|
|
|
BITMAP KEY ITERATION |
|
|
|
|
|
|
|
|
BUFFER SORT |
|
|
|
|
|
|
|
|
INDEX SKIP SCAN | CISCO_SYSTEMS.DIM_STUDY_UK | 1 | 17 | 1 |
|
|
|
|
BITMAP INDEX RANGE SCAN | CISCO_SYSTEMS.FACT_RESPONSE_JOB_ID_BMID_12 |
|
|
|
|
| KEY | KEY |
BITMAP MERGE |
|
|
|
|
|
|
|
|
BITMAP KEY ITERATION |
|
|
|
|
|
|
|
|
BUFFER SORT |
|
|
|
|
|
|
|
|
VIEW | CISCO_SYSTEMS.index$_join$_052 | 546 | 8 K | 9 |
|
|
|
|
HASH JOIN |
|
|
|
|
|
|
|
|
INDEX RANGE SCAN | CISCO_SYSTEMS.DIM_TIME_QUARTER_IDX | 546 | 8 K | 2 |
|
|
|
|
INDEX FAST FULL SCAN | CISCO_SYSTEMS.TIME_ID_PK | 546 | 8 K | 8 |
|
|
|
|
BITMAP INDEX RANGE SCAN | CISCO_SYSTEMS.FACT_RESPONSE_TIME_ID_BMIDX_11 |
|
|
|
|
| KEY | KEY |
TABLE ACCESS BY USER ROWID | CISCO_SYSTEMS.TEST_RESPONSE_COE_JOB_QTR | 1 | 15 | 19679 |
|
| ROWID | ROW L |
|
|
|
|
|
|
|
|
|
Operation
Object Name
Rows
Bytes
Cost
Object Node
In/Out
PStart
PStop
SELECT STATEMENT Optimizer Mode=ALL_ROWS |
| 1 |
| 1641 |
|
|
|
|
SORT AGGREGATE |
| 1 | 13 |
|
|
|
|
|
PARTITION LIST SINGLE |
| 198 K | 2 M | 1641 |
|
| KEY | KEY |
PARTITION RANGE SINGLE |
| 198 K | 2 M | 1641 |
|
| 1 | 1 |
TABLE ACCESS FULL | CISCO_SYSTEMS.TEST_RESPONSE_COE_JOB_QTR | 198 K | 2 M | 1641 |
|
| 36 | 36 |