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 --
分享好友
分享这个小栈给你的朋友们，一起进步吧。
订阅须知
• 所有用户可根据关注领域订阅专区或所有专区
• 付费订阅：虚拟交易，一经交易不退款；若特殊情况，可3日内客服咨询
• 专区发布评论属默认订阅所评论专区（除付费小栈外）