SQL優(yōu)化 - 表連接的 索引覆蓋優(yōu)化方式
表連接的索引覆蓋考慮：
1 驅(qū)動表和被驅(qū)動表的索引需要覆蓋到where條件涉及的列，和連接條件的列，還有select查詢的列
2 當驅(qū)動表和被驅(qū)動表的where條件(除了連接列)選擇性好，返回的結果集少的時候，where條件的列考慮放到索引列的前邊：連接的列放到索引列的后邊
3 當被驅(qū)動表的where條件選擇性不好的時候，但是驅(qū)動表只有少量的結果集的時候，執(zhí)行計劃可能考慮NL，連接列考慮放到索引列的前邊：where列考慮放到索引列的后邊
4 如果被驅(qū)動表除了連接列之外，沒有其它條件，HASH連接的方式只能是全表掃描，NL連接才考慮使用連接列上的索引
5 查詢列可以考慮放在索引的最后
6 如果是NL的連接，如果被驅(qū)動表連接列選擇性很好，也可以考慮使用連接列的單列索引
7 如果是HASH的連接，通常被驅(qū)動表的連接列放到組合索引where列的后面；
8 如果不是高并發(fā)執(zhí)行的業(yè)務SQL，都不用考慮建索引，維護索引也是需要成本的。
需要優(yōu)化的SQL類似如下
with aa as (
select t.* from XXXX_NN.XXXXXX_XXXXXXXt2 t
where t.yyyy_id in ( select xxx_id from XXXX_NN.XXXXXX_XXXXXt1 n where n.flags = 11 and n.ver_flags =2)
and t.ver_flags = 2
and t.flags in (0, 10)
and t.xxxxbbs is not null
) select t.*,rowid from XXXX_NN.XXXXXX_XXXXXXXt2 t
where xxx_id in(select xxx_id from aa)
;
Plan hash value: 3977745688
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 1682K(100)| | | |
|* 1 | HASH JOIN RIGHT SEMI | | 3630K| 495M| 39M| 1682K (1)| 05:36:35 | | |
| 2 | VIEW | VW_NSO_1 | 1667K| 20M| | 645K (2)| 02:09:08 | | |
|* 3 | HASH JOIN | | 1667K| 79M| 7752K| 645K (2)| 02:09:08 | | |
| 4 | PARTITION LIST ALL| | 293K| 4304K| | 130K (1)| 00:26:04 | 1 | 116 |
|* 5 | TABLE ACCESS FULL| XXXXXX_XXXXXt1 | 293K| 4304K| | 130K (1)| 00:26:04 | 1 | 116 |
| 6 | PARTITION LIST ALL| | 19M| 664M| | 470K (2)| 01:34:07 | 1 | 116 |
|* 7 | TABLE ACCESS FULL| XXXXXX_XXXXXXXt2 | 19M| 664M| | 470K (2)| 01:34:07 | 1 | 116 |
| 8 | PARTITION LIST ALL | | 84M| 10G| | 470K (2)| 01:34:02 | 1 | 116 |
| 9 | TABLE ACCESS FULL | XXXXXX_XXXXXXXt2 | 84M| 10G| | 470K (2)| 01:34:02 | 1 | 116 |
------------------------------------------------------------------------------------------------------------------
1 - access("xxx_id"="xxx_id")
3 - access("T"."yyyy_id"="xxx_id")
5 - filter(("N"."FLAGS"=11 AND "N"."VER_FLAGS"=2))
7 - filter(("T"."VER_FLAGS"=2 AND INTERNAL_FUNCTION("T"."FLAGS") AND "T"."xxxxbbs" IS NOT NULL))
-- SQL執(zhí)行的等待事件：
EVENT TOTAL WAIT CLASS
---------------------------------------- -------- ---------------
db file scattered read 434 User_IO
read by other session 257 User_IO
CPU 122 CPU
db file parallel read 42 User_IO
latch: cache buffers lru chain 27 Other
gc cr multi block request 19 Cluster
db file sequential read 14 User_IO
gc current block 2-way 3 Cluster
gc current grant busy 2 Cluster
gc buffer busy acquire 2 Cluster
latch: object queue header operation 2 Other
gc cr disk read 1 Cluster
gc cr block busy 1 Cluster
gc cr grant 2-way 1 Cluster
SQL執(zhí)行統(tǒng)計信息，邏輯讀看著不多才350萬，但是物理讀超乎想像，已經(jīng)不夠顯示了
CPU(MS) ELA(MS) DISK GET ROWS ROWS APPLI(MS) CONCUR(MS) CLUSTER(MS) USER_IO(MS) PLSQL JAVA
EXEC PRE EXEC PRE EXEC PRE EXEC PRE EXEC PRE EXEC PRE FETCH PER EXEC PER EXEC PER EXEC PER EXEC PER EXEC PER EXEC SQL_PROFILE
---- -------- -------- -------- --------- -------- --------- --------- ---------- ----------- ----------- -------- -------- ---------------
1 87,163 928,482 ######## 3,516,627 0 0 0 12 30,378 735,061 0 0
PLAN CHI USER CPU(MS) ELA(MS) DISK GET ROWS ROWS APPLI(MS) CONCUR(MS) CLUSTER(MS) USER_IO(MS) FIRST_LOAD_TIME
EXEC HASH VALUE NUM NAME PRE EXEC PRE EXEC PRE EXEC PRE EXEC PRE EXEC PRE FETCH PER EXEC PER EXEC PER EXEC PER EXEC LAST_LOAD_TIME
---- ---------- ---- ------- -------- -------- -------- ---------- -------- --------- --------- ---------- ----------- ----------- ----------------------
1 3977745688 0 XXXX_NN 87,163 928,482 ######## 3,516,627 0 0 0 12 30,378 735,061 10-28/16:4.10-28/16:4
優(yōu)化方案：使用索引覆蓋
create index XXXX_NN.IND_NET_LFUSE_VER521 on XXXX_NN.XXXXXX_XXXXXt1(ver_flags,flags,xxx_id) parallel 16 online;
alter index XXXX_NN.IND_NET_LFUSE_VER521 parallel 1;
drop index XXXX_NN.IND_NET_LFUSE_VER52;
create index XXXX_NN.IND_LS_METROLOGY_SITE on XXXX_NN.XXXXXX_XXXXXXXt2(ver_flags,flags,xxxxbbs,yyyy_id,xxx_id) parallel 16 online;
alter index XXXX_NN.IND_LS_METROLOGY_SITE parallel 1;
drop index XXXX_NN.IND_LS_METROLOGY_SITEID;
-- 使用HINT 優(yōu)化之后：
-- set autot traceonly實際執(zhí)行 查看統(tǒng)計信息
with aa as (
select t.* from XXXX_NN.XXXXXX_XXXXXXXt2 t
where t.yyyy_id in ( select xxx_id from XXXX_NN.XXXXXX_XXXXXt1 n where n.flags = 11 and n.ver_flags =2)
and t.ver_flags = 2
and t.flags in (0, 10)
and t.xxxxbbs is not null
) select /*+index(t PK2_XXXXXX_XXXXXXXt2)*/t.*,rowid from XXXX_NN.XXXXXX_XXXXXXXt2 t
where xxx_id in(select xxx_id from aa);
no rows selected
Elapsed: 00:01:33.04
實際執(zhí)行
Plan hash value: 3739262284
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 8277K(100)| | | | 0 |00:01:32.97 | 129K| 129K| | | |
| 1 | NESTED LOOPS | | 1 | | | | | | | | 0 |00:01:32.97 | 129K| 129K| | | |
| 2 | NESTED LOOPS | | 1 | 3544K| 479M| | 8277K (1)| 27:35:36 | | | 0 |00:01:32.97 | 129K| 129K| | | |
| 3 | VIEW | VW_NSO_1 | 1 | 1613K| 20M| | 196K (1)| 00:39:23 | | | 0 |00:01:32.97 | 129K| 129K| | | |
| 4 | HASH UNIQUE | | 1 | 1613K| 76M| | | | | | 0 |00:01:32.97 | 129K| 129K| 823K | 823K | |
|* 5 | HASH JOIN | | 1 | 1613K| 76M| 7432K| 196K (1)| 00:39:23 | | | 0 |00:01:32.97 | 129K| 129K| 23M | 3383K | 37M (0)|
|* 6 | INDEX RANGE SCAN | IND_NET_LFUSE_VER521 | 1 | 281K| 4126K| | 1116 (1)| 00:00:14 | | | 772K|00:00:02.25 | 3174 | 3171 | | | |
| 7 | INLIST ITERATOR | | 1 | | | | | | | | 17M|00:01:20.70 | 126K| 126K| | | |
|* 8 | INDEX RANGE SCAN | IND_LS_METROLOGY_SITE | 2 | 20M| 672M| | 150K (1)| 00:30:05 | | | 17M|00:01:16.51 | 126K| 126K| | | |
|* 9 | INDEX RANGE SCAN | PK2_XXXXXX_XXXXXXXt2 | 0 | 2 | | | 3 (0)| 00:00:01 | | | 0 |00:00:00.01 | 0 | 0 | | | |
| 10 | TABLE ACCESS BY GLOBAL INDEX ROWID| XXXXXX_XXXXXXXt2 | 0 | 2 | 258 | | 5 (0)| 00:00:01 | ROWID | ROWID | 0 |00:00:00.01 | 0 | 0 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5 - access("T"."yyyy_id"="xxx_id")
6 - access("N"."VER_FLAGS"=2 AND "N"."FLAGS"=11)
8 - access("T"."VER_FLAGS"=2 AND (("T"."FLAGS"=0 OR "T"."FLAGS"=10)))
filter("T"."xxxxbbs" IS NOT NULL)
9 - access("xxx_id"="xxx_id")
-- 執(zhí)行計劃中 索引 IND_LS_METROLOGY_SITE 的訪問使用了"INLIST ITERATOR"的方式， starts為2 索引訪問了2次，是因為 條件 flags in (0, 10) 所以定位不同葉子塊訪問了2次
2 兩個很大基數(shù)的分區(qū)表關聯(lián)，實際關聯(lián)返回行數(shù)卻很少(上面SQL執(zhí)行的時候已經(jīng)沒有返回)，CBO卻沒辦法確切的評估返回行數(shù)：
3 優(yōu)化之前的執(zhí)行計劃評估返回167萬，實際返回2行，優(yōu)化之后的執(zhí)行計劃評估返回161萬行，實際返回0行
4 如果兩個1億數(shù)據(jù)量的表做關聯(lián)，1個表全是奇數(shù)，1個表全是偶數(shù)，實際返回行數(shù)確實為0，只能實際的關聯(lián)匹配之后才知道實際返回行數(shù)
驗證測試索引覆蓋：
連接查詢使用索引覆蓋測試
-- 構造數(shù)據(jù)
create table t1 as select * from dba_objects;
create table t2 as select * from dba_objects;
create index IDX_T11 on t1(NAMESPACE,data_object_id,object_id);
create index idx_t22 on t2(status,object_id);
-- SQL
select t1.object_id from t1 where t1.NAMESPACE in(4,5) and
data_object_id in(select object_id from t2 where STATUS='11')
;
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6036 | 123K| 81 (2)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 6036 | 123K| 81 (2)| 00:00:01 |
| 2 | INLIST ITERATOR | | | | | |
|* 3 | INDEX RANGE SCAN| IDX_T11 | 6036 | 78468 | 22 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_T22 | 22099 | 172K| 58 (0)| 00:00:01 |
------------------------------------------------------------------------------
-- 構造隨機數(shù)據(jù)
drop table t1 purge;
drop table t2 purge;
create table t1 as select * from dba_objects;
create table t2 as select * from dba_objects;
update t1 set object_id=ceil(dbms_random.value(1,100000)),DATA_OBJECT_ID=ceil(dbms_random.value(1,100000));
update t2 set object_id=ceil(dbms_random.value(1,100000)),DATA_OBJECT_ID=ceil(dbms_random.value(1,100000));
update t1 set SUBOBJECT_NAME=to_char(ceil(dbms_random.value(1,60000))) where SUBOBJECT_NAME is null and rownum<=80000;
update t1 set STATUS=to_char(ceil(mod(OBJECT_ID,dbms_random.value(1,14))));
update t2 set STATUS=to_char(ceil(mod(OBJECT_ID,dbms_random.value(1,14))));
update t1 set NAMESPACE=to_char(ceil(mod(OBJECT_ID,dbms_random.value(1,14))));
update t2 set NAMESPACE=to_char(ceil(mod(OBJECT_ID,dbms_random.value(1,14))));
update t2 set STATUS='11' where status<>'11' and rownum<=20000;
update t2 set NAMESPACE=4 where NAMESPACE<>4 and rownum<=60000;
update t1 set NAMESPACE=2 where NAMESPACE<>2 and rownum<=50000;
update t1 set STATUS='10' where STATUS not in ('0','10') and rownum<=60000;
select object_id,DATA_OBJECT_ID,count(1) from t1 group by object_id,DATA_OBJECT_ID having count(1)>1;
select object_id,DATA_OBJECT_ID,count(1) from t2 group by object_id,DATA_OBJECT_ID having count(1)>1;
select NAMESPACE,count(1) from t2 group by NAMESPACE order by 2;
select STATUS,count(1) from t2 group by STATUS order by 2;
select NAMESPACE,count(1) from t1 group by NAMESPACE;
select STATUS,count(1) from t1 group by STATUS order by 2;
-- 構建索引
-- 唯一索引
create unique index pk_t1 on t1(object_id,DATA_OBJECT_ID);
create unique index pk_t2 on t2(object_id,DATA_OBJECT_ID);
-- 使用索引覆蓋: t2
create index ix_t2_SNO on t2(STATUS,NAMESPACE,object_id);
create index ix_t2_OSN on t2(object_id,STATUS,NAMESPACE);
-- 使用索引覆蓋: t1
create index ix_t1_NSSDO on t1(NAMESPACE,STATUS,SUBOBJECT_NAME,DATA_OBJECT_ID,object_id);
create index ix_t1_DNSSO on t1(DATA_OBJECT_ID,NAMESPACE,STATUS,SUBOBJECT_NAME,object_id);
create index ix_t1_NDO on t1(NAMESPACE,DATA_OBJECT_ID,OBJECT_ID);
測試1： 類似于生產(chǎn)上的SQL
with /*test1*/aa as (
select t.object_id from t1 t
where t.DATA_OBJECT_ID in (select object_id from t2 n where n.STATUS = '11' and n.NAMESPACE =5)
and t.NAMESPACE = 2 and t.STATUS in ('0','10') and t.SUBOBJECT_NAME is not null
) select w.*,rowid from t1 w
where object_id in(select object_id from aa);
-- autot執(zhí)行計劃-實際執(zhí)行并沒有產(chǎn)生這個執(zhí)行計劃
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 870 | 112K| 543 (1)| 00:00:01 |
|* 1 | HASH JOIN RIGHT SEMI | | 870 | 112K| 543 (1)| 00:00:01 |
| 2 | VIEW | VW_NSO_1 | 572 | 7436 | 116 (1)| 00:00:01 |
|* 3 | HASH JOIN | | 572 | 18876 | 116 (1)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IX_T2_SNO | 503 | 5533 | 3 (0)| 00:00:01 |
|* 5 | INDEX FAST FULL SCAN| IX_T1_DNSSO | 39891 | 857K| 113 (1)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T1 | 91338 | 10M| 427 (1)| 00:00:01 |
---------------------------------------------------------------------------------------
-- 實際執(zhí)行計劃1： 第一次執(zhí)行 with內(nèi)部NL 外部HASH
SQL_ID 6btba7a5ahzyk, child number 0
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 543 (100)| | 59 |00:00:00.05 | 1600 | 1583 | | | |
|* 1 | HASH JOIN RIGHT SEMI| | 1 | 870 | 112K| 543 (1)| 00:00:01 | 59 |00:00:00.05 | 1600 | 1583 | 2168K| 2168K| 1469K (0)|
| 2 | VIEW | VW_NSO_1 | 1 | 572 | 7436 | 116 (1)| 00:00:01 | 29 |00:00:00.01 | 58 | 49 | | | |
| 3 | NESTED LOOPS | | 1 | 572 | 18876 | 116 (1)| 00:00:01 | 29 |00:00:00.01 | 58 | 49 | | | |
|* 4 | INDEX RANGE SCAN | IX_T2_SNO | 1 | 503 | 5533 | 3 (0)| 00:00:01 | 54 |00:00:00.01 | 2 | 2 | | | |
|* 5 | INDEX RANGE SCAN | IX_T1_DNSSO | 54 | 1 | 22 | 113 (1)| 00:00:01 | 29 |00:00:00.01 | 56 | 47 | | | |
| 6 | TABLE ACCESS FULL | T1 | 1 | 91338 | 10M| 427 (1)| 00:00:01 | 91338 |00:00:00.02 | 1542 | 1534 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - access("OBJECT_ID"="OBJECT_ID")
4 - access("N"."STATUS"='11' AND "N"."NAMESPACE"=5)
5 - access("T"."DATA_OBJECT_ID"="OBJECT_ID" AND "T"."NAMESPACE"=2)
filter(("T"."SUBOBJECT_NAME" IS NOT NULL AND INTERNAL_FUNCTION("T"."STATUS")))
-- 實際執(zhí)行計劃2： 第二次執(zhí)行 with內(nèi)部NL 外部使用NL
SQL_ID 6btba7a5ahzyk, child number 1
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 61 (100)| | 59 |00:00:00.01 | 155 | | | |
| 1 | NESTED LOOPS | | 1 | 59 | 7847 | 61 (2)| 00:00:01 | 59 |00:00:00.01 | 155 | | | |
| 2 | NESTED LOOPS | | 1 | 59 | 7847 | 61 (2)| 00:00:01 | 59 |00:00:00.01 | 96 | | | |
| 3 | VIEW | VW_NSO_1 | 1 | 1 | 13 | 57 (0)| 00:00:01 | 29 |00:00:00.01 | 58 | | | |
| 4 | HASH UNIQUE | | 1 | 1 | 33 | | | 29 |00:00:00.01 | 58 | 2170K| 2170K| 2555K (0)|
| 5 | NESTED LOOPS | | 1 | 1 | 33 | 57 (0)| 00:00:01 | 29 |00:00:00.01 | 58 | | | |
|* 6 | INDEX RANGE SCAN | IX_T2_SNO | 1 | 54 | 594 | 3 (0)| 00:00:01 | 54 |00:00:00.01 | 2 | | | |
|* 7 | INDEX RANGE SCAN | IX_T1_DNSSO | 54 | 1 | 22 | 1 (0)| 00:00:01 | 29 |00:00:00.01 | 56 | | | |
|* 8 | INDEX RANGE SCAN | PK_T1 | 29 | 2 | | 1 (0)| 00:00:01 | 59 |00:00:00.01 | 38 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID| T1 | 59 | 59 | 7080 | 3 (0)| 00:00:01 | 59 |00:00:00.01 | 59 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
6 - access("N"."STATUS"='11' AND "N"."NAMESPACE"=5)
7 - access("T"."DATA_OBJECT_ID"="OBJECT_ID" AND "T"."NAMESPACE"=2)
filter(("T"."SUBOBJECT_NAME" IS NOT NULL AND INTERNAL_FUNCTION("T"."STATUS")))
8 - access("OBJECT_ID"="OBJECT_ID")
測試2： 在外部HINT使用主鍵索引
with /*test2*/aa as (
select t.object_id from t1 t
where t.DATA_OBJECT_ID in (select object_id from t2 n where n.STATUS = '11' and n.NAMESPACE =5)
and t.NAMESPACE = 2 and t.STATUS in ('0','10') and t.SUBOBJECT_NAME is not null
) select /*+index(w PK_T1)*/w.*,rowid from t1 w
where object_id in(select object_id from aa);
-- 執(zhí)行計劃: 執(zhí)行執(zhí)行是測試1中的第2種執(zhí)行計劃
SQL_ID a1gzm69hdn5rr, child number 0
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1833 (100)| | 59 |00:00:00.01 | 155 | 136 | | | |
| 1 | NESTED LOOPS | | 1 | 870 | 112K| 1833 (1)| 00:00:01 | 59 |00:00:00.01 | 155 | 136 | | | |
| 2 | NESTED LOOPS | | 1 | 1144 | 112K| 1833 (1)| 00:00:01 | 59 |00:00:00.01 | 96 | 78 | | | |
| 3 | VIEW | VW_NSO_1 | 1 | 572 | 7436 | 116 (1)| 00:00:01 | 29 |00:00:00.01 | 58 | 49 | | | |
| 4 | HASH UNIQUE | | 1 | 572 | 18876 | | | 29 |00:00:00.01 | 58 | 49 | 2170K| 2170K| 1374K (0)|
| 5 | NESTED LOOPS | | 1 | 572 | 18876 | 116 (1)| 00:00:01 | 29 |00:00:00.01 | 58 | 49 | | | |
|* 6 | INDEX RANGE SCAN | IX_T2_SNO | 1 | 503 | 5533 | 3 (0)| 00:00:01 | 54 |00:00:00.01 | 2 | 2 | | | |
|* 7 | INDEX RANGE SCAN | IX_T1_DNSSO | 54 | 1 | 22 | 113 (1)| 00:00:01 | 29 |00:00:00.01 | 56 | 47 | | | |
|* 8 | INDEX RANGE SCAN | PK_T1 | 29 | 2 | | 1 (0)| 00:00:01 | 59 |00:00:00.01 | 38 | 29 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID| T1 | 59 | 2 | 240 | 3 (0)| 00:00:01 | 59 |00:00:00.01 | 59 | 58 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
6 - access("N"."STATUS"='11' AND "N"."NAMESPACE"=5)
7 - access("T"."DATA_OBJECT_ID"="OBJECT_ID" AND "T"."NAMESPACE"=2)
filter(("T"."SUBOBJECT_NAME" IS NOT NULL AND INTERNAL_FUNCTION("T"."STATUS")))
8 - access("OBJECT_ID"="OBJECT_ID")
測試3： 在外部HINT使用主鍵索引 指定hash連接
with /*test3*/aa as (
select t.object_id from t1 t
where t.DATA_OBJECT_ID in (select object_id from t2 n where n.STATUS = '11' and n.NAMESPACE =5)
and t.NAMESPACE = 2 and t.STATUS in ('0','10') and t.SUBOBJECT_NAME is not null
) select /*+index(w PK_T1) use_hash(w) */w.*,rowid from t1 w
where object_id in(select object_id from aa);
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 91663 (100)| | 59 |00:00:00.40 | 91949 | 1700 | | | |
|* 1 | HASH JOIN RIGHT SEMI | | 1 | 870 | 112K| 91663 (1)| 00:00:04 | 59 |00:00:00.40 | 91949 | 1700 | 2168K| 2168K| 1483K (0)|
| 2 | VIEW | VW_NSO_1 | 1 | 572 | 7436 | 116 (1)| 00:00:01 | 29 |00:00:00.03 | 416 | 0 | | | |
|* 3 | HASH JOIN | | 1 | 572 | 18876 | 116 (1)| 00:00:01 | 29 |00:00:00.03 | 416 | 0 | 2293K| 2293K| 1591K (0)|
|* 4 | INDEX RANGE SCAN | IX_T2_SNO | 1 | 503 | 5533 | 3 (0)| 00:00:01 | 54 |00:00:00.01 | 2 | 0 | | | |
|* 5 | INDEX FAST FULL SCAN | IX_T1_DNSSO | 1 | 39891 | 857K| 113 (1)| 00:00:01 | 60774 |00:00:00.01 | 414 | 0 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 91338 | 10M| 91547 (1)| 00:00:04 | 91338 |00:00:00.34 | 91533 | 1700 | | | |
| 7 | INDEX FULL SCAN | PK_T1 | 1 | 91338 | | 254 (1)| 00:00:01 | 91338 |00:00:00.02 | 257 | 210 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - access("OBJECT_ID"="OBJECT_ID")
3 - access("T"."DATA_OBJECT_ID"="OBJECT_ID")
4 - access("N"."STATUS"='11' AND "N"."NAMESPACE"=5)
5 - filter(("T"."SUBOBJECT_NAME" IS NOT NULL AND "T"."NAMESPACE"=2 AND INTERNAL_FUNCTION("T"."STATUS")))
由于外部t1表除了object_id并沒有其它條件，如果使用HASH，將對t1進行查詢?nèi)淼慕Y果集，再做hash半連接
又指定的索引的HINT，所有全部掃描索引之后又回表，邏輯讀高達9萬，比全表掃描的成本1542高59倍
測試4：在with內(nèi)部使用hash
with /*test4*/aa as (
select /*+use_hash(t)*/t.object_id from t1 t
where t.DATA_OBJECT_ID in (select object_id from t2 n where n.STATUS = '11' and n.NAMESPACE =5)
and t.NAMESPACE = 2 and t.STATUS in ('0','10') and t.SUBOBJECT_NAME is not null
) select w.*,rowid from t1 w
where object_id in(select object_id from aa);
-- autot 執(zhí)行計劃--
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 870 | 112K| 543 (1)| 00:00:01 |
|* 1 | HASH JOIN RIGHT SEMI | | 870 | 112K| 543 (1)| 00:00:01 |
| 2 | VIEW | VW_NSO_1 | 572 | 7436 | 116 (1)| 00:00:01 |
|* 3 | HASH JOIN | | 572 | 18876 | 116 (1)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IX_T2_SNO | 503 | 5533 | 3 (0)| 00:00:01 |
|* 5 | INDEX FAST FULL SCAN| IX_T1_DNSSO | 39891 | 857K| 113 (1)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T1 | 91338 | 10M| 427 (1)| 00:00:01 |
---------------------------------------------------------------------------------------
1 - access("OBJECT_ID"="OBJECT_ID")
3 - access("T"."DATA_OBJECT_ID"="OBJECT_ID")
4 - access("N"."STATUS"='11' AND "N"."NAMESPACE"=5)
5 - filter("T"."SUBOBJECT_NAME" IS NOT NULL AND "T"."NAMESPACE"=2 AND
("T"."STATUS"='0' OR "T"."STATUS"='10'))
實際執(zhí)行計劃1：
SQL_ID dzs43s0uy04da, child number 0
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 543 (100)| | 59 |00:00:00.08 | 1958 | 1944 | | | |
|* 1 | HASH JOIN RIGHT SEMI | | 1 | 870 | 112K| 543 (1)| 00:00:01 | 59 |00:00:00.08 | 1958 | 1944 | 2168K| 2168K| 1441K (0)|
| 2 | VIEW | VW_NSO_1 | 1 | 572 | 7436 | 116 (1)| 00:00:01 | 29 |00:00:00.03 | 416 | 410 | | | |
|* 3 | HASH JOIN | | 1 | 572 | 18876 | 116 (1)| 00:00:01 | 29 |00:00:00.03 | 416 | 410 | 2293K| 2293K| 1539K (0)|
|* 4 | INDEX RANGE SCAN | IX_T2_SNO | 1 | 503 | 5533 | 3 (0)| 00:00:01 | 54 |00:00:00.01 | 2 | 2 | | | |
|* 5 | INDEX FAST FULL SCAN| IX_T1_DNSSO | 1 | 39891 | 857K| 113 (1)| 00:00:01 | 60774 |00:00:00.01 | 414 | 408 | | | |
| 6 | TABLE ACCESS FULL | T1 | 1 | 91338 | 10M| 427 (1)| 00:00:01 | 91338 |00:00:00.02 | 1542 | 1534 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - access("OBJECT_ID"="OBJECT_ID")
3 - access("T"."DATA_OBJECT_ID"="OBJECT_ID")
4 - access("N"."STATUS"='11' AND "N"."NAMESPACE"=5)
5 - filter(("T"."SUBOBJECT_NAME" IS NOT NULL AND "T"."NAMESPACE"=2 AND INTERNAL_FUNCTION("T"."STATUS")))
實際執(zhí)行計劃2： with 內(nèi)部使用hash,外部使用索引
SQL_ID dzs43s0uy04da, child number 1
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 204 (100)| | 59 |00:00:00.04 | 513 | | | |
| 1 | NESTED LOOPS | | 1 | 59 | 7847 | 204 (1)| 00:00:01 | 59 |00:00:00.04 | 513 | | | |
| 2 | NESTED LOOPS | | 1 | 59 | 7847 | 204 (1)| 00:00:01 | 59 |00:00:00.04 | 454 | | | |
| 3 | VIEW | VW_NSO_1 | 1 | 29 | 377 | 116 (1)| 00:00:01 | 29 |00:00:00.03 | 416 | | | |
| 4 | HASH UNIQUE | | 1 | 29 | 957 | | | 29 |00:00:00.03 | 416 | 2170K| 2170K| 2538K (0)|
|* 5 | HASH JOIN | | 1 | 29 | 957 | 116 (1)| 00:00:01 | 29 |00:00:00.03 | 416 | 2293K| 2293K| 1574K (0)|
|* 6 | INDEX RANGE SCAN | IX_T2_SNO | 1 | 54 | 594 | 3 (0)| 00:00:01 | 54 |00:00:00.01 | 2 | | | |
|* 7 | INDEX FAST FULL SCAN | IX_T1_DNSSO | 1 | 39891 | 857K| 113 (1)| 00:00:01 | 60774 |00:00:00.02 | 414 | | | |
|* 8 | INDEX RANGE SCAN | PK_T1 | 29 | 2 | | 1 (0)| 00:00:01 | 59 |00:00:00.01 | 38 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID| T1 | 59 | 2 | 240 | 3 (0)| 00:00:01 | 59 |00:00:00.01 | 59 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
5 - access("T"."DATA_OBJECT_ID"="OBJECT_ID")
6 - access("N"."STATUS"='11' AND "N"."NAMESPACE"=5)
7 - filter(("T"."SUBOBJECT_NAME" IS NOT NULL AND "T"."NAMESPACE"=2 AND INTERNAL_FUNCTION("T"."STATUS")))
8 - access("OBJECT_ID"="OBJECT_ID")
內(nèi)部hash的實際邏輯讀416，比之前NL的連接方式的邏輯讀58高7倍
測試5： 在外部HINT使用主鍵索引 在with內(nèi)部使用hash
-- 5.1 不指定使用的索引覆蓋
with /*test5.1*/aa as (
select /*+use_hash(t)*/t.object_id from t1 t
where t.DATA_OBJECT_ID in (select object_id from t2 n where n.STATUS = '11' and n.NAMESPACE =5)
and t.NAMESPACE = 2 and t.STATUS in ('0','10') and t.SUBOBJECT_NAME is not null
) select /*+index(w PK_T1)*/w.*,rowid from t1 w
where object_id in(select object_id from aa);
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1833 (100)| | 59 |00:00:00.12 | 513 | 536 | | | |
| 1 | NESTED LOOPS | | 1 | 870 | 112K| 1833 (1)| 00:00:01 | 59 |00:00:00.12 | 513 | 536 | | | |
| 2 | NESTED LOOPS | | 1 | 1144 | 112K| 1833 (1)| 00:00:01 | 59 |00:00:00.08 | 454 | 461 | | | |
| 3 | VIEW | VW_NSO_1 | 1 | 572 | 7436 | 116 (1)| 00:00:01 | 29 |00:00:00.05 | 416 | 410 | | | |
| 4 | HASH UNIQUE | | 1 | 572 | 18876 | | | 29 |00:00:00.05 | 416 | 410 | 2170K| 2170K| 1335K (0)|
|* 5 | HASH JOIN | | 1 | 572 | 18876 | 116 (1)| 00:00:01 | 29 |00:00:00.05 | 416 | 410 | 2293K| 2293K| 1590K (0)|
|* 6 | INDEX RANGE SCAN | IX_T2_SNO | 1 | 503 | 5533 | 3 (0)| 00:00:01 | 54 |00:00:00.01 | 2 | 2 | | | |
|* 7 | INDEX FAST FULL SCAN | IX_T1_DNSSO | 1 | 39891 | 857K| 113 (1)| 00:00:01 | 60774 |00:00:00.02 | 414 | 408 | | | |
|* 8 | INDEX RANGE SCAN | PK_T1 | 29 | 2 | | 1 (0)| 00:00:01 | 59 |00:00:00.03 | 38 | 51 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID| T1 | 59 | 2 | 240 | 3 (0)| 00:00:01 | 59 |00:00:00.04 | 59 | 75 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5 - access("T"."DATA_OBJECT_ID"="OBJECT_ID")
6 - access("N"."STATUS"='11' AND "N"."NAMESPACE"=5)
7 - filter(("T"."SUBOBJECT_NAME" IS NOT NULL AND "T"."NAMESPACE"=2 AND INTERNAL_FUNCTION("T"."STATUS")))
8 - access("OBJECT_ID"="OBJECT_ID")
和上面測試4的第2個實際執(zhí)行計劃一致
-- 5.2 指定不使用5.1使用的覆蓋索引
with /*test5.2*/aa as (
select /*+use_hash(t) no_index(t IX_T1_DNSSO) no_index(@SEL$2 n IX_T2_SNO)*/t.object_id from t1 t
where t.DATA_OBJECT_ID in (select object_id from t2 n where n.STATUS = '11' and n.NAMESPACE =5)
and t.NAMESPACE = 2 and t.STATUS in ('0','10') and t.SUBOBJECT_NAME is not null
) select /*+index(w PK_T1)*/w.*,rowid from t1 w
where object_id in(select object_id from aa);
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1906 (100)| | 59 |00:00:00.06 | 789 | 672 | | | |
| 1 | NESTED LOOPS | | 1 | 870 | 112K| 1906 (1)| 00:00:01 | 59 |00:00:00.06 | 789 | 672 | | | |
| 2 | NESTED LOOPS | | 1 | 1144 | 112K| 1906 (1)| 00:00:01 | 59 |00:00:00.06 | 730 | 672 | | | |
| 3 | VIEW | VW_NSO_1 | 1 | 572 | 7436 | 188 (1)| 00:00:01 | 29 |00:00:00.06 | 692 | 672 | | | |
| 4 | HASH UNIQUE | | 1 | 572 | 18876 | | | 29 |00:00:00.06 | 692 | 672 | 2170K| 2170K| 1377K (0)|
|* 5 | HASH JOIN | | 1 | 572 | 18876 | 188 (1)| 00:00:01 | 29 |00:00:00.06 | 692 | 672 | 2293K| 2293K| 1576K (0)|
|* 6 | INDEX FAST FULL SCAN | IX_T2_OSN | 1 | 503 | 5533 | 75 (0)| 00:00:01 | 54 |00:00:00.02 | 278 | 272 | | | |
|* 7 | INDEX FAST FULL SCAN | IX_T1_NSSDO | 1 | 39891 | 857K| 113 (1)| 00:00:01 | 60774 |00:00:00.02 | 414 | 400 | | | |
|* 8 | INDEX RANGE SCAN | PK_T1 | 29 | 2 | | 1 (0)| 00:00:01 | 59 |00:00:00.01 | 38 | 0 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID| T1 | 59 | 2 | 240 | 3 (0)| 00:00:01 | 59 |00:00:00.01 | 59 | 0 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5 - access("T"."DATA_OBJECT_ID"="OBJECT_ID")
6 - filter(("N"."NAMESPACE"=5 AND "N"."STATUS"='11'))
7 - filter(("T"."SUBOBJECT_NAME" IS NOT NULL AND "T"."NAMESPACE"=2 AND INTERNAL_FUNCTION("T"."STATUS")))
8 - access("OBJECT_ID"="OBJECT_ID")
1 雖然同樣是使用索引覆蓋，但是索引的使用訪問方式，實際消耗的邏輯讀卻不一樣
2 對t2表的索引使用方式從范圍掃描，變成了索引快速全掃，從直接定位訪問變成了filter的執(zhí)行計劃
3 with內(nèi)部的t1表索引有一個和t2表關聯(lián)的列,DATA_OBJECT_ID,一個查詢返回的列obect_id，還有3個where條件中的列，一共有5個列，都是使用快速全掃過濾的方式
測試6： 在外部HINT使用主鍵索引 在with內(nèi)部使用hash 在外部使用HASH
with /*test6*/aa as (
select /*+use_hash(t)*/t.object_id from t1 t
where t.DATA_OBJECT_ID in (select object_id from t2 n where n.STATUS = '11' and n.NAMESPACE =5)
and t.NAMESPACE = 2 and t.STATUS in ('0','10') and t.SUBOBJECT_NAME is not null
) select /*+index(w PK_T1) use_hash(w)*/w.*,rowid from t1 w
where object_id in(select object_id from aa);
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 91663 (100)| | 59 |00:00:00.40 | 91949 | 1700 | | | |
|* 1 | HASH JOIN RIGHT SEMI | | 1 | 870 | 112K| 91663 (1)| 00:00:04 | 59 |00:00:00.40 | 91949 | 1700 | 2168K| 2168K| 1483K (0)|
| 2 | VIEW | VW_NSO_1 | 1 | 572 | 7436 | 116 (1)| 00:00:01 | 29 |00:00:00.03 | 416 | 0 | | | |
|* 3 | HASH JOIN | | 1 | 572 | 18876 | 116 (1)| 00:00:01 | 29 |00:00:00.03 | 416 | 0 | 2293K| 2293K| 1591K (0)|
|* 4 | INDEX RANGE SCAN | IX_T2_SNO | 1 | 503 | 5533 | 3 (0)| 00:00:01 | 54 |00:00:00.01 | 2 | 0 | | | |
|* 5 | INDEX FAST FULL SCAN | IX_T1_DNSSO | 1 | 39891 | 857K| 113 (1)| 00:00:01 | 60774 |00:00:00.01 | 414 | 0 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 91338 | 10M| 91547 (1)| 00:00:04 | 91338 |00:00:00.34 | 91533 | 1700 | | | |
| 7 | INDEX FULL SCAN | PK_T1 | 1 | 91338 | | 254 (1)| 00:00:01 | 91338 |00:00:00.02 | 257 | 210 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - access("OBJECT_ID"="OBJECT_ID")
3 - access("T"."DATA_OBJECT_ID"="OBJECT_ID")
4 - access("N"."STATUS"='11' AND "N"."NAMESPACE"=5)
5 - filter(("T"."SUBOBJECT_NAME" IS NOT NULL AND "T"."NAMESPACE"=2 AND INTERNAL_FUNCTION("T"."STATUS")))
和之前測試3，對外部表hash的邏輯讀一致，邏輯讀高得無法想像
測試7： 在with內(nèi)部使用hash 但是被驅(qū)動表變成子查詢中的表
-- 7.1 把namespace條件改成=14 返回結果較少
with /*test7.1*/aa as (
select t.object_id from t1 t
where t.DATA_OBJECT_ID in (select object_id from t2 n where n.STATUS = '11' and n.NAMESPACE =5)
and t.NAMESPACE = 14 and t.STATUS in ('0','10') and t.SUBOBJECT_NAME is not null
) select w.*,rowid from t1 w
where object_id in(select object_id from aa);
-- 執(zhí)行計劃
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 183 (100)| | 0 |00:00:00.01 | 5 | | | |
| 1 | NESTED LOOPS | | 1 | 90 | 11970 | 183 (1)| 00:00:01 | 0 |00:00:00.01 | 5 | | | |
| 2 | NESTED LOOPS | | 1 | 118 | 11970 | 183 (1)| 00:00:01 | 0 |00:00:00.01 | 5 | | | |
| 3 | VIEW | VW_NSO_1 | 1 | 59 | 767 | 5 (0)| 00:00:01 | 0 |00:00:00.01 | 5 | | | |
| 4 | HASH UNIQUE | | 1 | 59 | 1947 | | | 0 |00:00:00.01 | 5 | 1063K| 1063K| |
|* 5 | HASH JOIN SEMI | | 1 | 59 | 1947 | 5 (0)| 00:00:01 | 0 |00:00:00.01 | 5 | 1753K| 1753K| 1103K (0)|
|* 6 | INDEX RANGE SCAN | IX_T1_NSSDO | 1 | 59 | 1298 | 2 (0)| 00:00:01 | 7 |00:00:00.01 | 3 | | | |
|* 7 | INDEX RANGE SCAN | IX_T2_SNO | 1 | 503 | 5533 | 3 (0)| 00:00:01 | 54 |00:00:00.01 | 2 | | | |
|* 8 | INDEX RANGE SCAN | PK_T1 | 0 | 2 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID| T1 | 0 | 2 | 240 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
5 - access("T"."DATA_OBJECT_ID"="OBJECT_ID")
6 - access("T"."NAMESPACE"=14)
filter(("T"."SUBOBJECT_NAME" IS NOT NULL AND INTERNAL_FUNCTION("T"."STATUS")))
7 - access("N"."STATUS"='11' AND "N"."NAMESPACE"=5)
8 - access("OBJECT_ID"="OBJECT_ID")
-- 注意1：驅(qū)動表變成了首先過濾之后結果集較少的T1，with內(nèi)部執(zhí)行的是hash半連接
-- 注意2：驅(qū)動表T1的使用的索引已經(jīng)從之前的 DNSSO 變成了NSSDO where篩選列在索引前面
-- 注意3：被驅(qū)動表T2使用的索引還是where篩選的列在前面的索引，索引中包含了連接查詢需要的全部列的信息
-- 注意4：驅(qū)動表T1的索引使用方式，如果索引的選擇性很好，返回的結果集很少，索引的使用方式就從之前的快速全掃再filter，變成了現(xiàn)在的access再filter
-- 7.2 t2表不使用7.1使用的索引
with /*test7.1*/aa as (
select /*+ use_hash(@SEL$2 n) no_index(@SEL$2 n IX_T2_SNO)*/t.object_id from t1 t
where t.DATA_OBJECT_ID in (select object_id from t2 n where n.STATUS = '11' and n.NAMESPACE =5)
and t.NAMESPACE = 14 and t.STATUS in ('0','10') and t.SUBOBJECT_NAME is not null
) select w.*,rowid from t1 w
where object_id in(select object_id from aa);
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 255 (100)| | 0 |00:00:00.01 | 281 | | | |
| 1 | NESTED LOOPS | | 1 | 90 | 11970 | 255 (1)| 00:00:01 | 0 |00:00:00.01 | 281 | | | |
| 2 | NESTED LOOPS | | 1 | 118 | 11970 | 255 (1)| 00:00:01 | 0 |00:00:00.01 | 281 | | | |
| 3 | VIEW | VW_NSO_1 | 1 | 59 | 767 | 77 (0)| 00:00:01 | 0 |00:00:00.01 | 281 | | | |
| 4 | HASH UNIQUE | | 1 | 59 | 1947 | | | 0 |00:00:00.01 | 281 | 1063K| 1063K| |
|* 5 | HASH JOIN SEMI | | 1 | 59 | 1947 | 77 (0)| 00:00:01 | 0 |00:00:00.01 | 281 | 1753K| 1753K| 1147K (0)|
|* 6 | INDEX RANGE SCAN | IX_T1_NSSDO | 1 | 59 | 1298 | 2 (0)| 00:00:01 | 7 |00:00:00.01 | 3 | | | |
|* 7 | INDEX FAST FULL SCAN | IX_T2_OSN | 1 | 503 | 5533 | 75 (0)| 00:00:01 | 54 |00:00:00.01 | 278 | | | |
|* 8 | INDEX RANGE SCAN | PK_T1 | 0 | 2 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID| T1 | 0 | 2 | 240 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
5 - access("T"."DATA_OBJECT_ID"="OBJECT_ID")
6 - access("T"."NAMESPACE"=14)
filter(("T"."SUBOBJECT_NAME" IS NOT NULL AND INTERNAL_FUNCTION("T"."STATUS")))
7 - filter(("N"."NAMESPACE"=5 AND "N"."STATUS"='11'))
8 - access("OBJECT_ID"="OBJECT_ID")
-- 注意1：被驅(qū)動表t2的覆蓋索引的使用方式已經(jīng)變成了索引快掃再filter的方式，邏輯讀為278,比7.1的邏輯讀2高136倍
「喜歡這篇文章，您的關注和贊賞是給作者最好的鼓勵」
關注作者
【版權聲明】本文為墨天輪用戶原創(chuàng)內(nèi)容，轉(zhuǎn)載時必須標注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權追究責任。如果您發(fā)現(xiàn)墨天輪中有涉嫌抄襲或者侵權的內(nèi)容，歡迎發(fā)送郵件至：contact@modb.pro進行舉報，并提供相關證據(jù)，一經(jīng)查實，墨天輪將立刻刪除相關內(nèi)容。
