SQL优化 - 表连接的 索引覆盖优化方式
表连接的索引覆盖考虑：
1 驱动表和被驱动表的索引需要覆盖到where条件涉及的列，和连接条件的列，还有select查询的列
2 当驱动表和被驱动表的where条件(除了连接列)选择性好，返回的结果集少的时候，where条件的列考虑放到索引列的前边：连接的列放到索引列的后边
3 当被驱动表的where条件选择性不好的时候，但是驱动表只有少量的结果集的时候，执行计划可能考虑NL，连接列考虑放到索引列的前边：where列考虑放到索引列的后边
4 如果被驱动表除了连接列之外，没有其它条件，HASH连接的方式只能是全表扫描，NL连接才考虑使用连接列上的索引
5 查询列可以考虑放在索引的最后
6 如果是NL的连接，如果被驱动表连接列选择性很好，也可以考虑使用连接列的单列索引
7 如果是HASH的连接，通常被驱动表的连接列放到组合索引where列的后面；
8 如果不是高并发执行的业务SQL，都不用考虑建索引，维护索引也是需要成本的。
需要优化的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执行的等待事件：
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执行统计信息，逻辑读看着不多才350万，但是物理读超乎想像，已经不够显示了
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
优化方案：使用索引覆盖
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 优化之后：
-- set autot traceonly实际执行 查看统计信息
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
实际执行
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")
-- 执行计划中 索引 IND_LS_METROLOGY_SITE 的访问使用了"INLIST ITERATOR"的方式， starts为2 索引访问了2次，是因为 条件 flags in (0, 10) 所以定位不同叶子块访问了2次
2 两个很大基数的分区表关联，实际关联返回行数却很少(上面SQL执行的时候已经没有返回)，CBO却没办法确切的评估返回行数：
3 优化之前的执行计划评估返回167万，实际返回2行，优化之后的执行计划评估返回161万行，实际返回0行
4 如果两个1亿数据量的表做关联，1个表全是奇数，1个表全是偶数，实际返回行数确实为0，只能实际的关联匹配之后才知道实际返回行数
验证测试索引覆盖：
连接查询使用索引覆盖测试
-- 构造数据
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 |
------------------------------------------------------------------------------
-- 构造随机数据
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： 类似于生产上的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执行计划-实际执行并没有产生这个执行计划
---------------------------------------------------------------------------------------
| 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： 第一次执行 with内部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")))
-- 实际执行计划2： 第二次执行 with内部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);
-- 执行计划: 执行执行是测试1中的第2种执行计划
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进行查询全表的结果集，再做hash半连接
又指定的索引的HINT，所有全部扫描索引之后又回表，逻辑读高达9万，比全表扫描的成本1542高59倍
测试4：在with内部使用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 执行计划--
---------------------------------------------------------------------------------------
| 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'))
实际执行计划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")))
实际执行计划2： with 内部使用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")
内部hash的实际逻辑读416，比之前NL的连接方式的逻辑读58高7倍
测试5： 在外部HINT使用主键索引 在with内部使用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个实际执行计划一致
-- 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的执行计划
3 with内部的t1表索引有一个和t2表关联的列,DATA_OBJECT_ID,一个查询返回的列obect_id，还有3个where条件中的列，一共有5个列，都是使用快速全扫过滤的方式
测试6： 在外部HINT使用主键索引 在with内部使用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内部使用hash 但是被驱动表变成子查询中的表
-- 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);
-- 执行计划
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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：驱动表变成了首先过滤之后结果集较少的T1，with内部执行的是hash半连接
-- 注意2：驱动表T1的使用的索引已经从之前的 DNSSO 变成了NSSDO where筛选列在索引前面
-- 注意3：被驱动表T2使用的索引还是where筛选的列在前面的索引，索引中包含了连接查询需要的全部列的信息
-- 注意4：驱动表T1的索引使用方式，如果索引的选择性很好，返回的结果集很少，索引的使用方式就从之前的快速全扫再filter，变成了现在的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：被驱动表t2的覆盖索引的使用方式已经变成了索引快扫再filter的方式，逻辑读为278,比7.1的逻辑读2高136倍
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
