ANSI Rearch in 12C Causes Full Table Scan Rather Than Index Range Scan on Tables Having Decorrelated Lateral View
(Doc ID 2363461.1)
Last updated on SEPTEMBER 20, 2022
Applies to:
Oracle Database Cloud Schema Service - Version N/A and laterOracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
- Full Table Scan is happening instead of an Index Range Scan on all the queries after upgrade from 11.2.0.4 to 12.1.0.2:
Good plan (11.2.0.4):
==============
============
Plan Table
============
-----------------------------------------------------------------+-----------------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
-----------------------------------------------------------------+-----------------------------------+---------------+
| 0 | SELECT STATEMENT | | | | 5 | | | |
| 1 | COUNT | | | | | | | |
| 2 | NESTED LOOPS OUTER | | 1 | 48 | 5 | 00:00:01 | | |
| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID | TP_CUSTOMERORDER| 1 | 14 | 3 | 00:00:01 | ROW LOCATION| ROW LOCATION|
| 4 | INDEX UNIQUE SCAN | PK_CUSTOMERORDER| 1 | | 2 | 00:00:01 | | |
| 5 | PARTITION REFERENCE ITERATOR | | 1 | 34 | 2 | 00:00:01 | KEY | KEY |
| 6 | VIEW | | 1 | 34 | 2 | 00:00:01 | | |
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID | TP_CO_RESULT | 1 | 145 | 2 | 00:00:01 | KEY | KEY |
| 8 | INDEX RANGE SCAN | I_CO_RESULT_CO | 2 | | 1 | 00:00:01 | KEY | KEY |
-----------------------------------------------------------------+-----------------------------------+---------------+
Bad plan (12.1.0.2):
=========================
Plan Table
============
----------------------------------------------------------------+-----------------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
----------------------------------------------------------------+-----------------------------------+---------------+
| 0 | SELECT STATEMENT | | | | 611 | | | |
| 1 | COUNT | | | | | | | |
| 2 | NESTED LOOPS OUTER | | 1 | 88 | 611 | 00:00:08 | | |
| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID | TP_CUSTOMERORDER| 1 | 14 | 3 | 00:00:01 | ROW LOCATION| ROW LOCATION|
| 4 | INDEX UNIQUE SCAN | PK_CUSTOMERORDER| 1 | | 2 | 00:00:01 | | |
| 5 | PARTITION REFERENCE ALL | | 1 | 74 | 608 | 00:00:08 | 1 | 50 |
| 6 | VIEW | VW_DCL_9A475B70 | 1 | 74 | 608 | 00:00:08 | | |
| 7 | TABLE ACCESS FULL | TABLE_NAME | 3801 | 538K | 608 | 00:00:08 | 1 | 50 |
----------------------------------------------------------------+-----------------------------------+---------------+
Changes
Database has been upgraded from 11.2.0.4 to 12.1.0.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 |
| References |