Question

Oracle 分页查询在子查询中使用了 排序和like 会影响效率吗? 怎样优化呢

像是这样写的。
SELECT * FROM (SELECT A.*,Rownum rn FROM
(SELECT * FROM tb_one where
id1 like 'xy%'
and name in (select name from tb_one where id2 like 'xy%')
and to_char(INSTIME,'yyyy-mm-dd hh24:mi:ss')>='2013-1-8 00:00:00'
and to_char(INSTIME,'yyyy-mm-dd hh24:mi:ss')<='2013-1-8 23:59:59' order by INSTIME desc) A
WHERE ROWNUM <=20 ) WHERE rn >=10 ;
解决的话有加分。谢谢各位了
and name in (select name from tb_one where id2 like 'xy%')

这一行的 tb_one 是另外一个表 写错了 ，当作tb_two 就好，like 'xy%' 里面 xy 可能是其他输入的值，比如 ‘ab%’

Answer 1

1。 看能不能建立索引（和其他几位仁兄一致）
2。 能否不用like 如果tb_one中的id2不是主键也不是索引，可以改为substr(id2,1,2) = 'xy'
3。 排序尽量不要，既然LZ说了这是前提那就省略不说
4。 加hint并行处理（这个会提高不少，但需要数据库开启并行，并且硬件足以支持）
5。 这个肯定是全表扫描，不用想（都like了）
6。 根据你的时间条件，可以写成substr(instime) = '20130108'
7。 加表别名，并不要使用星号
9。 少些几个子查询。。。

Answer 2

如果数据量非常大的情况下一般都建立有索引，而like模糊查询是会影响索引的检索效率，所以最好不要用like。
另外写查询语句时尽量避免用select *,一般将需要显示的列select出来就ok

Answer 3

SELECT * FROM (SELECT A.*,Rownum rn FROM
(SELECT * FROM tb_one where
id1 like 'xy%'
and name in (select name from tb_one where id2 like 'xy%')
and INSTIME>=to_date('2013-1-8 00:00:00','yyyy-mm-dd hh24:mi:ss')
and INSTIME<=to_date('2013-1-8 23:59:59','yyyy-mm-dd hh24:mi:ss') order by INSTIME desc) A
WHERE ROWNUM <=20 ) WHERE rn >=10 ;
name、instime上都要创建索引

追问

name索引是创建在tb1 还是tb2呢？另外 like 模糊查询 和 order by 会影响效率吗？

追答

条件中使用的name应该是tb1表里的字段，like和order by肯定会对效率有影响，但是如果你的需求就是这样的话，那就没办法了
如果数据量不太大的话，速度应该可以接受

追问

需求模糊查询跟排序 并且是大表呀（至少几百万数据）。 查询时间过长。like会全表查询吗？
还能怎样提高效率，或者有优化的sql 语句呢？如果不排序怎样快速取出tb1表前10条记录？谢谢

追答

这需要分析你的执行计划才行，是不是全表扫描，都要看执行计划才能知道
如果不排序取前10条，那么
select * from tb1 where rownum<=10就可以了

追问

模糊查询是一定会全表扫描吗？有什么建议吗? 排序也会全表扫描吗？谢谢

追答

是不是全表扫描最终要看你的执行计划，like语句也可能会用到索引，比如like 'xy%'这种方式，就有可能会用到索引
另外排序也可能使用到索引，但是效率很低，这些都要看你的执行计划来分析，光看语句是看不出来的