Partition Pruning Not Happening for Queries with Skewed Joins
(Doc ID 1949273.1)
Last updated on MAY 19, 2022
Applies to:
Oracle Database Exadata Cloud Machine - Version N/A and laterOracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
Partition pruning does not take place for the SQL statement with SKEWED JOINS and GROUP BY.
select a11.DT_SKEY DT_SKEY, a16.DT DT1, sum(a11.C1526727221) WJXBFS1
from DM_RSRC_PERF_LTE_EUTRAN.FACT_HWI_ECELL_RA_D a11
join DIM_COMMON.DIM_DT a16
on (a11.DT_SKEY = a16.DT_SKEY)
where a16.DT >= To_Date('26-08-2014', 'dd-mm-yyyy')
group by a11.DT_SKEY, a16.DT;
The query is a select from two tables. Table A is FACT_HWI_ECELL_RA_D
(partitioned by DT_SKEY) and table B is DIM_COMMON.DIM_DT.
Document 209070.1 states that for pruning to happen two conditions must be met
1. Table must be partitioned.
2. Partition key should either be a predicate or in a join condition.
In query case above, both the conditions are met.
----------------------------------------------------------
Plan hash value: 2228528873
------------------------------------------------------------------------------
---------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------
---------------------------------
| 0 | SELECT STATEMENT | | 708 | 23364 | 20484
(2)| 00:04:06 | | |
| 1 | HASH GROUP BY | | 708 | 23364 | 20484
(2)| 00:04:06 | | |
|* 2 | HASH JOIN | | 708 | 23364 | 20483
(2)| 00:04:06 | | |
| 3 | VIEW | VW_GBC_5 | 708 | 13452 | 20475
(2)| 00:04:06 | | |
| 4 | HASH GROUP BY | | 708 | 7788 | 20475
(2)| 00:04:06 | | |
| 5 | PARTITION RANGE ALL| | 5558K| 58M| 20207
(1)| 00:04:03 | 1 | 915 |
| 6 | PARTITION LIST ALL| | 5558K| 58M| 20207
(1)| 00:04:03 | 1 | LAST |
| 7 | TABLE ACCESS FULL| FACT_HWI_ECELL_RA_D | 5558K| 58M| 20207
(1)| 00:04:03 | 1 | 1586 |
|* 8 | INDEX FAST FULL SCAN | DIM_DT_PK | 2142 | 29988 | 7
(0)| 00:00:01 | | |
------------------------------------------------------------------------------
---------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ITEM_1"="A16"."DT_SKEY")
8 - filter("A16"."DT">=TO_DATE(' 2014-08-26 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Cause |
| Solution |
| References |