Home » RDBMS Server » Performance Tuning » How to remove left join and ON JOIN from query and use better things in place of that (11i)
How to remove left join and ON JOIN from query and use better things in place of that [message #619255] Mon, 21 July 2014 01:15 Go to next message
nishantranjan00787
Messages: 36
Registered: July 2014
Location: india
Member
There is one query and it using Left Join and ON JOIN and it's a old model of writing code.Now as it's causing performance issue.So we need to rewrite the code.The main problem is how to remove that left join and ON join and use better query in place of that.I am unable to pull out the trace file as when i was taking session level trace,after 2 hr automatically it got terminated.

SELECT *
FROM (WITH a AS
(SELECT c.LOCATION, c.site_use_code, p.party_site_number
FROM apps.hz_cust_site_uses_all c JOIN apps.hz_cust_acct_sites_all s
ON s.cust_acct_site_id = c.cust_acct_site_id
AND s.org_id = c.org_id
JOIN apps.hz_party_sites p
ON p.party_site_id = s.party_site_id
WHERE c.org_id = 4680 AND c.status = 'A'),
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 LEFT JOIN apps.oe_hold_sources_all s
ON s.org_id = h.org_id
AND s.hold_source_id = h.hold_source_id
AND s.hold_id IN
(2011,
3,
2021,
60,
2035,
2421,
2422,
2423
)
WHERE h.org_id = 4680 AND h.released_flag =
'N'
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 = 'PICK_STATUS'
AND c.LANGUAGE = 'US'
JOIN apps.fnd_lookup_values h
ON h.lookup_code = n.status_code
AND h.lookup_type = 'DELIVERY_STATUS'
AND h.LANGUAGE = 'US'
WHERE l.org_id = 4680
AND l.item_type_code IN ('STANDARD', 'CONFIG')
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, 'NULL',
NULL, v.dds,
'NULL'
) AS o_ln_pick_status,
DECODE (v.dlc,
NULL, 'NULL',
NULL, v.dls,
'NULL'
) 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)
+ NULL
THEN 'NULL'
ELSE 'NULL'
END AS o_ln_hub_promised,
CASE
WHEN TRUNC (l.schedule_ship_date) =
TRUNC (l.creation_date)
+ NULL
THEN 'NULL'
ELSE 'NULL'
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, NULL)
* r.q_tot
WHEN p.component_item IS NULL
THEN (SELECT SUM ( NVL (q.unit_selling_price,
NULL
)
* 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, NULL)
* 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 = 1)
END,
NULL
) AS o_ln_extended_price,
ROUND
(CASE
WHEN l.item_type_code = 'NULL'
THEN NVL (c.item_cost, NULL) * r.q_tot
WHEN l.item_type_code = 'NULL'
THEN (SELECT NVL (v.item_cost, NULL) * 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 = 'Frozen'
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 = 'CONFIG')
END,
NULL
) 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 = 'SHIP_TO') 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 = 'BILL_TO') 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 = 'SHIP_TO') 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 ('NULL', 'NULL')
) 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
JOIN
(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 = 4680
AND item_type_code IN ('MODEL', 'STANDARD')
GROUP BY org_id, header_id, line_number) r
ON r.org_id = l.org_id
AND r.header_id = l.header_id
AND r.line_number = l.line_number
JOIN apps.oe_order_headers_all h
ON h.org_id = l.org_id AND h.header_id = l.header_id
LEFT JOIN
(SELECT DISTINCT *
FROM xxom.xxom_xml1_pato_stg) p
ON p.config_hdr_id = l.config_header_id
LEFT JOIN xxom.xxom_3lp_sym_ora_order_lines t
ON t.org_id = l.org_id
AND t.header_id = l.header_id
AND t.line_id = l.line_id
LEFT JOIN xxom.xxom_3lp_sym_ora_order_hdr u
ON u.org_id = l.org_id AND u.header_id = l.header_id
LEFT JOIN apps.xxont_som_scheduler s
ON s.subscriber_id = u.order_admin
LEFT JOIN apps.oe_transaction_types_tl i
ON i.transaction_type_id = h.order_type_id
AND i.LANGUAGE = 'US'
LEFT JOIN apps.cst_item_cost_type_v c
ON c.organization_id = l.ship_from_org_id
AND c.inventory_item_id = l.inventory_item_id
AND c.cost_type = 'Frozen'
LEFT JOIN b
ON b.org_id = l.org_id
AND b.header_id = l.header_id
AND b.line_id = l.line_id
LEFT JOIN v
ON v.org_id = l.org_id
AND v.header_id = l.header_id
AND v.line_number = l.line_number
WHERE l.org_id = 4680
AND l.item_type_code IN ('MODEL', 'STANDARD')
AND SUBSTR (l.orig_sys_line_ref, 1, 18) != 'OE_ORDER_LINES_ALL')
Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619256 is a reply to message #619255] Mon, 21 July 2014 01:30 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member

Please read the very first post in the performance tuning section which explains how to post a question and much more information there.

Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619260 is a reply to message #619256] Mon, 21 July 2014 01:45 Go to previous messageGo to next message
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 JOIN apps.hz_cust_acct_sites_all s
                     ON s.cust_acct_site_id = c.cust_acct_site_id
                   AND s.org_id = c.org_id
                     JOIN apps.hz_party_sites p
                     ON p.party_site_id = s.party_site_id
               WHERE c.org_id = 4680 AND c.status = 'A'),
             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 LEFT JOIN apps.oe_hold_sources_all s
                       ON s.org_id = h.org_id
                     AND s.hold_source_id = h.hold_source_id
                     AND s.hold_id IN
                            (2011,
                             3,
                             2021,
                             60,
                             2035,
                             2421,
                             2422,
                            2423
                            )
                 WHERE h.org_id = 4680 AND h.released_flag =
                                                                 'N'
              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 = 'PICK_STATUS'
                     AND c.LANGUAGE = 'US'
                       JOIN apps.fnd_lookup_values h
                       ON h.lookup_code = n.status_code
                     AND h.lookup_type = 'DELIVERY_STATUS'
                     AND h.LANGUAGE = 'US'
                 WHERE l.org_id = 4680
                   AND l.item_type_code IN ('STANDARD', 'CONFIG')
              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, 'NULL',
                       NULL, v.dds,
                       'NULL'
                      ) AS o_ln_pick_status,
               DECODE (v.dlc,
                       NULL, 'NULL',
                       NULL, v.dls,
                       'NULL'
                      ) 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)
                         + NULL
                     THEN 'NULL'
                  ELSE 'NULL'
               END AS o_ln_hub_promised,
               CASE
                  WHEN TRUNC (l.schedule_ship_date) =
                           TRUNC (l.creation_date)
                         + NULL
                     THEN 'NULL'
                  ELSE 'NULL'
               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, NULL)
                              * r.q_tot
                      WHEN p.component_item IS NULL
                         THEN (SELECT SUM (  NVL (q.unit_selling_price,
                                                 NULL
                                                 )
                                           * 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, NULL)
                                   * 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 = 1)
                   END,
                   NULL
                  ) AS o_ln_extended_price,
               ROUND
                  (CASE
                      WHEN l.item_type_code = 'NULL'
                         THEN NVL (c.item_cost, NULL) * r.q_tot
                      WHEN l.item_type_code = 'NULL'
                         THEN (SELECT NVL (v.item_cost, NULL) * 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 = 'Frozen'
                                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 = 'CONFIG')
                   END,
                   NULL
                  ) 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 = 'SHIP_TO') 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 = 'BILL_TO') 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 = 'SHIP_TO') 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 ('NULL', 'NULL')
                        ) 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
               JOIN
               (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 = 4680
                     AND item_type_code IN ('MODEL', 'STANDARD')
                GROUP BY org_id, header_id, line_number) r
               ON r.org_id = l.org_id
             AND r.header_id = l.header_id
             AND r.line_number = l.line_number
               JOIN apps.oe_order_headers_all h
               ON h.org_id = l.org_id AND h.header_id = l.header_id
               LEFT JOIN
               (SELECT DISTINCT *
                           FROM xxom.xxom_xml1_pato_stg) p
               ON p.config_hdr_id = l.config_header_id
               LEFT JOIN xxom.xxom_3lp_sym_ora_order_lines t
               ON t.org_id = l.org_id
             AND t.header_id = l.header_id
             AND t.line_id = l.line_id
               LEFT JOIN xxom.xxom_3lp_sym_ora_order_hdr u
               ON u.org_id = l.org_id AND u.header_id = l.header_id
               LEFT JOIN apps.xxont_som_scheduler s
               ON s.subscriber_id = u.order_admin
               LEFT JOIN apps.oe_transaction_types_tl i
               ON i.transaction_type_id = h.order_type_id
             AND i.LANGUAGE = 'US'
               LEFT JOIN apps.cst_item_cost_type_v c
               ON c.organization_id = l.ship_from_org_id
             AND c.inventory_item_id = l.inventory_item_id
             AND c.cost_type = 'Frozen'
               LEFT JOIN b
               ON b.org_id = l.org_id
             AND b.header_id = l.header_id
             AND b.line_id = l.line_id
               LEFT JOIN v
               ON v.org_id = l.org_id
             AND v.header_id = l.header_id
             AND v.line_number = l.line_number
         WHERE l.org_id = 4680
           AND l.item_type_code IN ('MODEL', 'STANDARD')
           AND SUBSTR (l.orig_sys_line_ref, 1, 18) != 'OE_ORDER_LINES_ALL')
Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619261 is a reply to message #619260] Mon, 21 July 2014 01:59 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
This construct that you use several times is a bug:
CASE
                               WHEN s.hold_id = NULL
                                  THEN 'NULL'
                            END

