Re: index skip scan

From: jernigam_at_nospam.kochind.com <jernigam_at_kochind.com>
Date: Tue, 6 Jan 2009 08:45:51 -0800 (PST)
Message-ID: <be740ad3-4121-4db6-9e61-e2ffd29ae294_at_o4g2000pra.googlegroups.com>

I gathered statistics.
I see the bad plan on both.

I can see in the 10053 trace that the skip scan gets a lower cost. I think the biggest factor is what it returns for the index selectivity. I am trying to understand how the index selectivity and cost is calculated for skip scans.

SINGLE TABLE ACCESS PATH
  Column (#10): EFFECTIVE_DATE(DATE)
    AvgLen: 8.00 NDV: 4022 Nulls: 0 Density: 2.4863e-04 Min: 2448989 Max: 2456294
...

  Index: INV_TRANS_QTY_IND2 Col#: 10
    LVLS: 2 #LB: 33244 #DK: 4022 LB/K: 8.00 DB/K: 438.00 CLUF: 1765553.00
  Index: INV_TRANS_QTY_IND3 Col#: 4 10
    LVLS: 2 #LB: 29916 #DK: 865907 LB/K: 1.00 DB/K: 3.00 CLUF: 2607782.00
...

  Access Path: index (RangeScan)
    Index: INV_TRANS_QTY_IND2
    resc_io: 1435.00 resc_cpu: 23193396     ix_sel: 7.9620e-04 ix_sel_with_filters: 7.9620e-04     Cost: 726.76 Resp: 726.76 Degree: 1

  Access Path: index (SkipScan)
    Index: INV_TRANS_QTY_IND3
    resc_io: 987.00 resc_cpu: 9550211
    ix_sel: 1.6282e-04 ix_sel_with_filters: 1.6282e-04     Cost: 497.31 Resp: 497.31 Degree: 1

I haven't tried a SQL Profile. Received on Tue Jan 06 2009 - 10:45:51 CST