Home » SQL & PL/SQL » SQL & PL/SQL » Need help to rewrite the query (11G)
Need help to rewrite the query [message #683064] Mon, 30 November 2020 22:43 Go to next message
nikhiljuneja@gmail.com
Messages: 8
Registered: December 2012
Location: Delhi
Junior Member
Hi friends,

Sorry for not adhering to the rules in advance, as i am unable to get complete database access.

I need to rewrite this query in more efficient way. I am unable to understand even why it was written that way.

Please see if someone gets anything clicked in his/her mind by seeing this query:


WITH tmp_tab AS

(

SELECT DISTINCT EQP_LOCAL_EQP,F.EQP_MODEL_EQP, S.FLAG FROM EQP_BASE F

INNER JOIN POD_ROUTER S ON F.EQP_MODEL_EQP = S.EQP_MODEL_EQP

)

SELECT * FROM

(

SELECT A.SYS_ROIT AS SYS, A.PRIN_ROIT AS PRIN, A.AGNT_ROIT AS AGNT, A.SUB_ACCT_NO_ROIT AS SUB_ACCT_NO, A.ORDER_NO_ROIT AS ORDER_NO, E1.FLAG, 'R' AS FLAG1

FROM OIT_ITEMS A

LEFT JOIN OIT_ITEMS B ON A.SYS_ROIT = B.SYS_ROIT AND A.SUB_ACCT_NO_ROIT = B.SUB_ACCT_NO_ROIT AND A.ORDER_NO_ROIT = B.ORDER_NO_ROIT AND B.SERV_CDE_ROIT = 'INS30' AND B.ITEM_STATUS_ROIT != 'X' AND B.AFT_QTY_ROIT - B.BEF_QTY_ROIT > 0

LEFT JOIN OIT_ITEMS B1 ON A.SYS_ROIT = B1.SYS_ROIT AND A.SUB_ACCT_NO_ROIT = B1.SUB_ACCT_NO_ROIT AND A.ORDER_NO_ROIT = B1.ORDER_NO_ROIT AND B1.SERV_CDE_ROIT IN ('INS28','INS27') AND B1.ITEM_STATUS_ROIT != 'X' AND B1.AFT_QTY_ROIT - B1.BEF_QTY_ROIT > 0

LEFT JOIN tmp_tab E ON A.SUB_ACCT_NO_ROIT = E.EQP_LOCAL_EQP AND E.FLAG = 'SC'

LEFT JOIN tmp_tab E1 ON A.SUB_ACCT_NO_ROIT = E1.EQP_LOCAL_EQP AND E1.FLAG = 'AW'

WHERE A.ITEM_STATUS_ROIT != 'X' AND A.SERV_CDE_ROIT IN ('INS60', 'INS61') AND A.AFT_QTY_ROIT - A.BEF_QTY_ROIT > 0

AND

(

(B.SERV_CDE_ROIT IS NULL AND E.EQP_LOCAL_EQP IS NULL)

OR

((B.SERV_CDE_ROIT IS NULL OR B1.SERV_CDE_ROIT IS NULL) AND E.EQP_LOCAL_EQP IS NULL AND E1.EQP_LOCAL_EQP IS NOT NULL)

)

)

Re: Need help to rewrite the query [message #683068 is a reply to message #683064] Tue, 01 December 2020 00:14 Go to previous messageGo to next message
Michel Cadot
Messages: 67850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Format your query, if you don't know how to do it, learn it using SQL Formatter.

For any performances question, please read http://www.orafaq.com/forum/index.php/mv/msg/206002/433888/#msg_433888 and post the required information.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

Re: Need help to rewrite the query [message #683069 is a reply to message #683068] Tue, 01 December 2020 00:15 Go to previous messageGo to next message
nikhiljuneja@gmail.com
Messages: 8
Registered: December 2012
Location: Delhi
Junior Member
Thanks Michel. I will say this is more of rewriting the query rather than performance tuning of the query.

WITH tmp_tab 
     AS (SELECT DISTINCT eqp_local_eqp, 
                         F.eqp_model_eqp, 
                         S.flag 
         FROM   eqp_base F 
                INNER JOIN pod_router S 
                        ON F.eqp_model_eqp = S.eqp_model_eqp) 
SELECT * 
FROM   (SELECT A.sys_roit         AS SYS, 
               A.prin_roit        AS PRIN, 
               A.agnt_roit        AS AGNT, 
               A.sub_acct_no_roit AS SUB_ACCT_NO, 
               A.order_no_roit    AS ORDER_NO, 
               E1.flag, 
               'R'                AS FLAG1 
        FROM   oit_items A 
               LEFT JOIN oit_items B 
                      ON A.sys_roit = B.sys_roit 
                         AND A.sub_acct_no_roit = B.sub_acct_no_roit 
                         AND A.order_no_roit = B.order_no_roit 
                         AND B.serv_cde_roit = 'INS30' 
                         AND B.item_status_roit != 'X' 
                         AND B.aft_qty_roit - B.bef_qty_roit > 0 
               LEFT JOIN oit_items B1 
                      ON A.sys_roit = B1.sys_roit 
                         AND A.sub_acct_no_roit = B1.sub_acct_no_roit 
                         AND A.order_no_roit = B1.order_no_roit 
                         AND B1.serv_cde_roit IN ( 'INS28', 'INS27' ) 
                         AND B1.item_status_roit != 'X' 
                         AND B1.aft_qty_roit - B1.bef_qty_roit > 0 
               LEFT JOIN tmp_tab E 
                      ON A.sub_acct_no_roit = E.eqp_local_eqp 
                         AND E.flag = 'SC' 
               LEFT JOIN tmp_tab E1 
                      ON A.sub_acct_no_roit = E1.eqp_local_eqp 
                         AND E1.flag = 'AW' 
        WHERE  A.item_status_roit != 'X' 
               AND A.serv_cde_roit IN ( 'INS60', 'INS61' ) 
               AND A.aft_qty_roit - A.bef_qty_roit > 0 
               AND ( ( B.serv_cde_roit IS NULL 
                       AND E.eqp_local_eqp IS NULL ) 
                      OR ( ( B.serv_cde_roit IS NULL 
                              OR B1.serv_cde_roit IS NULL ) 
                           AND E.eqp_local_eqp IS NULL 
                           AND E1.eqp_local_eqp IS NOT NULL ) )) 

[Updated on: Tue, 01 December 2020 00:19]

Report message to a moderator

Re: Need help to rewrite the query [message #683071 is a reply to message #683069] Tue, 01 December 2020 00:25 Go to previous messageGo to next message
Michel Cadot
Messages: 67850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You (and we) need to understand your query and its requirements and to understand it you (and we) need the table structures and their relations otherwise how:
AND ( ( B.serv_cde_roit IS NULL 
                       AND E.eqp_local_eqp IS NULL ) 
                      OR ( ( B.serv_cde_roit IS NULL 
                              OR B1.serv_cde_roit IS NULL ) 
                           AND E.eqp_local_eqp IS NULL 
                           AND E1.eqp_local_eqp IS NOT NULL ) )) 
can be understood?

Re: Need help to rewrite the query [message #683072 is a reply to message #683071] Tue, 01 December 2020 00:27 Go to previous message
nikhiljuneja@gmail.com
Messages: 8
Registered: December 2012
Location: Delhi
Junior Member
I agree, actually this is someone else's written code and we do not have access to their DB directly.
I will try to get Table structures and sample data and will share as soon as I have it.
Thanks
Previous Topic: week start from saturday
Next Topic: Help for a noobie!
Goto Forum:
  


Current Time: Sun May 16 08:28:12 CDT 2021