It will always return a null. That will mess up the rest of the query considerably.

You also have this
WHEN l.item_type_code = 'NULL'
and this
CASE
                  WHEN TRUNC (l.promise_date) =
                           TRUNC (l.creation_date)
                         + NULL
                     THEN 'NULL'


These are basic programming errors that may render the entire query meaningless. Fix them, and then see if you have a performance problem.

--update: my second example above is not necessarily a bug - sorry about that. The others, most definitely, are.

[Updated on: Mon, 21 July 2014 02:04]

Report message to a moderator

Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619262 is a reply to message #619261] Mon, 21 July 2014 02:03 Go to previous messageGo to next message
nishantranjan00787
Messages: 36
Registered: July 2014
Location: india
Member
John,

Someone else have written long back and now it's causing problem and also you can see JOIN...on ... Many times it's also written and earlier that coding were used.Don't you think it will also cause performance issue.
Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619263 is a reply to message #619262] Mon, 21 July 2014 02:06 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I can't believe this. You have a query that is full of bugs, and you are worried about the performance? Wrong priorities, man.

Furthermore, fixing the bugs might fix the performance, too.
Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619273 is a reply to message #619263] Mon, 21 July 2014 03:09 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You've replaced a load of bind variables with 'NULL' haven't you?

Cause this bit will error out every time:
GREATEST (l.promise_date,
                         TO_DATE ('NULL', 'NULL')
                        ) AS o_ln_plant_prom_ship_dt,


Post the actual query.
Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619276 is a reply to message #619273] Mon, 21 July 2014 03:34 Go to previous messageGo to next message
nishantranjan00787
Messages: 36
Registered: July 2014
Location: india
Member
From sql id i have fetched that bind variables and put that in place of the bind
Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619277 is a reply to message #619276] Mon, 21 July 2014 03:39 Go to previous messageGo to next message
nishantranjan00787
Messages: 36
Registered: July 2014
Location: india
Member
can i completely remove that case statement from query?Like:

SELECT   h.org_id, h.header_id, h.line_id,
                FROM apps.oe_order_holds_all h LEFT JOIN apps.oe_hold_sources_all s
                       ON s.org_id = h.org_id
                     AND s.hold_source_id = h.hold_source_id
                     AND s.hold_id IN
                            (2011,
                             3,
                             2021,
                             60,
                             2035,
                             2421,
                             2422,
                            2423
                            )
                 WHERE h.org_id = 4680 AND h.released_flag =
                                                                 'N'
              GROUP BY h.org_id, h.header_id, h.line_id

Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619278 is a reply to message #619276] Mon, 21 July 2014 03:43 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
nishantranjan00787 wrote on Mon, 21 July 2014 09:34
From sql id i have fetched that bind variables and put that in place of the bind


