[Oracle]一个通过添加本地分区索引提高SQL性能的案例(三)
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 159 | 4838 | | |
| 1 | SORT UNIQUE | | 1 | 159 | 4838 | | |
|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID | META_DBQL_TABLE_USAGE_EXP_HST | 1 | 67 | 3 | | |
| 3 | NESTED LOOPS | | 1 | 159 | 4816 | | |
| 4 | NESTED LOOPS | | 18 | 1656 | 4762 | | |
| 5 | PARTITION RANGE ITERATOR | | 1 | 56 | 4746 | KEY | 14 |
|* 6 | TABLE ACCESS FULL | META_DR_QRY_LOG_TGT_ALL_HST | 1 | 56 | 4746 | KEY | 14 |
| 7 | PARTITION RANGE ITERATOR | | 18 | 648 | 16 | KEY | 14 |
|* 8 | TABLE ACCESS BY LOCAL INDEX ROWID| DR_QRY_LOG_EXP_HST | 18 | 648 | 16 | KEY | 14 |
|* 9 | INDEX RANGE SCAN | DR_QRY_LOG_EXP_HST_IDX2 | 31 | | 15 | KEY | 14 |
| 10 | PARTITION RANGE ITERATOR | | 1 | | 2 | KEY | KEY |
|* 11 | INDEX RANGE SCAN | META_DBQL_TUSAGE_EHST_IDX | 1 | | 2 | KEY | KEY |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter((UPPER("TGT"."SYSTEMNAME")<>UPPER("SRC"."SYSTEMNAME") OR
UPPER("TGT"."DATABASENAME")<>UPPER("SRC"."DATABASENAME") OR UPPER("TGT"."TABLENAME")<>UPPER("SRC"."TABLENAME"))
AND "SRC"."SYSTEMNAME"="RL"."SYSTEMNAME")
6 - filter(UPPER("TGT"."SYSTEMNAME")='MOZART' AND UPPER("TGT"."DATABASENAME")='GDW_TABLES' AND
UPPER("TGT"."TABLENAME")='SSA_SLNG_LSTG_MTRC_SD' AND "TGT"."ACCTSTRINGDATE">SYSDATE@!-30 AND "TGT"."SYSTEMNAME"
IS NOT NULL AND "TGT"."DATABASENAME" IS NOT NULL AND "TGT"."TABLENAME" IS NOT NULL)
8 - filter("RL"."SYSTEMNAME"="TGT"."SYSTEMNAME")
9 - access("RL"."STATEMENT_ID"="TGT"."STATEMENT_ID" AND "RL"."ACCTSTRINGDATE">SYSDATE@!-30 AND
"RL"."ACCTSTRINGDATE" IS NOT NULL)
11 - access("SRC"."QUERYID"="RL"."QUERYID" AND "SRC"."ACCTSTRINGDATE"="RL"."ACCTSTRINGDATE")
filter("SRC"."ACCTSTRINGDATE"="TGT"."ACCTSTRINGDATE" AND "SRC"."ACCTSTRINGDATE">SYSDATE@!-30)
从新的的执行计划可以看出，它的第一个NESTED LOOP果然用了最新创建的索引。
下面是执行时间：
[plain]
已用时间: 00: 00: 02.16
两秒种搞定，远远超出他期望的5s ：）
方法总结
NESTED LOOP高效的条件：驱动数据源有限，且被驱动表在连接列上有相应的索引。