SQL效能分析

Question

select (select pid
                      from product_maintain pm
                     where pm.product_number = ph.product_number
                       and pm.rstate = ph.r_state)
              from pack_history ph
             where ph.sn = p_serial_num
               and rownum <= 1) in
           (select child_pid
              from product_level pl
             where pl.pid = (select pid
                               from work_order wo
                              where wo.order_type = 'DPY'
                                and wo.line_id = p_line_id
                                and wo.order_state = 1))這樣的sql﹐你們覺得跟表直接關聯﹐在效能方面差別大嗎?也就是用表關聯和這種子查詢在效能差大嗎？
哪種更好點？

Answer 1

                                    select count(1)
     ----into v_allowance
      from dual
     where (select (select pid
                      from product_maintain pm
                     where pm.product_number = ph.product_number
                       and pm.rstate = ph.r_state)
              from pack_history ph
             where ph.sn = 'BX900000YQ'
               and rownum <= 1) in
           (select child_pid
              from product_level pl
             where pl.pid = (select pid
                               from work_order wo
                              where wo.order_type = 'DPY'
                                and wo.line_id = 'DL1'
                                and wo.order_state = 1))

Answer 2

要看sql的效果，最重要的是看这个sql的执行计划

Answer 3

建议不要用 ‘in’，用exists 将能在性能上好很多

Answer 4

另外 IN 不一定就不EXISTS 的效率差:)在不同的执行条件下这两个的效率是不一定的 切记