以下sql如何优化

Question

1.首先是一个视图CREATE OR REPLACE VIEW v_info AS
(SELECT code, TO_CHAR (id) AS no,
           agent_code AS agent_code1, '' AS agent_code2, '' AS agent_code3,
           (SELECT COUNT (1)
              FROM agd_pping apm2
             WHERE apm2.code = s.code
               AND apm2.id = s.id
               AND apm2.is_pol = 'N'
               AND NOT EXISTS (SELECT 1
                                FROM policy v
                                WHERE v.no = apm2.no)) AS number1,
      FROM bbs_info s
     WHERE del_flag <> 'Y')
   UNION ALL
   (SELECT re_code AS code,
           t.no AS no
           TO_CHAR(mod(t.gtet_no,1000000)) AS agent_code1,
           TO_CHAR(mod(t.gtet_no1,1000000)) AS agent_code2,
           TO_CHAR(mod(t.gtet_no2,1000000)) AS agent_code3
       FROM policy t, status sts
       WHERE t.no = sts.no
       AND sts.status = 'I'
       AND NOT EXISTS (SELECT 1
                        FROM mapping_table u
                        WHERE u.no = t.no AND u.is_pol = 'Y'))
   UNION ALL
   (SELECT '' AS code,no,
    TO_CHAR(MOD(agt1,1000000)) AS agent_code1,
    TO_CHAR(MOD(agt12,1000000)) AS agent_code2,
    '' as agent_code3
     FROM life v
     WHERE NOT EXISTS (SELECT 1
                        FROM mapping_table w
                        WHERE w.no = v.no AND w.is_pol = 'N')
)该视图的相关联的表单数据均为100W条记录问题如下:
1.单单从这个view里取记录,很快--也就3秒左右,如果要select count(1) from  v_info  
where code = 'L' and (AGENT_CODE1='9865' or AGENT_CODE2='9865' or AGENT_CODE3='9865');计算记录数目则很慢,需要30秒左右!!!
2.select * 
from ( no,id,status,u_date
from  v_info where code = 'L' and (AGENT_CODE1='32854' or AGENT_CODE2='32854' or AGENT_CODE3='32854') 
order by  u_date desc,id desc) 
where rownum <= 10;这样取出头十条记录----也需要30-40秒
3.客户要求要很快,这样不行,希望大家出手相助,多谢!!!!

Answer 1

where 后面的 or是不是对性能有影响呀,去掉后就会查询等速度快一点,这个or---(AGENT_CODE1='32854' or AGENT_CODE2='32854' or AGENT_CODE3='32854') 
有没有取代的办法,大家都发表高见呀!!

Answer 2

VIEW后面的where条件是否可以建索引,能建的话怎么建??我建了索引没有作用!!

Answer 3

执行计划(explain plan)
Explain plan forSELECT code, TO_CHAR (id) AS no, 
          agent_code AS agent_code1, '' AS agent_code2, '' AS agent_code3, 
          (SELECT COUNT (1) 
              FROM agd_pping apm2 
            WHERE apm2.code = s.code 
              AND apm2.id = s.id 
              AND apm2.is_pol = 'N' 
              AND NOT EXISTS (SELECT 1 
                                FROM policy v 
                                WHERE v.no = apm2.no)) AS number1, 
      FROM bbs_info s 
    WHERE del_flag <> 'Y') 
  UNION ALL 
  (SELECT re_code AS code, 
          t.no AS no 
          TO_CHAR(mod(t.gtet_no,1000000)) AS agent_code1, 
          TO_CHAR(mod(t.gtet_no1,1000000)) AS agent_code2, 
          TO_CHAR(mod(t.gtet_no2,1000000)) AS agent_code3 
      FROM policy t, status sts 
      WHERE t.no = sts.no 
      AND sts.status = 'I' 
      AND NOT EXISTS (SELECT 1 
                        FROM mapping_table u 
                        WHERE u.no = t.no AND u.is_pol = 'Y')) 
  UNION ALL 
  (SELECT '' AS code,no, 
    TO_CHAR(MOD(agt1,1000000)) AS agent_code1, 
    TO_CHAR(MOD(agt12,1000000)) AS agent_code2, 
    '' as agent_code3 
    FROM life v 
    WHERE NOT EXISTS (SELECT 1 
                        FROM mapping_table w 
                        WHERE w.no = v.no AND w.is_pol = 'N') 
Select * from table(dbms_xplan.display);看看结果中有没有全表扫描。

Answer 4

执行计划(explain plan) 
Explain plan for SELECT code, TO_CHAR (id) AS no, 
          agent_code AS agent_code1, '' AS agent_code2, '' AS agent_code3, 
          (SELECT COUNT (1) 
              FROM agd_pping apm2 
            WHERE apm2.code = s.code 
              AND apm2.id = s.id 
              AND apm2.is_pol = 'N' 
              AND NOT EXISTS (SELECT 1 
                                FROM policy v 
                                WHERE v.no = apm2.no)) AS number1, 
      FROM bbs_info s 
    WHERE del_flag <> 'Y') 
  UNION ALL 
  (SELECT re_code AS code, 
          t.no AS no 
          TO_CHAR(mod(t.gtet_no,1000000)) AS agent_code1, 
          TO_CHAR(mod(t.gtet_no1,1000000)) AS agent_code2, 
          TO_CHAR(mod(t.gtet_no2,1000000)) AS agent_code3 
      FROM policy t, status sts 
      WHERE t.no = sts.no 
      AND sts.status = 'I' 
      AND NOT EXISTS (SELECT 1 
                        FROM mapping_table u 
                        WHERE u.no = t.no AND u.is_pol = 'Y')) 
  UNION ALL 
  (SELECT '' AS code,no, 
    TO_CHAR(MOD(agt1,1000000)) AS agent_code1, 
    TO_CHAR(MOD(agt12,1000000)) AS agent_code2, 
    '' as agent_code3 
    FROM life v 
    WHERE NOT EXISTS (SELECT 1 
                        FROM mapping_table w 
                        WHERE w.no = v.no AND w.is_pol = 'N') Select * from table(dbms_xplan.display); 
看看结果中有没有全表扫描。

Answer 5

三个union all分开单独测试一下估计是第一个太耗时间，明显可以用表关联，这样子查询效率不好

Answer 6

和七楼的一样,把单个SQL分别测试,来看一下它执行效率如何
重点优化!效率差的那段SQL语句!

Answer 7

根据大家的意见,把sql分开进行各自测试,有如下发下:
   1.查询慢的问题在以下的这句sql上:
      SELECT code, TO_CHAR (id) AS no, 
          agent_code AS agent_code1, '' AS agent_code2, '' AS agent_code3, 
          (SELECT COUNT (1) 
              FROM agd_pping apm2 
            WHERE apm2.code = s.code 
              AND apm2.id = s.id 
              AND apm2.is_pol = 'N' 
              AND NOT EXISTS (SELECT 1 
                                FROM policy v 
                                WHERE v.no = apm2.no)) AS number1, 
      FROM bbs_info s 
    WHERE del_flag <> 'Y')
     
     2.但是有没有办法建上索引,而查询该视图的sql:(因为view的AGENT_CODE2和AGENT_CODE3为空,该如何建表--            bbs_info的索引? )
       .select * 
        from ( no,id,status,u_date 
        from  v_info where code = 'L' and (AGENT_CODE1='32854' or AGENT_CODE2='32854' or          
        AGENT_CODE3='32854') 
         order by  u_date desc,id desc) 
         where rownum <= 10;
     3.谢谢大家的不懈帮助,问题解决,马上给分!!!