Don't do that. The current query looks like absolute non-sense. Post it with the binds.
Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619281 is a reply to message #619278] Mon, 21 July 2014 03:54 Go to previous messageGo to next message
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  
join apps.hz_cust_acct_sites_all s on s.cust_acct_site_id = c.cust_acct_site_id and s.org_id = c.org_id 
join apps.hz_party_sites p on p.party_site_id = s.party_site_id 
where c.org_id = :"SYS_B_00" and c.status = :"SYS_B_01" 
), b as ( 
select 
h.org_id, h.header_id, h.line_id, 
max(case when s.hold_id = :"SYS_B_02" Then :"SYS_B_03" end) as h2011, 
max(case when s.hold_id = :"SYS_B_04" Then :"SYS_B_05" end) as h0003, 
max(case when s.hold_id = :"SYS_B_06" Then :"SYS_B_07" end) as h2021, 
max(case when s.hold_id = :"SYS_B_08" Then :"SYS_B_09" end) as h0060, 
max(case when s.hold_id = :"SYS_B_10" Then :"SYS_B_11" end) as h2035, 
max(case when s.hold_id = :"SYS_B_12" Then :"SYS_B_13" end) as h2421, 
max(case when s.hold_id = :"SYS_B_14" Then :"SYS_B_15" end) as h2422, 
max(case when s.hold_id = :"SYS_B_16" Then :"SYS_B_17" end) as h2423 
from apps.oe_order_holds_all h 
left join apps.oe_hold_sources_all s on 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 
  join ( 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 on r.org_id = l.org_id and r.header_id = l.header_id and r.line_number = l.line_number 
  join apps.oe_order_headers_all h on h.org_id = l.org_id and h.header_id = l.header_id 
  left join (select distinct * from xxom.xxom_xml1_pato_stg) p on p.config_hdr_id = l.config_header_id 
  left join xxom.xxom_3lp_sym_ora_order_lines t on t.org_id = l.org_id and t.header_id = l.header_id and t.line_id = l.line_id 
  left join xxom.xxom_3lp_sym_ora_order_hdr u on u.org_id = l.org_id and u.header_id = l.header_id 
  left join apps.xxont_som_scheduler s on s.subscriber_id = u.order_admin 
  left join apps.oe_transaction_types_tl i on i.transaction_type_id = h.order_type_id and i.language = :"SYS_B_67" 
  left join apps.cst_item_cost_type_v c on c.organization_id = l.ship_from_org_id and c.inventory_item_id = l.inventory_item_id and c.cost_type = :"SYS_B_68" 
  left join b on b.org_id = l.org_id and b.header_id = l.header_id and b.line_id = l.line_id 
  left join v on 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: How to remove left join and ON JOIN from query and use better things in place of that [message #619283 is a reply to message #619281] Mon, 21 July 2014 03:55 Go to previous messageGo to next message
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 JOIN apps.hz_cust_acct_sites_all s
                     ON s.cust_acct_site_id = c.cust_acct_site_id
                   AND s.org_id = c.org_id
                     JOIN apps.hz_party_sites p
                     ON p.party_site_id = s.party_site_id
               WHERE c.org_id = :"SYS_B_00" AND c.status = :"SYS_B_01"),
             b AS
             (SELECT   h.org_id, h.header_id, h.line_id,
                       MAX (CASE
                               WHEN s.hold_id = :"SYS_B_02"
                                  THEN :"SYS_B_03"
                            END
                           ) AS h2011,
                       MAX (CASE
                               WHEN s.hold_id = :"SYS_B_04"
                                  THEN :"SYS_B_05"
                            END
                           ) AS h0003,
                       MAX (CASE
                               WHEN s.hold_id = :"SYS_B_06"
                                  THEN :"SYS_B_07"
                            END
                           ) AS h2021,
                       MAX (CASE
                               WHEN s.hold_id = :"SYS_B_08"
                                  THEN :"SYS_B_09"
                            END
                           ) AS h0060,
                       MAX (CASE
                               WHEN s.hold_id = :"SYS_B_10"
                                  THEN :"SYS_B_11"
                            END
                           ) AS h2035,
                       MAX (CASE
                               WHEN s.hold_id = :"SYS_B_12"
                                  THEN :"SYS_B_13"
                            END
                           ) AS h2421,
                       MAX (CASE
                               WHEN s.hold_id = :"SYS_B_14"
                                  THEN :"SYS_B_15"
                            END
                           ) AS h2422,
                       MAX (CASE
                               WHEN s.hold_id = :"SYS_B_16"
                                  THEN :"SYS_B_17"
                            END
                           ) AS h2423
                  FROM apps.oe_order_holds_all h LEFT JOIN apps.oe_hold_sources_all s
                       ON 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
               JOIN
               (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
               ON r.org_id = l.org_id
             AND r.header_id = l.header_id
             AND r.line_number = l.line_number
               JOIN apps.oe_order_headers_all h
               ON h.org_id = l.org_id AND h.header_id = l.header_id
               LEFT JOIN
               (SELECT DISTINCT *
                           FROM xxom.xxom_xml1_pato_stg) p
               ON p.config_hdr_id = l.config_header_id
               LEFT JOIN xxom.xxom_3lp_sym_ora_order_lines t
               ON t.org_id = l.org_id
             AND t.header_id = l.header_id
             AND t.line_id = l.line_id
               LEFT JOIN xxom.xxom_3lp_sym_ora_order_hdr u
               ON u.org_id = l.org_id AND u.header_id = l.header_id
               LEFT JOIN apps.xxont_som_scheduler s
               ON s.subscriber_id = u.order_admin
               LEFT JOIN apps.oe_transaction_types_tl i
               ON i.transaction_type_id = h.order_type_id
             AND i.LANGUAGE = :"SYS_B_67"
               LEFT JOIN apps.cst_item_cost_type_v c
               ON c.organization_id = l.ship_from_org_id
             AND c.inventory_item_id = l.inventory_item_id
             AND c.cost_type = :"SYS_B_68"
               LEFT JOIN b
               ON b.org_id = l.org_id
             AND b.header_id = l.header_id
             AND b.line_id = l.line_id
               LEFT JOIN v
               ON 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: How to remove left join and ON JOIN from query and use better things in place of that [message #619284 is a reply to message #619283] Mon, 21 July 2014 04:00 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
This is meaningful code:
                       MAX (CASE
                               WHEN s.hold_id = :"SYS_B_02"
                                  THEN :"SYS_B_03"
                            END
                           ) AS h2011,

This is meaningless code:
MAX (CASE
                               WHEN s.hold_id = NULL
                                  THEN 'NULL'
                            END
                           ) AS h2011,


So no, you can't remove the CASE statements, they actually do something useful.
Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619288 is a reply to message #619284] Mon, 21 July 2014 04:19 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
nishantranjan00787 wrote on Mon, 21 July 2014 07:15
There is one query and it using Left Join and ON JOIN and it's a old model of writing code.

Define old model. JOIN clauses are relatively new.

nishantranjan00787 wrote on Mon, 21 July 2014 07:15

The main problem is how to remove that left join and ON join and use better query in place of that.

You use left join if you want to be able to get data from a query even if some of the tables involved don't have matching data. The only thing faster than an outer (left) join is in inner join, but that changes the meaning of the query. So if you need those joins to be outer joins you can't rewrite that in any way that would improve performance.

On clauses generally should have no performance issue at all.

There are probably things that can be done to improve the performance, but rewriting may not be one of them (or it may be).
Considering the complexity of the query it's almost impossible for us to say anything useful without a lot more information.
An explain plan would be a good start.
Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619289 is a reply to message #619284] Mon, 21 July 2014 04:19 Go to previous messageGo to next message
nishantranjan00787
Messages: 36
Registered: July 2014
Location: india
Member
Cookiemaster if that not be the issue then is that Left Join and right join is the issue?
Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619290 is a reply to message #619289] Mon, 21 July 2014 04:20 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Read my last post above.
Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619291 is a reply to message #619290] Mon, 21 July 2014 04:29 Go to previous messageGo to next message
nishantranjan00787
Messages: 36
Registered: July 2014
Location: india
Member
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2364847176

-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                         | Name                          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                  |                               |   156K|   547M|       |   196K  (1)| 00:39:16 |
|   1 |  SORT AGGREGATE                                   |                               |     1 |    20 |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID                     | OE_ORDER_LINES_ALL            |     1 |    20 |       |    20   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                               | OE_ORDER_LINES_N10            |    47 |       |       |     4   (0)| 00:00:01 |
|   4 |    COUNT                                          |                               |       |       |       |            |          |
|*  5 |     FILTER                                        |                               |       |       |       |            |          |
|*  6 |      TABLE ACCESS BY INDEX ROWID                  | OE_ORDER_LINES_ALL            |     1 |    33 |       |    20   (0)| 00:00:01 |
|*  7 |       INDEX RANGE SCAN                            | OE_ORDER_LINES_N10            |    47 |       |       |     4   (0)| 00:00:01 |
|*  8 |  FILTER                                           |                               |       |       |       |            |          |
|   9 |   NESTED LOOPS OUTER                              |                               |     1 |   168 |       |    22   (0)| 00:00:01 |
|  10 |    NESTED LOOPS                                   |                               |     1 |   151 |       |    19   (0)| 00:00:01 |
|  11 |     NESTED LOOPS OUTER                            |                               |     1 |   137 |       |    17   (0)| 00:00:01 |
|  12 |      NESTED LOOPS                                 |                               |     1 |    96 |       |    14   (0)| 00:00:01 |
|  13 |       NESTED LOOPS                                |                               |     1 |    81 |       |    12   (0)| 00:00:01 |
|  14 |        NESTED LOOPS                               |                               |     1 |    77 |       |    12   (0)| 00:00:01 |
|  15 |         NESTED LOOPS                              |                               |     1 |    59 |       |    11   (0)| 00:00:01 |
|  16 |          NESTED LOOPS                             |                               |     1 |    42 |       |     7   (0)| 00:00:01 |
|  17 |           TABLE ACCESS BY INDEX ROWID             | MTL_DEFAULT_CATEGORY_SETS     |     1 |     8 |       |     1   (0)| 00:00:01 |
|* 18 |            INDEX UNIQUE SCAN                      | MTL_DEFAULT_CATEGORY_SETS_U1  |     1 |       |       |     0   (0)| 00:00:01 |
|* 19 |           TABLE ACCESS BY INDEX ROWID             | OE_ORDER_LINES_ALL            |     1 |    34 |       |     6   (0)| 00:00:01 |
|* 20 |            INDEX RANGE SCAN                       | OE_ORDER_LINES_ALL_X14        |     6 |       |       |     3   (0)| 00:00:01 |
|  21 |          TABLE ACCESS BY INDEX ROWID              | CST_ITEM_COSTS                |     1 |    17 |       |     4   (0)| 00:00:01 |
|* 22 |           INDEX RANGE SCAN                        | CST_ITEM_COSTS_U1             |     1 |       |       |     3   (0)| 00:00:01 |
|* 23 |         TABLE ACCESS BY INDEX ROWID               | CST_COST_TYPES                |     1 |    18 |       |     1   (0)| 00:00:01 |
|* 24 |          INDEX UNIQUE SCAN                        | CST_COST_TYPES_U1             |     1 |       |       |     0   (0)| 00:00:01 |
|* 25 |        INDEX UNIQUE SCAN                          | CST_COST_TYPES_U1             |     1 |     4 |       |     0   (0)| 00:00:01 |
|  26 |       TABLE ACCESS BY INDEX ROWID                 | MTL_SYSTEM_ITEMS_B            |     1 |    15 |       |     2   (0)| 00:00:01 |
|* 27 |        INDEX UNIQUE SCAN                          | MTL_SYSTEM_ITEMS_B_UX2        |     1 |       |       |     1   (0)| 00:00:01 |
|* 28 |      INDEX RANGE SCAN                             | FND_LOOKUP_VALUES_U1          |     1 |    41 |       |     3   (0)| 00:00:01 |
|* 29 |     INDEX UNIQUE SCAN                             | MTL_SYSTEM_ITEMS_TL_U1        |     2 |    28 |       |     2   (0)| 00:00:01 |
|* 30 |    INDEX RANGE SCAN                               | MTL_ITEM_CATEGORIES_U1        |     1 |    17 |       |     3   (0)| 00:00:01 |
|* 31 |  VIEW                                             |                               | 67091 |  2555K|       |   153   (2)| 00:00:02 |
|  32 |   TABLE ACCESS FULL                               | SYS_TEMP_0FD9D67C2_3588970D   | 67091 |  1703K|       |   153   (2)| 00:00:02 |
|* 33 |  VIEW                                             |                               | 67091 |  3669K|       |   153   (2)| 00:00:02 |
|  34 |   TABLE ACCESS FULL                               | SYS_TEMP_0FD9D67C2_3588970D   | 67091 |  1703K|       |   153   (2)| 00:00:02 |
|* 35 |  VIEW                                             |                               | 67091 |  3669K|       |   153   (2)| 00:00:02 |
|  36 |   TABLE ACCESS FULL                               | SYS_TEMP_0FD9D67C2_3588970D   | 67091 |  1703K|       |   153   (2)| 00:00:02 |
|* 37 |  VIEW                                             |                               | 67091 |  3669K|       |   153   (2)| 00:00:02 |
|  38 |   TABLE ACCESS FULL                               | SYS_TEMP_0FD9D67C2_3588970D   | 67091 |  1703K|       |   153   (2)| 00:00:02 |
|  39 |  VIEW                                             |                               |   156K|   547M|       |   196K  (1)| 00:39:16 |
|  40 |   TEMP TABLE TRANSFORMATION                       |                               |       |       |       |            |          |
|  41 |    LOAD AS SELECT                                 | SYS_TEMP_0FD9D67C2_3588970D   |       |       |       |            |          |
|* 42 |     HASH JOIN                                     |                               | 67091 |  3931K|  2752K| 61430   (1)| 00:12:18 |
|  43 |      TABLE ACCESS BY INDEX ROWID                  | HZ_CUST_SITE_USES_ALL         | 67091 |  1965K|       | 15617   (1)| 00:03:08 |
|* 44 |       INDEX RANGE SCAN                            | HZ_CUST_SITE_USES_ALL_X2      | 67091 |       |       |   120   (2)| 00:00:02 |
|* 45 |      HASH JOIN                                    |                               | 70245 |  2057K|       | 45534   (1)| 00:09:07 |
|  46 |       TABLE ACCESS BY INDEX ROWID                 | HZ_CUST_ACCT_SITES_ALL        | 70245 |  1097K|       | 38373   (1)| 00:07:41 |
|* 47 |        INDEX RANGE SCAN                           | HZ_CUST_ACCT_SITES_X1         | 70245 |       |       |   131   (2)| 00:00:02 |
|  48 |       TABLE ACCESS FULL                           | HZ_PARTY_SITES                |  1124K|    15M|       |  7142   (1)| 00:01:26 |
|* 49 |    HASH JOIN RIGHT OUTER                          |                               |   156K|   164M|       |   134K  (1)| 00:26:59 |
|* 50 |     TABLE ACCESS FULL                             | OE_TRANSACTION_TYPES_TL       |  2398 | 74338 |       |   131   (2)| 00:00:02 |
|* 51 |     HASH JOIN RIGHT OUTER                         |                               |   101K|   103M|       |   134K  (1)| 00:26:57 |
|  52 |      TABLE ACCESS FULL                            | XXONT_SOM_SCHEDULER           |  5390 |   115K|       |    30   (0)| 00:00:01 |
|* 53 |      HASH JOIN RIGHT OUTER                        |                               |   101K|   101M|    13M|   134K  (1)| 00:26:57 |
|  54 |       VIEW                                        |                               | 39160 |    12M|       |  1392   (1)| 00:00:17 |
|  55 |        HASH UNIQUE                                |                               | 39160 |  5047K|  6680K|  1392   (1)| 00:00:17 |
|  56 |         TABLE ACCESS FULL                         | XXOM_XML1_PATO_STG            | 39160 |  5047K|       |   230   (2)| 00:00:03 |
|  57 |       NESTED LOOPS OUTER                          |                               | 72910 |    48M|       |   130K  (1)| 00:26:02 |
|  58 |        NESTED LOOPS OUTER                         |                               |  4576 |  3034K|       |   111K  (2)| 00:22:23 |
|  59 |         NESTED LOOPS                              |                               |  4576 |  2860K|       | 97330   (2)| 00:19:28 |
|* 60 |          HASH JOIN RIGHT OUTER                    |                               |  4576 |  2524K|       | 88164   (2)| 00:17:38 |
|* 61 |           TABLE ACCESS FULL                       | XXOM_3LP_SYM_ORA_ORDER_HDR    |  8303 |   356K|       |  7833   (4)| 00:01:34 |
|* 62 |           HASH JOIN RIGHT OUTER                   |                               |  4576 |  2328K|       | 80331   (2)| 00:16:04 |
|  63 |            VIEW                                   |                               |   984 |   163K|       | 15533   (4)| 00:03:07 |
|  64 |             HASH GROUP BY                         |                               |   984 | 38376 |       | 15533   (4)| 00:03:07 |
|* 65 |              FILTER                               |                               |       |       |       |            |          |
|  66 |               NESTED LOOPS OUTER                  |                               |   984 | 38376 |       | 15531   (4)| 00:03:07 |
|* 67 |                TABLE ACCESS FULL                  | OE_ORDER_HOLDS_ALL            |   984 | 24600 |       | 13561   (5)| 00:02:43 |
|* 68 |                TABLE ACCESS BY INDEX ROWID        | OE_HOLD_SOURCES_ALL           |     1 |    14 |       |     2   (0)| 00:00:01 |
|* 69 |                 INDEX UNIQUE SCAN                 | OE_HOLD_SOURCES_U1            |     1 |       |       |     1   (0)| 00:00:01 |
|* 70 |            HASH JOIN RIGHT OUTER                  |                               |  4576 |  1568K|       | 64797   (1)| 00:12:58 |
|  71 |             VIEW                                  |                               |    10 |  1420 |       | 14456   (1)| 00:02:54 |
|  72 |              SORT GROUP BY                        |                               |    10 |  1980 |       | 14456   (1)| 00:02:54 |
|* 73 |               FILTER                              |                               |       |       |       |            |          |
|  74 |                NESTED LOOPS                       |                               |       |       |       |            |          |
|  75 |                 NESTED LOOPS                      |                               |    10 |  1980 |       | 14455   (1)| 00:02:54 |
|* 76 |                  HASH JOIN                        |                               |  1821 |   295K|       |  8985   (1)| 00:01:48 |
|  77 |                   TABLE ACCESS BY INDEX ROWID     | FND_LOOKUP_VALUES             |    13 |   702 |       |     6   (0)| 00:00:01 |
|* 78 |                    INDEX RANGE SCAN               | XXAR_FND_LOOKUP_VALUES_N1     |    13 |       |       |     3   (0)| 00:00:01 |
|  79 |                   NESTED LOOPS                    |                               |       |       |       |            |          |
|  80 |                    NESTED LOOPS                   |                               |  1887 |   206K|       |  8978   (1)| 00:01:48 |
|* 81 |                     HASH JOIN                     |                               |  1887 |   167K|       |  5199   (1)| 00:01:03 |
|  82 |                      TABLE ACCESS BY INDEX ROWID  | FND_LOOKUP_VALUES             |    13 |   702 |       |     6   (0)| 00:00:01 |
|* 83 |                       INDEX RANGE SCAN            | XXAR_FND_LOOKUP_VALUES_N1     |    13 |       |       |     3   (0)| 00:00:01 |
|  84 |                      NESTED LOOPS                 |                               |       |       |       |            |          |
|  85 |                       NESTED LOOPS                |                               |  1956 | 72372 |       |  5192   (1)| 00:01:03 |
|  86 |                        TABLE ACCESS BY INDEX ROWID| WSH_DELIVERY_DETAILS          |  1956 | 48900 |       |  1266   (1)| 00:00:16 |
|* 87 |                         INDEX RANGE SCAN          | WSH_DELIVERY_DETAILS_X99      |  2021 |       |       |   537   (2)| 00:00:07 |
|* 88 |                        INDEX RANGE SCAN           | WSH_DELIVERY_ASSIGNMENTS_N3   |     1 |       |       |     2   (0)| 00:00:01 |
|* 89 |                       TABLE ACCESS BY INDEX ROWID | WSH_DELIVERY_ASSIGNMENTS      |     1 |    12 |       |     3   (0)| 00:00:01 |
|* 90 |                     INDEX UNIQUE SCAN             | WSH_NEW_DELIVERIES_U1         |     1 |       |       |     1   (0)| 00:00:01 |
|  91 |                    TABLE ACCESS BY INDEX ROWID    | WSH_NEW_DELIVERIES            |     1 |    21 |       |     2   (0)| 00:00:01 |
|* 92 |                  INDEX UNIQUE SCAN                | OE_ORDER_LINES_U1             |     1 |       |       |     2   (0)| 00:00:01 |
|* 93 |                 TABLE ACCESS BY INDEX ROWID       | OE_ORDER_LINES_ALL            |     1 |    32 |       |     3   (0)| 00:00:01 |
|  94 |             NESTED LOOPS                          |                               |       |       |       |            |          |
|  95 |              NESTED LOOPS                         |                               |  4576 |   933K|       | 50341   (1)| 00:10:05 |
|  96 |               VIEW                                |                               |  4576 |   218K|       |  7596   (1)| 00:01:32 |
|  97 |                HASH GROUP BY                      |                               |  4576 |   107K|       |  7596   (1)| 00:01:32 |
|* 98 |                 FILTER                            |                               |       |       |       |            |          |
|* 99 |                  TABLE ACCESS BY INDEX ROWID      | OE_ORDER_LINES_ALL            |  4576 |   107K|       |  7595   (1)| 00:01:32 |
|*100 |                   INDEX RANGE SCAN                | OE_ORDER_LINES_ALL_X7         | 18305 |       |       |  2549   (3)| 00:00:31 |
|*101 |               INDEX RANGE SCAN                    | OE_ORDER_LINES_ALL_X14        |    20 |       |       |     3   (0)| 00:00:01 |
|*102 |              TABLE ACCESS BY INDEX ROWID          | OE_ORDER_LINES_ALL            |     1 |   160 |       |    11   (0)| 00:00:01 |
|*103 |          TABLE ACCESS BY INDEX ROWID              | OE_ORDER_HEADERS_ALL          |     1 |    75 |       |     2   (0)| 00:00:01 |
|*104 |           INDEX UNIQUE SCAN                       | OE_ORDER_HEADERS_U1           |     1 |       |       |     1   (0)| 00:00:01 |
|*105 |         TABLE ACCESS BY INDEX ROWID               | XXOM_3LP_SYM_ORA_ORDER_LINES  |     1 |    39 |       |     4   (0)| 00:00:01 |
|*106 |          INDEX RANGE SCAN                         | XXOM_3LP_SYM_ORA_ORDER_LIN_N4 |     1 |       |       |     3   (0)| 00:00:01 |
| 107 |        VIEW PUSHED PREDICATE                      | CST_ITEM_COST_TYPE_V          |    16 |   368 |       |     4   (0)| 00:00:01 |
|*108 |         FILTER                                    |                               |       |       |       |            |          |
| 109 |          NESTED LOOPS OUTER                       |                               |     1 |   134 |       |    16   (0)| 00:00:01 |
| 110 |           NESTED LOOPS                            |                               |     1 |   117 |       |    13   (0)| 00:00:01 |
| 111 |            NESTED LOOPS                           |                               |     1 |   113 |       |    13   (0)| 00:00:01 |
| 112 |             NESTED LOOPS                          |                               |     1 |    95 |       |    12   (0)| 00:00:01 |
| 113 |              NESTED LOOPS OUTER                   |                               |     1 |    78 |       |     8   (0)| 00:00:01 |
| 114 |               NESTED LOOPS                        |                               |     1 |    37 |       |     5   (0)| 00:00:01 |
| 115 |                NESTED LOOPS                       |                               |     1 |    23 |       |     3   (0)| 00:00:01 |
| 116 |                 TABLE ACCESS BY INDEX ROWID       | MTL_DEFAULT_CATEGORY_SETS     |     1 |     8 |       |     1   (0)| 00:00:01 |
|*117 |                  INDEX UNIQUE SCAN                | MTL_DEFAULT_CATEGORY_SETS_U1  |     1 |       |       |     0   (0)| 00:00:01 |
| 118 |                 TABLE ACCESS BY INDEX ROWID       | MTL_SYSTEM_ITEMS_B            |     1 |    15 |       |     2   (0)| 00:00:01 |
|*119 |                  INDEX UNIQUE SCAN                | MTL_SYSTEM_ITEMS_B_UX2        |     1 |       |       |     1   (0)| 00:00:01 |
|*120 |                INDEX UNIQUE SCAN                  | MTL_SYSTEM_ITEMS_TL_U1        |     1 |    14 |       |     2   (0)| 00:00:01 |
|*121 |               INDEX RANGE SCAN                    | FND_LOOKUP_VALUES_U1          |     1 |    41 |       |     3   (0)| 00:00:01 |
| 122 |              TABLE ACCESS BY INDEX ROWID          | CST_ITEM_COSTS                |     1 |    17 |       |     4   (0)| 00:00:01 |
|*123 |               INDEX RANGE SCAN                    | CST_ITEM_COSTS_U1             |     1 |       |       |     3   (0)| 00:00:01 |
|*124 |             TABLE ACCESS BY INDEX ROWID           | CST_COST_TYPES                |     1 |    18 |       |     1   (0)| 00:00:01 |
|*125 |              INDEX UNIQUE SCAN                    | CST_COST_TYPES_U1             |     1 |       |       |     0   (0)| 00:00:01 |
|*126 |            INDEX UNIQUE SCAN                      | CST_COST_TYPES_U1             |     1 |     4 |       |     0   (0)| 00:00:01 |
|*127 |           INDEX RANGE SCAN                        | MTL_ITEM_CATEGORIES_U1        |     1 |    17 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("Q"."HEADER_ID"=:B1 AND "Q"."ORG_ID"=:B2)
   3 - access("Q"."TOP_MODEL_LINE_ID"=:B1)
   5 - filter(ROWNUM=TO_NUMBER(:SYS_B_50))
   6 - filter("Q"."HEADER_ID"=:B1 AND "Q"."ORDERED_ITEM"=:B2 AND "Q"."ORG_ID"=:B3)
   7 - access("Q"."TOP_MODEL_LINE_ID"=:B1)
   8 - filter("MDC"."CATEGORY_SET_ID"=NVL("MIC"."CATEGORY_SET_ID","MDC"."CATEGORY_SET_ID"))
  18 - access("MDC"."FUNCTIONAL_AREA_ID"=5)
  19 - filter("Q"."ORG_ID"=:B1)
  20 - access("Q"."HEADER_ID"=:B1 AND "Q"."TOP_MODEL_LINE_ID"=:B2 AND "Q"."ITEM_TYPE_CODE"=:SYS_B_57)
  22 - access("CIC"."INVENTORY_ITEM_ID"="Q"."INVENTORY_ITEM_ID" AND "CIC"."ORGANIZATION_ID"="Q"."SHIP_FROM_ORG_ID")
  23 - filter("CCT"."COST_TYPE"=:SYS_B_56)
  24 - access("CCT"."COST_TYPE_ID"="CIC"."COST_TYPE_ID")
  25 - access("CCT2"."COST_TYPE_ID"="CCT"."DEFAULT_COST_TYPE_ID")
  27 - access("INVENTORY_ITEM_ID"="CIC"."INVENTORY_ITEM_ID" AND "ORGANIZATION_ID"="CIC"."ORGANIZATION_ID" AND
              "COSTING_ENABLED_FLAG"='Y')
  28 - access("LV"."LOOKUP_TYPE"(+)='MTL_PLANNING_MAKE_BUY' AND "LV"."VIEW_APPLICATION_ID"(+)=700 AND
              "LV"."LANGUAGE"(+)=USERENV('LANG'))
       filter("LV"."LANGUAGE"(+)=USERENV('LANG') AND "LV"."SECURITY_GROUP_ID"(+)="FND_GLOBAL"."LOOKUP_SECURITY_GROUP"("LV"."LOOKUP_
              TYPE"(+),"LV"."VIEW_APPLICATION_ID"(+)) AND "PLANNING_MAKE_BUY_CODE"=TO_NUMBER("LV"."LOOKUP_CODE"(+)))
  29 - access("INVENTORY_ITEM_ID"="T"."INVENTORY_ITEM_ID" AND "ORGANIZATION_ID"="T"."ORGANIZATION_ID" AND
              "T"."LANGUAGE"=USERENV('LANG'))
  30 - access("MIC"."ORGANIZATION_ID"(+)="CIC"."ORGANIZATION_ID" AND "MIC"."INVENTORY_ITEM_ID"(+)="CIC"."INVENTORY_ITEM_ID")
  31 - filter(TO_NUMBER("A"."LOCATION")=:B1)
  33 - filter(TO_NUMBER("A"."LOCATION")=:B1 AND "A"."SITE_USE_CODE"=:SYS_B_59)
  35 - filter(TO_NUMBER("A"."LOCATION")=:B1 AND "A"."SITE_USE_CODE"=:SYS_B_60)
  37 - filter(TO_NUMBER("A"."LOCATION")=:B1 AND "A"."SITE_USE_CODE"=:SYS_B_61)
  42 - access("S"."CUST_ACCT_SITE_ID"="C"."CUST_ACCT_SITE_ID" AND "S"."ORG_ID"="C"."ORG_ID")
  44 - access("C"."ORG_ID"=TO_NUMBER(:SYS_B_00) AND "C"."STATUS"=:SYS_B_01)
  45 - access("P"."PARTY_SITE_ID"="S"."PARTY_SITE_ID")
  47 - access("S"."ORG_ID"=TO_NUMBER(:SYS_B_00))
  49 - access("I"."TRANSACTION_TYPE_ID"(+)="H"."ORDER_TYPE_ID")
  50 - filter("I"."LANGUAGE"(+)=:SYS_B_67)
  51 - access("S"."SUBSCRIBER_ID"(+)="U"."ORDER_ADMIN")
  53 - access("P"."CONFIG_HDR_ID"(+)="L"."CONFIG_HEADER_ID")
  60 - access("U"."HEADER_ID"(+)="L"."HEADER_ID" AND "U"."ORG_ID"(+)="L"."ORG_ID")
  61 - filter("U"."HEADER_ID"(+) IS NOT NULL AND "U"."ORG_ID"(+)=TO_NUMBER(:SYS_B_69))
  62 - access("B"."LINE_ID"(+)="L"."LINE_ID" AND "B"."HEADER_ID"(+)="L"."HEADER_ID" AND "B"."ORG_ID"(+)="L"."ORG_ID")
  65 - filter(TO_NUMBER(:SYS_B_26)=TO_NUMBER(:SYS_B_69))
  67 - filter("H"."RELEASED_FLAG"=:SYS_B_27 AND "H"."ORG_ID"=TO_NUMBER(:SYS_B_26) AND "H"."ORG_ID"=TO_NUMBER(:SYS_B_69))
  68 - filter("S"."ORG_ID"(+)=TO_NUMBER(:SYS_B_26) AND "S"."ORG_ID"(+)=TO_NUMBER(:SYS_B_69) AND
              ("S"."HOLD_ID"(+)=TO_NUMBER(:SYS_B_18) OR "S"."HOLD_ID"(+)=TO_NUMBER(:SYS_B_19) OR "S"."HOLD_ID"(+)=TO_NUMBER(:SYS_B_20) OR
              "S"."HOLD_ID"(+)=TO_NUMBER(:SYS_B_21) OR "S"."HOLD_ID"(+)=TO_NUMBER(:SYS_B_22) OR "S"."HOLD_ID"(+)=TO_NUMBER(:SYS_B_23) OR
              "S"."HOLD_ID"(+)=TO_NUMBER(:SYS_B_24) OR "S"."HOLD_ID"(+)=TO_NUMBER(:SYS_B_25)))
  69 - access("S"."HOLD_SOURCE_ID"(+)="H"."HOLD_SOURCE_ID")
  70 - access("V"."LINE_NUMBER"(+)="L"."LINE_NUMBER" AND "V"."HEADER_ID"(+)="L"."HEADER_ID" AND "V"."ORG_ID"(+)="L"."ORG_ID")
  73 - filter(TO_NUMBER(:SYS_B_32)=TO_NUMBER(:SYS_B_69))
  76 - access("H"."LOOKUP_CODE"="N"."STATUS_CODE")
  78 - access("H"."LOOKUP_TYPE"=:SYS_B_30 AND "H"."LANGUAGE"=:SYS_B_31)
  81 - access("C"."LOOKUP_CODE"="D"."RELEASED_STATUS")
  83 - access("C"."LOOKUP_TYPE"=:SYS_B_28 AND "C"."LANGUAGE"=:SYS_B_29)
  87 - access("D"."ORG_ID"=TO_NUMBER(:SYS_B_69))
       filter("D"."ORG_ID"=TO_NUMBER(:SYS_B_32))
  88 - access("M"."DELIVERY_DETAIL_ID"="D"."DELIVERY_DETAIL_ID")
  89 - filter("M"."DELIVERY_ID" IS NOT NULL)
  90 - access("N"."DELIVERY_ID"="M"."DELIVERY_ID")
  92 - access("D"."SOURCE_LINE_ID"="L"."LINE_ID")
  93 - filter(("L"."ITEM_TYPE_CODE"=:SYS_B_33 OR "L"."ITEM_TYPE_CODE"=:SYS_B_34) AND "L"."ORG_ID"=TO_NUMBER(:SYS_B_32) AND
              "L"."ORG_ID"=TO_NUMBER(:SYS_B_69) AND "N"."ORGANIZATION_ID"="L"."SHIP_FROM_ORG_ID" AND "D"."SOURCE_HEADER_ID"="L"."HEADER_ID")
  98 - filter(TO_NUMBER(:SYS_B_64)=TO_NUMBER(:SYS_B_69))
  99 - filter("ITEM_TYPE_CODE"=:SYS_B_65 OR "ITEM_TYPE_CODE"=:SYS_B_66)
 100 - access("ORG_ID"=TO_NUMBER(:SYS_B_69))
       filter("ORG_ID"=TO_NUMBER(:SYS_B_64))
 101 - access("R"."HEADER_ID"="L"."HEADER_ID")
       filter("L"."ITEM_TYPE_CODE"=:SYS_B_70 OR "L"."ITEM_TYPE_CODE"=:SYS_B_71)
 102 - filter("L"."ORG_ID"=TO_NUMBER(:SYS_B_69) AND SUBSTR("L"."ORIG_SYS_LINE_REF",TO_NUMBER(:SYS_B_72),TO_NUMBER(:SYS_B_73))<>:SYS
              _B_74 AND "R"."LINE_NUMBER"="L"."LINE_NUMBER")
 103 - filter("H"."ORG_ID"=TO_NUMBER(:SYS_B_69))
 104 - access("H"."HEADER_ID"="L"."HEADER_ID")
 105 - filter("T"."ORG_ID"(+)=TO_NUMBER(:SYS_B_69))
 106 - access("T"."HEADER_ID"(+)="L"."HEADER_ID" AND "T"."LINE_ID"(+)="L"."LINE_ID")
       filter("T"."LINE_ID"(+) IS NOT NULL AND "T"."HEADER_ID"(+) IS NOT NULL)
 108 - filter("MDC"."CATEGORY_SET_ID"=NVL("MIC"."CATEGORY_SET_ID","MDC"."CATEGORY_SET_ID"))
 117 - access("MDC"."FUNCTIONAL_AREA_ID"=5)
 119 - access("INVENTORY_ITEM_ID"="L"."INVENTORY_ITEM_ID" AND "ORGANIZATION_ID"="L"."SHIP_FROM_ORG_ID" AND
              "COSTING_ENABLED_FLAG"='Y')
 120 - access("T"."INVENTORY_ITEM_ID"="L"."INVENTORY_ITEM_ID" AND "T"."ORGANIZATION_ID"="L"."SHIP_FROM_ORG_ID" AND
              "T"."LANGUAGE"=USERENV('LANG'))
       filter("INVENTORY_ITEM_ID"="T"."INVENTORY_ITEM_ID" AND "ORGANIZATION_ID"="T"."ORGANIZATION_ID")
 121 - access("LV"."LOOKUP_TYPE"(+)='MTL_PLANNING_MAKE_BUY' AND "LV"."VIEW_APPLICATION_ID"(+)=700 AND
              "LV"."LANGUAGE"(+)=USERENV('LANG'))
       filter("LV"."LANGUAGE"(+)=USERENV('LANG') AND "LV"."SECURITY_GROUP_ID"(+)="FND_GLOBAL"."LOOKUP_SECURITY_GROUP"("LV"."LOOKUP_
              TYPE"(+),"LV"."VIEW_APPLICATION_ID"(+)) AND "PLANNING_MAKE_BUY_CODE"=TO_NUMBER("LV"."LOOKUP_CODE"(+)))
 123 - access("INVENTORY_ITEM_ID"="CIC"."INVENTORY_ITEM_ID" AND "ORGANIZATION_ID"="CIC"."ORGANIZATION_ID")
       filter("CIC"."INVENTORY_ITEM_ID"="L"."INVENTORY_ITEM_ID" AND "CIC"."ORGANIZATION_ID"="L"."SHIP_FROM_ORG_ID")
 124 - filter("CCT"."COST_TYPE"=:SYS_B_68)
 125 - access("CCT"."COST_TYPE_ID"="CIC"."COST_TYPE_ID")
 126 - access("CCT2"."COST_TYPE_ID"="CCT"."DEFAULT_COST_TYPE_ID")
 127 - access("MIC"."ORGANIZATION_ID"(+)="L"."SHIP_FROM_ORG_ID" AND "MIC"."INVENTORY_ITEM_ID"(+)="L"."INVENTORY_ITEM_ID")
       filter("MIC"."INVENTORY_ITEM_ID"(+)="CIC"."INVENTORY_ITEM_ID" AND "MIC"."ORGANIZATION_ID"(+)="CIC"."ORGANIZATION_ID")


Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619292 is a reply to message #619289] Mon, 21 July 2014 04:49 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Now that you have posted the actual query, I've had another look Smile

It looks to me as though this is a classic case of a progammer (you and your predecessor)
who does not understand his data. Take this snippet:
FROM apps.oe_order_holds_all h LEFT JOIN apps.oe_hold_sources_all s
                       ON s.org_id = h.org_id
                     AND s.hold_source_id = h.hold_source_id

If you look up the definitions of OE_ORDER_HOLDS_ALL and OE_HOLD_SOURCES_ALL,
you will see that HOLD_SOURCE_ID is a mandatory column in both tables and is
declared as a foreign key. ORG_ID is not in fact mandatory, but I am prepared
to bet that it is in fact populated in all rows of both tables. If I am right,
then the outer join is not necessary. You may be able to remove all the outer
joins in this way. That will give the CBO many more options for running the query.

Outer join cripples performance because it forces a sub-optimal join order.
I do not know how many times I have set this: you must understand your data.
I see so much code where programmers throw in an outer join because they are
afraid of losing rows, when (if they understood their data) they would know
that they could not lose any rows with an inner join.

Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619294 is a reply to message #619292] Mon, 21 July 2014 05:07 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I take it that oe_order_holds_all is a child of oe_hold_sources_all?
Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619295 is a reply to message #619294] Mon, 21 July 2014 05:19 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Yes. You don't need to fire up an EBS database, this sort of information is all
published in the Electronic Technical Reference, etrm.oracle.com. An astonishing
number of soi-disant Apps DBAs don't appear to know that ETRM exists.
Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619296 is a reply to message #619294] Mon, 21 July 2014 05:21 Go to previous messageGo to next message
nishantranjan00787
Messages: 36
Registered: July 2014
Location: india
Member
I HAVE done modification in the top query.Please have a look:

 (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 = 'A')
Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619298 is a reply to message #619296] Mon, 21 July 2014 05:23 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Er... what was the purpose of that? apart from using literals instead of binds,
it is an equivalent SQL that will run in the same way
Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619299 is a reply to message #619298] Mon, 21 July 2014 05:27 Go to previous messageGo to next message
nishantranjan00787
Messages: 36
Registered: July 2014
Location: india
Member
but john if you have to change the query or removing outer join of the below ,what will be your approach:
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 = 'PICK_STATUS'
                     AND c.LANGUAGE = 'US'
                       JOIN apps.fnd_lookup_values h
                       ON h.lookup_code = n.status_code
                     AND h.lookup_type = 'DELIVERY_STATUS'
                     AND h.LANGUAGE = 'US'
                 WHERE l.org_id = 4680
                   AND l.item_type_code IN ('STANDARD', 'CONFIG')
              GROUP BY l.org_id, l.header_id, l.line_number
              ORDER BY l.org_id, l.header_id, l.line_number)
Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619300 is a reply to message #619299] Mon, 21 July 2014 05:34 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I don't understand your previous comment. I can only re-iterate: you need to understand your data.
Whenever you see an outer join or an aggregation (unnecessary DISTINCTs are often a huge problem) you
should ask "why am I doing this?" The answer is often "because I have not bothered to think if it is needed".
Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619301 is a reply to message #619295] Mon, 21 July 2014 05:34 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
John Watson wrote on Mon, 21 July 2014 11:19
An astonishing
number of soi-disant Apps DBAs don't appear to know that ETRM exists.


Considering the number of oracle developers who don't know how to find the DB documentation online, I'm sure I wouldn't be even slightly astonished by that number.
Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619302 is a reply to message #619296] Mon, 21 July 2014 05:40 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
nishantranjan00787 wrote on Mon, 21 July 2014 11:21
I HAVE done modification in the top query.Please have a look:

 (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 = 'A')


You do realise that the JOIN is an inner-join unless it's preceeded by a LEFT, RIGHT or FULL keyword?
Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619303 is a reply to message #619302] Mon, 21 July 2014 05:43 Go to previous messageGo to next message
nishantranjan00787
Messages: 36
Registered: July 2014
Location: india
Member
cookiemaster i have just removed that JOIN..ON condition and make it a simple join query
Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619313 is a reply to message #619303] Mon, 21 July 2014 06:16 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
*sigh*
This:
 (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 = 'A')

Will perform exactly the same as:
SELECT c.LOCATION, c.site_use_code, p.party_site_number
 FROM apps.hz_cust_site_uses_all c JOIN apps.hz_cust_acct_sites_all s
 ON s.cust_acct_site_id = c.cust_acct_site_id
 AND s.org_id = c.org_id
 JOIN apps.hz_party_sites p
 ON p.party_site_id = s.party_site_id
 WHERE c.org_id = 4680 AND c.status = 'A'


JOIN is not a performance issue. repeat that until you believe it.
Using an outer join when you don't need to is a performance issue, but the above example contains no outer joins.
I suggest you spend some time reading the manuals on joins, since you don't appear to understand what JOIN does.
Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619433 is a reply to message #619255] Tue, 22 July 2014 07:58 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Posting TKPROF may help to identify the correct place of the problem
Re: How to remove left join and ON JOIN from query and use better things in place of that [message #620075 is a reply to message #619433] Mon, 28 July 2014 10:55 Go to previous messageGo to next message
nishantranjan00787
Messages: 36
Registered: July 2014
Location: india
Member
I have just rewrite the code to take the session level trace and to tune it.But i am getting error while running

/* Formatted on 2014/07/28 20:01 (Formatter Plus v4.8.8) */
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 = 'A'),
             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
                                  (2011, 3, 2021, 60, 2035, 2421, 2422, 2423)
                   AND h.org_id = 4680
                   AND h.released_flag = 'N'
              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,
                       apps.wsh_delivery_details d,
                       apps.wsh_delivery_assignments m,
                       apps.wsh_new_deliveries n,
                       apps.fnd_lookup_values c,
                       apps.fnd_lookup_values h
                 WHERE d.org_id = l.org_id
                   AND d.source_header_id = l.header_id
                   AND d.source_line_id = l.line_id
                   AND m.delivery_detail_id = d.delivery_detail_id
                   AND n.organization_id = l.ship_from_org_id
                   AND n.delivery_id = m.delivery_id
                   AND c.lookup_code = d.released_status
                   AND c.lookup_type = 'PICK_STATUS'
                   AND c.LANGUAGE = 'US'
                   AND h.lookup_code = n.status_code
                   AND h.lookup_type = 'DELIVERY_STATUS'
                   AND h.LANGUAGE = 'US'
                   AND l.org_id = 4680
                   AND l.item_type_code IN ('STANDARD', 'CONFIG')
              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, 'NULL',
                       NULL, v.dds,
                       'NULL'
                      ) AS o_ln_pick_status,
               DECODE (v.dlc,
                       NULL, 'NULL',
                       NULL, v.dls,
                       'NULL'
                      ) 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)
                           + NULL
                     THEN 'NULL'
                  ELSE 'NULL'
               END AS o_ln_hub_promised,
               CASE
                  WHEN TRUNC (l.schedule_ship_date) =
                          TRUNC (l.creation_date)
                          + NULL
                     THEN 'NULL'
                  ELSE 'NULL'
               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, NULL) * r.q_tot
                      WHEN p.component_item IS NULL
                         THEN (SELECT SUM (  NVL (q.unit_selling_price, NULL)
                                           * 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, NULL) * 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 = 1)
                   END,
                   NULL
                  ) AS o_ln_extended_price,
               ROUND
                  (CASE
                      WHEN l.item_type_code = 'NULL'
                         THEN NVL (c.item_cost, NULL) * r.q_tot
                      WHEN l.item_type_code = 'NULL'
                         THEN (SELECT NVL (v.item_cost, NULL) * r.q_tot
                                 FROM apps.oe_order_lines_all q,
                                      apps.cst_item_cost_type_v v
                                WHERE v.organization_id = q.ship_from_org_id
                                  AND v.inventory_item_id =
                                                           q.inventory_item_id
                                  AND v.cost_type = 'Frozen'
                                  AND 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 = 'CONFIG')
                   END,
                   NULL
                  ) 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 = 'SHIP_TO') 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 = 'BILL_TO') 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 = 'SHIP_TO') 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 ('NULL', 'NULL')
                        ) 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,
               apps.oe_order_headers_all h,
               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,
               (SELECT DISTINCT *
                           FROM xxom.xxom_xml1_pato_stg) p,
               (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 = 4680
                     AND item_type_code IN ('MODEL', 'STANDARD')
                GROUP BY org_id, header_id, line_number) r
         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 = 'US'
          AND c.organization_id = l.ship_from_org_id(+)
           AND c.inventory_item_id = l.inventory_item_id
           AND c.cost_type = 'Frozen'
          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
           AND l.org_id = 4680
           AND l.item_type_code IN ('MODEL', 'STANDARD')
           AND SUBSTR (l.orig_sys_line_ref, 1, 18) != 'OE_ORDER_LINES_ALL');


