性能优化日志【20090708】

查询语句:

 select level, t.*, t.pcontent_name || '【' || t.pact_code || '】' allName
    from v_list_pact_contents t
   where t.main_pact_code = 'JAQ/C3'
     AND T.base_pid is not null
   start with t.parent_id is null
  connect by t.parent_id = prior t.id
   order siblings by t.pcontent_code

 

执行时间:4.4s

优化过程:

分析当前查询的目标应用场景发现,仅有id,Item_type,pcontent_type,pcontent_code1,unit被使用,因此,修改结果列表为

select level,
       t.id,
       t.ITEM_TYPE,
       t.PCONTENT_TYPE,
       t.pcontent_code1,
       t.unit,    
       t.pcontent_name || '【' || t.pact_code || '】' allName

后执行时间0.2s

查询语句:

select level, t.*, t.pcontent_name || '【' || t.pact_code || '】' allName
  from (select distinct t.*
          from pact_contents t
         start with t.id in (select distinct t.id
                               from pact_below_viseinfo_base   b,
                                    pact_below_viseinfo_detail d,
                                    pact_contents              t
                              where b.cur_state >= 0
                                and b.vise_type = 0
                                and b.span_code <= '200905C'
                                and b.pact_code in
                                    (select P.pact_code
                                       from pact_base p, pact_base t
                                      where p.relation_id = t.id
                                        and t.pact_code = 'JAQ/C3')
                                and d.pcontent_id = t.id
                                and t.pact_code = b.pact_code
                                AND t.base_pcontent_id is null)
        connect by t.id = prior t.parent_id
         order by t.parent_id, t.pact_code, t.pcontent_code) t
 start with t.parent_id is null
connect by t.parent_id = prior t.id

 

执行时间:2.7s

优化过程:

子查询(颜色标识)独立执行时间2.7s,分析该子查询发现

pact_contents.pact_code没有建立索引,创建索引(index_PACT_CONTENTS_090708)后,子查询执行时间2.0s,宿主查询执行时间2.0s

优化结论:

经上述处理之后,可节省查询时间大约4.9 s左右