索引优化index skip scan

最新推荐文章于 2023-10-07 11:10:01 发布

congdiku5262

最新推荐文章于 2023-10-07 11:10:01 发布

阅读量263

点赞数

文章标签：数据库

上午碰到一个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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15480802/viewspace-688356/