一条SQL的优化

其它的不多说了，用了一些很平常的技巧
原SQL：
SELECT *
  FROM (SELECT ROWNUM num, connector_id, conn.sidea_port_id,
               conn.sideb_port_id, conn.rsc_spec_id AS line_rsc_spec_id,
               pg_trp_function.fun_getcodebyportandspec
                                      (1,
                                       conn.sidea_port_id,
                                       conn.sidea_port_spec_id
                                      ) AS up_dev_code,
               pg_trp_function.fun_getcodebyportandspec
                                     (2,
                                      conn.sidea_port_id,
                                      conn.sidea_port_spec_id
                                     ) AS up_port_code,
               cab.code AS cable_code, conn.code AS line_code,
               pg_trp_function.fun_getcodebyportandspec
                                    (1,
                                     conn.sideb_port_id,
                                     conn.sideb_port_spec_id
                                    ) AS down_dev_code,
               pg_trp_function.fun_getcodebyportandspec
                                   (2,
                                    conn.sideb_port_id,
                                    conn.sideb_port_spec_id
                                   ) AS down_port_code,
               pg_trp_function.fun_getdevidbyportandspec
                                     (conn.sidea_port_id,
                                      conn.sidea_port_spec_id
                                     ) AS up_device_id,
               pg_trp_function.fun_getdevidbyportandspec
                                   (conn.sideb_port_id,
                                    conn.sideb_port_spec_id
                                   ) AS down_device_id,
               pg_trp_function.fun_getdevspecidbyportandspec
                               (conn.sidea_port_id,
                                conn.sidea_port_spec_id
                               ) AS up_dev_rsc_spec_id,
               pg_trp_function.fun_getdevspecidbyportandspec
                             (conn.sideb_port_id,
                              conn.sideb_port_spec_id
                             ) AS down_dev_rsc_spec_id,
               (SELECT dvl.NAME
                  FROM device_item di,
                       discrete_value_list dvl
                 WHERE di.item_spec_id = 200006
                   AND di.device_id = conn.device_id
                   AND di.item_spec_id = dvl.item_spec_id
                   AND di.VALUE = dvl.meta_key_value
                   AND ROWNUM <= 1) AS cab_spec_name,
               pg_trp_function.fun_getdictname
                                      (conn.line_ability)
                                                         AS line_ability_name,
               pg_trp_function.fun_getdevnamebyportandspec
                             (conn.sidea_port_id,
                              conn.sidea_port_spec_id
                             ) AS up_dev_rsc_spec_name,
               pg_trp_function.fun_getdevnamebyportandspec
                           (conn.sideb_port_id,
                            conn.sideb_port_spec_id
                           ) AS down_dev_rsc_spec_name,
               pg_trp_function.fun_getmdfrackcdbyportandspec
                                   (conn.sidea_port_id,
                                    conn.sidea_port_spec_id,
                                    conn.sideb_port_id,
                                    conn.sideb_port_spec_id
                                   ) AS mdf_rack_code
          FROM connector conn,
               device cab,
               user_side_port usp,
               (SELECT s.side_id
                  FROM side s, device dev, node nd
                 WHERE dev.device_id = nd.device_id
                   AND dev.rsc_spec_id IN (10301001, 10301002, 10301003)
                   AND dev.code = :1
                   AND dev.area_id = :2
                   AND s.node_id_a = nd.node_id) sa
         WHERE conn.device_id = cab.device_id
           AND conn.sidea_port_id = usp.port_id
           AND conn.side_id = sa.side_id
           AND conn.sidea_port_id IS NOT NULL
           AND conn.sideb_port_id IS NOT NULL
           AND usp.code >= :3
           AND usp.code <= :4)
 WHERE num BETWEEN :5 AND :6

