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

<mccmx@hotmail.com> wrote in message
news:1143634570.989648.119660@t31g2000cwb.googlegr oups.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/com...ce1.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

> 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.

Yes I am,

Here is the bottom of the 10053:

Join order[1]: PS_TL_PAYABLE_TIME[A]#0
Best so far: TABLE#: 0 CST: 5 CDN: 74 BYTES:
1406
prefetching is on for PSDTL_PAYABLE_TIME
Final - All Rows Plan:
JOIN ORDER: 1
CST: 5 CDN: 74 RSC: 5 RSP: 5 BYTES: 1406
IO-RSC: 5 IO-RSP: 5 CPU-RSC: 0 CPU-RSP: 0

QUERY
explain plan for
select count(*)
from
PS_TL_PAYABLE_TIME A
WHERE
A.DUR BETWEEN TO_DATE('2006-03-29','YYYY-MM-DD') AND
TO_DATE('2006-03-31','YYYY-MM-DD')
AND A.SETID_DEPT = 'TMMF_'
AND A.TRC = 'C3'

PLAN
Cost of plan: 5
Operation...........Object name.....Options.........Id...Pid..
SELECT STATEMENT 0
SORT AGGREGATE 1
TABLE ACCESS PS_TL_PAYABLE_TI BY INDEX ROWID 2 1
INDEX PSDTL_PAYABLE_TI SKIP SCAN 3 2


the 10053 says: BEST_CST: 5.00 PATH: 4 Degree: 1

but I've never been able to correlate the PATH number (i.e. 4) with the
evaluated PATHs.

Matt

<mccmx@hotmail.com> wrote in message
news:1143641846.622016.74340@i39g2000cwa.googlegro ups.com...
>> 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.
>
> Yes I am,
>
> Here is the bottom of the 10053:
>
> Join order[1]: PS_TL_PAYABLE_TIME[A]#0
> Best so far: TABLE#: 0 CST: 5 CDN: 74 BYTES:
> 1406
> prefetching is on for PSDTL_PAYABLE_TIME
> Final - All Rows Plan:
> JOIN ORDER: 1
> CST: 5 CDN: 74 RSC: 5 RSP: 5 BYTES: 1406
> IO-RSC: 5 IO-RSP: 5 CPU-RSC: 0 CPU-RSP: 0
>
> QUERY
> explain plan for
> select count(*)
> from
> PS_TL_PAYABLE_TIME A
> WHERE
> A.DUR BETWEEN TO_DATE('2006-03-29','YYYY-MM-DD') AND
> TO_DATE('2006-03-31','YYYY-MM-DD')
> AND A.SETID_DEPT = 'TMMF_'
> AND A.TRC = 'C3'
>
> PLAN
> Cost of plan: 5
> Operation...........Object name.....Options.........Id...Pid..
> SELECT STATEMENT 0
> SORT AGGREGATE 1
> TABLE ACCESS PS_TL_PAYABLE_TI BY INDEX ROWID 2 1
> INDEX PSDTL_PAYABLE_TI SKIP SCAN 3 2
>
>
> the 10053 says: BEST_CST: 5.00 PATH: 4 Degree: 1
>
> but I've never been able to correlate the PATH number (i.e. 4) with the
> evaluated PATHs.
>
> Matt
>


Sorry,

I got lost tracking up and down the paper.
The significant bit from the first trace was
this - where the io cost of 5 first appears -
I managed to read it as the PSCTL index.

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


I can't get the numbers to work out properly.
For a start, the selectivity is smaller than 1/num_rows
and the index selectivity, even on
where dur = '29-MAR-206'
and trc = 'C3'
shouldn't be less than the 1/(202 * 826) which
is about 5.99e-6... your selectivity ought to be
much larger.


However - as an informal explanation of why
the skip scan could make sense:

You have (dur, trc) as the index.

Your predicate does a range scan for
what I guess is 3 dates on the first column,
on an index where every key value returns
316 rows (18,000,000 / Distinct keys),

But for every date, there are 21791 rows
(18,000,000 / 826 dates). So if you range
scanned the index, you would walk through
about 65,000 entries, discarding all but
about 950 of them according to the available
statistics. So Oracle should choose the skip scan.

And it has - but the numbers making it do so
look all wrong - that 5.4946e-008 has got
to be a mistake.

--
Regards

Jonathan Lewis
http://www.oracle.com/technology/com...ce1.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

Hi Jonathan,

Are you suggesting that the statistics are incorrect for the
PS_TL_PAYABLE_TIME table..?

Matt

<mccmx@hotmail.com> wrote in message
news:1143702550.387365.248010@j33g2000cwa.googlegr oups.com...
> Hi Jonathan,
>
> Are you suggesting that the statistics are incorrect for the
> PS_TL_PAYABLE_TIME table..?
>
> Matt
>

No.

At the moment I'm suggesting that there's
something in the trace file I don't understand.

That's why I had to give you the hand-waving
mechanical explanation without being able to
give you the trace file numbers that show Oracle
deriving the same thing.

--
Regards

Jonathan Lewis
http://www.oracle.com/technology/com...ce1.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

Jonathan,

thanks for your time.

I disabled the Skip Scanning feature by forcing the optimizer into RULE
mode. This shows that the Skip Scan is slightly more efficient than
the range scan so I am less concerned about that path now. I'm still a
little curious about the selectivity of the index but I don't think its
worth pursuing any further...

Here is the comparison between the Range Scan of psDtl and the Skip
Scan of psDtl:
(The Skip Scan requires slightly less logical block visits than the
range scan which explains why it is chosen)


1 select /*+ RULE */ -- i.e. disable skip scan feature.....
2 count(*)
3 from
4 PS_TL_PAYABLE_TIME A
5 WHERE
6 A.DUR BETWEEN TO_DATE('2006-03-29','YYYY-MM-DD')
7 AND TO_DATE('2006-03-31','YYYY-MM-DD')
8 AND A.SETID_DEPT = 'TMMF_'
9* AND A.TRC = 'C3'
SQL> /

COUNT(*)
----------
3549

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'PS_TL_PAYABLE_TIME'
3 2 INDEX (RANGE SCAN) OF 'PSDTL_PAYABLE_TIME' (NON-UNIQUE
)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1999 consistent gets
0 physical reads
0 redo size
380 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> ed
Wrote file afiedt.buf

1 select
2 count(*)
3 from
4 PS_TL_PAYABLE_TIME A
5 WHERE
6 A.DUR BETWEEN TO_DATE('2006-03-29','YYYY-MM-DD')
7 AND TO_DATE('2006-03-31','YYYY-MM-DD')
8 AND A.SETID_DEPT = 'TMMF_'
9* AND A.TRC = 'C3'
SQL> /

COUNT(*)
----------
3549

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=19)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'PS_TL_PAYABLE_TIME' (C
ost=5 Card=74 Bytes=1406)

3 2 INDEX (SKIP SCAN) OF 'PSDTL_PAYABLE_TIME' (NON-UNIQUE)
(Cost=4 Card=1)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1915 consistent gets
0 physical reads
0 redo size
380 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed