[20180725]index skip-scan operation.txt

[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');
--//這樣不用!!