How to replace the OR condition in the Oracle SQL query?-CodePudding

I am using Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Can somebody please tell me how to rewrite the below cursor query so that

it does not use the "OR" clause in the where condition

for performance benefits.

Basically the OR condition should be removed appropriately in the query.

CURSOR c_picklist_dtl IS
SELECT DISTINCT pd.dc_code,
               pd.storer,
               pd.picklist_key,
               pd.pickdetail_key,
               pd.line_no,
               pd.pick_type,
               pd.case_id,
               pd.order_type,
               pd.sub_type,
               pd.order_no,
               pd.item,
               pd.consignee,
               pd.bin_code,
               pd.lot,
               pd.pallet_id,
               pd.packkey,
               pd.to_pick_qty,
               am.bin_code dispatch_bin_code,
               ph.wave_id,
               ph.po_no,
               pd.pick_area_type,
               pd.assignment_id,
               pd.pick_method,
               pd.pack_method,
               pd.ord_priority,
               pd.pick_seq_no 

 FROM table1 ph,
      table2 pd,
      table3 ot,
      table4 am
WHERE    pd.dc_code = ph.dc_code
      AND pd.storer = ph.storer
      AND pd.picklist_key = ph.picklist_key
      AND ph.dc_code = p_dc_code
      AND ph.storer = p_storer
        AND (  (   ph.po_no = p_order_no
               AND p_order_prefix = 'X'
               AND pd.pick_method = 'P'
               AND pd.status = 'E' 
                                   )
           OR (   ph.wave_id = p_order_no
               AND p_order_prefix = 'W'
               AND pd.pick_method = 'P'
               AND pd.status = 'E' 
                                   ) 

          )
      AND pd.item = p_item
      AND pd.consignee = p_consignee
      AND pd.dc_code = ot.dc_code
      AND pd.order_type = ot.order_type
      AND pd.sub_type = ot.sub_type
      AND am.dc_code( ) = ot.dc_code
      AND am.dc_area( ) = ot.dispatch_area
      AND pd.to_pick_qty > 0
  ORDER BY pd.dc_code,
      pd.item,
      pd.consignee,
      pd.pick_seq_no,
      pd.pickdetail_key,
      pd.line_no;


Thanks

CodePudding user response：

Your OR clause is pretty much the same apart from one literal comparison and different matching column for one of the input parameters, which pretty much means you can replace:

   AND (  (   ph.po_no = p_order_no
           AND p_order_prefix = 'X'
           AND pd.pick_method = 'P'
           AND pd.status = 'E' 
                               )
       OR (   ph.wave_id = p_order_no
           AND p_order_prefix = 'W'
           AND pd.pick_method = 'P'
           AND pd.status = 'E' 
                               ) 

      )

with just something like:

AND p_order_no = CASE WHEN p_order_prefix = 'X' THEN ph.po_no
                      WHEN p_order_prefix = 'W' THEN ph.wave_id
                      ELSE NULL
                 END
AND pd.pick_method = 'P'
AND pd.status      = 'E'

CodePudding user response：

You can avoid repeating the 2 tests which are the same.
To go further you need to know the application and understand the logic.

        AND (  (   ph.po_no = p_order_no
               AND p_order_prefix = 'X' 
            OR                       )
                (   ph.wave_id = p_order_no
               AND p_order_prefix = 'W')  )   
         AND pd.pick_method = 'P'
         AND pd.status = 'E'

can replace

        AND (  (   ph.po_no = p_order_no
               AND p_order_prefix = 'X'
               AND pd.pick_method = 'P'
               AND pd.status = 'E' 
                                   )
           OR (   ph.wave_id = p_order_no
               AND p_order_prefix = 'W'
               AND pd.pick_method = 'P'
               AND pd.status = 'E' 
                                   ) 

          )