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 MARCH 10, 2015

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

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