RSS Feed

Method R Discussion Group

I don't understand why the skip scan doesn't work for me here:

Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE    9.2.0.6.0    Production
TNS for Solaris: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

CREATE table dataconv.wo_test as
(SELECT WORKORDERID,WONUM,STATUS,STATUSDATE, LOCATION, Rowstamp FROM maximo.workorder)

>desc wo_test
 Name                 
 ----------------------
 WORKORDERID           
 WONUM                 
 STATUS               
 STATUSDATE           
 LOCATION             
 ROWSTAMP             

>EXEC DBMS_STATS.gather_table_stats('DATACONV', 'WO_TEST', estimate_percent => 35);
PL/SQL procedure successfully completed.

>SELECT table_name, num_rows, blocks, empty_blocks, AVG_ROW_LEN FROM user_tables
  2  WHERE table_name = 'WO_TEST';

TABLE_NAME    NUM_ROWS    BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
----------- ---------- ---------- ------------ -----------
WO_TEST        3205786      24302            0          49

>CREATE INDEX WO_STATUSDATE_WONUM ON WO_TEST
  2  ( statusdate ASC, wonum ASC );
Index created.

>EXEC DBMS_STATS.gather_index_stats('DATACONV', 'WO_STATUSDATE_WONUM');
PL/SQL procedure successfully completed.

>SELECT index_name, table_name, uniqueness, blevel, leaf_blocks, distinct_keys,  num_rows
  2    FROM user_indexes
  3  WHERE table_name = 'WO_TEST';

INDEX_NAME            TABLE_NAME  UNIQUENES BLEVEL LEAF_BLOCKS DISTINCT_KEYS  NUM_ROWS
--------------------- ----------- --------- ------ ----------- ------------- ----------
WO_STATUSDATE_WONUM  WO_TEST    NONUNIQUE      2      13954      3200999    3201540

>SELECT COUNT(DISTINCT STATUSDATE)
  2  FROM WO_TEST;

COUNT(DISTINCTSTATUSDATE)
-------------------------
                  283781

>SELECT COUNT(DISTINCT WONUM)
  2  FROM WO_TEST;

COUNT(DISTINCTWONUM)
--------------------
            2960173

>EXPLAIN plan FOR
  2      SELECT workorderid, wonum, rowstamp
  3      FROM  WO_TEST WHERE WONUM = '950054000';
Explained.

>SELECT * FROM table(DBMS_XPLAN.DISPLAY());

--------------------------------------------------------------------
| Id  | Operation            |  Name      | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|  0 | SELECT STATEMENT    |            |    1 |    29 |  2339 |
|*  1 |  TABLE ACCESS FULL  | WO_TEST    |    1 |    29 |  2339 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  1 - filter("WO_TEST"."WONUM"='950054000')

The reason the SKIP SCAN isn't chosen in this case is because there are way too many distinct values of statusdate (283781).  In order to choose a skip scan, the optimizer checks to see how many distinct values there are of the leading columns in the index and then estimates the number of trips through the index it would have to make using that number.  In this case, there would have to be 283781 trips through the index structure and that is ALOT of block accesses (particularly when you figure in the index root and branch block accesses each time through as well)! 

The optimizer looks at the total number of blocks in the table (24302) and wisely determines it's alot more efficient to simply do a full table scan instead.  The full scan would require a substantially lower number of block accesses to answer the query.

So, the bottom-line is that with skip scans, the leading columns in the multi-column index need to be relatively distinct in order for that scan method to appear reasonable and be selected over a full table scan.