求高手帮忙调试一个sql

Question

求高手帮忙调试一个sql表tbl基数： 三千万条
Index_a 和 index_b 是表tbl上的索引，根据不同查询需求建立
Index_a (CZO_ID, VES_ID, ENTRY_TIME DESC, OUT_TIME DESC, ID )
Index_b(VES_ID, OUT_TIME, ENTRY_TIME, CZO_ID, ID)目前的情况是括号里面的查询t速度很快，2~3秒，但加上外层过滤条件“t.czo_id = 80”就慢了很多，15秒以上SELECT t.*
  FROM (select /*+ index(a index_a)*/
         a.id,
         a.ves_id,
         a.entry_time,
         a.out_time,
         (SELECT /*+ index(b index_b)*/
           b.czo_id
            FROM tbl b
           WHERE b.ves_id = a.ves_id
             AND b.OUT_TIME > a.out_time
             AND ROWNUM = 1) as czo_id
          from tbl a
         where a.czo_id = 70
           and a.entry_time < to_date('2011-06-01', 'yyyy-mm-dd')
           and a.out_time >= to_date('2011-01-01', 'yyyy-mm-dd')
           and a.out_time < to_date('2011-06-01', 'yyyy-mm-dd')
           ) t
WHERE t.czo_id = 80

Answer 1

目前的情况是括号里面的查询t速度很快，2~3秒--你这个查询是在pl/SQL等工具 里面执行还是sqlplus 执行 是3秒？？？

Answer 2

看sql跑多少时间千万别在pl/sql看，工具有优化，是假象。下面的sql在sqlplus里面跑下看要多久？select /*+ index(a index_a)*/
         a.id,
         a.ves_id,
         a.entry_time,
         a.out_time,
         (SELECT /*+ index(b index_b)*/
           b.czo_id
            FROM tbl b
           WHERE b.ves_id = a.ves_id
             AND b.OUT_TIME > a.out_time
             AND ROWNUM = 1) as czo_id
          from tbl a
         where a.czo_id = 70
           and a.entry_time < to_date('2011-06-01', 'yyyy-mm-dd')
           and a.out_time >= to_date('2011-01-01', 'yyyy-mm-dd')
           and a.out_time < to_date('2011-06-01', 'yyyy-mm-dd')
           

Answer 3

这个数据太多了，
我做了分页取前20条和集合总数，不加外层过滤条件“t.czo_id = 80”， 大约2秒。
但是加上就很慢了

Answer 4

新手乱吐，吐错了，请纠正，不要骂我哈。
今天看书说，组合index，超过3个的话，跟全表扫描差别不大。
请各位大大审阅

Answer 5

(SELECT /*+ index(b index_b)*/           b.czo_id            FROM tbl b           WHERE b.ves_id = a.ves_id             AND b.OUT_TIME > a.out_time             AND ROWNUM = 1) as czo_id
既然外层要加czo_id = 80
不妨直接放到里面试一下。
(SELECT /*+ index(b index_b)*/           b.czo_id            FROM tbl b           WHERE b.ves_id = a.ves_id             AND b.OUT_TIME > a.out_time             AND
b.czo_id = 80 AND ROWNUM = 1) as czo_id

Answer 6

SELECT *
   FROM (SELECT T3.*, ROWNUM ROW_NUM
           FROM (SELECT t2.*, MAX(ROWNUM) OVER() TOTALCOUNT
                   FROM (SELECT t.*
                           FROM (SELECT /*+ index(a index_a)*/
                                  a.id,
                                  a.ves_id,
                                  a.entry_time,
                                  a.out_time,
                                  (SELECT /*+ index(b index_b)*/
                                    b.czo_id
                                     FROM tbl b
                                    WHERE b.ves_id = a.ves_id
                                      AND b.OUT_TIME > a.out_time
                                      AND ROWNUM = 1) AS czo_id
                                   FROM tbl a
                                  WHERE a.czo_id = 70
                                    AND a.entry_time <
                                        to_date('2011-06-01', 'yyyy-mm-dd')
                                    AND a.out_time >=
                                        to_date('2011-01-01', 'yyyy-mm-dd')
                                    AND a.out_time <
                                        to_date('2011-06-01', 'yyyy-mm-dd')) t
                          WHERE t.czo_id = 80) t2) t3
          WHERE ROWNUM <= 10)
  WHERE ROW_NUM >= 1
Execution plan:SELECT STATEMENT, GOAL = ALL_ROWS                                   Cost=20  Cardinality=10         Bytes=830
COUNT STOPKEY                                      
  TABLE ACCESS BY INDEX ROWID         Object name=tbl         Cost=4     Cardinality=1  Bytes=17
   INDEX RANGE SCAN                          Object name=index_b   Cost=3     Cardinality=2  
VIEW                                                             Cost=20  Cardinality=10         Bytes=830
  COUNT STOPKEY                                             
   VIEW                                                         Cost=20  Cardinality=106      Bytes=7420
    WINDOW BUFFER                                                Cost=20  Cardinality=106      Bytes=6042
     COUNT                                       
      VIEW                                                       Cost=20  Cardinality=106      Bytes=6042
       INDEX RANGE SCAN                      Object name=index_a    Cost=20  Cardinality=106      Bytes=34981. 若去掉MAX(ROWNUM) OVER() TOTALCOUNT，性能较好，猜想是因为只需要计算出前10条
2. 若保留MAX(ROWNUM) OVER() TOTALCOUNT，去掉外层过滤条件WHERE t.czo_id = 80，性能较好，猜想是因为index_a的作用，所有select的字段都在index_a里，过滤条件也在index_a里，所以即使计算所有符合条件的条数，也很快
3. 保留MAX(ROWNUM) OVER() TOTALCOUNT和外层过滤条件WHERE t.czo_id = 80，性能较差，猜想因为即便有分页只需要取前10条，但totalcount需要计算所有符合条件的条数，而此时index_a和index_b都需要参与， 不知道这种情况，该怎么调