Re: How to rewrite the query using with Clause

From: ddf <oratune_at_msn.com>
Date: Tue, 23 Dec 2008 05:58:37 -0800 (PST)
Message-ID: <cba5d2f2-791e-4ceb-9ea0-99e50df68438@z28g2000prd.googlegroups.com>


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 Received on Tue Dec 23 2008 - 07:58:37 CST

Original text of this message