Oracle 子查询in字句优化

前言:

本文主要介绍子查询in字句在不同数据量下的表的连接方式以及执行路径选择

测试表:

create table a (id number,owner varchar2(200),name varchar2(200));
create table b (id number,addrselect * from a where a.id in (select id from b)ess varchar2(200),card varchar2(200));

查询语句:

select * from a where a.id in (select id from b)

场景一:当A表为1000W,B表为100行

如果a表id的可选择率很高,则高效的执行计划为b,a表nest loop ,b表作为驱动表,a表做为被驱动表并且通过索引访问a表

​---创建索引
create index test_ind1_a on a(id);
---插入数据
truncate table a;
truncate table b;
create or replace procedure  p1  is
   v_count number(10):=0;
begin
   for i in 1..10000000 loop
     insert into a values(v_count,'aaaaaaaaa','bbbbbbbbbbbbbbbb');
     v_count:=v_count+1;
     if v_count>=10000 THEN
       commit;
       v_count:=0;
     end if;
   end loop;
   commit;
end;
/
​
exec p1
​
begin
for i in 1..100 loop
     insert into b values(i,'aaaaaaaaa','bbbbbbbbbbbbbbbb');
end loop;
commit;
end;
/
---收集统计信息
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'test',tabname=>'a',ESTIMATE_PERCENT=>30,method_opt=>'for all columns size auto',cascade=>true,force=>true,degree=>4);
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'test',tabname=>'b',ESTIMATE_PERCENT=>30,method_opt=>'for all columns size auto',cascade=>true,force=>true,degree=>4);
---执行语句
set autotrace trace
select /*+use_nl(a) leading(B@TEMP)  index(a test_ind1_a) */ * from a where a.id in (select /*+qb_name(temp) */ id from b);
set autotrace off
​
Execution Plan
----------------------------------------------------------
Plan hash value: 4263097039
​
--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             | 99880 |  3316K| 50114   (1)| 00:00:02 |
|   1 |  NESTED LOOPS                |             | 99880 |  3316K| 50114   (1)| 00:00:02 |
|   2 |   NESTED LOOPS               |             | 99880 |  3316K| 50114   (1)| 00:00:02 |
|   3 |    SORT UNIQUE               |             |   100 |   300 |     3   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL        | B           |   100 |   300 |     3   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | TEST_IND1_A |   999 |       |     3   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID| A           |   999 | 30969 |  1002   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
​
Predicate Information (identified by operation id):
---------------------------------------------------
​
   5 - access("A"."ID"="ID")
​
​
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     107234  consistent gets
          0  physical reads
          0  redo size
    1774294  bytes sent via SQL*Net to client
      73810  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     100000  rows processed

如果a表id的可选择率不高,则高效的执行计划为b,a表hash join semi ,b小表作为驱动表,a表做为被驱动表

---插入数据
truncate table a;
truncate table b;
create or replace procedure  p1  is
   v_count number(10):=0;
begin
   for i in 1..10000000 loop
     insert into a values(v_count,'aaaaaaaaa','bbbbbbbbbbbbbbbb');
     v_count:=v_count+1;
     if v_count>=100 THEN
       commit;
       v_count:=0;
     end if;
   end loop;
   commit;
end;
/
exec p1
​
begin
for i in 1..100 loop
     insert into b values(i,'aaaaaaaaa','bbbbbbbbbbbbbbbb');
end loop;
commit;
end;
/
---收集统计信息
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'test',tabname=>'a',ESTIMATE_PERCENT=>30,method_opt=>'for all columns size auto',cascade=>true,force=>true,degree=>4);
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'test',tabname=>'b',ESTIMATE_PERCENT=>30,method_opt=>'for all columns size auto',cascade=>true,force=>true,degree=>4);
---执行查询
set autotrace trace
select  * from a where a.id in (select /*+hash_sj */ id from b);
set autotrace off
​
9900000 rows selected.
​
Elapsed: 00:01:51.35
​
Execution Plan
----------------------------------------------------------
Plan hash value: 2016728674
​
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |  9991K|   314M| 13324   (1)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT SEMI|      |  9991K|   314M| 13324   (1)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | B    |   100 |   300 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL  | A    |  9991K|   285M| 13294   (1)| 00:00:01 |
-----------------------------------------------------------------------------
​
Predicate Information (identified by operation id):
---------------------------------------------------
​
   1 - access("A"."ID"="ID")
