Re: Subquery partition pruning
- From: amonte <ax.mount@xxxxxxxxx>
- To: Oracle-L Group <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 30 Mar 2010 14:37:19 +0200
Forgot to mention this is 10.2.0.4 with no one-off or PSU patches
Thank you
2010/3/30 amonte <ax.mount@xxxxxxxxx>
> 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:
