请各位oracle高手给优化一下查询sql

Question

两张表topic表（百万级记录），cmt表（千万级记录）。
想通过下面的sql查询，目前的执行有两个问题，一个是第一次执行的时候时间很慢10s左右，然后一般的执行时间在2s左右。
看能否给优化一下。谢谢。
select * from(select c.cmt_id,c.cmt_content,c.cmt_author,c.cmt_ip,c.cmt_count,c.cmt_ctime from cmt c,topic t
       where c.cmt_topicid=t.topic_id and c.cmt_status=1 and c.cmt_ctime>to_date('2008-01-01','yyyy-MM-dd')   
       and t.topic_ctime>to_date('2008-01-01','yyyy-MM-dd') and t.topic_category like '200390431%' and instr(t.topic_title,'保时捷')>0 
       order by c.cmt_count desc) where rownum<=50;

Answer 1

可以把 c.cmt_topicid=t.topic_id 
放到最后，这样在执行连接条件之前已经把两表的数据过滤掉一些了 可以增加查询速度。

Answer 2

把where的顺序换一下SELECT *
  FROM (SELECT   c.cmt_id, c.cmt_content, c.cmt_author, c.cmt_ip, c.cmt_count,
                 c.cmt_ctime
            FROM cmt c, topic t
           WHERE c.cmt_topicid = t.topic_id
             AND INSTR (t.topic_title, '保时捷') > 0
             AND t.topic_category LIKE '200390431%'
             AND c.cmt_status = 1
             AND c.cmt_ctime > TO_DATE ('2008-01-01', 'yyyy-MM-dd')
             AND t.topic_ctime > TO_DATE ('2008-01-01', 'yyyy-MM-dd')
        ORDER BY c.cmt_count DESC)
 WHERE ROWNUM <= 50

Answer 3

楼上你好 好久不见你说的顺序似乎INDEX用不到多少可以换成SELECT *
  FROM (SELECT   c.cmt_id, c.cmt_content, c.cmt_author, c.cmt_ip, c.cmt_count,
                 c.cmt_ctime
            FROM cmt c, topic t
           WHERE c.cmt_topicid = t.topic_id
             AND c.cmt_status = 1
             AND INSTR (t.topic_title, '保时捷') > 0
             AND t.topic_category LIKE '200390431%'
             AND c.cmt_ctime > TO_DATE ('2008-01-01', 'yyyy-MM-dd')
             AND t.topic_ctime > TO_DATE ('2008-01-01', 'yyyy-MM-dd')
        ORDER BY c.cmt_count DESC)
 WHERE ROWNUM <= 50
INDEX cmt_status 和  TOPICID   记得设置为有效INDEX

Answer 4

质的提高~  其实如果你SQL复杂点还有希望在不改变表结构的前提下但是SQL不难的时候提高的也就不多了哦目前只想到INDEX入手 ~ ~  本人太菜....hebo2005  是老鸟估计有点法子

Answer 5

贴出语句的Explain Plan。
感觉topic_category应该是过滤能力很强的，是否加上了索引。
不要加太多索引，否则更新时会非常的慢。

Answer 6

恩，顺序和加条件确实有点作用，最原始的sql执行时间是20-30s，后来优化了一下只能到2s左右了。

Answer 7

你可以贴下你的执行计划,看看瓶颈在哪
如果不会用
可以在sql plus里
运行set autotrace on explain
或者set autotrace on
最后几行就是执行计划

Answer 8

这个是执行计划：Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=15987 Card=50 Byte
          s=105750)   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=15987 Card=3320 Bytes=7021800)
   3    2       SORT (ORDER BY STOPKEY) (Cost=15987 Card=3320 Bytes=95
          9480)   4    3         TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'CMT' (TABLE
          ) (Cost=7 Card=6 Bytes=1146)   5    4           NESTED LOOPS (Cost=15987 Card=3320 Bytes=959480)
   6    5             TABLE ACCESS (BY INDEX ROWID) OF 'TOPIC' (TABLE)
           (Cost=11982 Card=580 Bytes=56840)   7    6               BITMAP CONVERSION (TO ROWIDS)
   8    7                 BITMAP AND
   9    8                   BITMAP CONVERSION (FROM ROWIDS)
  10    9                     SORT (ORDER BY)
  11   10                       INDEX (RANGE SCAN) OF 'IN_TOPIC_CATEGO
          RY' (INDEX) (Cost=375 Card=151823)  12    8                   BITMAP CONVERSION (FROM ROWIDS)
  13   12                     SORT (ORDER BY)
  14   13                       INDEX (RANGE SCAN) OF 'IN_TOPIC_CTIME'
           (INDEX) (Cost=1490 Card=151823)  15    5             INDEX (RANGE SCAN) OF 'IDX_IN_CMT_TOPICID' (INDE
          X) (Cost=1 Card=60)

