10053 Interpretation....

From: <mccmx_at_hotmail.com>
Date: 29 Mar 2006 04:16:11 -0800
Message-ID: <1143634570.989648.119660@t31g2000cwb.googlegroups.com>

Oracle 9.2.0.6 EE WIN 2K

I have a query against a large table (18 million rows) which contains 2 WHERE clauses. These columns form a composite index called PSDTL_PAYABLE_TIME(DUR,TRC). If I query this table with one of the WHERE clauses as such:

select count(*) from ps_tl_payable_time where dur between '29-MAR-206' and '31-MAR-2006';

the query happily drives off the above index (PSDTL_P...) with a range scan.

If I query this table with both of the WHERE clauses as such:

select count(*) from ps_tl_payable_time where dur between '29-MAR-206' and '31-MAR-2006'
and trc = 'C3';

the plan changes and does an Index Skip Scan off the same index.

This doesnt seem to make sense. I would have expected the optimizer to still do a range scan off the index because the combination of the two columns is much more restricitive than just DUR alone.

Any ideas why it would choose the skip scan despite the fact that both index columns form the WHERE clause of the query...?

I've included a 10053 trace output which may help...

BASE STATISTICAL INFORMATION

Table stats Table: PS_TL_PAYABLE_TIME Alias: A   TOTAL :: CDN: 18001896 NBLKS: 498352 AVG_ROW_LEN: 191 -- Index stats
  INDEX NAME: PSATL_PAYABLE_TIME COL#: 4     TOTAL :: LVLS: 3 #LB: 148820 #DK: 17999818 LB/K: 1 DB/K: 1 CLUF: 17023537
  INDEX NAME: PSBTL_PAYABLE_TIME COL#: 24     TOTAL :: LVLS: 3 #LB: 65847 #DK: 505 LB/K: 130 DB/K: 2282 CLUF: 1152603
  INDEX NAME: PSCTL_PAYABLE_TIME COL#: 1 7 3     TOTAL :: LVLS: 3 #LB: 109938 #DK: 16782389 LB/K: 1 DB/K: 1 CLUF: 17591114
  INDEX NAME: PSDTL_PAYABLE_TIME COL#: 3 7     TOTAL :: LVLS: 2 #LB: 63534 #DK: 56864 LB/K: 1 DB/K: 277 CLUF: 15767283
  INDEX NAME: PS_TL_PAYABLE_TIME COL#: 1 2 3 4     TOTAL :: LVLS: 3 #LB: 127351 #DK: 18001896 LB/K: 1 DB/K: 1 CLUF: 5664223
_OPTIMIZER_PERCENT_PARALLEL = 0

SINGLE TABLE ACCESS PATH

Column: SETID_DEPT  Col#: 36     Table: PS_TL_PAYABLE_TIME   Alias:  A
    NDV: 4         NULLS: 0         DENS: 2.5000e-001
    NO HISTOGRAM: #BKT: 1 #VAL: 2
Column:        TRC  Col#: 7      Table: PS_TL_PAYABLE_TIME   Alias:  A
    NDV: 202       NULLS: 0         DENS: 4.9505e-003
    NO HISTOGRAM: #BKT: 1 #VAL: 2
Column:        DUR  Col#: 3      Table: PS_TL_PAYABLE_TIME   Alias:  A
    NDV: 826       NULLS: 0         DENS: 1.2107e-003 LO:  2452883  HI:

2453826

    NO HISTOGRAM: #BKT: 1 #VAL: 2
  TABLE: PS_TL_PAYABLE_TIME ORIG CDN: 18001896 ROUNDED CDN: 74 CMPTD CDN: 74
  Access path: tsc Resc: 75638 Resp: 75638   Skip scan: ss-sel 0 andv 14080
    ss cost 14080
    table io scan cost 75638
  Skip scan chosen
  Access path: index (no sta/stp keys)

      Index: PSCTL_PAYABLE_TIME
  TABLE: PS_TL_PAYABLE_TIME
      RSC_CPU: 0 RSC_IO: 14374
  IX_SEL: 5.4946e-008 TB_SEL: 1.6493e-005   Skip scan: ss-sel 0 andv 1
    ss cost 2
    index io scan cost 212
  Skip scan chosen
  Access path: index (scan)

      Index: PSDTL_PAYABLE_TIME
  TABLE: PS_TL_PAYABLE_TIME
      RSC_CPU: 0 RSC_IO: 5
  IX_SEL: 5.4946e-008 TB_SEL: 5.4946e-008   Skip scan: ss-sel 0 andv 14080
    ss cost 14080
    table io scan cost 75638
  Skip scan chosen
  Access path: index (no sta/stp keys)

      Index: PS_TL_PAYABLE_TIME
  TABLE: PS_TL_PAYABLE_TIME
      RSC_CPU: 0 RSC_IO: 32954
  IX_SEL: 1.1099e-005 TB_SEL: 3.3315e-003   BEST_CST: 5.00 PATH: 4 Degree: 1

Thanks in advance....

Matt Received on Wed Mar 29 2006 - 06:16:11 CST