Prev: Grid Control 10gR2: repository password
Next: a contrast between oracle articles currently playing
From: balu on 23 Dec 2008 06:39
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.

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
From: Mladen Gogala on 23 Dec 2008 08:03
On Tue, 23 Dec 2008 03:39:05 -0800, balu 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.

My body could probably do that but it needs to be stimulated by $70/hr.
Other than that, asking other people to do your job for free while you
will get paid for the solution is considered impolite.

--
Mladen Gogala
http://mgogala.freehostia.com
From: Noons on 23 Dec 2008 08:12
Mladen Gogala wrote,on my timestamp of 24/12/2008 12:03 AM:
> On Tue, 23 Dec 2008 03:39:05 -0800, balu 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.
>
> My body could probably do that but it needs to be stimulated by $70/hr.
> Other than that, asking other people to do your job for free while you
> will get paid for the solution is considered impolite.

LOL!
Next you gonna tell us you like being paid for your work?
<g,d&r>
From: ddf on 23 Dec 2008 08:58
Comments embedded.

On Dec 23, 5:39 am, balu <krishna...(a)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
From: balu on 23 Dec 2008 22:16
On Dec 23, 6:58 pm, ddf <orat...(a)msn.com> wrote:
> Comments embedded.
>
> On Dec 23, 5:39 am, balu <krishna...(a)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
 |  Next  |  Last
Pages: 1 2
Prev: Grid Control 10gR2: repository password
Next: a contrast between oracle articles currently playing