Answer 9

应该是
 10    9                    SORT (ORDER BY
这个消耗过大然后
5    4          NESTED LOOPS (Cost=15987 Card=3320 Bytes=959480) 
这个再消耗了点

Answer 10

应该是这两个形成了位图索引
t.topic_category 
          c.cmt_ctime 把你表上的索引贴出来看看，看看能不能改一下索引提高速度
我网上搜索了下，说可以建个联合索引防止位图索引
不过看情况你这是两张表形成的位图索引

Answer 11

是在cmt表中的cmt_debate列上有个bitmap类型的索引。
怎么看出来10 9消耗比较大的？

Answer 12

cmt_debate
你的SQL里没用到啊
你看每行的cost
还有每行的第一个号码和第二个号码
这两个表示执行的层次顺序的执行顺序如下
同一层的先检查编号高的
然后检查里面的子顺序，同样先选编号高，直到没有子层，然后从最里层开始执行
就是一个树查询，先查子叶，然后查兄弟叶，然后查根根的COST是包含叶的COST的
你的总COST是15987
11那行是最先执行的,
一直到8那行,出现另一个兄弟叶,形成位图索引
两个叶本身的COST都不大
但到6时，成本就有11982,不过这里也比较难说，可能下面的位图索引造成的，也有可能是本身造成
反正到这里差不多70%的时间耗掉了
然后到5,两个表做NESTED LOOPS
又消耗掉省下的这里可能可以优化下，你看下哪个表的数据量小，就放在from的最后边，或者指定驱动表

Answer 13

我已经把数据量小的表放到from的最后了，而且where语句的顺序也根据能够筛选掉的记录做了优化
但速度没什么变化。这个是优化后的sqlselect * from(select c.cmt_id,c.cmt_content,c.cmt_author,c.cmt_ip,c.cmt_count,c.cmt_ctime from cmt c,topic t
       where c.cmt_topicid=t.topic_id and c.cmt_status=1 and c.cmt_ctime>to_date('2008-01-01','yyyy-MM-dd')   
       and t.topic_ctime>to_date('2008-01-01','yyyy-MM-dd') and t.topic_category like '200390431%' and instr(t.topic_title,'一汽大众')>0 
       order by c.cmt_count desc) where rownum<=50;

Answer 14

cmt
bitmap    cmt_debate
normal    cmt_status
normal    cmt_category
normal    cmt_count
normal    cmt_ctime
normal    cmt_replyid
normal    cmt_topicid
unique    cmt_idtopic
normal    topic_category
normal    topic_count
normal    topic_ctime
unique    topic_id

Answer 15

normal    topic_category 
normal    topic_ctime 
你建成个联合索引看看,是否有效果

Answer 16

还有就是让你们的DBA对这两张表做下分析,这样会根据数据的分布来选择执行计划

Answer 17

select c.cmt_id,
       c.cmt_content,
       c.cmt_author,
       c.cmt_ip,
       c.cmt_count,
       c.cmt_ctime
  from (select cmt_id, cmt_content, cmt_author, cmt_count, cmt_ctime
          from cmt
         where cmt_status = 1
           and cmt_ctime > to_date('2008-01-01', 'yyyy-MM-dd')) t,
       (select topic_id
          from topic
         where topic_ctime > to_date('2008-01-01', 'yyyy-MM-dd')
           and topic_category like '200390431%'
           and instr(topic_title, '保时捷') > 0) b
 where c.cmt_topicid = t.topic_id and rownum<=50 order by c.cmt_count