有同事问我，在ORACLE的SQL执行中IN和OR谁更高效呢？
让我们来完成如下实验：
通过上述实验，查看执行计划（粗体字的部分），我们不难发现执行IN的SQL在被ORACLE分析后，其过滤谓词(filter predicate)已经被转换称为了
("V2"=1 OR "V2"=2)
和使用OR关键字的SQL完全相同！
所以我们的结论就是：IN和OR没有伯仲之分，性能是相同的！
--------------------附加
ORACLE确实是在不断进步的，这个实验在10G中完成，显示的执行计划等都完成了格式化，非常整齐好看。虽然在9I中结果是相同的，但是执行计划如下显示：
不十分好看，且没有主动显示过滤谓词。我只能通过EXPLAIN PLAN FOR命令得到执行计划，然后在到PLAN_TABLE中查找确认。
欢迎这种进步 -:)
附赠关于Autotrace几个常用选项的说明:
让我们来完成如下实验：
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
SQL> create table larry_test (v1 number,v2 number);
Table created.
SQL> insert into larry_test values(100,1);
1 row created.
SQL> insert into larry_test values(101,2);
1 row created.
SQL> insert into larry_test values(103,1);
1 row created.
SQL> commit;
Commit complete.
SQL> set autotrace on explain
SQL> select * from larry_test where v2 in (1,2);
Execution Plan
----------------------------------------------------------
Plan hash value: 2936416851
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 78 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LARRY_TEST | 3 | 78 | 15 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("V2"=1 OR "V2"=2)
Note
-----
- dynamic sampling used for this statement
SQL> select * from larry_test where v2 =1 or v2=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 2936416851
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 78 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LARRY_TEST | 3 | 78 | 15 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("V2"=1 OR "V2"=2)
Note
-----
- dynamic sampling used for this statement
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
SQL> create table larry_test (v1 number,v2 number);
Table created.
SQL> insert into larry_test values(100,1);
1 row created.
SQL> insert into larry_test values(101,2);
1 row created.
SQL> insert into larry_test values(103,1);
1 row created.
SQL> commit;
Commit complete.
SQL> set autotrace on explain
SQL> select * from larry_test where v2 in (1,2);
Execution Plan
----------------------------------------------------------
Plan hash value: 2936416851
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 78 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LARRY_TEST | 3 | 78 | 15 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("V2"=1 OR "V2"=2)
Note
-----
- dynamic sampling used for this statement
SQL> select * from larry_test where v2 =1 or v2=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 2936416851
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 78 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LARRY_TEST | 3 | 78 | 15 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("V2"=1 OR "V2"=2)
Note
-----
- dynamic sampling used for this statement
所以我们的结论就是：IN和OR没有伯仲之分，性能是相同的！
--------------------附加
ORACLE确实是在不断进步的，这个实验在10G中完成，显示的执行计划等都完成了格式化，非常整齐好看。虽然在9I中结果是相同的，但是执行计划如下显示：
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'LARRY_TEST'
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'LARRY_TEST'
欢迎这种进步 -:)
附赠关于Autotrace几个常用选项的说明:
SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式
SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告
SET AUTOTRACE ON ----------------- 包含执行计划和统计信息
SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告
SET AUTOTRACE ON ----------------- 包含执行计划和统计信息
SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息
SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不显示查询输出
SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不显示查询输出
本文转自Be the miracle!博客51CTO博客，原文链接http://blog.51cto.com/miracle/54792如需转载请自行联系原作者
Larry.Yue