Answer 8

SELECT code, TO_CHAR (id) AS no, 
          agent_code AS agent_code1, '' AS agent_code2, '' AS agent_code3, 
          (SELECT COUNT (1) 
              FROM agd_pping apm2 
            WHERE apm2.code = s.code 
              AND apm2.id = s.id 
              AND apm2.is_pol = 'N' 
              AND NOT EXISTS (SELECT 1 
                                FROM policy v 
                                WHERE v.no = apm2.no)) AS number1, 
      FROM bbs_info s 
    WHERE del_flag <> 'Y') 
_______________________________
先把那段NOT exists去掉!
SELECT code, TO_CHAR (id) AS no, 
          agent_code AS agent_code1, '' AS agent_code2, '' AS agent_code3, 
          (SELECT COUNT (1) 
              FROM agd_pping apm2 
            WHERE apm2.code = s.code 
              AND apm2.id = s.id 
              AND apm2.is_pol = 'N' )
             AS number1, 
      FROM bbs_info s 
    WHERE del_flag <> 'Y') 估计是NOT exists惹的祸
你可以把给数据实例,看SQL用别的办法来实现你的要求,不用NOT EXISTS!

Answer 9

为了测试我把NOT exists--改成--exists,但是分析了一下那个view,索引还是没有起作用,查询还是需要很长时间!!!

Answer 10

这个表--bbs_info 的上面该怎么建索引,才能是索引有效,关键是视图里面的 where只有一个条件del_flag;
而v_info 视图的where却有条件code,AGENT_CODE1,AGENT_CODE2,AGENT_CODE3!!!这样的情况该在表bbs_info 上的那个(些)字段检索引!
 thanks!!

Answer 11

可以跟些实例的数据吧,看看有SQL有没有可以优化,或是换种写法的!
还有
SELECT code, TO_CHAR (id) AS no, 
          agent_code AS agent_code1, '' AS agent_code2, '' AS agent_code3
      FROM bbs_info s 
    WHERE del_flag <> 'Y') 测试一下这个语句用的时间?

Answer 12

20多妙,实例数据---没有具体的数据,这个view就是把三个没有相同字段的表uion all起来成一个表的数据,然后用来查询!

Answer 13

为了测试---是否是子查询的问题影响了速度,我去掉子查询,然后再执行查找view查找,
 select * 
        from ( no,id,status,u_date 
        from  v_info where code = 'L' and (AGENT_CODE1='32854' or AGENT_CODE2='32854' or          
        AGENT_CODE3='32854') 
        order by  u_date desc,id desc) 
        where rownum <= 10; 
  结果是还要20多秒,没有变化,看来子查询的影响不大,还不是真正的病根!!!

Answer 14

按union all拆成3个view
然后分别查询条件的字段都加索引
有函数的字段，加函数索引这样就可以比较快了

Answer 15

不能拆开,要是能拆开,那就不建索引了,每个表单独查找就ok了,正是因为查找在这三个表里面同时查,所以建了view!!

Answer 16

但是如果我把(20秒左右)
select * 
        from ( no,id,status,u_date 
        from  v_info where code = 'L' and (AGENT_CODE1='32854' or AGENT_CODE2='32854' or          
        AGENT_CODE3='32854')         order by  u_date desc,id desc) 
        where rownum <= 10; 改成下面这样,就很快(2秒左右)
select * 
        from ( no,id,status,u_date 
        from  v_info where code = 'L' and (AGENT_CODE1='32854')        order by  u_date desc,id desc) 
        where rownum <= 10;