Page History

Perform the following:

• Examine associated statements in the Statements tab.
• Focus on the Index Skip Scan access type in the Access Types table.
• Examine column usage for each statement in the Columns table.

Perform one of the following options:

• Change the columns sequence within the index that use the index skip scan. Check the effect of this change in the What-If tab.
• Identify common high selectivity columns for top statements. Create an index matching the statements predicates.
• If you cannot create a new index, use hints, such as, "full" and "index_ffs" to determine if by using these access types you can achieve better performance results.

Table: TAB1 (C1 number, C2 number, C3 number, C10 Date)

Index: IX1 (C1,C2) (C1 has two distinct values Yes and No)

Statement: select * from TAB1 where C2=10;

Execution plan uses Index Skip Scan on IX1.

In this case Oracle has to perform two range scans on the index—one with a key of (Yes,10) and another with a key of (No,10)and then unite the results. The more distinct values defined for C1, the more index scanning required.

Defining a new index on (C2), or changing the column sequence in IX1 to be (C2,C1), enables a more efficient access path for the Index Range Scan.