优化后的SQL：
SELECT v.connector_id,
       v.sidea_port_id,
       v.sideb_port_id,
       v.rsc_spec_id AS line_rsc_spec_id,
       v.rsc_status_cd AS line_status,
       pg_trp_function.fun_getrscstatusname(v.rsc_status_cd) AS line_status_name,
       pg_trp_function.fun_getcodebyportandspec(1,v.sidea_port_id,v.sidea_port_spec_id) AS up_dev_code,
       pg_trp_function.fun_getcodebyportandspec(2,v.sidea_port_id,v.sidea_port_spec_id) AS up_port_code,
       (select d.code from device d where d.device_id = v.device_id) AS cable_code,
       v.code AS line_code,
       pg_trp_function.fun_getcodebyportandspec(1,v.sideb_port_id,v.sideb_port_spec_id) AS down_dev_code,
       pg_trp_function.fun_getcodebyportandspec(2,v.sideb_port_id,v.sideb_port_spec_id) AS down_port_code,
       pg_trp_function.fun_getdevidbyportandspec(v.sidea_port_id,v.sidea_port_spec_id) AS up_device_id,
       pg_trp_function.fun_getdevidbyportandspec(v.sideb_port_id,v.sideb_port_spec_id) AS down_device_id,
       pg_trp_function.fun_getdevspecidbyportandspec(v.sidea_port_id,v.sidea_port_spec_id) AS up_dev_rsc_spec_id,
       pg_trp_function.fun_getdevspecidbyportandspec(v.sideb_port_id,v.sideb_port_spec_id) AS down_dev_rsc_spec_id,
       (SELECT dvl.NAME
          FROM device_item di,
               discrete_value_list dvl
         WHERE di.item_spec_id = 200006
           AND di.device_id = v.device_id
           AND di.item_spec_id = dvl.item_spec_id
           AND di.VALUE = dvl.meta_key_value
           AND ROWNUM <= 1) AS cab_spec_name,
        pg_trp_function.fun_getdictname(v.line_ability) AS line_ability_name,
        pg_trp_function.fun_getdevnamebyportandspec(v.sidea_port_id,v.sidea_port_spec_id) AS up_dev_rsc_spec_name,
        pg_trp_function.fun_getdevnamebyportandspec(v.sideb_port_id,v.sideb_port_spec_id) AS down_dev_rsc_spec_name,
        pg_trp_function.fun_getmdfrackcdbyportandspec(v.sidea_port_id,v.sidea_port_spec_id,v.sideb_port_id,v.sideb_port_spec_id) AS mdf_rack_code
from (
  SELECT /*+no_merge(sa) ORDERED use_nl(sa, conn, cab, usp) INDEX_JOIN(O)*/
                 ROWNUM num, conn.connector_id, conn.code, conn.rsc_status_cd, conn.rsc_spec_id, conn.line_ability,
                             conn.sidea_port_id, conn.sidea_port_spec_id,
                             conn.sideb_port_id, conn.sideb_port_spec_id,
                             sa.device_id
            FROM (select /*+ORDERED*/ s.side_id, sd.device_id
                    from device dev
                   inner join node nd on nd.device_id = dev.device_id
                   inner join side s  on s.node_id_a = nd.node_id
                   inner join side_device sd on sd.side_id = s.side_id
                   inner join device_item di on di.device_id = sd.device_id and di.item_spec_id = 200006 and di.value = 4
                  where dev.code = 'MDF-0315'
                    and dev.rsc_spec_id in (10301001, 10301002, 10301003)
                    and dev.area_id = 127
                     ) sa,
                 connector conn,
                 user_side_port usp
           WHERE conn.side_id = sa.side_id
             AND conn.device_id = sa.device_id
             AND conn.sidea_port_id = usp.port_id
             AND conn.sidea_port_id IS NOT NULL
             AND conn.sideb_port_id IS NOT NULL
             AND usp.code >= '001-01-00001'
             AND usp.code <= '079-01-00001'
             AND ROWNUM <= 100) v
 where num >= 1