有如下执行计划（从 AWR 中抓出）。

    1. select * from table(dbms_xplan.display_awr('3m7f7xdpkdrtv', NULL, NULL, 'ALL')) ;
    1. SQL_ID 3m7f7xdpkdrtv
    2. --------------------
    3. select a.int_id,a.zh_label,a0.zh_label from VIEW_RMS_POS_PORT a inner
    4. join (select int_id,zh_label from RMS_LOCALNET_POS where
    5. stateflag=:"SYS_B_00") a0 on to_char(a.up_pos_id)=to_char(a0.int_id)
    6. where   a0.zh_label in (:"SYS_B_01",:"SYS_B_02",:"SYS_B_03",:"SYS_B_04",
    7. :"SYS_B_05",:"SYS_B_06",:"SYS_B_07",:"SYS_B_08",:"SYS_B_09",:"SYS_B_10")
    8.  and  :"SYS_B_11"=:"SYS_B_12" and (a.zh_label in  (:"SYS_B_13")) and
    9. a.stateflag=:"SYS_B_14"
    11. Plan hash value: 494215470
    13. ------------------------------------------------------------------------------
    14. | Id  | Operation                     | Name                 | Rows  | Bytes |
    15. ------------------------------------------------------------------------------
    16. |   0 | SELECT STATEMENT              |                      |       |       |
    17. |   1 |  FILTER                       |                      |       |       |
    18. |   2 |   NESTED LOOPS                |                      |     1 |    94 |
    19. |   3 |    INDEX RANGE SCAN           | RMS_JK_POS_PORT_PK   |     1 |    43 |
    20. |   4 |    TABLE ACCESS BY INDEX ROWID| RMS_LOCALNET_POS     |     1 |    51 |
    21. |   5 |     INDEX SKIP SCAN           | RMS_LOCALNET_POS_PUI |    35 |       |
    22. ------------------------------------------------------------------------------
    24. Query Block Name / Object Alias (identified by operation id):
    25. -------------------------------------------------------------
    27.    1 - SEL$D26F4AE5
    28.    3 - SEL$D26F4AE5 / RMS_JK_POS_PORT@SEL$2
    29.    4 - SEL$D26F4AE5 / RMS_LOCALNET_POS@SEL$3
    30.    5 - SEL$D26F4AE5 / RMS_LOCALNET_POS@SEL$3

    该 SQL 在 AWR 中属于 TOP SQL，执行计划走了嵌套循环，被驱动表走了 INDEX SKIP SCAN。在第 5 章中我们讲到，嵌套循环被驱动表只能走 INDEX UNIQUE SCAN 或者 INDEX RANGE SCAN。为什么嵌套循环被驱动表不能走 INDEX SKIP SCAN 呢？这是因为嵌套循环会传值，从驱动表传值给被驱动表，传值相当于过滤条件。有过滤条件但是走了 INDEX SKIP SCAN，很有可能是被驱动表连接列没包含在索引中，或者连接列在索引中放错了位置。

    被驱动表连接列是 int_id，现在我们查看索引 RMS_LOCALNET_POS_PUI 具体情况。

    1. SELECT DBMS_METADATA.GET_DDL('INDEX','RMS_LOCALNET_POS_PUI','HBRMW6') FROM DUAL;
    1.   CREATE INDEX "HBRMW6"."RMS_LOCALNET_POS_PUI" ON "HBRMW6"."RMS_LOCALNET_POS" ("PRO_TASK_ID", "STATEFLAG")
    2.   PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
    3.   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    4.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    5.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    6.   TABLESPACE "HBRMW_TBS"

    被驱动表索引中竟然没有包含连接列。这说明该执行计划是错误的。我们将连接列和过滤列组合起来创建组合索引，从而解决该 SQL 性能问题。