性能优化日志【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 nullconnect 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左右