ORACLE中IN和OR谁更高效？【WHICH KEY WORD CAN GET BETTER PERFORMANCE? 】 
版权声明：原创作品，允许转载，转载时请务必以超链接形式标明文章 原始出处 、作者信息和本声明。否则将追究法律责任。http://miracle.blog.51cto.com/255044/54792 |
有同事问我，在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和OR没有伯仲之分，性能是相同的！ --------------------附加 ORACLE确实是在不断进步的，这个实验在10G中完成，显示的执行计划等都完成了格式化，非常整齐好看。虽然在9I中结果是相同的，但是执行计划如下显示： Execution Plan ---------------------------------------------------------- 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 STATISTICS -- 只显示执行统计信息 SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不显示查询输出 本文出自 “Be the miracle!” 博客，请务必保留此出处http://miracle.blog.51cto.com/255044/54792 本文出自 51CTO.COM技术博客 |
Larry.Yue
博客统计信息
热门文章
最新评论
友情链接