程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> oracle走錯索引不出結果

oracle走錯索引不出結果

編輯:Oracle教程

oracle走錯索引不出結果


有一個腳本跑了很久不出結果,優化之後瞬間出結果。原語句如下:
SQL> explain plan for
  2  select *
  3        from crm_dg.tb_ba_channelstaff      a,
  4             crm_dg.tb_ba_subscription_hist b,
  5             crm_dg.tb_cm_serv              c
  6       where a.subs_id = b.subs_id
  7         and b.serv_id = c.serv_id
  8         and a.create_date >= to_date('20150201', 'yyyymmdd')
  9         and c.acc_nbr = '15322926784';

Explained.

Elapsed: 00:00:00.03
SQL> @getplan
'general,outline,starts'

Enter value for plan type:

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1257311340

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                              |     1 |   562 |    12   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                  |                              |     1 |   562 |    12   (0)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN         |                              |     2 |   716 |     8   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID | PROD_INST                    |     1 |   273 |     4   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN           | IX_PROD_INST_NUM             |     1 |       |     3   (0)| 00:00:01 |
|   5 |    BUFFER SORT                 |                              |     2 |   170 |     4   (0)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID| TB_BA_CHANNELSTAFF           |     2 |   170 |     4   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN          | IDX_BA_CHANNELSTAFF_CRT_DATE |     2 |       |     2   (0)| 00:00:01 |
|*  8 |   TABLE ACCESS BY INDEX ROWID  | ORDER_ITEM_HIST              |     1 |   204 |     2   (0)| 00:00:01 |
|*  9 |    INDEX UNIQUE SCAN           | PKH_ORDER_ITEM               |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("ACC_NBR"='15322926784')
   7 - access("A"."CREATE_DATE">=TO_DATE(' 2015-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   8 - filter("SERV_ID"="PROD_INST_ID")
   9 - access("A"."SUBS_ID"="ORDER_ITEM_ID")


......getting segment size......

OWNER                SEGMENT_NAME                   SEGMENT_TYPE           Size(Mb)
-------------------- ------------------------------ -------------------- ----------
CRM_DG               IX_PROD_INST_NUM               INDEX                  602.0625
CRM_DG               IDX_BA_CHANNELSTAFF_CRT_DATE   INDEX                 1799.5625
CRM_DG               PKH_ORDER_ITEM                 INDEX                      6199
CRM_DG               PROD_INST                      TABLE                      5126
CRM_DG               TB_BA_CHANNELSTAFF             TABLE                      7390
CRM_DG               ORDER_ITEM_HIST                TABLE                     48776

6 rows selected.

Elapsed: 00:00:01.26
......getting table infomation......

OWNER                TABLE_NAME                       Size(Mb) PAR DEGREE       NUM_ROWS GLO STATS GATHER TIME
-------------------- ------------------------------ ---------- --- ---------- ---------- --- ------------------
CRM_DG               *PROD_INST                     3958.84835 NO           1   15205690 YES         7.84770833
CRM_DG               PROD_INST                      3958.84835 NO           1   15205690 YES         7.84770833
CRM_DG               *TB_BA_CHANNELSTAFF            5265.49083 NO           1   64956086 YES         102.696563
CRM_DG               TB_BA_CHANNELSTAFF             5265.49083 NO           1   64956086 YES         102.696563
CRM_DG               *ORDER_ITEM_HIST               40876.7086 NO           1  210109488 YES         10.4260532
CRM_DG               ORDER_ITEM_HIST                40876.7086 NO           1  210109488 YES         10.4260532

6 rows selected.

Elapsed: 00:00:01.20
......getting index infomation......

OWNER                INDEX_NAME                     TABLE_NAME                     PAR UNIQUENES DEGREE     INDEX_TYPE LEAF_BLOCKS     BLEVEL CLUSTERING_FACTOR
-------------------- ------------------------------ ------------------------------ --- --------- ---------- ---------- ----------- ---------- ----------------- ----
CRM_DG               IDX_BA_CHANNELSTAFF_CRT_DATE   TB_BA_CHANNELSTAFF             NO  NONUNIQUE 1       NORMAL      84968          2          50669112  36.412511
CRM_DG               IX_PROD_INST_NUM               PROD_INST                      NO  NONUNIQUE 1       NORMAL      37438          2          12501881        100
CRM_DG               PKH_ORDER_ITEM                 ORDER_ITEM_HIST                NO  UNIQUE    1       NORMAL     399394          2         166506822        100


這裡c和b表都是視圖。
最後的結果只有2條記錄。返回數據量少,可以考慮嵌套循環走索引。
IDX_BA_CHANNELSTAFF_CRT_DATE非常差的選擇性,而且將近1.8G非常大,索引掃描單塊讀,非常慢。
為了避免走IDX_BA_CHANNELSTAFF_CRT_DATE,這裡用了no_index這個hint,oracle自動選擇了關聯列的索引,而且是主鍵索引PK_CHANNELSTAFF_SUBS_ID,基本上瞬間出結果。




以下是優化後的語句:

SQL> explain plan for
  2  select /*+leading(c,b) use_nl(c,b) no_index(a,IDX_BA_CHANNELSTAFF_CRT_DATE)*/*
  3        from crm_dg.tb_ba_channelstaff      a,
  4             crm_dg.tb_ba_subscription_hist b,
  5             crm_dg.tb_cm_serv              c
  6       where a.subs_id = b.subs_id
  7         and b.serv_id = c.serv_id
  8         and a.create_date >= to_date('20150201', 'yyyymmdd')
  9         and c.acc_nbr = '15322926784';

Explained.

Elapsed: 00:00:00.09
SQL> @getplan
'general,outline,starts'

Enter value for plan type:

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------

Plan hash value: 3198218290

---------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                    | Rows  | Bytes | Cost (%CPU)| Time  |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                         |     1 |   562 |    39   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |                         |     1 |   562 |    39   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |                         |    16 |  7632 |    18   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| PROD_INST               |     1 |   273 |     4   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IX_PROD_INST_NUM        |     1 |       |     3   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| ORDER_ITEM_HIST         |    16 |  3264 |    14   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | IXH_ORDERITEM_SERVID    |    16 |       |     2   (0)| 00:00:01 |
|*  7 |   TABLE ACCESS BY INDEX ROWID | TB_BA_CHANNELSTAFF      |     1 |    85 |     2   (0)| 00:00:01 |
|*  8 |    INDEX UNIQUE SCAN          | PK_CHANNELSTAFF_SUBS_ID |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("ACC_NBR"='15322926784')
   6 - access("SERV_ID"="PROD_INST_ID")
   7 - filter("A"."CREATE_DATE">=TO_DATE(' 2015-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   8 - access("A"."SUBS_ID"="ORDER_ITEM_ID")
SQL>

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved