为什么速度差别如此之大?(关于Like) 在线等待

Question

我数据库表格中有4W条记录,做这样的查询:
select * from t where t1 like '%我们%' order by reid; --其中Reid列是主键,t1上建唯一索引
需要29秒如果这样写:
select * from t where t1 like '%我%' order by reid; --其中Reid列是主键,t1上建唯一索引
就只需要0.3秒他们的区别就是 Like的对象一个是两个字"我们",一个是一个字"我".
我知道 Like 后前面有"%"是用不上索引的,但为什么上面两个查询差别那么大???我可以优化第一种查询吗?在线等待中....

Answer 1

估计是你在两个查询之前没有清空共享内存所致。
在两个查询前都执行：
alter system flush shared_pool;这样的话两个所花的时间应该差不多。

Answer 2

我大概知道原因了，因为我是前５０个匹配查询，如果只有一个字，那么很快就可以找到前５０条记录并返回，但如果匹配两个字，那么要找一会才能找到前５０个．所以要慢一些。我的表格中只有４W表记录，６０列。就无条件Count一下，需要４秒多５秒。这正常吗？我的查询是这样的：
select * from dvfirstreturn where TopS >=80 and LowS <=90 and  GardenName like '%花园%' order by reid desc;
符合条件的记录有１３条，需要２９秒，其中reid是主键，GardenName是唯一键，其他没有建立索引我要怎么优化啊？？

Answer 3

我的表格中只有４W表记录，６０列。就无条件Count一下，需要４秒多５秒。这正常吗？绝对不正常，楼主最好把这句SQL的执行计划贴出来看看。

Answer 4

我觉得不会相差近百倍吧，以前做过一个，相差5-10倍差不多。Count一下，需要４秒多５秒，这很正常

Answer 5

是这样的，我的查询语句是动态生成的，条件是不定的，未必一定会对Lows和Tops查询还可能是其他字段，如果把所有可能查询的字段都建立索引，我怕会影响插入和修改的速度。能不能通过调整Oracle的环境来优化查询？或者写出更优化的SQL语句？我认为select count(*) from t这样的语句应该用不到什么索引，
其实很不好意思的，我不是很会看执行计划，也只能在Developer中的“解释计划窗口”中看执行计划（但不是很理解），不知道在SQL＊PLUS里怎么看，在Developer中我无法拷贝，因为执行计划是在表格中显示的。to waterfirer(水清)
4-5秒是正常的吗？但Oracle中不会对４W条记录处理起来都那么吃力吧，我郁闷死了，速度要怎么样才能提上来啊～～～～～

Answer 6

我试了一下，好像在t1上建不建索引没有什么区别。
我建了一个30列的表，有3w数据，
select count(*) from t;
30000条数据2秒
select * from t where t1 like '%我们%' order by reid; 
25条数据需要19秒
select * from t where t1 like '%我%' order by reid; 
25条数据需要9.5秒你的那个0.3秒是不是太快了？

Answer 7

select count(*) from t这样的语句应该用不到什么索引否，会使用主键索引t1字段查询不能使用索引引起了全表扫描
楼主可以move一下表，使表紧凑一些或者把主键和t1字段拿出来建一个紧凑的表，提高全表扫描的速度

Answer 8

to  nebulaly(极高明而道中庸) 
楼主可以move一下表，使表紧凑一些如何Move啊？能讲得详细点吗？在你的机器上对４W条记录做无条件Count，需要多少时间？我建立了一个简单的表格，只有３列，也生成了４W记录，Count，只需要0.11秒，我看很多资料上说列数对查询速度影响不大，我现在的T表有６０列，Count一下需要４－５秒，但如果是３列，就只需要0.11秒？这是什么原因呢？？？

Answer 9

alter table scott.emp move因为没有主键，count(*)做全表扫描
60列的表当然比3列的表要大另外看一下buffer cache和shared pool是不是设得太大了

Answer 10

>>我试了一下，好像在t1上建不建索引没有什么区别。
如果你的like开头没有%，结尾有%,那么索引会用的,但是只是为了在B树上缩小查寻范围，最后在叶结点上还是要扫描，对效率还有点提高，但是如果%开头，肯定要做全表扫描>>我的表格中只有４W表记录，６０列。就无条件Count一下，需要４秒多５秒。这正常吗？
大家的机器配置不一样也有可能有很大差别，不过你真的是很慢！我觉得不正常除非你的机器暴烂。>>select * from dvfirstreturn where TopS >=80 and LowS <=90 and  GardenName like '%花园%' order by reid desc;符合条件的记录有１３条，需要２９秒，其中reid是主键，GardenName是唯一键，其他没有建立索引
TopS和LowS上建索引，如果还慢，手工更新statistics，最好在reid上建立clustered index，这样oracle对这个列的排序的时间其实就省了

Answer 11

to DanielYWoo(绿色毒汁)
  我一直希望能＂在reid上建立clustered index＂，最好能在reid上物理降序排列．可是我无法实现，在SQL　Server中建立聚集索引可以按物理升序排列，但在Oracle中却不行，你能告诉我如何实现吗？排序是最大的障碍，不要排序就可以快很多的．
　如果能不用　like '%'那当然好，可是不行啊，需求是要模糊查询的，能用其他方式实现这种两头都模糊的查询吗？
　我的机器１７００＋　１G内存，硬盘是７２００转的，感觉运行一些大型软件都还不错的．to　nebulaly(极高明而道中庸)
　move会影响物理存储结构吗？能说说Move的原理吗？我的６０列的表格上有主键的．
　＂另外看一下buffer cache和shared pool是不是设得太大了＂　这个要多少才大，如何调整才比较好呢？

Answer 12

to iihero(阿黑哥)
  现在要改变表结构基本是不可能的．你说不超过１５个字段，那么字段长度可以很长吗？如果是１５个很长的字符型字段组成的表格，会产生行迁移吗？

Answer 13

move就是重建表，这样可以消除行迁移和碎片，重置高水位标志，全表扫描就快了因为楼主的查询需要4-5秒执行时间，说明进行了磁盘读写有三种可能性
1、sort_area_size太小，发生磁盘排序，
2、buffer pool等oracle内存结构太大，操作系统使用虚拟内存发生了物理读写
3、buffer pool太小，不能缓冲查询需要的数据，但是表只有4W行，可能性不大

Answer 14

>>我一直希望能＂在reid上建立clustered index＂，最好能在reid上物理降序排列．可是我无法实现http://www.liacs.nl/databases/show.cgi?indexing>>我的机器１７００＋　１G内存，硬盘是７２００转的，感觉运行一些大型软件都还不错的．
CPU太差，以前我是用P4 2.66 + 1G也不爽，现在换了3Ghz的双线程，感觉除了Task Manager显示两个CPU之外，没什么差别。还是装到服务器上玩本地才跑的比较轻松。

Answer 15

在sqlplus中执行语句
set autotrance on
然后再执行sql语句就有执行计划了.