Intelligent Rewrite with UNION ALL / Table Expansion in the Presence of Unusable local Index Partition or Partial Index
Last updated on MARCH 10, 2015
Applies to:Oracle Database - Enterprise Edition - Version 18.104.22.168 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.
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