Query with inlist chooses Slower Index Under 11.2.0.2 Optimizer

(Doc ID 1385160.1)

Last updated on AUGUST 25, 2017

Applies to:

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

Symptoms

Query containing inlist may choose a slow index plan where a more efficient index doing index range scan and table access is available.

Poor Plan:

-----------------------------------------------------------
| Operation         | Name   | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------
| SELECT STATEMENT  |        |     1 |    77 |  4398   (1)| 
|  INLIST ITERATOR  |        |       |       |            |
|   INDEX RANGE SCAN| DRAD~0 |     1 |    77 |  4397   (1)|
-----------------------------------------------------------  



 A more efficient plan  using a different index that does a range scan and table access is available.

------------------------------------------------------------------------
| Operation                    | Name     | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------
| SELECT STATEMENT             |          |     1 |    77 |     2  (50)| 
|  INLIST ITERATOR             |          |       |       |            |
|   TABLE ACCESS BY INDEX ROWID| DRAD     |     1 |    77 |     1   (0)|
|    INDEX RANGE SCAN          | DRAD~ZOB |     1 |       |     1   (0)|
------------------------------------------------------------------------


Setting OPTIMIZER_FEATURES_ENABLE='10.1.0' produces the good plan





Changes

Database upgrade to Oracle 11.2.0.2

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