ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【实验】IN与EXISTS效率测试一例——CBO效率一样,RBO下EXISTS效率高于IN

【实验】IN与EXISTS效率测试一例——CBO效率一样,RBO下EXISTS效率高于IN

原创 Linux操作系统 作者:secooler 时间:2009-09-30 14:36:31 0 删除 编辑
1.这个实验有以下场景和实验结论:
CBO没有索引的情况下:IN与EXISTS效率一样(IN:00:00:00.85 EXISTS:00:00:00.85)
CBO在列上创建索引后:IN与EXISTS效率一样(IN:00:00:00.16 EXISTS:00:00:00.16)
RBO没有索引的情况下:IN效率高于EXISTS(IN:00:00:01.20 EXISTS:00:01:21.50)
RBO在列上创建索引后:EXISTS效率高于IN(IN:00:18:16.76 EXISTS:00:01:04.62)

2.用到的实验SQL语句如下:
SELECT count(*)
  FROM big_table a
 WHERE a.object_name = 'T'
   AND EXISTS
 (SELECT 1
    FROM big_table1 b
   WHERE a.id = b.id)
/

SELECT count(*)
  FROM big_table a
 WHERE a.object_name = 'T'
   AND a.id IN
       (SELECT b.id
          FROM big_table1 b)
/

SELECT /*+ RULE */ count(*)
  FROM big_table1 a
 WHERE a.object_name = 'T'
   AND EXISTS
 (SELECT 1
    FROM big_table b
   WHERE a.id = b.id)
/

SELECT /*+ RULE */ count(*)
  FROM big_table1 a
 WHERE a.object_name = 'T'
   AND a.id IN
       (SELECT b.id
          FROM big_table b)
/

3.CBO没有索引的情况下:IN与EXISTS效率一样(IN:00:00:00.85 EXISTS:00:00:00.85)
sec@ora10g> SELECT count(*)
  FROM big_table a
 WHERE a.object_name = 'T'
   AND EXISTS
 (SELECT 1
    FROM big_table1 b
   WHERE a.id = b.id)
/
  2    3    4    5    6    7    8
  COUNT(*)
----------
        86

Elapsed: 00:00:00.85

Execution Plan
----------------------------------------------------------
Plan hash value: 2715735594

------------------------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |     1 |    36 |       | 34170   (1)| 00:06:51 |
|   1 |  SORT AGGREGATE     |            |     1 |    36 |       |            |          |
|*  2 |   HASH JOIN SEMI    |            |   100K|  3516K|  3424K| 34170   (1)| 00:06:51 |
|*  3 |    TABLE ACCESS FULL| BIG_TABLE  |   100K|  2246K|       | 29868   (1)| 00:05:59 |
|   4 |    TABLE ACCESS FULL| BIG_TABLE1 |   971K|    12M|       |  2974   (1)| 00:00:36 |
------------------------------------------------------------------------------------------


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

   2 - access("A"."ID"="B"."ID")
   3 - filter("A"."OBJECT_NAME"='T')

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


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     148401  consistent gets
          0  physical reads
        692  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_table a
 WHERE a.object_name = 'T'
   AND a.id IN
       (SELECT b.id
          FROM big_table1 b)
/
  2    3    4    5    6    7
  COUNT(*)
----------
        86

Elapsed: 00:00:00.85

Execution Plan
----------------------------------------------------------
Plan hash value: 2715735594

------------------------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |     1 |    36 |       | 34170   (1)| 00:06:51 |
|   1 |  SORT AGGREGATE     |            |     1 |    36 |       |            |          |
|*  2 |   HASH JOIN SEMI    |            |   100K|  3516K|  3424K| 34170   (1)| 00:06:51 |
|*  3 |    TABLE ACCESS FULL| BIG_TABLE  |   100K|  2246K|       | 29868   (1)| 00:05:59 |
|   4 |    TABLE ACCESS FULL| BIG_TABLE1 |   971K|    12M|       |  2974   (1)| 00:00:36 |
------------------------------------------------------------------------------------------


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

   2 - access("A"."ID"="B"."ID")
   3 - filter("A"."OBJECT_NAME"='T')

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


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     148401  consistent gets
          0  physical reads
        648  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

4.CBO在列上创建索引后:IN与EXISTS效率一样(IN:00:00:00.16 EXISTS:00:00:00.16)
create index i_big_table on big_table(object_name) parallel 8;
sec@ora10g> SELECT count(*)
  FROM big_table a
 WHERE a.object_name = 'T'
   AND EXISTS
 (SELECT 1
    FROM big_table1 b
   WHERE a.id = b.id)
/
  2    3    4    5    6    7    8
  COUNT(*)
----------
        86

Elapsed: 00:00:00.16

Execution Plan
----------------------------------------------------------
Plan hash value: 290523449

-----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     1 |    36 |       | 33983   (1)| 00:06:48 |
|   1 |  SORT AGGREGATE               |             |     1 |    36 |       |            |          |
|*  2 |   HASH JOIN SEMI              |             |   100K|  3516K|  3424K| 33983   (1)| 00:06:48 |
|   3 |    TABLE ACCESS BY INDEX ROWID| BIG_TABLE   |   100K|  2246K|       | 29681   (1)| 00:05:57 |
|*  4 |     INDEX RANGE SCAN          | I_BIG_TABLE | 40009 |       |       |     6   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL          | BIG_TABLE1  |   971K|    12M|       |  2974   (1)| 00:00:36 |
-----------------------------------------------------------------------------------------------------


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

   2 - access("A"."ID"="B"."ID")
   4 - access("A"."OBJECT_NAME"='T')

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


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      14246  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_table a
 WHERE a.object_name = 'T'
   AND a.id IN
       (SELECT b.id
          FROM big_table1 b)
/
  2    3    4    5    6    7
  COUNT(*)
----------
        86

Elapsed: 00:00:00.16

Execution Plan
----------------------------------------------------------
Plan hash value: 290523449

-----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     1 |    36 |       | 33983   (1)| 00:06:48 |
|   1 |  SORT AGGREGATE               |             |     1 |    36 |       |            |          |
|*  2 |   HASH JOIN SEMI              |             |   100K|  3516K|  3424K| 33983   (1)| 00:06:48 |
|   3 |    TABLE ACCESS BY INDEX ROWID| BIG_TABLE   |   100K|  2246K|       | 29681   (1)| 00:05:57 |
|*  4 |     INDEX RANGE SCAN          | I_BIG_TABLE | 40009 |       |       |     6   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL          | BIG_TABLE1  |   971K|    12M|       |  2974   (1)| 00:00:36 |
-----------------------------------------------------------------------------------------------------


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

   2 - access("A"."ID"="B"."ID")
   4 - access("A"."OBJECT_NAME"='T')

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


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      14246  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没有索引的情况下:IN效率高于EXISTS(IN:00:00:01.20 EXISTS:00:01:21.50)
sec@ora10g> SELECT /*+ RULE */ count(*)
  FROM big_table a
 WHERE a.object_name = 'T'
   AND a.id IN
       (SELECT b.id
          FROM big_table1 b)
/
  2    3    4    5    6    7
  COUNT(*)
----------
        86

Elapsed: 00:00:01.20

Execution Plan
----------------------------------------------------------
Plan hash value: 1142876600

---------------------------------------------
| Id  | Operation              | Name       |
---------------------------------------------
|   0 | SELECT STATEMENT       |            |
|   1 |  SORT AGGREGATE        |            |
|   2 |   VIEW                 |            |
|   3 |    SORT UNIQUE         |            |
|   4 |     MERGE JOIN         |            |
|   5 |      SORT JOIN         |            |
|*  6 |       TABLE ACCESS FULL| BIG_TABLE  |
|*  7 |      SORT JOIN         |            |
|   8 |       TABLE ACCESS FULL| BIG_TABLE1 |
---------------------------------------------

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

   6 - filter("A"."OBJECT_NAME"='T')
   7 - access("A"."ID"="B"."ID")
       filter("A"."ID"="B"."ID")

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


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     148401  consistent gets
          0  physical reads
        692  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
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed


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


  COUNT(*)
----------
        86

Elapsed: 00:01:21.50

Execution Plan
----------------------------------------------------------
Plan hash value: 432798818

------------------------------------------
| Id  | Operation           | Name       |
------------------------------------------
|   0 | SELECT STATEMENT    |            |
|   1 |  SORT AGGREGATE     |            |
|*  2 |   FILTER            |            |
|*  3 |    TABLE ACCESS FULL| BIG_TABLE  |
|*  4 |    TABLE ACCESS FULL| BIG_TABLE1 |
------------------------------------------

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

   2 - filter( EXISTS (SELECT 0 FROM "BIG_TABLE1" "B" WHERE
              "B"."ID"=:B1))
   3 - filter("A"."OBJECT_NAME"='T')
   4 - filter("B"."ID"=:B1)

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


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
   11133507  consistent gets
          0  physical reads
        868  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


6.RBO在列上创建索引后:EXISTS效率高于IN(IN:00:18:16.76 EXISTS:00:01:04.62)
sec@ora10g> create index i_big_table on big_table(object_name) parallel 8;

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

  COUNT(*)
----------
        86

Elapsed: 00:01:04.62

Execution Plan
----------------------------------------------------------
Plan hash value: 2377613799

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

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

   2 - filter( EXISTS (SELECT 0 FROM "BIG_TABLE1" "B" WHERE
              "B"."ID"=:B1))
   4 - access("A"."OBJECT_NAME"='T')
   5 - filter("B"."ID"=:B1)

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


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
   10999352  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_table a
 WHERE a.object_name = 'T'
   AND a.id IN
       (SELECT b.id
          FROM big_table1 b)
/
  2    3    4    5    6    7


  COUNT(*)
----------
        86

Elapsed: 00:18:16.76

Execution Plan
----------------------------------------------------------
Plan hash value: 4287364898

------------------------------------------------------
| Id  | Operation                      | Name        |
------------------------------------------------------
|   0 | SELECT STATEMENT               |             |
|   1 |  SORT AGGREGATE                |             |
|   2 |   VIEW                         |             |
|   3 |    SORT UNIQUE                 |             |
|*  4 |     TABLE ACCESS BY INDEX ROWID| BIG_TABLE   |
|   5 |      NESTED LOOPS              |             |
|   6 |       TABLE ACCESS FULL        | BIG_TABLE1  |
|*  7 |       INDEX RANGE SCAN         | I_BIG_TABLE |
------------------------------------------------------

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

   4 - filter("A"."ID"="B"."ID")
   7 - access("A"."OBJECT_NAME"='T')

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


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
  869012509  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
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

-- The End --

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

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

注册时间:2008-03-16

  • 博文量
    797
  • 访问量
    7975035