理解：复合索引，前导列或前导列组合的结果可选择性小，才会发生索引跳跃，这样后面的列单独作为条件也会走这个复合索引

现象：SQL执行一次耗时6秒

分析

1.  查看sql的执行计划，发现TMS_TRAN_SEND_NOTICE和TMS_TRAN_INFO都是全表扫描，其中TMS_TRAN_INFO表的记录只有几百条，走全表扫描比索引扫描更合适。而TMS_TRAN_SEND_NOTICE表超过4000条，应该走索引扫描的。

2.  执行计划ID 5步骤对应列TRAN_SEND_NO，发现该列有索引的，但是没有使用到该索引，具体查下来发现是复合索引

CREATE UNIQUE INDEX TMS.TMS_TRAN_SEND_PK ON TMS.TMS_TRAN_SEND_NOTICE

(NOTICE_NO, TRAN_SEND_NO)

3.  查询复合索引第一列的count(distinct(notice_no))接近表总行数count(*)，前导列的选择性太大，不会发生索引跳跃，所以第二列单独作为条件将不会再使用该复合索引。

处理过程

重新对第二列单独创建索引

CREATE INDEX TMS.IX_TRAN_SEND_NO ON TMS.TMS_TRAN_SEND_NOTICE

(TRAN_SEND_NO)

结果

执行速度只需要2秒了，TRAN_SEND_NO列走了新的索引

 


原来的执行计划
SQL_ID  31hdwz7ghsqd, child number 0
-------------------------------------
select t.id,
       t.tran_no,
       t.carriers_id,
       c.name,
       c.contact,
       c.contact_name,
       t.carriers_house_id,
       h.address,
       t.node_id,
       n.node_name,
       get_node_detail(n.id) node_detail,
       p.placename,
       p.adrress placeadrress,
       p.contact placecontact,
       p.contact_num placecontact_num,
       t.driver_id,
       d.driver_name,
       d.driver_num,
       d.driver_card,
       d.car_num,
       d.hkormacao_car_num,
       t.driver_id_two,
       d2.driver_name driver_name_two,
       d2.driver_num driver_num_two,
       d2.driver_card driver_card_two,
       d2.car_num car_num_two,
       t.factory_id,
       t.receive_name,
       t.receive_user_name,
       t.car_lock,
       t.to_factory_date,
       t.from_factory_date,
       t.quality_user_name,
       t.quality_date,
       t.load_user_name,
       t.update_by,
       to_char(t.update_date, 'yyyy-mm-dd') update_date,
       t.state,
       (select r.receipt_no
          from tms_receipt_tran_info r
         where r.tran_no = t.tran_no
           and rownum = 1) as receipt_no,
       (select count(*)
          from tms_tran_send_notice e, packing_list_view@dmt plv
         where e.tran_send_no = t.tran_no
           and e.notice_no = plv.packing_list_id) noticenum,
       (select count(*)
          from tms_book_tran_send_notice e, sp_overall_notice@dmt plv
         where e.tran_send_no = t.tran_no
           and e.notice_no = plv.noticeid) booknoticenum,
       get_tran_leave_way(t.tran_no, t.state) leaveway,
       get_pallet_num(t.tran_no) palletnum,
       t.booked_time,
       t.input_pallet_num,
       t.book_state,
       t.book_car_kind,
       t.t_combine_car
  from (select *
          from tms_tran_info t
         where t.create_date <= sysdate
           and t.create_date >= sysdate - 10) t,
       tms_carriers_info c,
       tms_carriers_house_info h,
       tms_node_info n,
       tms_driver_info d,
       tms_driver_info d2,
       tms_place_info p
 where t.carriers_id = c.id(+)
   and t.carriers_house_id = h.id(+)
   and t.node_id = n.id(+)
   and t.driver_id = d.id(+)
   and t.factory_id = p.id(+)
   and t.driver_id_two = d2.id(+)
 order by t.update_date desc


 
Plan hash value: 3601169143
 
