Re: 10053 Interpretation....

<mccmx_at_hotmail.com> wrote in message
news:1143634570.989648.119660_at_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
>

Are you sure it's going through the psDtl index, the trace file seems to be saying that it's going to use the psCtl index.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html