My Oracle Support Banner

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 JULY 16, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Information 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

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.