强难的一个效率问题！！（表中有近1000万条数据）

Question

现有一个table1索引为COL1,COL2,COL3,COL4
我要判断符合条件的数据是否超过百条。（表中有近1000万条数据）
SELECT count(0) FROM (SELECT * FROM table1 WHERE COL1 = 'A' and COL2 = 'B' and COL3 LIKE '%C%' AND ROWNUM < 102)
和
SELECT count(0) FROM (SELECT COL1 FROM table1 WHERE COL1 = 'A' and COL2 = 'B' and COL3 LIKE '%C%' AND ROWNUM < 102)
上面的那个效率更高一些？为什么啊！？还可以怎么优化？

Answer 1

用不上索引，效率低
因为like '%...%'，所以用不了索引，like 子句是这样判断的：
如果第一个字符为'%'则做全表扫描，因为是全匹配
建议修改条件

Answer 2

like的条件不能改啊！就是想要那种结果！

Answer 3

无论索引建在(COL1,COL2,COL3,COL4)上,
还是分别建在COL1,COL2,COL3,COL4 四个列上 ,COL1 = 'A' and COL2 = 'B'  这两个条件都会去用索引的, 但
LIKE '%C%'是用不上索引了..
其实看到你的列值COL1 = 'A' and COL2 = 'B', 就可以想象, 这两个列的值重复很多(就是有很多行的COL1值相同), 所以建B-TREE索引效率提高不了多少, 不如在四个列上分别建BITMAP索引. 
另外, 表实在太大 ,  那就按COL1 或COL2 建表分区, 这样效果绝对比你搞那些B-TREE索引高很多.  (个人意见~ )

Answer 4

现在表的结构不能变，而且COL1 = 'A' and COL2 = 'B'  这两个条件都不是主要条件，在有些情况下还没有；最重要的就是[COL3 LIKE '%C%']这个条件了。
我现在在159万的数据库中执行时，当满足条件的数据超过一千条时，第一个语句时间为10秒左右；第二个未2分多。为什么会这样呢？

Answer 5

在自己的库里测试了一下，4000万记录，找1000条，无索引。select * 方式66秒，select col1 方式70秒，相差不是很大，但select *确实稍微快一些。据个人猜测：由于硬盘读取时间一样，而select * 的结果直接整行复制,select col1需要解读每一行，所以在CPU方面稍微耗了一些。

Answer 6

最近，我换了个数据库又执行了一下。
sql也做了一些变化
SELECT  count(0)  FROM  (SELECT  *  FROM  table1  WHERE  COL1  =  'A'  and  COL2  =  'B'  and  COL3  LIKE  '%C%'  AND  ROWNUM  <  102 ORDER BY COL1,COL2,COL3,COL4)  
和  
SELECT  count(0)  FROM  (SELECT  COL1  FROM  table1  WHERE  COL1  =  'A'  and  COL2  =  'B'  and  COL3  LIKE  '%C%'  AND  ROWNUM  <  102 ORDER BY COL1,COL2,COL3,COL4)  数据量为800万时，检索的数据不存在时，第二个效率高为2分钟；而第一个为4分钟左右。
另外，table1有一些变化，table1不再是一个表了，而是由两个表得到的一个视图。
其中，COL1,COL2,COL3,COL4是其中一个表的索引。
请大家再帮帮忙！看可不可以！

Answer 7

建partition ~ 
按col1或col2的值来建

Answer 8

这个看得，select * from table 未必比select 1 from table 因为有索引的关系。
其实要看效率的话你各自都查询100000条比较下就好了

Answer 9

试试这个：
SELECT sum(1) FROM (SELECT COL3 FROM table1 WHERE COL1 = 'A' and COL2 = 'B' ) where  COL3 LIKE '%C%' AND ROWNUM < 102

Answer 10

晕like '%c%'当然要全表扫描了，得改成(COL3 like '%c' or  COL3 like 'c%')'%xxx%'　这样写是不会用到索引的,一般都要分成两段写。