有同事问我,在ORACLE的SQL执行中IN和OR谁更高效呢?
    让我们来完成如下实验:
   
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
    通过上述实验,查看执行计划(粗体字的部分),我们不难发现执行IN的SQL在被ORACLE分析后,其过滤谓词(filter predicate)已经被转换称为了("V2"=1 OR "V2"=2)和使用OR关键字的SQL完全相同!
    所以我们的结论就是:IN和OR没有伯仲之分,性能是相同的!

--------------------附加
    ORACLE确实是在不断进步的,这个实验在10G中完成,显示的执行计划等都完成了格式化,非常整齐好看。虽然在9I中结果是相同的,但是执行计划如下显示:
  
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'LARRY_TEST'
    不十分好看,且没有主动显示过滤谓词。我只能通过EXPLAIN PLAN FOR命令得到执行计划,然后在到PLAN_TABLE中查找确认。  
    欢迎这种进步 -:)
    附赠关于Autotrace几个常用选项的说明:
    SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告,这是缺省模式
    SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告
    SET AUTOTRACE ON ----------------- 包含执行计划和统计信息
    SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息
    SET AUTOTRACE TRACEONLY ------ 同set autotrace on,但是不显示查询输出