10053 Interpretation....
- From: mccmx@xxxxxxxxxxx
- Date: 29 Mar 2006 04:16:11 -0800
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
.
- Follow-Ups:
- Re: 10053 Interpretation....
- From: Jonathan Lewis
- Re: 10053 Interpretation....
- Prev by Date: Re: Create database link from inside a stored procedure
- Next by Date: Re: Create database link from inside a stored procedure
- Previous by thread: DatabaseMetaData.getColumns() returns 0 fileds when create a synonym on?
- Next by thread: Re: 10053 Interpretation....
- Index(es):
Relevant Pages
|
Loading
