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 NOVEMBER 07, 2023
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.1 and laterInformation in this document applies to any platform.
Goal
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.
Solution
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
| Goal |
| Solution |
| Scenario 1 All the index partitions are usable |
| Scenario 2 Index partition belonging to Partition 4 becomes unusable and optimizer_features_enable='11.2.0.1' or above |
| Scenario 3 Index partition belonging to Partition 4 is unusable and optimizer_features_enable is 11.1.x.x or below |
| References |