------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                              |       |       |    75 (100)|          |        |      |
|*  1 |  COUNT STOPKEY                      |                              |       |       |            |          |        |      |
|*  2 |   TABLE ACCESS FULL                 | TMS_RECEIPT_TRAN_INFO        |     1 |    32 |     3   (0)| 00:00:01 |        |      |
|   3 |  SORT AGGREGATE                     |                              |     1 |   190 |            |          |        |      |
|   4 |   NESTED LOOPS                      |                              |    15 |  2850 |   112   (0)| 00:00:02 |        |      |
|*  5 |    TABLE ACCESS FULL                | TMS_TRAN_SEND_NOTICE         |     5 |   190 |   102   (0)| 00:00:02 |        |      |
|   6 |    REMOTE                           | PACKING_LIST_VIEW            |     3 |   456 |     2   (0)| 00:00:01 |    DMT | R->S |
|   7 |  SORT AGGREGATE                     |                              |     1 |   184 |            |          |        |      |
|   8 |   NESTED LOOPS                      |                              |     4 |   736 |    10   (0)| 00:00:01 |        |      |
|*  9 |    INDEX RANGE SCAN                 | TMS_BOOK_TRAN_SEND_NOTICE_PK |     4 |   128 |     2   (0)| 00:00:01 |        |      |
|  10 |    REMOTE                           | SP_OVERALL_NOTICE            |     1 |   152 |     2   (0)| 00:00:01 |    DMT | R->S |
|  11 |  SORT ORDER BY                      |                              |     1 |   480 |    75   (2)| 00:00:01 |        |      |
|* 12 |   FILTER                            |                              |       |       |            |          |        |      |
|  13 |    NESTED LOOPS OUTER               |                              |     1 |   480 |    74   (0)| 00:00:01 |        |      |
|  14 |     NESTED LOOPS OUTER              |                              |     1 |   421 |    73   (0)| 00:00:01 |        |      |
|  15 |      NESTED LOOPS OUTER             |                              |     1 |   364 |    72   (0)| 00:00:01 |        |      |
|  16 |       NESTED LOOPS OUTER            |                              |     1 |   306 |    71   (0)| 00:00:01 |        |      |
|  17 |        NESTED LOOPS OUTER           |                              |     1 |   251 |    70   (0)| 00:00:01 |        |      |
|  18 |         NESTED LOOPS OUTER          |                              |     1 |   138 |    69   (0)| 00:00:01 |        |      |
|* 19 |          TABLE ACCESS FULL          | TMS_TRAN_INFO                |     1 |   124 |    68   (0)| 00:00:01 |        |      |
|  20 |          TABLE ACCESS BY INDEX ROWID| TMS_NODE_INFO                |     1 |    14 |     1   (0)| 00:00:01 |        |      |
|* 21 |           INDEX UNIQUE SCAN         | PK_TMS_NODE_INFO             |     1 |       |     0   (0)|          |        |      |
|  22 |         TABLE ACCESS BY INDEX ROWID | TMS_PLACE_INFO               |     1 |   113 |     1   (0)| 00:00:01 |        |      |
|* 23 |          INDEX UNIQUE SCAN          | PK_TMS_ADDRESS_INFO          |     1 |       |     0   (0)|          |        |      |
|  24 |        TABLE ACCESS BY INDEX ROWID  | TMS_CARRIERS_HOUSE_INFO      |     1 |    55 |     1   (0)| 00:00:01 |        |      |
|* 25 |         INDEX UNIQUE SCAN           | PK_TMS_CARRIERS_HOUSE_INFO   |     1 |       |     0   (0)|          |        |      |
|  26 |       TABLE ACCESS BY INDEX ROWID   | TMS_CARRIERS_INFO            |     1 |    58 |     1   (0)| 00:00:01 |        |      |
|* 27 |        INDEX UNIQUE SCAN            | PK_TMS_CARRIERS_INFO         |     1 |       |     0   (0)|          |        |      |
|  28 |      TABLE ACCESS BY INDEX ROWID    | TMS_DRIVER_INFO              |     1 |    57 |     1   (0)| 00:00:01 |        |      |
|* 29 |       INDEX UNIQUE SCAN             | PK_TMS_DRIVER_INFO           |     1 |       |     0   (0)|          |        |      |
|  30 |     TABLE ACCESS BY INDEX ROWID     | TMS_DRIVER_INFO              |     1 |    59 |     1   (0)| 00:00:01 |        |      |
|* 31 |      INDEX UNIQUE SCAN              | PK_TMS_DRIVER_INFO           |     1 |       |     0   (0)|          |        |      |
------------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM=1)
   2 - filter("R"."TRAN_NO"=:B1)
   5 - filter("E"."TRAN_SEND_NO"=:B1)
   9 - access("E"."TRAN_SEND_NO"=:B1)
  12 - filter(SYSDATE@!-10<=SYSDATE@!)
  19 - filter(("T"."CREATE_DATE">=SYSDATE@!-10 AND "T"."CREATE_DATE"<=SYSDATE@!))
  21 - access("N"."ID"=TO_NUMBER("T"."NODE_ID"))
  23 - access("T"."FACTORY_ID"="P"."ID")
  25 - access("T"."CARRIERS_HOUSE_ID"="H"."ID")
  27 - access("T"."CARRIERS_ID"="C"."ID")
  29 - access("T"."DRIVER_ID_TWO"="D2"."ID")
  31 - access("T"."DRIVER_ID"="D"."ID")
 


 
 
