绑定完请刷新页面
取消
刷新

分享好友

×
取消 复制
【实验】IN与EXISTS效率测试一例
2020-02-06 19:57:36
1.创建测试表,并初始化数据(千万级别的big_table表和百万级别的big_table1表)
sec@ora10g> @big_table 10000000
sec@ora10g> create table big_table1 as select * from big_table where rownum<1000001;

2.创建完成后
big_table 表的id字段上有主键索
big_table1表上没有索引

3.表结构如
sec@ora10g> desc big_table;
 Name              Null?    Type
 ----------------- -------- --------------
 ID                NOT NULL NUMBER
 OWNER             NOT NULL VARCHAR2(30)
 OBJECT_NAME       NOT NULL VARCHAR2(30)
 SUBOBJECT_NAME             VARCHAR2(30)
 OBJECT_ID         NOT NULL NUMBER
 DATA_OBJECT_ID             NUMBER
 OBJECT_TYPE                VARCHAR2(19)
 CREATED           NOT NULL DATE
 LAST_DDL_TIME     NOT NULL DATE
 TIMESTAMP                  VARCHAR2(19)
 STATUS                     VARCHAR2(7)
 TEMPORARY                  VARCHAR2(1)
 GENERATED                  VARCHAR2(1)
 SECONDARY                  VARCHAR2(1)

            
4.CBO下的执行效果实验
实验结论:执行路径相同,效率一样
sec@ora10g> SELECT count(*)
  FROM big_table1 a
 WHERE a.object_name IN
       (SELECT b.object_name
          FROM big_table b
         WHERE b.id = 1
        )
/
  2    3    4    5    6    7    8
  COUNT(*)
----------
        87

Elapsed: 00:00:00.11

Execution Plan
----------------------------------------------------------
Plan hash value: 797099448

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     1 |    40 |  2980   (1)| 00:00:36 |
|   1 |  SORT AGGREGATE               |              |     1 |    40 |            |          |
|   2 |   NESTED LOOPS                |              |   176 |  7040 |  2980   (1)| 00:00:36 |
|   3 |    TABLE ACCESS BY INDEX ROWID| BIG_TABLE    |     1 |    23 |     3   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN         | BIG_TABLE_PK |     1 |       |     2   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL          | BIG_TABLE1   |   176 |  2992 |  2977   (1)| 00:00:36 |
----------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("B"."ID"=1)
   5 - filter("A"."OBJECT_NAME"="B"."OBJECT_NAME")

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      13380  consistent gets
          0  physical reads
          0  redo size
        515  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


sec@ora10g> SELECT count(*)
  FROM big_table1 a
 WHERE EXISTS
   (SELECT 1
      FROM big_table b
     WHERE b.id = 1
       AND a.object_name = b.object_name
    )
/
  2    3    4    5    6    7    8    9
  COUNT(*)
----------
        87

Elapsed: 00:00:00.11

Execution Plan
----------------------------------------------------------
Plan hash value: 797099448

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     1 |    40 |  2980   (1)| 00:00:36 |
|   1 |  SORT AGGREGATE               |              |     1 |    40 |            |          |
|   2 |   NESTED LOOPS                |              |   176 |  7040 |  2980   (1)| 00:00:36 |
|   3 |    TABLE ACCESS BY INDEX ROWID| BIG_TABLE    |     1 |    23 |     3   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN         | BIG_TABLE_PK |     1 |       |     2   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL          | BIG_TABLE1   |   176 |  2992 |  2977   (1)| 00:00:36 |
----------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("B"."ID"=1)
   5 - filter("A"."OBJECT_NAME"="B"."OBJECT_NAME")

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      13380  consistent gets
          0  physical reads
          0  redo size
        515  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

5.RBO下的执行效果实验(我们使用HINT来强制RBO优化方式)
实验结论:使用IN效率明显高,可以具体比较一下他们的执行计划
sec@ora10g> SELECT /*+ RULE */ count(*)
  FROM big_table1 a
 WHERE a.object_name IN
       (SELECT b.object_name
          FROM big_table b
         WHERE b.id = 1
        )
