SQL> select handler_id,end_time,rule_no from PA18CLM.T_DISPATCH_TASK_DETAIL where rownum <2;
HANDLER_ID END_TIME RULE_NO
------------------------------ ------------------- ---------------------------------
actjck-00014 11-07-2012 06:31:52
---> 为了调整性能，调整了该表上的一个索引。如下：
------------------------------ ------------------- ---------------------------------
actjck-00014 11-07-2012 06:31:52
---> 为了调整性能，调整了该表上的一个索引。如下：
CREATE INDEX PA18CLM.IND_T_TASK_DETAIL_TM_ID_RLNO ON PA18CLM.T_DISPATCH_TASK_DETAIL
(END_TIME,HANDLER_ID, END_TIME, RULE_NO)
(END_TIME,HANDLER_ID, END_TIME, RULE_NO)
调整为：
DROP INDEX PA18CLM.IND_T_TASK_DETAIL_TM_ID_RLNO;
create index PA18CLM.IND_T_TASK_DETAIL_TM_ID_RLNO2 on PA18CLM.T_DISPATCH_TASK_DETAIL (HANDLER_ID,END_TIME, RULE_NO)
tablespace PCISAUTOCLM_IDX
Compute Statistics;
tablespace PCISAUTOCLM_IDX
Compute Statistics;
++++++++++++++++++++++++++++++++++
执行计划改变如下：
Plan for Before index changed. .default{font: 8pt "tahoma";color:#000080;} .header{font: 8pt "tahoma";color:#000000;} .noborder{border-style.:none; border-bottom:0;border-left:0;border-right:0; border-top:0;padding-left:2px;padding-right:2px;} .normalborder {border-top:none; border-left:none; border-right:none;border-bottom:none;padding-left:2px;padding-right:2px;}
| Plan | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 9 INSERT STATEMENT CHOOSE Cost: 3,815 Bytes: 512 Cardinality: 4 IO Cost: 3,815 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 8 FILTER TO_DATE(:Z)<=TO_DATE(:Z) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 7 NESTED LOOPS Cost: 3,815 Bytes: 512 Cardinality: 4 IO Cost: 3,815 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 4 NESTED LOOPS Cost: 3,803 Bytes: 2,840 Cardinality: 40 IO Cost: 3,803 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 1 INDEX FULL SCAN CQMSDATA.PK_TCADU_USER_POSITION [Analyzed] Bytes: 156 Cardinality: 12 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 3 TABLE ACCESS BY INDEX ROWID PA18CLM.T_DISPATCH_TASK_DETAIL [Analyzed] Cost: 317 Bytes: 174 Cardinality: 3 IO Cost: 317 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 2 INDEX RANGE SCAN PA18CLM.IND_T_TASK_DETAIL_TM_ID_RLNO [Analyzed] "B"."END_TIME">=:Z AND "B"."HANDLER_ID"="C"."LOW_USER_NAME" AND "B"."END_TIME"<=:Z "B"."HANDLER_ID"="C"."LOW_USER_NAME" Cost: 1,050 Cardinality: 84,235 IO Cost: 1,050 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 6 TABLE ACCESS BY INDEX ROWID PA18CLM.T_DISPATCH_TASK [Analyzed] "A"."TASK_TACHE_ID"='23' AND ("B"."DEAL_FLAG"='7' OR "B"."DEAL_FLAG"='8') OR "A"."TASK_TACHE_ID"='6-d' AND ("B"."DEAL_FLAG"='7' OR "B"."DEAL_FLAG"='8') OR "A"."TASK_TACHE_ID"='34' AND ("B"."DEAL_FLAG"='28' OR "B"."DEAL_FLAG"='29' OR "B"."DEAL_FLAG"='3' OR "B"."DEAL_FLAG"='31' OR "B"."DEAL_FLAG"='8') OR "A"."TASK_TACHE_ID"='35' AND ("B"."DEAL_FLAG"='28' OR "B"."DEAL_FLAG"='29' OR "B"."DEAL_FLAG"='30' OR "B"."DEAL_FLAG"='8') OR "A"."TASK_TACHE_ID"='13' AND ("B"."DEAL_FLAG"='15' OR "B"."DEAL_FLAG"='29' OR "B"."DEAL_FLAG"='30' OR "B"."DEAL_FLAG"='31' OR "B"."DEAL_FLAG"='4' OR "B"."DEAL_FLAG"='9') OR "A"."TASK_TACHE_ID"='21' AND ("B"."DEAL_FLAG"='26' OR "B"."DEAL_FLAG"='8') OR "A"."TASK_TACHE_ID"='33' AND ("B"."DEAL_FLAG"='7' OR "B"."DEAL_FLAG"='8') Cost: 1 Bytes: 57 Cardinality: 1 IO Cost: 1 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
5 INDEX UNIQUE SCAN PA18CLM.PK_T_DISPATCH_TASK [Analyzed] "B"."TASK_NO"="A"."TASK_NO" Cardinality: 1 Plan for After index changed.
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/22578826/viewspace-739923/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
聚簇因子的实验 (转发）
请登录后发表评论
登录
全部评论
