0

sql优化 -利来国际app

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倍
「喜欢文章,快来给作者赞赏墨值吧」
【利来手机国际的版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:[email protected]进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论