​
​
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     705207  consistent gets
      48345  physical reads
          0  redo size
  205260414  bytes sent via SQL*Net to client
    7260414  bytes received via SQL*Net from client
     660001  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    9900000  rows processed
​

场景二:当A表为100行,B表为1000W

如果b表id的可选择率很高,高效的执行计划为a,b表nest loop  semi,a表作为驱动表,b表做为被驱动表并且通过索引访问b表

---创建索引
create index test_ind1_b on b(id);
---插入数据
truncate table a;
truncate table b;
create or replace procedure  p1  is
   v_count number(10):=0;
begin
   for i in 1..10000000 loop
     insert into b values(v_count,'aaaaaaaaa','bbbbbbbbbbbbbbbb');
     v_count:=v_count+1;
     if v_count>=10000 THEN
       commit;
       v_count:=0;
     end if;
   end loop;
   commit;
end;
/
​
exec p1;
​
begin
for i in 1..100 loop
     insert into a values(i,'aaaaaaaaa','bbbbbbbbbbbbbbbb');
end loop;
commit;
end;
/
​
---收集统计信息
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'test',tabname=>'a',ESTIMATE_PERCENT=>30,method_opt=>'for all columns size auto',cascade=>true,force=>true,degree=>4);
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'test',tabname=>'b',ESTIMATE_PERCENT=>30,method_opt=>'for all columns size auto',cascade=>true,force=>true,degree=>4);
​
---执行语句
set autotrace trace
select * from a where a.id in (select /*+nl_sj index(b test_ind1_b) */ id from b);
set autotrace off
​
​
​
Execution Plan
----------------------------------------------------------
Plan hash value: 3690787731
​
----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |   100 |  3400 |   203   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI |             |   100 |  3400 |   203   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| A           |   100 |  3000 |     3   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | TEST_IND1_B |  9995K|    38M|     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------
​
Predicate Information (identified by operation id):
---------------------------------------------------
​
   3 - access("A"."ID"="ID")
​
Note
-----
   - this is an adaptive plan
​
​
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        221  consistent gets
          0  physical reads
          0  redo size
       2731  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed

如果b表id的可选择率不高,高效的执行计划为a,b 表hash join semi ,a表作为驱动表,b表做为被驱动表

---插入数据
truncate table a;
truncate table b;
create or replace procedure  p1  is
   v_count number(10):=0;
begin
   for i in 1..10000000 loop
     insert into b values(v_count,'aaaaaaaaa','bbbbbbbbbbbbbbbb');
     v_count:=v_count+1;
     if v_count>=100 THEN
       commit;
       v_count:=0;
     end if;
   end loop;
   commit;
end;
/
exec p1;
​
begin
for i in 1..100 loop
     insert into a values(i,'aaaaaaaaa','bbbbbbbbbbbbbbbb');
end loop;
commit;
end;
/
​
---执行统计信息收集
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'test',tabname=>'a',ESTIMATE_PERCENT=>30,method_opt=>'for all columns size auto',cascade=>true,force=>true,degree=>4);
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'test',tabname=>'b',ESTIMATE_PERCENT=>30,method_opt=>'for all columns size auto',cascade=>true,force=>true,degree=>4);
---执行查询
set autotrace trace
select  * from a where a.id in (select /*+hash_sj  */ id from b);
set autotrace off
Execution Plan
----------------------------------------------------------
Plan hash value: 3821265669
​
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   100 |  3300 | 13313   (1)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |      |   100 |  3300 | 13313   (1)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| A    |   100 |  3000 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| B    |  9996K|    28M| 13284   (1)| 00:00:01 |
---------------------------------------------------------------------------
​
Predicate Information (identified by operation id):
---------------------------------------------------
​
   1 - access("A"."ID"="ID")
