My Oracle Support Banner

Partitioned Table Uses Different Optimizer Plan (Doc ID 2729221.1)

Last updated on JANUARY 15, 2021

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Information in this document applies to any platform.

Symptoms

Good plan looks Like

 

        0          0          0  SORT ORDER BY (cr=7 pr=1 pw=0 time=4807399 us starts=86 cost=4807399 size=22591045958 card=56057186)
        0          0          0   COUNT  (cr=7 pr=1 pw=0 time=0 us starts=66)
        0          0          0    FILTER  (cr=7 pr=1 pw=0 time=0 us starts=61)
        0          0          0     TABLE ACCESS BY INDEX ROWID Table(cr=7 pr=1 pw=0 time=8 us starts=58 cost=8 size=22591045958 card=56057186)
        0          0          0      BITMAP CONVERSION TO ROWIDS (cr=7 pr=1 pw=0 time=0 us starts=51)
        0          0          0       BITMAP OR  (cr=7 pr=1 pw=0 time=0 us starts=46)
        0          0          0        BITMAP CONVERSION FROM ROWIDS (cr=4 pr=1 pw=0 time=0 us starts=28)
        0          0          0         INDEX RANGE SCAN index1 (cr=4 pr=1 pw=0 time=4 us starts=26 cost=4 size=0 card=0)(object id 96111)
        0          0          0        BITMAP CONVERSION FROM ROWIDS (cr=3 pr=0 pw=0 time=0 us starts=11)
        0          0          0         INDEX RANGE SCAN index2(cr=3 pr=0 pw=0 time=3 us starts=8 cost=3 size=0 card=0)(object id 96203)

 

the SLOW Plan looks like

 

        0          0          0  SORT ORDER BY (cr=4732796 pr=4724269 pw=0 time=6059305 us starts=239401008 cost=6059305 size=22385876240 card=55548080)
        0          0          0   COUNT  (cr=4732796 pr=4724269 pw=0 time=0 us starts=239400853)
        0          0          0    FILTER  (cr=4732796 pr=4724269 pw=0 time=0 us starts=239400843)
 55663317   55663317   55663317     PARTITION RANGE ALL PARTITION: 1 229 (cr=4732796 pr=4724269 pw=0 time=1295576 us starts=186017393 cost=1295576 size=22385876240 card=55548080)
 55663317   55663317   55663317      TABLE ACCESS FULL Table PARTITION: 1 229 (cr=4732796 pr=4724269 pw=0 time=1295576 us starts=85494364 cost=1295576 size=22385876240 card=55548080)

 

Changes

 after partitioning the table

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
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.