Re: How to rewrite the query using with Clause

"balu" <krishna000_at_gmail.com> wrote in message news:c4c5b770-5969-44f1-b25d-8d526305a1a2_at_w24g2000prd.googlegroups.com... 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

If you want to use the with clause go to http://www.psoug.org/library.html some nice examples. Give it a whirl first. The first think I notice though is TRUNC (wnd.confirm_date) >= :b4 might not be very effecient. I believe it has to look at each row to decide that (function on a column). Jim Received on Tue Dec 23 2008 - 23:43:12 CST