​
​
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      48368  consistent gets
      48345  physical reads
          0  redo size
       2723  bytes sent via SQL*Net to client
        491  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         99  rows processed
​
SQL> 
SQL> 
--
​
​
Predicate Information (identified by operation id):
---------------------------------------------------
​
   1 - access("A"."ID"="ID")
​
​
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        495  bytes sent via SQL*Net to client
        436  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

场景三:当A表为100W,B表为1000W

这时候高效的执行计划是a,b 表hash join  semi,a表作为驱动表,b表做为被驱动表

---插入数据
truncate table a;
truncate table b;
create or replace procedure  p1  is
   v_count number(10):=0;
begin
   for i in 1..10000000 loop
     insert into b values(v_count,'aaaaaaaaa','bbbbbbbbbbbbbbbb');
     v_count:=v_count+1;
     if v_count>=100 THEN
       commit;
       v_count:=0;
     end if;
   end loop;
   commit;
end;
/
​
exec p1;
​
begin
for i in 1..1000000 loop
     insert into a values(i,'aaaaaaaaa','bbbbbbbbbbbbbbbb');
end loop;
commit;
end;
/
​
---执行统计信息收集
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'test',tabname=>'a',ESTIMATE_PERCENT=>30,method_opt=>'for all columns size auto',cascade=>true,force=>true,degree=>4);
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'test',tabname=>'b',ESTIMATE_PERCENT=>30,method_opt=>'for all columns size auto',cascade=>true,force=>true,degree=>4);
​
​
---执行查询
set autotrace trace
select  * from a where a.id in (select /*+hash_sj  */ id from b);
set autotrace off
​
​
Execution Plan
----------------------------------------------------------
Plan hash value: 3821265669
​
-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   101 |  3535 |       | 23918   (1)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |      |   101 |  3535 |    42M| 23918   (1)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| A    |  1001K|    30M|       |  1405   (1)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| B    |  9996K|    28M|       | 13284   (1)| 00:00:01 |
-----------------------------------------------------------------------------------
​
Predicate Information (identified by operation id):
---------------------------------------------------
​
   1 - access("A"."ID"="ID")
​
​
Statistics
----------------------------------------------------------
         85  recursive calls
          0  db block gets
      53496  consistent gets
      63610  physical reads
          0  redo size
       2723  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         99  rows processed

  • 2
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Oracle 数据库中的子查询可以提供更为灵活的数据查询和分析功能,但是子查询性能优化也是我们需要考虑的问题。以下是一些可以优化 Oracle 子查询性能的方法: 1. 使用 EXISTS 替代 IN:在使用子查询时,IN 子句可能会导致性能问题,因为它需要对外部查询和子查询之间的结果集进行比较。使用 EXISTS 替代 IN 可以避免这种情况。 2. 使用 WITH 优化递归查询:对于大型数据集上的递归查询,使用 WITH 可以提高性能。WITH 子句可以在查询中定义一个递归共用表达式,使得查询更加简洁且易于维护。 3. 使用内连接替代子查询:使用内连接可以避免使用子查询时可能出现的性能问题。内连接可以在查询中同时检索两个表的数据,并且效率更高。 4. 使用子查询缓存:Oracle 数据库可以缓存子查询的结果,以便在下次查询时直接从缓存中获取结果。这可以减少查询的时间和资源消耗。 5. 优化索引:在使用子查询时,确保查询的字段都有索引,可以大大提高查询性能。 6. 正确使用 GROUP BY:当使用子查询时,需要正确使用 GROUP BY 子句。如果 GROUP BY 子句使用不当,可能会导致性能问题。因此,需要确保 GROUP BY 子句中的所有字段都在 SELECT 子句中出现。 总之,优化 Oracle 子查询性能需要综合考虑多个因素,包括查询语句的结构、索引、缓存等等。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值