Re: 10053 Interpretation....

<mccmx_at_hotmail.com> wrote in message
news:1143641846.622016.74340_at_i39g2000cwa.googlegroups.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/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