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
---&gt 為了調整效能，調整了該表上的一個索引。如下：

CREATE INDEX PA18CLM.IND_T_TASK_DETAIL_TM_ID_RLNO ON PA18CLM.T_DISPATCH_TASK_DETAIL
(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;

++++++++++++++++++++++++++++++++++

執行計劃改變如下：

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;}

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.

Plan
9 INSERT STATEMENT CHOOSE Cost: 43 Bytes: 508 Cardinality: 4 IO Cost: 43
	8 FILTER TO_DATE(:Z)<=TO_DATE(:Z)
		7 NESTED LOOPS Cost: 43 Bytes: 508 Cardinality: 4 IO Cost: 43
			4 NESTED LOOPS Cost: 30 Bytes: 3,080 Cardinality: 44 IO Cost: 30
				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: 3 Bytes: 228 Cardinality: 4 IO Cost: 3
					2 INDEX RANGE SCAN PA18CLM.IND_T_TASK_DETAIL_TM_ID_RLNO2 [Analyzed] "B"."HANDLER_ID"="C"."LOW_USER_NAME" AND "B"."END_TIME">=:Z AND "B"."END_TIME"<=:Z Cost: 1 Cardinality: 6 IO Cost: 1
			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

調整index 後，cost 降低？

相關文章