Subquery partition pruning
- From: amonte <ax.mount@xxxxxxxxx>
- To: Oracle-L Group <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 30 Mar 2010 14:33:26 +0200
Hello all
I have this simple query (T_MOBILE_EVENT partitioned by DATE_EID) but
partition elimination is not happening due to the hash join, when changed to
Nested Loop partition pruning does happen.
I dont understand why the pruning is not happening with
TBL$OR$IDX$PART$NUM() function call (subquery pruning) when hash join is
involved. According to Note 209070.1 Partition Pruning based on Joins to
Partitioning Criteria Stored in Dimension Tables the conditions are met
which are following:
1. the presence of a join on the partition key column of the fact table.
2. the cost and selectivity of one of the dimension tables being less
than 2 predefined values. The cost of the recursive subquery must not exceed
5% of the cost of accessing all data in the partitioned fact table and the
predicates on the dimension table must select less than 50% of the data in
the dimension table.
Can anyone throw some lights? :)
Thank you
*No pruning with HASH JOIN*
SELECT
TO_CHAR(A.DATE_NAME,'YYYY-MM-DD'),
B.MSISDN,
B.IMSI,
SUM(B.EVENT_AMOUNT)
FROM DW.D_DATE A,
DWH_OWN.T_MOBILE_EVENT B
WHERE A.DATE_EID = B.DATE_EID
AND A.DATE_NAME = TO_DATE('20100322', 'yyyymmdd') -- this only returns
one row
GROUP BY TO_CHAR(A.DATE_NAME,'YYYY-MM-DD'),
B.MSISDN,
B.IMSI
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc|
Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 237M| 10G|
| 29M (5)| 23:43:11 | | |
| 1 | HASH GROUP BY | | 237M| 10G|
26G| 29M (5)| 23:43:11 | | |
|* 2 | HASH JOIN | | 237M| 10G|
| 26M (5)| 21:05:52 | | |
|* 3 | INDEX RANGE SCAN | D_DATE_NAME_EID_I | 1 | 12 |
| 2 (0)| 00:00:01 | | |
| 4 | PARTITION RANGE ALL| | 7128M| 225G|
| 26M (4)| 20:58:30 | 1 | 751 |
| 5 | PARTITION HASH ALL| | 7128M| 225G|
| 26M (4)| 20:58:30 | 1 | 8 |
| 6 | TABLE ACCESS FULL| T_MOBILE_EVENT | 7128M| 225G|
| 26M (4)| 20:58:30 | 1 | 6008 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."DATE_EID"="B"."DATE_EID")
3 - access("A"."DATE_NAME"=TO_DATE(' 2010-03-22 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
*Pruning with Nested Loop*
SELECT /*+ USE_NL (A B) */
TO_CHAR(A.DATE_NAME,'YYYY-MM-DD'),
B.MSISDN,
B.IMSI,
SUM(B.EVENT_AMOUNT)
FROM DW.D_DATE A,
DWH_OWN.T_MOBILE_EVENT B
WHERE A.DATE_EID = B.DATE_EID
AND A.DATE_NAME = TO_DATE('20100322', 'yyyymmdd')
GROUP BY TO_CHAR(A.DATE_NAME,'YYYY-MM-DD'),
B.MSISDN,
B.IMSI
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes
|TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 237M|
10G| | 29M (4)| 23:35:50 | | |
| 1 | HASH GROUP BY | | 237M| 10G|
26G| 29M (4)| 23:35:50 | | |
| 2 | NESTED LOOPS | | 237M|
10G| | 26M (4)| 20:58:30 | | |
|* 3 | INDEX RANGE SCAN | D_DATE_NAME_EID_I | 1 | 12
| | 2 (0)| 00:00:01 | | |
| 4 | PARTITION RANGE ITERATOR| | 237M|
7704M| | 26M (4)| 20:58:30 | KEY | KEY |
| 5 | PARTITION HASH ALL | | 237M|
7704M| | 26M (4)| 20:58:30 | 1 | 8 |
|* 6 | TABLE ACCESS FULL | T_MOBILE_EVENT | 237M|
7704M| | 26M (4)| 20:58:30 | KEY | KEY |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."DATE_NAME"=TO_DATE(' 2010-03-22 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
6 - filter("A"."DATE_EID"="B"."DATE_EID")
Other related posts:
