Partition Pruning Not Happening for Queries with Skewed Joins (Doc ID 1949273.1)

Last updated on DECEMBER 04, 2014

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 to 11.2.0.4 [Release 11.1 to 11.2]
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.

 

 Execution Plan
 ----------------------------------------------------------
 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

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms