|
AAAAAAA_PLANID TB_XX_XXXX_XXXX_XXX NO NONUNIQUE 1 NORMAL 284214 2 31263628 19.8460594
--由上可知，索引IX_AA_AAA_AAAAAAA的集群因子100，distinct_keys/NUM_ROWS*100=100，选择性非常好，类似于主键，基本返回1行。
--其他3个索引非常垃圾，完全可以不用。如果走4个索引的话，体积都大于全表扫描了，而且INDEX RANGE SCAN 是单块读，全表是多块读，还不如走全表了。
--由上面Outline Data信息里面得到如下信息：
BITMAP_TREE(@"DEL$1" "TB_XX_XXXX_XXXX_XXX"@"DEL$1" AND(("TB_XX_XXXX_XXXX_XXX"."LOG_ID")
("TB_XX_XXXX_XXXX_XXX"."PLAN_ID") ("TB_XX_XXXX_XXXX_XXX"."OP_DATE")
("TB_XX_XXXX_XXXX_XXX"."PLAN_RESULT")))
--这里走bitmap肯定是错的，这里可以走索引IX_AA_AAA_AAAAAAA，但如何得到走IX_AA_AAA_AAAAAAA的hint呢？
SQL> explain plan for
2 DELETE /*+index(a,IX_AA_AAA_AAAAAAA)*/ FROM "SPS_SZ"."TB_XX_XXXX_XXXX_XXX" a
3 WHERE "LOG_ID" = :b0
4 AND "PLAN_ID" = :b1
5 AND "LOG_NO" = :b2
6 AND "PLAN_RESULT" = :b3
7 AND "STAFF_CODE" = :b4
8 AND "WORK_STAFF" = :b5
9 AND "OP_DATE" = :b6
10 AND "OP_TIME" = :b7
11 AND "LOG_NOTES" = :b8
12 AND "UNIT_ID" = :b9
13 AND ROWNUM = 1;
Explained.
SQL> @getplan
'general,outline,starts'
Enter value for plan type:outline
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2457304297
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 352 | 744K (1)| 02:53:43 |
| 1 | DELETE | TB_XX_XXXX_XXXX_XXX| | | | |
|* 2 | COUNT STOPKEY | | | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID| TB_XX_XXXX_XXXX_XXX| 1 | 352 | 744K (1)| 02:53:43 |
|* 4 | INDEX RANGE SCAN | IX_AA_AAA_AAAAAAA | 1757K| | 14 (79)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - DEL$1
3 - DEL$1 / A@DEL$1
4 - DEL$1 / A@DEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"DEL$1" "A"@"DEL$1" ("TB_XX_XXXX_XXXX_XXX"."LOG_ID")) --要用INDEX_RS_ASC这个hint，把A替换成表名就是了
OUTLINE_LEAF(@"DEL$1")
ALL_ROWS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=1)
3 - filter("STAFF_CODE"=:B4 AND "WORK_STAFF"=:B5 AND "OP_DATE"=:B6 AND "OP_TIME"=:B7 AND
"LOG_NOTES"=:B8 AND "PLAN_ID"=TO_NUMBER(:B1) AND "LOG_NO"=TO_NUMBER(:B2) AND
"PLAN_RESULT"=TO_NUMBER(:B3) AND "UNIT_ID"=TO_NUMBER(:B9))
4 - access("LOG_ID"=TO_NUMBER(:B0))
Note
-----
- dynamic sampling used for this statement
--使用profile
declare
v_hints sys.sqlprof_attr;
begin
v_hints := sys.sqlprof_attr('INDEX_RS_ASC(@"DEL$1" "TB_XX_XXXX_XXXX_XXX"@"DEL$1" ("TB_XX_XXXX_XXXX_XXX"."LOG_ID"))');
dbms_sqltune.import_sql_profile('DELETE FROM "SPS_SZ"."TB_XX_XXXX_XXXX_XXX" WHERE "LOG_ID" = :b0 AND "PLAN_ID" = :b1 AND "LOG_NO" = :b2 AND "PLAN_RESULT" = :b3 AND "STAFF_CODE" = :b4 AND "WORK_STAFF" = :b5 AND "OP_DATE" = :b6 AND "OP_TIME" = :b7 AND "LOG_NOTES" = :b8 AN |