Histogram of Character Columns Longer Than 32 Characters Causes Incorrect SQL Plan of Table Full Scan

(Doc ID 2413826.1)

Last updated on JUNE 27, 2018

Applies to:

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

Symptoms

Bad SQL Plan:

--------------------------------------------------------------------------------
| Operation                      | PHV/Object Name     |  Rows | Bytes|   Cost |
--------------------------------------------------------------------------------
|SELECT STATEMENT                |----- 2561654947 ----|       |      |  23552 |
|COUNT                           |                     |       |      |        |
| CONNECT BY WITH FILTERING      |                     |       |      |        |
|  TABLE ACCESS FULL             |USERTABLENA          |     8K|    2M|   7505 |
|  HASH JOIN                     |                     |     8K|    2M|  14960 |
|   CONNECT BY PUMP              |                     |       |      |        |
|   TABLE ACCESS FULL            |USERTABLENA          |   740K|  180M|   7453 |
--------------------------------------------------------------------------------


Good SQL Plan:


--------------------------------------------------------------------------------
| Operation                      | PHV/Object Name     |  Rows | Bytes|   Cost |
--------------------------------------------------------------------------------
|SELECT STATEMENT                |----- 4221160861 ----|       |      |     68 |
|COUNT                           |                     |       |      |        |
| CONNECT BY WITH FILTERING      |                     |       |      |        |
|  INLIST ITERATOR               |                     |       |      |        |
|   INDEX RANGE SCAN             |IDX_USERTABLENA_1251 |    15 |    3K|     18 |
|  NESTED LOOPS                  |                     |    15 |    4K|     48 |
|   CONNECT BY PUMP              |                     |       |      |        |
|   INDEX RANGE SCAN             |IDX_USERTABLENA_1251 |     1 |  260 |      2 |
--------------------------------------------------------------------------------
CBO Statistics and relevant attributes.      
Index Name Column Name Avg Num Histogram
Col Buckets
Len  
IDX_USERTABLENA_1251 ID 37 254 HEIGHT BALANCED

 

 

Changes

 Gathered table statistics.

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