这几组SQL语句效率哪个高？

Question

第一组：
a: where dm like '660712%'
b: where instr(dm,1,6)='660712'第二组：
a: where to_char(rq,'yyyymmdd')>'20070701'
b: where rq>to_date('20070701','yyyymmdd')第三组：
a: where dm between 5 and 10
b: where dm>=5 and dm<=10第四组：
a: where dm in ('1','2','3')
b: where dm='1' or dm='2' or dm='3'数据量为几十万到几百万左右。

Answer 1

"通常情况下，1A，2B效率高，3和4的两个语句没什么区别。"
======================================================
不能这么说,要看具体的情况
一句SQL不可能永远都是高效的,随着数据的变化,索引等环境的变化,相同的SQL执行的开销都是不一样的
就拿第一组来说
如果索引是 create INDEX IDX_TEST ON TABLE (DM)
那么第一种快
如果索引是 create INDEX IDX_TEST ON TABLE (substr(DM,1,6))
那么第二种快

Answer 2

这几题明显是要考索引使用的，第一组中dm为索引字段的话，则a. 使用索引，b没有用索引。
其它类似，还有第四组明显是or 比in快，上百万的数据用in很惨的， 如果上千万的数据用in 或not in之类的，老板不开了他才怪.

Answer 3

目前常用的oracle（9i或更高版本）版本的优化器，一般都会把in优化为or来执行的。测试如下
SQL> create table t_test as select rownum id, object_name from all_objects;表已创建。SQL> insert into t_test select rownum+(select max(id) from t_test),object_name f已创建23532行。SQL> insert into t_test select rownum+(select max(id) from t_test),object_name f已创建47064行。SQL> insert into t_test select rownum+(select max(id) from t_test),object_name f已创建94128行。SQL> insert into t_test select rownum+(select max(id) from t_test),object_name f已创建188256行。SQL> commit;提交完成。SQL> create index idx_test on t_test(id);索引已创建。SQL> set autot on
SQL> select id,object_name from t_test where id in (2,6454,324332);        ID OBJECT_NAME
---------- ------------------------------
    324332 java/awt/Toolkit$1
      6454 SQLDOUBLESEQUENCE
         2 /10076b23_OraCustomDatumClosur
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   CONCATENATION
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_TEST'
   3    2       INDEX (RANGE SCAN) OF 'IDX_TEST' (NON-UNIQUE)
   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_TEST'
   5    4       INDEX (RANGE SCAN) OF 'IDX_TEST' (NON-UNIQUE)
   6    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_TEST'
   7    6       INDEX (RANGE SCAN) OF 'IDX_TEST' (NON-UNIQUE)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         13  consistent gets
          6  physical reads
          0  redo size
        543  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processedSQL> select id,object_name from t_test where id=2 or id=6454 or id=324332;        ID OBJECT_NAME
---------- ------------------------------
    324332 java/awt/Toolkit$1
      6454 SQLDOUBLESEQUENCE
         2 /10076b23_OraCustomDatumClosur
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   CONCATENATION
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_TEST'
   3    2       INDEX (RANGE SCAN) OF 'IDX_TEST' (NON-UNIQUE)
   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_TEST'
   5    4       INDEX (RANGE SCAN) OF 'IDX_TEST' (NON-UNIQUE)
   6    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_TEST'
   7    6       INDEX (RANGE SCAN) OF 'IDX_TEST' (NON-UNIQUE)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
        543  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processedSQL>

Answer 4

对于第一组，这样效率或许更高substr(dm,1,6)='660712';
第二组，个人认为A的效率更高，字符到时间花的时间要多于时间转换到字符；
第三组，我认为A的效率相对高些；
第四组，差不多吧，好象有SQL书籍提醒最好用IN。

Answer 5

不好意思，第一组的b写错了，instr(dm,1,6)='660712' 应为 substr(dm,1,6)='660712'

Answer 6

第二组：
a: where to_char(rq,'yyyymmdd')>'20070701'
b: where rq>to_date('20070701','yyyymmdd')
========================
这一组哪个高，有没有有说服力一点的说法？

Answer 7

刚好今天在公司测试效率问题的时候碰到了第四组这个问题。
也就顺便做了个测试，查询大网16W条光纤路径数据。
用IN =120秒,OR =128秒 ，虽然差距不大，但是不赞同5楼的说法。