Intelligent Rewrite with UNION ALL / Table Expansion in the Presence of Unusable local Index Partition or Partial Index
(Doc ID 1638318.1)
Last updated on OCTOBER 15, 2019
Applies to:Oracle Database - Enterprise Edition - Version 22.214.171.124 and later
Information in this document applies to any platform.
Starting from 11.2 a local index can be used by the optimizer if some index partitions are unusable and some ones are usable.
This is achived by table expansion, and it is happening by doing intelligent rewrite of queries using UNION ALL in the background.
Prior to 11.2 static pruning and only access of usable index partitions mandatory in order to be able to use the index as an access path.
In 12c this mechanism also works in the scenario when partitioned table has local index on some partitions only (see <Note 1482460.1> on partial indexes).
Note that this feature in 11.2 and above is about the possibility of using the above described index, and its actual use in an execution plan is a cost based decision.
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
|Scenario 1 All the index partitions are usable|
|Scenario 2 Index partition belonging to Partition 4 becomes unusable and optimizer_features_enable='126.96.36.199' or above|
|Scenario 3 Index partition belonging to Partition 4 is unusable and optimizer_features_enable is 11.1.x.x or below|