Tuesday, February 15, 2011

How to make sure Optimizer chooses NUSI over Full Table Scan (FTS)?


How to make sure Optimizer chooses NUSI over Full Table Scan (FTS)?
Answer:

A optimizer would prefer FTS over NUSI, when there are no Statistics defined on NUSI columns.
It would prefer scanning for full table instead of going for Subtables in NUSI  since optimized does not have information about subtables of NUSI
It is always suggested to collect statistics whenever NUSI columns are defined on the table.
Verify whether index is being used by checking in Explain plan.

2 comments:

  1. OK. And what if the stats exists and optimizer still prefer a full table scan instead of index range scan ?

    ReplyDelete
  2. first .. We need to make sure proper set of column/columns to be defined as NUSI on table as NUSI subtable takes enough space on AMPs.

    Second... Stats must be upto date on NUSI columns. say NUSI is defined on column a,column b. You should collect stats on Index level(column a, column b) and just not on one column.
    third ...We have to ensure that NUSI columns are used in Join .

    Inspite of these considerations if optimzer goes for full table scan then it might be due to
    1.) Type of data to be chosen ..for ex. say >,< ,not null
    2.) Optimzer thinks FTS is much faster than NUSI range scan
    3.) There is a formula based on which optimizer goes for FTS
    for rows/amp > 25-50% then FTS is chosen.

    Hope this helps :)

    ReplyDelete