/
  2    3    4    5    6    7    8
  COUNT(*)
----------
        87

Elapsed: 00:00:00.12

Execution Plan
----------------------------------------------------------
Plan hash value: 2320907971

------------------------------------------------------
| Id  | Operation                     | Name         |
------------------------------------------------------
|   0 | SELECT STATEMENT              |              |
|   1 |  SORT AGGREGATE               |              |
|   2 |   MERGE JOIN                  |              |
|   3 |    TABLE ACCESS BY INDEX ROWID| BIG_TABLE    |
|*  4 |     INDEX UNIQUE SCAN         | BIG_TABLE_PK |
|*  5 |    FILTER                     |              |
|   6 |     TABLE ACCESS FULL         | BIG_TABLE1   |
------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("B"."ID"=1)
   5 - filter("A"."OBJECT_NAME"="B"."OBJECT_NAME")

Note
-----
   - rule based optimizer used (consider using cbo)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      13380  consistent gets
          0  physical reads
          0  redo size
        515  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

与上面的执行路径近似,时间近似。
再看下面的测试

sec@ora10g> SELECT /*+ RULE */ count(*)
  FROM big_table1 a
 WHERE EXISTS
   (SELECT 1
      FROM big_table b
     WHERE b.id = 1
       AND a.object_name = b.object_name
    )
/
  2    3    4    5    6    7    8    9
  COUNT(*)
----------
        87

Elapsed: 00:00:03.12

Execution Plan
----------------------------------------------------------
Plan hash value: 1709940279

------------------------------------------------------
| Id  | Operation                     | Name         |
------------------------------------------------------
|   0 | SELECT STATEMENT              |              |
|   1 |  SORT AGGREGATE               |              |
|*  2 |   FILTER                      |              |
|   3 |    TABLE ACCESS FULL          | BIG_TABLE1   |
|*  4 |    TABLE ACCESS BY INDEX ROWID| BIG_TABLE    |
|*  5 |     INDEX UNIQUE SCAN         | BIG_TABLE_PK |
------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter( EXISTS (SELECT 0 FROM "BIG_TABLE" "B" WHERE "B"."ID"=1
              AND "B"."OBJECT_NAME"=:B1))
   4 - filter("B"."OBJECT_NAME"=:B1)
   5 - access("B"."ID"=1)

Note
-----
   - rule based optimizer used (consider using cbo)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    3258008  consistent gets
          0  physical reads
          0  redo size
        515  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

明显使用EXISTS效率比使用IN效率低下。

6.结论
在CBO优化模式下,使用IN和EXISTS效率是一样的。
在RBO优化模式下,如果在被选取的字段上有索引,使用EXISTS的效率反而会比使用IN的效率低很多。

-- The End --
 
分享好友

分享这个小栈给你的朋友们,一起进步吧。

OCM联盟
创建时间:2019-12-27 14:04:54
OCM联盟(OCMU – Oracle Certified Master Union)是一群有着共同理想,共同志向的DBA的家。 ⚠️该小栈仅限ocm成员入驻!审核制! Oracle Certified Master (OCM) -Oracle认证大师,是Oracle认证的别,是对数据库从业人员的技术、知识和操作技能的别的认可。Oracle OCM是解决困难的技术难题和复杂的系统故障的佳Oracle专家人选,也是IT行业衡量IT专家和经理人的高专业程度及经验的基准。
展开
订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅:虚拟交易,一经交易不退款;若特殊情况,可3日内客服咨询

• 专区发布评论属默认订阅所评论专区(除付费小栈外)

栈主、嘉宾

查看更多
  • 侯圣文@secooler
    栈主

小栈成员

查看更多
  • gaokeke123
  • ?
  • 山中老狐狸
  • 飘絮絮絮丶
戳我,来吐槽~