Results 1 to 4 of 4

Thread: Rewrite SQL

  1. #1
    Join Date
    Jun 2004
    Posts
    115

    Unanswered: Rewrite SQL

    Hi

    Is there anyway to rewrite the following sql query


    Code:
    select      od.order_nbr order_nbr,    
                to_char(oh.order_dt,'MM/DD/YYYY') order_dt,
                oh.ship_zip_txt ship_zip,
                oh.bill_zip_txt bill_zip,
                case
                    when od.prod_type is not null
                       then 'Y'
                    else '  '
                 end hhc_item,
                od.otc_prod_nbr otc_prod_nbr,
                od.prod_name prod_name,
                od.selling_price selling_price,
                od.item_qty item_qty,
                od.iadjust_regular_amt tot_amt,
                (nvl (sku.sku_cost_amt , 0) * od.item_qty) cvs_cost
            from order_detail od,
                 order_header oh,
                 v_sku sku
          where  od.order_nbr = oh.order_nbr
             and nvl (od.otc_prod_nbr,od.rx_prod_nbr) = sku.sku_nbr(+)
             and oh.order_nbr >= (select min (minoh.order_nbr) 
    				     from order_header minoh 
    		                     where minoh.order_dt >= (trunc(To_Date('01-01-2006', 'dd-mm-yyyy')))
    	  and minoh.order_dt < (trunc(To_Date('18-10-2006', 'dd-mm-yyyy')))) 
              and oh.order_nbr <= (select max (maxoh.order_nbr) 
    				      from order_header maxoh 
    		                      where maxoh.order_dt >= (trunc(To_Date('01-01-2006', 'dd-mm-yyyy')))
              and maxoh.order_dt  < (trunc(To_Date('18-10-2006', 'dd-mm-yyyy')))) 
              and nvl (oh.BILL_ADDR2_TXT,'.') != 'TEST DATA ZZZ'
              and exists (select 1 
    				from order_detail od1
          				where od1.order_nbr = oh.order_nbr
              			and od1.order_nbr >= (select min (minoh.order_nbr) 
    							from order_header minoh 
    		                        		where minoh.order_dt >= (trunc(To_Date('01-01-2006', 'dd-mm-yyyy'))) 
    							and minoh.order_dt  < (trunc(To_Date('18-10-2006', 'dd-mm-yyyy')))) 
              and od1.order_nbr <= (select max (maxoh.order_nbr) 
    					from order_header maxoh 
    		                        where maxoh.order_dt >= (trunc(To_Date('01-01-2006', 'dd-mm-yyyy')))
    					and maxoh.order_dt  < (trunc(To_Date('18-10-2006', 'dd-mm-yyyy')))) 
              and od1.prod_type is not null)
              and nvl (oh.stat_cd,'.') != 'CANCELLED'
              and nvl (od.status_cd, '.') != 'CANCELLED'
              and not exists (select /*+INDEX(otl,OTC_OC_PK)*/1 
    				from order_tracking_log otl
          				where otl.order_nbr = oh.order_nbr
              			and otl.order_nbr >= (select min (minoh.order_nbr) 
    							from order_header minoh
    		                        		where minoh.order_dt >= (trunc(To_Date('01-01-2006', 'dd-mm-yyyy')))
    							and minoh.order_dt  < (trunc(To_Date('18-10-2006', 'dd-mm-yyyy')))) 
              and otl.order_nbr <= (select max (maxoh.order_nbr) 
    				from order_header maxoh 
    		                where maxoh.order_dt >= (trunc(To_Date('01-01-2006', 'dd-mm-yyyy'))) 
    				and maxoh.order_dt  < (trunc(To_Date('18-10-2006', 'dd-mm-yyyy')))) 
              and otl.stat_ind in ('REFER TO ISSUER', 'COMM ERROR'))
              and not exists (select /*+INDEX(ot,OT_OC_PK)*/1 
    			  from order_tracking ot
          			  where ot.order_nbr = oh.order_nbr
              		  and ot.order_nbr >= (select min (minoh.order_nbr) 
    						from order_header minoh 
    						where minoh.order_dt >= (trunc(To_Date('01-01-2006', 'dd-mm-yyyy'))) 
    						and minoh.order_dt  < (trunc(To_Date('18-10-2006', 'dd-mm-yyyy')))) 
              and ot.order_nbr <= (select max (maxoh.order_nbr) from order_header maxoh 
    		               where maxoh.order_dt >= (trunc(To_Date('01-01-2006', 'dd-mm-yyyy')))
    			       and maxoh.order_dt  < (trunc(To_Date('18-10-2006', 'dd-mm-yyyy')))) 
              and ot.stat_ind in ('REFER TO ISSUER', 'COMM ERROR')) 
    Order by  to_char(oh.order_dt,'MM/DD/YYYY'),od.order_nbr
    Last edited by db2hrishy; 10-21-06 at 06:50.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by db2hrishy
    Hi

    Is there anyway to rewrite the following sql query

    <snip>
    Yes, there are many ways to write any but the most trival SQL query.

    Presumably you are have performance problems with this version? If so your best bet is to ensure tables have been analyzed and then to run it through "explain plan" or get a trace file and see how the optimizer is working, which may then give you a clue as to what need changing. It could be, for example, that you are missing a useful index.

  3. #3
    Join Date
    Jun 2004
    Posts
    115
    Hi Tony

    I was looking to rewrite the sql to make it more legible.And to avoid the corelated subquery.

    I havent tested the performance yet as we havent loaded the data in our test environment as yet

    regards
    Hrishy

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    A trivial point to aid readibility:

    Instead of
    Code:
    (trunc(To_Date('01-01-2006', 'dd-mm-yyyy')))
    you can just have
    Code:
    DATE '2006-01-01'
    This is the ISO standard date formatted supported in 9i and above. There is no need to TRUNC a literal date value.

    Having done that, I noticed that one subquery appears again and again, so is a candidate for "query factoring":
    Code:
    with (select min (oh.order_nbr) min_order_nbr, max(oh.order_nbr) max_order_nbr
          from   order_header oh 
          where  oh.order_dt >= DATE '2006-01-01'
          and    oh.order_dt <  DATE '2006-10-18') as ohinfo
    select od.order_nbr order_nbr,    
           to_char(oh.order_dt,'MM/DD/YYYY') order_dt,
           oh.ship_zip_txt ship_zip,
           oh.bill_zip_txt bill_zip,
           case
               when od.prod_type is not null
               then 'Y'
               else '  '
               end hhc_item,
           od.otc_prod_nbr otc_prod_nbr,
           od.prod_name prod_name,
           od.selling_price selling_price,
           od.item_qty item_qty,
           od.iadjust_regular_amt tot_amt,
           (nvl (sku.sku_cost_amt , 0) * od.item_qty) cvs_cost
    from   order_detail od,
           order_header oh,
           v_sku sku,
           ohinfo
    where  od.order_nbr = oh.order_nbr
    and    nvl (od.otc_prod_nbr,od.rx_prod_nbr) = sku.sku_nbr(+)
    and    oh.order_nbr between ohinfo.min_order_nbr and ohinfo.max_order_nbr
    and    nvl (oh.BILL_ADDR2_TXT,'.') != 'TEST DATA ZZZ'
    and    exists (select 1 
                   from   order_detail od1
                   where  od1.order_nbr = oh.order_nbr
                   and    od1.order_nbr between ohinfo.min_order_nbr and ohinfo.max_order_nbr --**
                   and    od1.prod_type is not null)
    and    nvl (oh.stat_cd,'.') != 'CANCELLED'
    and    nvl (od.status_cd, '.') != 'CANCELLED'
    and    not exists (select /*+INDEX(otl,OTC_OC_PK)*/1 
                       from   order_tracking_log otl
                       where  otl.order_nbr = oh.order_nbr
                       and    ot1.order_nbr between ohinfo.min_order_nbr and ohinfo.max_order_nbr --**
                       and    otl.stat_ind in ('REFER TO ISSUER', 'COMM ERROR'))
    and    not exists (select /*+INDEX(ot,OT_OC_PK)*/1 
                       from   order_tracking ot
                       where  ot.order_nbr = oh.order_nbr
                       and    ot.order_nbr between ohinfo.min_order_nbr and ohinfo.max_order_nbr --**
                       and    ot.stat_ind in ('REFER TO ISSUER', 'COMM ERROR')) 
    Order by to_char(oh.order_dt,'MM/DD/YYYY'),od.order_nbr
    Now you can see the wood for the trees more clearly, and I noticed that the lines I have marked --** above are redundant because the combination of (e.g.) "oh.order_nbr between ohinfo.min_order_nbr and ohinfo.max_order_nbr" and "od1.order_nbr = oh.order_nbr" guarantees that "od1.order_nbr between ohinfo.min_order_nbr and ohinfo.max_order_nbr". So now we have just:
    Code:
    with (select min (oh.order_nbr) min_order_nbr, max(oh.order_nbr) max_order_nbr
          from   order_header oh 
          where  oh.order_dt >= DATE '2006-01-01'
          and    oh.order_dt <  DATE '2006-10-18') as ohinfo
    select od.order_nbr order_nbr,    
           to_char(oh.order_dt,'MM/DD/YYYY') order_dt,
           oh.ship_zip_txt ship_zip,
           oh.bill_zip_txt bill_zip,
           case
               when od.prod_type is not null
               then 'Y'
               else '  '
               end hhc_item,
           od.otc_prod_nbr otc_prod_nbr,
           od.prod_name prod_name,
           od.selling_price selling_price,
           od.item_qty item_qty,
           od.iadjust_regular_amt tot_amt,
           (nvl (sku.sku_cost_amt , 0) * od.item_qty) cvs_cost
    from   order_detail od,
           order_header oh,
           v_sku sku,
           ohinfo
    where  od.order_nbr = oh.order_nbr
    and    nvl (od.otc_prod_nbr,od.rx_prod_nbr) = sku.sku_nbr(+)
    and    oh.order_nbr between ohinfo.min_order_nbr and ohinfo.max_order_nbr
    and    nvl (oh.BILL_ADDR2_TXT,'.') != 'TEST DATA ZZZ'
    and    exists (select 1 
                   from   order_detail od1
                   where  od1.order_nbr = oh.order_nbr
                   and    od1.prod_type is not null)
    and    nvl (oh.stat_cd,'.') != 'CANCELLED'
    and    nvl (od.status_cd, '.') != 'CANCELLED'
    and    not exists (select /*+INDEX(otl,OTC_OC_PK)*/1 
                       from   order_tracking_log otl
                       where  otl.order_nbr = oh.order_nbr
                       and    otl.stat_ind in ('REFER TO ISSUER', 'COMM ERROR'))
    and    not exists (select /*+INDEX(ot,OT_OC_PK)*/1 
                       from   order_tracking ot
                       where  ot.order_nbr = oh.order_nbr
                       and    ot.stat_ind in ('REFER TO ISSUER', 'COMM ERROR')) 
    Order by to_char(oh.order_dt,'MM/DD/YYYY'),od.order_nbr
    I don't know if it can be simplified further. Do you need the first EXISTS subquery? It says only report on orders where at least one of the order details has a non-null prod_type.

    I would remove the hints unless you know you need them.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •