Re: 10053 Interpretation....

From: <mccmx_at_hotmail.com>
Date: 30 Mar 2006 02:20:47 -0800
Message-ID: <1143714047.014309.194610@z34g2000cwc.googlegroups.com>

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

Received on Thu Mar 30 2006 - 04:20:47 CST