ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 索引优化index skip scan

索引优化index skip scan

原创 Linux操作系统 作者:myownstars 时间:2010-12-08 16:18:46 0 删除 编辑
上午碰到一个sql,执行计划使用了Index skip scan,头儿让我看一下能否优化,大致信息如下:
select t.ID,
       t.DO_ID,
       t.DO_CODE,
       t2.CODE,
        *****
        ******            
from justin1 t left join justin2 t2 on t. STAFF_ID = t2.ID
       where
       t.ERROR_STATE = :1 and t.RECEIVE_STATION_ID = :2
       and t.CONSIGNEE_ID = :3
       and (t.BELONG = :4 or t.BELONG = :5)
       and (t.DO_STATE = :6 or t.DO_STATE = :7)
       and (t.FINANCE_STATE = :8 or t.FINANCE_STATE = :9)
       and (t.DELIVER_STAT E = :10 or t.DELIVER_STATE = :11)
       and (t.DELIVER_TYPE = :12 or t.DELIVER_TYPE = :13)
       and t.BACK_ORDER_LOCK is null;
执行计划
Operation                         |  Name                        |  Rows | Bytes|  Cost  | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| SELECT STATEMENT                  |                              |       |      |   1045 |       |       |
| HASH JOIN OUTER                   |                              |    43 |   15K|   1045 |       |       |
|  TABLE ACCESS BY INDEX ROWID      |justin1              |    43 |   13K|   1039 |       |       |
|  TABLE ACCESS FULL                |justin2                     |   648 |   31K|      5 |       |       |
|   INDEX SKIP SCAN                 |IDX_justin1_RCMD4COL      |     2K|      |    313 |       |   
首先查看一下表justin1上各个字段的选择率:
SQL> select count(*),count(distinct f.driver_order),count(distinct f.receive_station_id),count(distinct f.consignee_id),count(distinct f.more_boxes) from justin1 f;

  COUNT(*) COUNT(DISTINCT F.DRIVER_ORDER) COUNT(DISTINCTF.RECEIVE_STATIO COUNT(DISTINCTF.CONSIGNEE_ID) COUNT(DISTINCTF.MORE_BOXES)
---------- ----------------------------- ------------------------------ ----------------------------- ---------------------------
   1017576                           321                             40                             5                           2

SQL> select count(distinct t.error_state),count(distinct t.do_state),count(distinct t.belong)  from justin1 t;

COUNT(DISTINCTT.ERROR_STATE) COUNT(DISTINCTT.DO_STATE) COUNT(DISTINCTT.BELONG)
---------------------------- ------------------------- -----------------------
                           2                         3                       7

SQL> select count(distinct t.finance_state),count(distinct t.deliver_state),count(distinct t.deliver_type)  from justin1 t;

COUNT(DISTINCTT.FINANCE_STATE) COUNT(DISTINCTT.DELIVER_STATE) COUNT(DISTINCTT.DELIVER_TYPE)
------------------------------ ------------------------------ -----------------------------
                             4                              5                             2
可以看到justin1总共有  1017576   条记录,但是选择率最高的字段DRIVER_ORDER却只有321个distinct值,选择率奇差无比,只能选择建立组合索引。
再来看看使用到的索引IDX_justin1_RCMD4COL的结构,建立在以下四个字段上(DRIVER_ORDER, RECEIVE_STATION_ID, CONSIGNEE_ID, MORE_BOXES)。
而出现问题的sql的where条件中却没有引用到DRIVER_ORDER列,故会执行计划中会出现index skip scan。
现在考虑建立一个新的组合索引,依据where条件中出现的列以及其选择率,最终新建索引为,
SQL> alter session enable resumable;

Session altered

SQL> alter session set workarea_size_policy=manual;

Session altered

SQL> alter session set sort_area_size=104857600;

Session altered

SQL> alter session set db_file_multiblock_read_count = 128;

Session altered

SQL> create index IDX_justin1__RCMD3COL on justin1 (RECEIVE_STATION_ID, CONSIGNEE_ID, belong)
  2    tablespace justin1 online;
注意: 此为线上库,一定要加online关键字。

再看执行计划,cost从1045变为202,执行时间也从12秒缩减至3秒,鉴于该sql每小时执行一次,个人觉得此索引还是有添加的必要;
----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                          |    88 | 32296 |   202   (1)| 00:00:03 |
|*  1 |  HASH JOIN OUTER              |                          |    88 | 32296 |   202   (1)| 00:00:03 |
|   2 |   INLIST ITERATOR             |                          |       |       |            |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID| justin1         |    88 | 27896 |   196   (0)| 00:00:03 |
|*  4 |     INDEX RANGE SCAN          | IDX_Tjustin1_RCMD3COL |   684 |       |     4   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL           | justin2                |   652 | 32600 |     5   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15480802/viewspace-688356/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-03-18

  • 博文量
    375
  • 访问量
    3158146