It is giving the error ORA-00904: : invalid identifier
AND v.line_number = l.line_number
Error while running script [message #620087 is a reply to message #619255] Mon, 28 July 2014 12:47 Go to previous messageGo to next message
nishantranjan00787
Messages: 36
Registered: July 2014
Location: india
Member
I have just rewrite the code to take the session level trace and to tune it.But i am getting error while running

 
/* Formatted on 2014/07/28 20:01 (Formatter Plus v4.8.8) */
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 = 'A'),
             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
                                  (2011, 3, 2021, 60, 2035, 2421, 2422, 2423)
                   AND h.org_id = 4680
                   AND h.released_flag = 'N'
              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,
                       apps.wsh_delivery_details d,
                       apps.wsh_delivery_assignments m,
                       apps.wsh_new_deliveries n,
                       apps.fnd_lookup_values c,
                       apps.fnd_lookup_values h
                 WHERE d.org_id = l.org_id
                   AND d.source_header_id = l.header_id
                   AND d.source_line_id = l.line_id
                   AND m.delivery_detail_id = d.delivery_detail_id
                   AND n.organization_id = l.ship_from_org_id
                   AND n.delivery_id = m.delivery_id
                   AND c.lookup_code = d.released_status
                   AND c.lookup_type = 'PICK_STATUS'
                   AND c.LANGUAGE = 'US'
                   AND h.lookup_code = n.status_code
                   AND h.lookup_type = 'DELIVERY_STATUS'
                   AND h.LANGUAGE = 'US'
                   AND l.org_id = 4680
                   AND l.item_type_code IN ('STANDARD', 'CONFIG')
              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, 'NULL',
                       NULL, v.dds,
                       'NULL'
                      ) AS o_ln_pick_status,
               DECODE (v.dlc,
                       NULL, 'NULL',
                       NULL, v.dls,
                       'NULL'
                      ) 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)
                           + NULL
                     THEN 'NULL'
                  ELSE 'NULL'
               END AS o_ln_hub_promised,
               CASE
                  WHEN TRUNC (l.schedule_ship_date) =
                          TRUNC (l.creation_date)
                          + NULL
                     THEN 'NULL'
                  ELSE 'NULL'
               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, NULL) * r.q_tot
                      WHEN p.component_item IS NULL
                         THEN (SELECT SUM (  NVL (q.unit_selling_price, NULL)
                                           * 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, NULL) * 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 = 1)
                   END,
                   NULL
                  ) AS o_ln_extended_price,
               ROUND
                  (CASE
                      WHEN l.item_type_code = 'NULL'
                         THEN NVL (c.item_cost, NULL) * r.q_tot
                      WHEN l.item_type_code = 'NULL'
                         THEN (SELECT NVL (v.item_cost, NULL) * r.q_tot
                                 FROM apps.oe_order_lines_all q,
                                      apps.cst_item_cost_type_v v
                                WHERE v.organization_id = q.ship_from_org_id
                                  AND v.inventory_item_id =
                                                           q.inventory_item_id
                                  AND v.cost_type = 'Frozen'
                                  AND 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 = 'CONFIG')
                   END,
                   NULL
                  ) 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 = 'SHIP_TO') 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 = 'BILL_TO') 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 = 'SHIP_TO') 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 ('NULL', 'NULL')
                        ) 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,
               apps.oe_order_headers_all h,
               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,
               (SELECT DISTINCT *
                           FROM xxom.xxom_xml1_pato_stg) p,
               (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 = 4680
                     AND item_type_code IN ('MODEL', 'STANDARD')
                GROUP BY org_id, header_id, line_number) r
         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 = 'US'
          AND c.organization_id = l.ship_from_org_id(+)
           AND c.inventory_item_id = l.inventory_item_id
           AND c.cost_type = 'Frozen'
          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
           AND l.org_id = 4680
           AND l.item_type_code IN ('MODEL', 'STANDARD')
           AND SUBSTR (l.orig_sys_line_ref, 1, 18) != 'OE_ORDER_LINES_ALL');


