Home » RDBMS Server » Performance Tuning » How to remove left join and ON JOIN from query and use better things in place of that (11i)
|
Re: Error while running script [message #620100 is a reply to message #620099] |
Mon, 28 July 2014 14:26 |
John Watson
Messages: 8922 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
ANSI syntax is easier understand, to easier to write, and less prone to error. To re-write your code to the ancient syntax is lunacy. You are ignoring the simple tuning advice I have given.
Clearly, I cannot help you. I'll stop attempting to assist. Goodbye.
|
|
|
|
|
|
Re: Error while running script [message #620146 is a reply to message #620131] |
Tue, 29 July 2014 08:04 |
cookiemonster
Messages: 13917 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
As I pointed out previously the actual query doesn't contain many (or possibly any) nulls. John's advice about nulls was pointless because you posted a query where you replaced binds with nulls without telling us that you had done that.
The advice John gave that you should still be following is about checking if all the outer joins need to be outer joins or if they can safely be converted to inner joins.
|
|
|
Re: Error while running script [message #620149 is a reply to message #620146] |
Tue, 29 July 2014 08:08 |
cookiemonster
Messages: 13917 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
I see your latest version of the query has put the nulls back in. STOP DOING THAT.
It changes the meaning of the query.
You should leave the binds in, if you really feel the need to replace them do so with sensible values.
|
|
|
Re: Error while running script [message #620166 is a reply to message #620149] |
Tue, 29 July 2014 11:15 |
|
nishantranjan00787
Messages: 36 Registered: July 2014 Location: india
|
Member |
|
|
SELECT *
FROM (WITH a AS
((SELECT c.LOCATION, c.site_use_code, p.party_site_number
FROM apps.hz_cust_site_uses_all c,
apps.hz_cust_acct_sites_all s,
apps.hz_party_sites p
WHERE s.cust_acct_site_id = c.cust_acct_site_id
AND s.org_id = c.org_id
AND p.party_site_id = s.party_site_id
AND c.org_id = 4680
AND c.status = :"SYS_B_01"),
b AS
(SELECT h.org_id, h.header_id, h.line_id,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END) AS h2011,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END) AS h0003,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END) AS h2021,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END) AS h0060,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END) AS h2035,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END) AS h2421,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END) AS h2422,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END) AS h2423
FROM apps.oe_order_holds_all h, apps.oe_hold_sources_all s
WHERE s.org_id(+) = h.org_id
AND s.hold_source_id(+) = h.hold_source_id
AND s.hold_id IN(:"SYS_B_18",
:"SYS_B_19",
:"SYS_B_20",
:"SYS_B_21",
:"SYS_B_22",
:"SYS_B_23",
:"SYS_B_24",
:"SYS_B_25"
)
WHERE h.org_id = :"SYS_B_26" AND h.released_flag =
:"SYS_B_27"
GROUP BY h.org_id, h.header_id, h.line_id),
v AS
(SELECT l.org_id, l.header_id, l.line_number,
MAX (n.last_update_date) AS ddu,
COUNT (DISTINCT (c.meaning)) AS ddc,
MAX (c.meaning) AS dds,
COUNT (DISTINCT (h.meaning)) AS dlc,
MAX (h.meaning) AS dls
FROM apps.oe_order_lines_all l JOIN apps.wsh_delivery_details d
ON d.org_id = l.org_id
AND d.source_header_id = l.header_id
AND d.source_line_id = l.line_id
JOIN apps.wsh_delivery_assignments m
ON m.delivery_detail_id = d.delivery_detail_id
JOIN apps.wsh_new_deliveries n
ON n.organization_id = l.ship_from_org_id
AND n.delivery_id = m.delivery_id
JOIN apps.fnd_lookup_values c
ON c.lookup_code = d.released_status
AND c.lookup_type = :"SYS_B_28"
AND c.LANGUAGE = :"SYS_B_29"
JOIN apps.fnd_lookup_values h
ON h.lookup_code = n.status_code
AND h.lookup_type = :"SYS_B_30"
AND h.LANGUAGE = :"SYS_B_31"
WHERE l.org_id = :"SYS_B_32"
AND l.item_type_code IN (:"SYS_B_33", :"SYS_B_34")
GROUP BY l.org_id, l.header_id, l.line_number
ORDER BY l.org_id, l.header_id, l.line_number)
SELECT h.order_number AS o_order_nr, i.NAME AS o_order_type,
l.line_number AS o_ln_nr,
l.orig_sys_document_ref AS o_orig_sys_document_ref,
l.orig_sys_line_ref AS o_ln_orig_sys_line_ref,
p.orig_sys_line_ref AS o_sato_orig_sys_line_ref,
NVL (p.orig_sys_line_ref,
l.orig_sys_line_ref
) AS o_ln_full_line_ref,
l.ordered_item AS o_ln_ordered_item,
p.model_string AS o_sato_model,
NVL (NVL (t.model_string, p.model_string),
l.ordered_item
) AS o_ln_full_model,
p.pato_parent_item AS o_sato_parent_item,
p.component_item AS o_sato_component_item,
h.flow_status_code AS o_hd_flow_status,
l.flow_status_code AS o_ln_flow_status,
DECODE (v.ddc,
NULL, :"SYS_B_35",
:"SYS_B_36", v.dds,
:"SYS_B_37"
) AS o_ln_pick_status,
DECODE (v.dlc,
NULL, :"SYS_B_38",
:"SYS_B_39", v.dls,
:"SYS_B_40"
) AS o_ln_delivery_status,
b.h2011 AS o_ln_configuration_hold,
b.h0003 AS o_ln_conf_validation_hold,
b.h2021 AS o_ln_calibration_hold,
b.h0060 AS o_ln_conf_exception_hold,
b.h2035 AS o_ln_auto_conf_hold,
b.h2421 AS o_ln_engineering_hold,
b.h2422 AS o_ln_scheduling_hold,
b.h2423 AS o_ln_invoicing_hold,
CASE
WHEN TRUNC (l.promise_date) =
TRUNC (l.creation_date)
+ :"SYS_B_41"
THEN :"SYS_B_42"
ELSE :"SYS_B_43"
END AS o_ln_hub_promised,
CASE
WHEN TRUNC (l.schedule_ship_date) =
TRUNC (l.creation_date)
+ :"SYS_B_44"
THEN :"SYS_B_45"
ELSE :"SYS_B_46"
END AS o_ln_hub_scheduled,
h.creation_date AS o_hd_creation_dt,
p.creation_date AS o_sato_creation_dt,
l.creation_date AS o_ln_creation_dt,
v.ddu AS o_ln_delivery_update_dt,
s.subscriber_name AS o_hd_subscriber_name,
r.q_tot AS o_ln_quantity, r.q_split AS o_ln_split_count,
l.order_quantity_uom AS o_ln_uom,
h.transactional_curr_code AS o_hd_trans_currency,
ROUND
(CASE
WHEN l.top_model_line_id IS NULL
THEN NVL (l.unit_selling_price, :"SYS_B_47")
* r.q_tot
WHEN p.component_item IS NULL
THEN (SELECT SUM ( NVL (q.unit_selling_price,
:"SYS_B_48"
)
* r.q_tot
)
FROM apps.oe_order_lines_all q
WHERE q.org_id = l.org_id
AND q.header_id = l.header_id
AND q.top_model_line_id = l.line_id)
ELSE (SELECT NVL (q.unit_selling_price, :"SYS_B_49")
* r.q_tot
FROM apps.oe_order_lines_all q
WHERE q.org_id = l.org_id
AND q.header_id = l.header_id
AND q.top_model_line_id = l.top_model_line_id
AND q.ordered_item = p.component_item
AND ROWNUM = :"SYS_B_50")
END,
:"SYS_B_51"
) AS o_ln_extended_price,
ROUND
(CASE
WHEN l.item_type_code = :"SYS_B_52"
THEN NVL (c.item_cost, :"SYS_B_53") * r.q_tot
WHEN l.item_type_code = :"SYS_B_54"
THEN (SELECT NVL (v.item_cost, :"SYS_B_55") * r.q_tot
FROM apps.oe_order_lines_all q JOIN apps.cst_item_cost_type_v v
ON v.organization_id =
q.ship_from_org_id
AND v.inventory_item_id =
q.inventory_item_id
AND v.cost_type = :"SYS_B_56"
WHERE q.org_id = l.org_id
AND q.header_id = l.header_id
AND q.top_model_line_id = l.line_id
AND q.item_type_code = :"SYS_B_57")
END,
:"SYS_B_58"
) AS o_ln_extended_cost,
h.cust_po_number AS o_hd_customer_po,
h.fob_point_code AS o_hd_shipping_terms,
u.rep_order_nbr AS o_hd_representative_order,
u.project_number AS o_hd_project_nr, u.sic_code AS o_hd_sic,
u.ultimate_dest AS o_hd_ultimate_dest,
h.end_customer_site_use_id AS o_hd_end_user_nr,
(SELECT a.party_site_number
FROM a
WHERE a.LOCATION =
h.end_customer_site_use_id)
AS o_hd_end_user_psn,
h.ship_to_org_id AS o_hd_ship_to_org_id,
(SELECT a.party_site_number
FROM a
WHERE a.LOCATION = h.ship_to_org_id
AND a.site_use_code = :"SYS_B_59") AS o_hd_ship_to_psn,
h.invoice_to_org_id AS o_hd_bill_to_org_id,
(SELECT a.party_site_number
FROM a
WHERE a.LOCATION = h.invoice_to_org_id
AND a.site_use_code = :"SYS_B_60") AS o_hd_bill_to_psn,
u.named_place AS o_hd_named_place,
t.shiptoaddr AS o_ln_ship_to_nr,
(SELECT a.party_site_number
FROM a
WHERE a.LOCATION = t.shiptoaddr
AND a.site_use_code = :"SYS_B_61") AS o_ln_ship_to_psn,
u.po_originated AS o_hd_cust_po_dt,
u.poreceiptdate AS o_hd_branch_po_received_dt,
u.requested_delivery_date AS o_hd_cust_req_delivery_dt,
t.requested_delivery_date AS o_ln_cust_req_delivery_dt,
l.request_date AS o_ln_branch_req_ship_dt,
GREATEST (l.promise_date,
TO_DATE (:"SYS_B_62", :"SYS_B_63")
) AS o_ln_plant_prom_ship_dt,
l.schedule_ship_date AS o_ln_plant_sched_ship_dt,
t.creation_date AS o_ln_3lp_creation_dt,
t.model_string AS o_ln_3lp_model,
t.mse_config_status AS o_ln_3lp_mse_config_status
FROM apps.oe_order_lines_all l
,
(SELECT org_id, header_id, line_number,
SUM (ordered_quantity) AS q_tot, COUNT
(*) AS q_split
FROM apps.oe_order_lines_all
WHERE org_id = :"SYS_B_64"
AND item_type_code IN (:"SYS_B_65",:"SYS_B_66")
GROUP BY org_id, header_id, line_number) r,
apps.oe_order_headers_all h,
(SELECT DISTINCT *
FROM xxom.xxom_xml1_pato_stg) p,
xxom.xxom_3lp_sym_ora_order_lines t,
xxom.xxom_3lp_sym_ora_order_hdr u,
apps.xxont_som_scheduler s,
apps.oe_transaction_types_tl i,
apps.cst_item_cost_type_v c,
b,
v
WHERE r.org_id = l.org_id
AND r.header_id = l.header_id
AND r.line_number = l.line_number
AND h.org_id = l.org_id
AND h.header_id = l.header_id
AND p.config_hdr_id(+) = l.config_header_id
AND t.org_id(+) = l.org_id
AND t.header_id(+) = l.header_id
AND t.line_id(+) = l.line_id
AND u.org_id(+) = l.org_id
AND u.header_id(+) = l.header_id
AND s.subscriber_id(+) = u.order_admin
AND i.transaction_type_id(+) = h.order_type_id
AND i.LANGUAGE = :"SYS_B_67"
AND c.organization_id(+) = l.ship_from_org_id
AND c.inventory_item_id(+) = l.inventory_item_id
AND c.cost_type = :"SYS_B_68"
AND b.org_id(+) = l.org_id
AND b.header_id(+) = l.header_id
AND b.line_id(+) = l.line_id
AND v.org_id(+) = l.org_id
AND v.header_id(+) = l.header_id
AND v.line_number(+) = l.line_number
WHERE l.org_id = :"SYS_B_69"
AND l.item_type_code IN (:"SYS_B_70", :"SYS_B_71")
AND SUBSTR (l.orig_sys_line_ref, :"SYS_B_72", :"SYS_B_73") !=
:"SYS_B_74")
|
|
|
|
|
|
Re: Error while running script [message #620338 is a reply to message #620333] |
Thu, 31 July 2014 05:50 |
John Watson
Messages: 8922 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I had not intended to reply to this any more. But...
Nishant, can you confirm that you do know what an outer join and an inner join are?
Consider these queries:
select ename,dname from emp join dept using (deptno);
select ename,dname from emp,dept where emp.deptno=dept.deptno;
select ename,dname from emp right join dept using (deptno);
select ename,dname from emp,dept where emp.deptno(+)=dept.deptno;
Which, if any, do you think are outer joins or inner joins?
|
|
|
Goto Forum:
Current Time: Fri Mar 29 04:31:37 CDT 2024
|