添加索引后的执行计划
SQL_ID  31hdwz7ghsqdq, child number 0
-------------------------------------
select t.id,
       t.tran_no,
       t.carriers_id,
       c.name,
       c.contact,
       c.contact_name,
       t.carriers_house_id,
       h.address,
       t.node_id,
       n.node_name,
       get_node_detail(n.id) node_detail,
       p.placename,
       p.adrress placeadrress,
       p.contact placecontact,
       p.contact_num placecontact_num,
       t.driver_id,
       d.driver_name,
       d.driver_num,
       d.driver_card,
       d.car_num,
       d.hkormacao_car_num,
       t.driver_id_two,
       d2.driver_name driver_name_two,
       d2.driver_num driver_num_two,
       d2.driver_card driver_card_two,
       d2.car_num car_num_two,
       t.factory_id,
       t.receive_name,
       t.receive_user_name,
       t.car_lock,
       t.to_factory_date,
       t.from_factory_date,
       t.quality_user_name,
       t.quality_date,
       t.load_user_name,
       t.update_by,
       to_char(t.update_date, 'yyyy-mm-dd') update_date,
       t.state,
       (select r.receipt_no
          from tms_receipt_tran_info r
         where r.tran_no = t.tran_no
           and rownum = 1) as receipt_no,
       (select count(*)
          from tms_tran_send_notice e, packing_list_view@dmt plv
         where e.tran_send_no = t.tran_no
           and e.notice_no = plv.packing_list_id) noticenum,
       (select count(*)
          from tms_book_tran_send_notice e, sp_overall_notice@dmt plv
         where e.tran_send_no = t.tran_no
           and e.notice_no = plv.noticeid) booknoticenum,
       get_tran_leave_way(t.tran_no, t.state) leaveway,
       get_pallet_num(t.tran_no) palletnum,
       t.booked_time,
       t.input_pallet_num,
       t.book_state,
       t.book_car_kind,
       t.t_combine_car
  from (select *
          from tms_tran_info t
         where t.create_date <= sysdate
           and t.create_date >= sysdate - 10) t,
       tms_carriers_info c,
       tms_carriers_house_info h,
       tms_node_info n,
       tms_driver_info d,
       tms_driver_info d2,
       tms_place_info p
 where t.carriers_id = c.id(+)
   and t.carriers_house_id = h.id(+)
   and t.node_id = n.id(+)
   and t.driver_id = d.id(+)
   and t.factory_id = p.id(+)
   and t.driver_id_two = d2.id(+)
 order by t.update_date desc


 
Plan hash value: 2119257045
 
