Oracle走錯索引不出結果

來源:互聯網
上載者:User

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>

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.