oracle走错索引不出结果

来源：互联网发布：通过网络安装系统编辑：程序博客网时间：2024/04/23 19:59
有一个脚本跑了很久不出结果，优化之后瞬间出结果。原语句如下：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.03SQL> @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.0625CRM_DG               IDX_BA_CHANNELSTAFF_CRT_DATE   INDEX                 1799.5625CRM_DG               PKH_ORDER_ITEM                 INDEX                      6199CRM_DG               PROD_INST                      TABLE                      5126CRM_DG               TB_BA_CHANNELSTAFF             TABLE                      7390CRM_DG               ORDER_ITEM_HIST                TABLE                     487766 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.84770833CRM_DG               PROD_INST                      3958.84835 NO           1   15205690 YES         7.84770833CRM_DG               *TB_BA_CHANNELSTAFF            5265.49083 NO           1   64956086 YES         102.696563CRM_DG               TB_BA_CHANNELSTAFF             5265.49083 NO           1   64956086 YES         102.696563CRM_DG               *ORDER_ITEM_HIST               40876.7086 NO           1  210109488 YES         10.4260532CRM_DG               ORDER_ITEM_HIST                40876.7086 NO           1  210109488 YES         10.42605326 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.412511CRM_DG               IX_PROD_INST_NUM               PROD_INST                      NO  NONUNIQUE 1       NORMAL      37438          2          12501881        100CRM_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.09SQL> @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>
0 0