It is giving the error ORA-00904: : invalid identifier
ERROR at line 258:
ORA-00904: "V"."LINE_NUMBER": invalid identifier

*BlackSwan added {code} tags. Please do so yourself in the future.
see http://www.orafaq.com/forum/t/174502/

[Updated on: Mon, 28 July 2014 12:52] by Moderator

Report message to a moderator

Re: Error while running script [message #620088 is a reply to message #620087] Mon, 28 July 2014 12:51 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
What is anyone supposed to do with that unformatted mess? Find a bug that you have just introduced? I would fire any of our developers who produced stuff like that.

And what about the advice I gave you to remove a totally unnecessary outer join? And investigate whether the other outer joins are needed? If you do not intend to do that, it would be polite to explain why.
Re: Error while running script [message #620089 is a reply to message #620088] Mon, 28 July 2014 12:54 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Oh no! I confused your post with another one! Please accept my apologies, the comment regarding outer join was intended for someone else.

--update:
this is not my day. I see that you are the same person who ignored my fix for out joins. Just on a different topic.

[Updated on: Mon, 28 July 2014 12:56]

Report message to a moderator

Re: Error while running script [message #620090 is a reply to message #620089] Mon, 28 July 2014 12:58 Go to previous messageGo to next message
nishantranjan00787
Messages: 36
Registered: July 2014
Location: india
Member
already all things are in place and now after rearranging query it showing the error and i was trying to figure out why this error occur but couldn't find so.
Re: Error while running script [message #620092 is a reply to message #620087] Mon, 28 July 2014 13:01 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Now that BS has added [code] tags, your code is readable. What table is aliased as V? I can't see one. It really does help if you use more meaningfue aliases than single letters.
Re: Error while running script [message #620093 is a reply to message #620092] Mon, 28 July 2014 13:02 Go to previous messageGo to next message
nishantranjan00787
Messages: 36
Registered: July 2014
Location: india
Member
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,
apps.wsh_delivery_details d,
apps.wsh_delivery_assignments m,
apps.wsh_new_deliveries n,
apps.fnd_lookup_values c,
apps.fnd_lookup_values h
Re: Error while running script [message #620094 is a reply to message #620093] Mon, 28 July 2014 13:07 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
The problem is due to nesting or scope of the "V" alias
Re: Error while running script [message #620095 is a reply to message #620093] Mon, 28 July 2014 13:09 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I've merged your two topics.

You have made a conversion from modern, easily readable, ANSI join syntax to the
twentieth century Oracle join syntax. That is a terrible idea. The fact that you
have introduced a bug should convince you of that. ANSI syntax is easier to
read and less prone to error.

If you want to remove outer joins, I've already told the one that you can get rid
of. If you want to get remove any others, look at your data wand work out if the
outer join is needed.
You cannot tune SQL if you do not understand your data.

Re: Error while running script [message #620096 is a reply to message #620095] Mon, 28 July 2014 13:16 Go to previous messageGo to next message
nishantranjan00787
Messages: 36
Registered: July 2014
Location: india
Member
As it was old way of writing code.Currently we are working on the performance fix of this query .So our first priority is to write the code in current trend and then work on improving performance
Re: Error while running script [message #620097 is a reply to message #620096] Mon, 28 July 2014 13:23 Go to previous messageGo to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
So why have you re-written the code in old fashioned way?
Are you under the impression (+) is newer than OUTER JOIN?
Previous Topic: After Table analyze, the number of blocks goes Down
Next Topic: gather table stats
Goto Forum:
  


Current Time: Thu Mar 28 13:53:27 CDT 2024