------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                              |       |       |    75 (100)|          |        |      |
|*  1 |  COUNT STOPKEY                      |                              |       |       |            |          |        |      |
|*  2 |   TABLE ACCESS FULL                 | TMS_RECEIPT_TRAN_INFO        |     1 |    32 |     3   (0)| 00:00:01 |        |      |
|   3 |  SORT AGGREGATE                     |                              |     1 |   190 |            |          |        |      |
|   4 |   NESTED LOOPS                      |                              |    15 |  2850 |    12   (0)| 00:00:01 |        |      |
|   5 |    TABLE ACCESS BY INDEX ROWID      | TMS_TRAN_SEND_NOTICE         |     5 |   190 |     2   (0)| 00:00:01 |        |      |
|*  6 |     INDEX RANGE SCAN                | IX_TRAN_SEND_NO              |     5 |       |     1   (0)| 00:00:01 |        |      |
|   7 |    REMOTE                           | PACKING_LIST_VIEW            |     3 |   456 |     2   (0)| 00:00:01 |    DMT | R->S |
|   8 |  SORT AGGREGATE                     |                              |     1 |   184 |            |          |        |      |
|   9 |   NESTED LOOPS                      |                              |     4 |   736 |    10   (0)| 00:00:01 |        |      |
|* 10 |    INDEX RANGE SCAN                 | TMS_BOOK_TRAN_SEND_NOTICE_PK |     4 |   128 |     2   (0)| 00:00:01 |        |      |
|  11 |    REMOTE                           | SP_OVERALL_NOTICE            |     1 |   152 |     2   (0)| 00:00:01 |    DMT | R->S |
|  12 |  SORT ORDER BY                      |                              |     1 |   480 |    75   (2)| 00:00:01 |        |      |
|* 13 |   FILTER                            |                              |       |       |            |          |        |      |
|  14 |    NESTED LOOPS OUTER               |                              |     1 |   480 |    74   (0)| 00:00:01 |        |      |
|  15 |     NESTED LOOPS OUTER              |                              |     1 |   421 |    73   (0)| 00:00:01 |        |      |
|  16 |      NESTED LOOPS OUTER             |                              |     1 |   364 |    72   (0)| 00:00:01 |        |      |
|  17 |       NESTED LOOPS OUTER            |                              |     1 |   306 |    71   (0)| 00:00:01 |        |      |
|  18 |        NESTED LOOPS OUTER           |                              |     1 |   251 |    70   (0)| 00:00:01 |        |      |
|  19 |         NESTED LOOPS OUTER          |                              |     1 |   138 |    69   (0)| 00:00:01 |        |      |
|* 20 |          TABLE ACCESS FULL          | TMS_TRAN_INFO                |     1 |   124 |    68   (0)| 00:00:01 |        |      |
|  21 |          TABLE ACCESS BY INDEX ROWID| TMS_NODE_INFO                |     1 |    14 |     1   (0)| 00:00:01 |        |      |
|* 22 |           INDEX UNIQUE SCAN         | PK_TMS_NODE_INFO             |     1 |       |     0   (0)|          |        |      |
|  23 |         TABLE ACCESS BY INDEX ROWID | TMS_PLACE_INFO               |     1 |   113 |     1   (0)| 00:00:01 |        |      |
|* 24 |          INDEX UNIQUE SCAN          | PK_TMS_ADDRESS_INFO          |     1 |       |     0   (0)|          |        |      |
|  25 |        TABLE ACCESS BY INDEX ROWID  | TMS_CARRIERS_HOUSE_INFO      |     1 |    55 |     1   (0)| 00:00:01 |        |      |
|* 26 |         INDEX UNIQUE SCAN           | PK_TMS_CARRIERS_HOUSE_INFO   |     1 |       |     0   (0)|          |        |      |
|  27 |       TABLE ACCESS BY INDEX ROWID   | TMS_CARRIERS_INFO            |     1 |    58 |     1   (0)| 00:00:01 |        |      |
|* 28 |        INDEX UNIQUE SCAN            | PK_TMS_CARRIERS_INFO         |     1 |       |     0   (0)|          |        |      |
|  29 |      TABLE ACCESS BY INDEX ROWID    | TMS_DRIVER_INFO              |     1 |    57 |     1   (0)| 00:00:01 |        |      |
|* 30 |       INDEX UNIQUE SCAN             | PK_TMS_DRIVER_INFO           |     1 |       |     0   (0)|          |        |      |
|  31 |     TABLE ACCESS BY INDEX ROWID     | TMS_DRIVER_INFO              |     1 |    59 |     1   (0)| 00:00:01 |        |      |
|* 32 |      INDEX UNIQUE SCAN              | PK_TMS_DRIVER_INFO           |     1 |       |     0   (0)|          |        |      |
------------------------------------------------------------------------------------------------------------------------------------
 
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM=1)
   2 - filter("R"."TRAN_NO"=:B1)
   6 - access("E"."TRAN_SEND_NO"=:B1)
  10 - access("E"."TRAN_SEND_NO"=:B1)
  13 - filter(SYSDATE@!-10<=SYSDATE@!)
  20 - filter(("T"."CREATE_DATE">=SYSDATE@!-10 AND "T"."CREATE_DATE"<=SYSDATE@!))
  22 - access("N"."ID"=TO_NUMBER("T"."NODE_ID"))
  24 - access("T"."FACTORY_ID"="P"."ID")
  26 - access("T"."CARRIERS_HOUSE_ID"="H"."ID")
  28 - access("T"."CARRIERS_ID"="C"."ID")
  30 - access("T"."DRIVER_ID_TWO"="D2"."ID")
  32 - access("T"."DRIVER_ID"="D"."ID")