此脚本不依赖统计信息。

    当执行计划中出现了 INDEX SKIP SCAN，通常说明需要额外添加一个索引。以下脚本抓出走了 INDEX SKIP SCAN 的 SQL。

    1. select c.sql_text, c.sql_id, b.object_name, d.mb
    2.   from v$sql_plan b,
    3.        v$sql c,
    4.        (select owner, segment_name, sum(bytes / 1024 / 1024) mb
    5.           from dba_segments
    6.          group by owner, segment_name) d
    7.  where b.sql_id = c.sql_id
    8.    and b.child_number = c.child_number
    9.    and b.object_owner = 'SCOTT'
    10.    and b.operation = 'INDEX'
    11.    and b.options = 'SKIP SCAN'
    12.    and b.object_owner = d.owner
    13.    and b.object_name = d.segment_name
    14.  order by 4 desc;

    在 Scott 账户中创建如下测试表。

    1. create table t_skip as select * from dba_objects;
    1. Table created.

    在 owner 字段上创建一个索引。

    1. create index idx_owner_id on t_skip(owner,object_id);
    1. Index created.

    对表收集统计信息。

    1.  BEGIN
    2.     DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
    3.                                   tabname          => 'T_SKIP',
    4.                                  estimate_percent => 100,
    5.                           method_opt       => 'for all columns size skewonly',
    6.                                   no_invalidate    => FALSE,
    7.                                   degree           => 1,
    8.                                   cascade          => TRUE);
    9.   END;
    1. PL/SQL procedure successfully completed.

    执行如下 SQL 并且查看执行计划。

    1. select * from t_skip where object_id < 100;
    1. 98 rows selected.
    3. Execution Plan
    4. ----------------------------------------------------------
    5. Plan hash value: 979686564
    7. -------------------------------------------------------------------------------------
    8. | Id |Operation                   |Name        |Rows| Bytes | Cost (%CPU)| Time     |
    9. -------------------------------------------------------------------------------------
    10. |  0 |SELECT STATEMENT            |            |  91|  8827 |    95   (0)| 00:00:02 |
    11. |  1 | TABLE ACCESS BY INDEX ROWID|T_SKIP      |  91|  8827 |    95   (0)| 00:00:02 |
    12. |* 2 |  INDEX SKIP SCAN           |IDX_OWNER_ID|  91|       |    92   (0)| 00:00:02 |
    13. -------------------------------------------------------------------------------------
    15. Predicate Information (identified by operation id):
    16. ---------------------------------------------------
    18.    2 - access("OBJECT_ID"<100)
    19.        filter("OBJECT_ID"<100)

    通过脚本抓出走了 INDEX SKIP SCAN 的 SQL。

    1.  select c.sql_text, c.sql_id, b.object_name, d.mb
    2.     from v$sql_plan b,
    3.          v$sql c,
    4.          (select owner, segment_name, sum(bytes / 1024 / 1024) mb
    5.             from dba_segments
    6.            group by owner, segment_name) d
    7.    where b.sql_id = c.sql_id
    8.      and b.child_number = c.child_number
    9.      and b.object_owner = 'SCOTT'
    10.      and b.operation = 'INDEX'
    11.      and b.options = 'SKIP SCAN'
    12.      and b.object_owner = d.owner
    13.      and b.object_name = d.segment_name
    14.    order by 4 desc;
    1. SQL_TEXT                                   SQL_ID          OBJECT_NAME             MB
    2. ------------------------------------------ --------------- --------------- ----------
    3. select * from t_skip where object_id < 100 0837hu8zxha2y   IDX_OWNER_ID             2