[20180725]index skip-scan operation.txt

--//上午看了1会生产系统awr报表,发现一条语句出现问题,选择了错误的执行计划.
--//当时看执行计划有点怪,明明执行计划查询中查询字段出现在索引的第一列,为什么执行计划出现skip-scan.

--//仔细看才发现skip-scan不一定发生在第一列.我单独抽取sql语句访问该表的部分:
--//sql_id = 7n4kqvamms25z

select *  FROM yf_db01 a where
 WHERE a.mbyf in (3, 168, 6)
   AND a.ckbz =  1
   AND a.ckrq >= to_date(:V00001, 'yyyy-mm-dd hh24:mi:ss')
   AND a.ckrq <= to_date(:V00002, 'yyyy-mm-dd hh 24:mi:ss')
   AND a.mbyf =  :V00003;

--//yf_db01表建立索引如下:
I_YF_DB01_CKBZ_TJBZ_TYPB_MBYF 包括字段CKBZ, TJBZ, TYPB, MBYF.
I_YF_DB01_CKRQ_SQYF 包括字段 CKRQ, SQYF

EXPLAIN PLAN FOR
SELECT *
FROM yf_db01 a
WHERE     a.mbyf IN (3, 168, 6)
AND a.ckbz = 1
AND a.ckrq >= TO_DATE ( :V00001, 'yyyy-mm-dd hh24:mi:ss')
AND a.ckrq <= TO_DATE ( :V00002, 'yyyy-mm-dd hh24:mi:ss')
AND a.mbyf = :V00003;

SQL> @ &r/dp '' ''
PLAN_TABLE_OUTPUT
---------------------------
Plan hash value: 2010999957
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                               |     1 |    67 |    30   (0)| 00:00:01 |
|*  1 |  FILTER                      |                               |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| YF_DB01                       |     1 |    67 |    30   (0)| 00:00:01 |
|*  3 |    INDEX SKIP SCAN           | I_YF_DB01_CKBZ_TJBZ_TYPB_MBYF |   370 |       |    10   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / A@SEL$1
   3 - SEL$1 / A@SEL$1
Outline Data

  /*+
      BEGIN_OUTLINE_DATA
      INDEX_SS(@"SEL$1" "A"@"SEL$1" ("YF_DB01"."CKBZ" "YF_DB01"."TJBZ" "YF_DB01"."TYPB" "YF_DB01"."MBYF"))
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.4')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_DATE(:V00002,'yyyy-mm-dd hh 24:mi:ss')>=TO_DATE(:V00001,'yyyy-mm-dd hh24:mi:ss') AND
              (TO_NUMBER(:V00003)=3 OR TO_NUMBER(:V00003)=168 OR TO_NUMBER(:V00003)=6))
   2 - filter("A"."CKRQ">=TO_DATE(:V00001,'yyyy-mm-dd hh24:mi:ss') AND
              "A"."CKRQ"<=TO_DATE(:V00002,'yyyy-mm-dd hh 24:mi:ss'))
   3 - access("A"."CKBZ"=1 AND "A"."MBYF"=TO_NUMBER(:V00003))
       filter("A"."MBYF"=TO_NUMBER(:V00003) AND ("A"."MBYF"=3 OR "A"."MBYF"=6 OR "A"."MBYF"=168))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "A"."SQYF"[NUMBER,22], "A"."SQDH"[NUMBER,22], "A"."MBYF"[NUMBER,22], "A"."SQRQ"[DATE,7],
       "A"."CZGH"[VARCHAR2,10], "A"."TJBZ"[NUMBER,22], "A"."CKBZ"[NUMBER,22], "A"."CKGH"[VARCHAR2,10],
       "A"."CKRQ"[DATE,7], "A"."RKBZ"[NUMBER,22], "A"."RKGH"[VARCHAR2,10], "A"."RKRQ"[DATE,7],
       "A"."TYPB"[NUMBER,22], "A"."BZXX"[VARCHAR2,100], "A"."AUTOCREATE"[NUMBER,22]
   2 - "A"."SQYF"[NUMBER,22], "A"."SQDH"[NUMBER,22], "A"."MBYF"[NUMBER,22], "A"."SQRQ"[DATE,7],
       "A"."CZGH"[VARCHAR2,10], "A"."TJBZ"[NUMBER,22], "A"."CKBZ"[NUMBER,22], "A"."CKGH"[VARCHAR2,10],
       "A"."CKRQ"[DATE,7], "A"."RKBZ"[NUMBER,22], "A"."RKGH"[VARCHAR2,10], "A"."RKRQ"[DATE,7],
       "A"."TYPB"[NUMBER,22], "A"."BZXX"[VARCHAR2,100], "A"."AUTOCREATE"[NUMBER,22]
   3 - "A".ROWID[ROWID,10], "A"."CKBZ"[NUMBER,22], "A"."TJBZ"[NUMBER,22], "A"."TYPB"[NUMBER,22],
       "A"."MBYF"[NUMBER,22]
55 rows selected.

--//可以查询的第一列是CKBZ,也就是查询第2列不在where条件中,这样也可能出现INDEX SKIP SCAN.开始有点不理解.
--//数据分布如下:ckbz=1占大部分.在ckbz=1的情况下走这个索引不合适.

SQL> select CKBZ, TJBZ,count(*) from YF_DB01 group by CKBZ, TJBZ;

      CKBZ       TJBZ   COUNT(*)
---------- ---------- ----------
         1          0          4
         0          0        327
         1          1     103822
         0          1         58



SQL> @ &r/bind_cap_awr 7n4kqvamms25z ''
   SNAP_ID SQL_ID        WAS LAST_CAPTURED       NAME                   POSITION MAX_LENGTH DATATYPE_STR VALUE_STRING
---------- ------------- --- ------------------- -------------------- ---------- ---------- ------------ -------------------
...
     32796 7n4kqvamms25z YES 2018-07-25 08:49:09 :V00001                       1         32 VARCHAR2(32) 2018-07-25 08:30:00
                                                 :V00002                       2         32 VARCHAR2(32) 2018-07-25 09:00:00
                                                 :V00003                       3         32 VARCHAR2(32) 3

--//很明显走I_YF_DB01_CKRQ_SQYF索引效果更好.最后使用sql-profile控制执行计划.

--//exec dbms_stats.gather_table_stats(user,'YF_DB01',cascade => true,method_opt => 'for columns (ckbz,MBYF) size 254 'FOR ALL COLUMNS SIZE repeat ', No_Invalidate  => FALSE);
--//exec dbms_stats.delete_column_stats('portal_his','YF_DB01','SYS_STUYKI9FJXHL_64MMDTLG9Z0E6');
--//这样不用!!