ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【实验】IN与EXISTS效率测试一例

【实验】IN与EXISTS效率测试一例

原创 Linux操作系统 作者:secooler 时间:2009-09-26 11:46:00 0 删除 编辑
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 --
 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/519536/viewspace-615592/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE 总监,阿里云MVP,北京大学理学硕士,恩墨学院创始人,教育专家,中国区 Cloudera 首位官方授权大数据讲师,金牌培训专家,BDA大数据联盟创始人,OCM联盟创始人,ACCUG创始人、ACOUG核心专家,Blogger。

注册时间:2008-03-16

  • 博文量
    797
  • 访问量
    7974781