My Oracle Support Banner

Ineffecient Index Selection For Query With ORDER BY Clause Caused By Setting FIRST_ROWS in 12.2 (Doc ID 2442546.1)

Last updated on DECEMBER 12, 2019

Applies to:

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

Symptoms

In 12.2 with ORDER BY clause, index full scan is used:

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

  

SQL> select col4,col5  from tbl1_tbl2_join_view
where col4='16316363' ORDER BY col1,col2,col3;

col4 col5
-------- -------------
16316363 9999999999999
16316363 9999999777777
16316363 4549454945490
16316363 2099209920995
16316363 2000000000000

Elapsed: 00:00:55.81  *<<<<<---------

SQL Plan
----------------------------------------------------------
Plan hash value: 966240503

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name                  | Rows  |Bytes | Cost  |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                       |     1 |  65 |   161K|
|   1 |  NESTED LOOPS                |                       |     1 |  65 |   161K|
|*  2 |   TABLE ACCESS BY INDEX ROWID| TBL1                  |     1 |  45 |   161K|
|   3 |    INDEX FULL SCAN           | TBL1_PK               |   715K|     |  4080 |*<<<<--------
|*  4 |   INDEX UNIQUE SCAN          | TBL2_PK               |     1 |  20 |     1 |
--------------------------------------------------------------------------------------
... ...
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     219276  consistent gets *<<<<--------
     118571  physical reads  *<<<<--------
          0  redo size
        774  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

In 12.2, without ORDER BY clause:

SQL> select col4,col5  from tbl1_tbl2_join_view
where col4='16316363';

col4 col5
-------- -------------
16316363 9999999999999
16316363 9999999777777
16316363 4549454945490
16316363 2099209920995
16316363 2000000000000

Elapsed: 00:00:00.00  *<<<<<---------

SQL Plan
----------------------------------------------------------
Plan hash value: 3099082192

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name                  | Rows  |Bytes | Cost  |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                       |     1 |  65 |     5 |
|   1 |  NESTED LOOPS                |                       |     1 |  65 |     5 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TBL1                  |     1 |  45 |     4 |
|*  3 |    INDEX RANGE SCAN          | TBL1_IE01             |     3 |     |     3 |
|*  4 |   INDEX UNIQUE SCAN          | TBL2_PK               |     1 |  20 |     1 |
--------------------------------------------------------------------------------------
... ...
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         23  consistent gets
          0  physical reads
          0  redo size
        774  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

  

In 9.2, both with and without ORDER BY clause, index range scan is used:

col4 col5
-------- -------------
16316363 9999999999999
16316363 9999999777777
16316363 4549454945490
16316363 2099209920995
16316363 2000000000000

Elapsed: 00:00:00.48

SQL Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (ORDER BY)
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'TBL1'
   4    3         INDEX (RANGE SCAN) OF 'TBL1_IE01' (NON-UNIQUE)
   5    2       INDEX (UNIQUE SCAN) OF 'TBL2_PK' (UNIQUE)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         22  consistent gets
          0  physical reads
          0  redo size
        709  bytes sent via SQL*Net to client
        656  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          5  rows processed

Changes

 Upgrade from 9.2 to 12.2 and set OPTIMIZER_MODE=FIRST_ROWS as same as 9.2.

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


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