Index advice and OR predicates

Index advice generation to handle OR predicates

Index Advisor has been extended to include queries that OR together local selection (WHERE clause) columns over a single table. OR advice requires two or more indexes to be created as a dependent set.

If any of the OR'd indexes are missing, the optimizer won’t be able to cost and choose these dependent indexes for implementation of the OR based query.

This relationship between OR based indexes in the SYSIXADV index advice table is with a new DEPENDENT_ADVICE_COUNT column.

Some restrictions with this support:

  • OR'd predicate advice appears only if no other advice is generated
  • Maximum of 5 predicates OR'd together
  • Advised for files with OR'd local selection that get costed in the primary join dial when optimizing a join query

When Index Advisor shows highly dependent advice, use of the exact match capability from Show Statements to find the query in the plan cache is helpful. Once found, use Visual Explain to discover the dependent index advice specific to that query.

Index Or Advice example

  • Should advise indexes over all OR'd predicate columns
  • All 3 advised indexes will have DEPENDENT_ADVICE_COUNT>0
  • Execution with indexes should produce bitmap implementation and register no new advice
SELECT orderkey, partkey, suppkey, 
linenumber, shipmode orderpriority 
FROM item_fact 
WHERE OrderKey <= 10 OR 
SuppKey <= 10 OR
PartKey <= 10 
OPTIMIZE FOR ALL ROWS

The graphic below shows the Index advice for the OR'd predicate columns:

This index advice for the OR'd predicate columns graphic is described in the previous paragraph
The graphic below depicts the Visual Explain showing the implementation of merge bitmap representation using the OR'd advice indexes:
This Visual explain graphic is described in the previous paragraph