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倍
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【米乐app官网下载的版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。