Re: How to rewrite the query using with Clause

On Dec 23, 6:58 pm, ddf <orat..._at_msn.com> wrote:
> Comments embedded.
>
> On Dec 23, 5:39 am, balu <krishna..._at_gmail.com> wrote:
>
> > Hi,
>
> > Can any body help me out in rewriting the query using with clause
> > where ever necessary or any method which suits for better performance.
>
> What leads you to believe the performance of the current query is
> bad?  What evidence can you present to prove that claim?
> And what brought you to the conclusion that using the WITH clause
> would help with this?
>
> If you know enough to ask about the WITH clause you know enough to
> rewrite the query yourself using it.
>
>
>
>
>
> > SELECT distinct (SELECT ood.organization_name
> >           FROM org_organization_definitions ood
> >          WHERE ood.operating_unit = ood.organization_id
> >            AND ood.operating_unit = oeh.org_id) "OPERATING_UNIT",
> >        (SELECT organization_name
> >           FROM org_organization_definitions
> >          WHERE organization_id = wnd.organization_id) "ORGANIZATION",
> >        (SELECT location_code
> >           FROM hr_locations
> >          WHERE location_id = wsh_loc_hdr.location_id) "LOCATION",
> >        oeh.order_number "ORDER NUMBER", wnd.delivery_id "DELIVERY NO",
> >        TO_CHAR (wnd.confirm_date) "CONFIRM DATE",
> >        (SELECT NAME
> >           FROM oe_transaction_types_tl
> >          WHERE transaction_type_id = oeh.order_type_id) "ORDER TYPE",
> >        ott.attribute1 "NATURE OF REMOVAL", msik.concatenated_segments,
> >        wdd.subinventory,
> >        DECODE ((SELECT COUNT (1)
> >                   FROM jai_om_oe_bond_reg_hdrs jai_hd,
> >                        jai_om_oe_bond_reg_dtls jai_dt
> >                  WHERE jai_hd.organization_id = wdd.organization_id
> >                    AND jai_hd.location_id = :b1
> >                    AND jai_hd.register_id = jai_dt.register_id
> >                    AND jai_dt.order_type_id = oeh.order_type_id),
> >                0, 'ORDER
> > TYPE NOT ATTACHED',
> >                1, 'ORDER TYPE ATTACHED',
> >                'COUNT > 1'
> >               ) exception1,
> >        DECODE ((SELECT COUNT (1)
> >                   FROM jai_inv_itm_setups jai_itm
> >                  WHERE jai_itm.inventory_item_id =
> > msik.inventory_item_id
> >                    AND jai_itm.organization_id =
> > msik.organization_id),
> >                0, 'TEMPLATE NOT ASSIGNED',
> >                1, 'TEMPLATE
> > ASSIGNED',
> >                'COUNT > 1'
> >               ) exception2,
> >        (SELECT jai_itm.item_class
> >           FROM jai_inv_itm_setups jai_itm
> >          WHERE jai_itm.inventory_item_id = msik.inventory_item_id
> >            AND jai_itm.organization_id = msik.organization_id)
> > exception3,
> >        (SELECT jai_itm.excise_flag
> >           FROM jai_inv_itm_setups jai_itm
> >          WHERE jai_itm.inventory_item_id = msik.inventory_item_id
> >            AND jai_itm.organization_id = msik.organization_id)
> > exception4,
> >        DECODE ((SELECT COUNT (1)
> >                   FROM jai_inv_subinv_dtls loc_sub
> >                  WHERE 1 = 1
> >                    AND loc_sub.organization_id = wdd.organization_id
> >                    AND loc_sub.location_id = :location_id
> >                    AND loc_sub.sub_inventory_name = wdd.subinventory
> >                    AND loc_sub.bonded = 'Y'),
> >                0, 'SUN INV NOT ATTACHED/NOT
> > BONDED',
> >                1, 'SUB INV ATTACHED',
> >                'COUNT > 1'
> >               ) exception5
> >   FROM wsh_new_deliveries wnd,
> >        wsh_delivery_assignments wda,
> >        wsh_delivery_details wdd,
> >        oe_order_headers_all oeh,
> >        oe_transaction_types_all ott,
> >        mtl_system_items_kfv msik,
> >        jai_om_wsh_lines_all wsh_loc_hdr,
> >        jai_om_wsh_line_taxes wsh_loc_lin,
> >        jai_cmn_taxes_all btax
> >  WHERE 1 = 1
> >    AND UPPER (btax.tax_type) LIKE '%EXCISE%'
> >    AND btax.tax_id = wsh_loc_lin.tax_id
> >    AND wsh_loc_lin.delivery_detail_id = wsh_loc_hdr.delivery_detail_id
> >    AND wsh_loc_hdr.location_id = :b2
> >    AND wsh_loc_hdr.delivery_detail_id = wdd.delivery_detail_id
> >    AND msik.organization_id = wdd.organization_id
> >    AND msik.inventory_item_id = wdd.inventory_item_id
> >    AND ott.transaction_type_id = oeh.order_type_id
> >    AND oeh.transactional_curr_code = 'INR'
> >    AND oeh.org_id = :org_id
> >    AND oeh.ship_from_org_id = wdd.organization_id
> >    AND oeh.header_id = wdd.source_header_id
> >    AND wdd.organization_id = wnd.organization_id
> >    AND wdd.org_id = :b3
> >    AND wdd.delivery_detail_id = wda.delivery_detail_id
> >    AND wnd.delivery_id = wda.delivery_id
> >    AND wnd.status_code = 'CL'
> >    AND TRUNC (wnd.confirm_date) >= :b4
> >    AND TRUNC (wnd.confirm_date) <= :b5
>
> > Regards
>
> > Bala
>
> David Fitzjarrell

Hi,

If you observe query we have used 2 multiple times the same query on the tables , i just want to avoid where every necessary . i Can paste the explain plan for your better understanding.

Regards

Bala Received on Tue Dec 23 2008 - 21:16:26 CST