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进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
墨天轮福利君
3年前
评论
0❤️您好，您这篇文章可以参与墨力计划！
只要文章发表处打上：墨力计划 的标签就好啦。
👍只要合格就能获得10墨值奖励，还能角逐入围奖、优秀文章奖。
3年前
点赞 评论
相关阅读
【纯干货】Oracle 19C RU 19.27 发布，如何快速升级和安装？
Lucifer三思而后行
802次阅读
2025-04-18 14:18:38
Oracle数据库一键巡检并生成HTML结果，免费脚本速来下载!
陈举超
602次阅读
2025-04-20 10:07:02
【ORACLE】你以为的真的是你以为的么?--ORA-38104: Columns referenced in the ON Clause cannot be updated
DarkAthena
551次阅读
2025-04-22 00:13:51
【活动】分享你的压箱底干货文档，三篇解锁进阶奖励！
墨天轮编辑部
538次阅读
2025-04-17 17:02:24
【ORACLE】记录一些ORACLE的merge into语句的BUG
DarkAthena
511次阅读
2025-04-22 00:20:37
一页概览：Oracle GoldenGate
甲骨文云技术
503次阅读
2025-04-30 12:17:56
火焰图--分析复杂SQL执行计划的利器
听见风的声音
474次阅读
2025-04-17 09:30:30
OR+DBLINK的关联SQL优化思路
布衣
386次阅读
2025-05-05 19:28:36
Oracle数据库Hint大全，31个使用案例，速来下载！
陈举超
371次阅读
2025-04-16 21:25:19
RisingWave 产品月报｜25 年 4 月
RisingWave中文开源社区
349次阅读
2025-05-08 10:04:55
热门文章
Oracle password 密码策略 密码复杂度
2020-06-24 9768浏览
python paramiko 设置远程ssh执行命令超时限制
2021-06-22 9752浏览
library cache lock 原因及解决方案
2021-05-10 7477浏览
linux Oracle修改ORACLE_SID、修改DB_NAME，修改实例名，服务名
2020-05-31 6133浏览
使用SecureCRT开启图形化界面功能，在Windown显示Linux服务器图形化界面
2020-06-20 5892浏览
最新文章
人大金仓 安装报错ArrayIndexOutOfBoundsException
2025-02-07 78浏览
CRS-42216: No interfaces are configured on the local node for interface definition ib1(:.*)?:10.10.1
2024-08-02 187浏览
opengauss数据库初始化报错：[GAUSS-51607] : Failed to start instance. Error: Please check the gs_ctl log for failure details.
2024-03-05 668浏览
“Oracle_tns_listener远程投毒”故障诊断
2023-11-30 870浏览
ORA-01873 故障处理
2023-11-30 2272浏览
目录
