Oracle query optimization (with joins and subselects)

Question

I want to Optimize the Query below. I don't have expertise on optimization techniques. Please suggest me something which can help me to Optimize the query below :

SELECT 
       ad.towncity,
       ad.state,

FROM   promptdescription pd,
       osquestion osq,

WHERE  acc.status = 1
AND    acc.customer_id = con.customer_id
       ap.os_id
AND NOT EXISTS (SELECT 1
                FROM   osquestion osq2,
                       orderedproduct op3
                WHERE  osq2.ext_quest_id = pd.id
                AND osq2.question_id > osq.question_id

see:http://stackoverflow.com/questions/761204/what-resources-exist-for-database-performance-tuning and http://stackoverflow.com/questions/3706750/sql-tuning-in-oracle — Kevin Burton, Oct 05 '11 at 09:36
@yakub_moriss: maybe some nice indenting would attract readers. In the current form, I am not attracted by it. Also, I think the person who caused this query to exist should be shot. — wildplasser, Oct 05 '11 at 09:47
@catcall: beautiful! You even fixed the ugly correlation names. — wildplasser, Oct 05 '11 at 10:05
Start with `EXPLAIN PLAN`: http://stackoverflow.com/questions/7591354/sql-optimizer-for-large-db-table/7591482#7591482 — Mike Sherrill 'Cat Recall', Oct 05 '11 at 10:08
@wildplasser: I can't take credit for that. http://www.wangz.net/cgi-bin/pp/gsqlparser/sqlpp/sqlformat.tpl — Mike Sherrill 'Cat Recall', Oct 05 '11 at 10:09

Benoit · Answer 1 · 2011-10-05T10:45:21.610

First, your query is equivalent to:

SELECT ACC.id
     , OP.rootprODUCT_ID
     , ACC.externALBILLID
     , CON.firstnAME
     , CON.lastnaME
     , AD.line1
     , NVL ( AD.line2 , '' ) AS     LINE2
     , AD.towncity
     , AD.state
     , AD.postalzipcode
     , PROD.billingcode
     , PD.name               as     Prompt_Name
     , OSQ.current_value
  FROM promptdescription             PD
  JOIN osquestion                   OSQ ON        OSQ.ext_quest_id  =         PD.id
  JOIN osquestiongroup           OSQGRP ON     OSQGRP.qg_id         =        OSQ.qg_id
  JOIN osparamdatacapability OSPARAMCAP ON OSPARAMCAP.cap_id        =     OSQGRP.cap_id
  JOIN orderedservice                OS ON         OS.id            = OSPARAMCAP.os_id
  JOIN servicedescription            SD ON         SD.id            =         OS.svcdesc_id
  JOIN orderedproduct                OP ON         OP.ordservice_id =         OS.id
  JOIN productdescription          PROD ON       PROD.id            =         OP.proddesc_id_root
  JOIN customerorder                 CO ON         CO.id            =         OP.custorder_id
  JOIN account                      ACC ON        ACC.id            =         OP.account_id
  JOIN contact                        C ON          C.customer_id   =        ACC.customer_id
  JOIN address                     ADDR ON       ADDR.id            =          C.address_id
  JOIN contact                      CON ON        CON.customer_id   =        ACC.customer_id
  JOIN address                       AD ON         AD.id            =        CON.address_id
 WHERE PD.name IN ( 'County Code' , 'Service Occurrence' , 'VoIP Port' , 'VoIP MTA MAC address' , 'Primary TN' , 'HSDS CM MAC address' , 'VoIP FQDN' , 'Caller Id' )
   AND OSQ.current_value IS NOT NULL
   AND SD.name in ( 'Voice over IP' )
   AND OP.resultingaction <> 2
   AND OP.status = 10
   AND ACC.status = 1
   AND EXISTS ( SELECT 1
       FROM ORDEREDPRODUCT OP1
       WHERE OP1.PRODUCT_ID = OP.PRODUCT_ID
       AND OP1.STATUS = 10
       HAVING MAX ( OP1.ID ) = OP.ID )
   AND CO.ID IN ( select ID
       FROM CUSTOMERORDER CO1
       where CO1.CUSTOMER_ID = ACC.CUSTOMER_ID
       AND CO1.STATUS = 10 )
   AND NOT EXISTS ( SELECT 1
                      FROM OSQUESTION OSQ2
                         , ORDEREDSERVICE OS1
                         , OSPARAMDATACAPABILITY OSPARAMCAP1
                         , OSQUESTIONGROUP OSQGRP1
                         , ORDEREDPRODUCT OP3
                     where OSQ2.EXT_QUEST_ID = PD.ID
                       AND OSQ2.QUESTION_ID > OSQ.QUESTION_ID
                       AND OSQ2.PROVISIONED_VALUE = OSQ.CURRENT_VALUE
                       AND OSQ2.CURRENT_VALUE != OSQ.CURRENT_VALUE
                       AND PD.NAME IN ( 'Primary TN'
                                      , 'VoIP MTA MAC address'
                                      , 'HSDS CM MAC address' )
                       AND OSQGRP1.QG_ID = OSQ2.QG_ID
                       AND OSPARAMCAP1.CAP_ID = OSQGRP1.CAP_ID
                       AND OS1.ID = OSPARAMCAP1.OS_ID
                       AND OP.ROOTPRODUCT_ID = OP3.ROOTPRODUCT_ID
                       AND OP3.ORDSERVICE_ID = OS1.ID
                       AND OP3.STATUS = 10 )
 ORDER BY ACC.EXTERNALBILLID
        , OP.ROOTPRODUCT_ID
        , CO.ID

Remarks :

you are joining Contact and Address twice (it is useless).
if you want orderedproduct with maximal id you can use a semi-join (you will find other questions about that).
checking CO.ID against the contact table can be done in the JOIN part.

I could rewrite the first part as:

SELECT ACC.id
     , OP.rootprODUCT_ID
     , ACC.externALBILLID
     , CON.firstnAME
     , CON.lastnaME
     , AD.line1
     , NVL ( AD.line2 , '' ) AS     LINE2
     , AD.towncity
     , AD.state
     , AD.postalzipcode
     , PROD.billingcode
     , PD.name               as     Prompt_Name
     , OSQ.current_value
  FROM promptdescription             PD
  JOIN osquestion                   OSQ  ON        OSQ.ext_quest_id  =         PD.id
  JOIN osquestiongroup           OSQGRP  ON     OSQGRP.qg_id         =        OSQ.qg_id
  JOIN osparamdatacapability OSPARAMCAP  ON OSPARAMCAP.cap_id        =     OSQGRP.cap_id
  JOIN orderedservice                OS  ON         OS.id            = OSPARAMCAP.os_id
  JOIN servicedescription            SD  ON         SD.id            =         OS.svcdesc_id
  JOIN orderedproduct                OP  ON         OP.ordservice_id =         OS.id
  JOIN productdescription          PROD  ON       PROD.id            =         OP.proddesc_id_root
  JOIN account                      ACC  ON        ACC.id            =         OP.account_id
  JOIN contact                        C  ON          C.customer_id   =        ACC.customer_id
  JOIN address                     ADDR  ON       ADDR.id            =          C.address_id
  JOIN contact                      CON  ON        CON.customer_id   =        ACC.customer_id
  JOIN address                       AD  ON         AD.id            =        CON.address_id
  JOIN customerorder                 CO  ON         CO.id            =         OP.custorder_id
                                        AND         CO.customer_id   =        ACC.customer_id
  LEFT JOIN orderedproduct    ismaxopid  ON ismaxopid.product_id = op.product_id
                                        AND ismaxopid.status = 10
                                        AND ismaxopid.id > op.id
 WHERE PD.name IN ( 'County Code' , 'Service Occurrence' , 'VoIP Port' , 'VoIP MTA MAC address' , 'Primary TN' , 'HSDS CM MAC address' , 'VoIP FQDN' , 'Caller Id' )
   AND OSQ.current_value IS NOT NULL
   AND SD.name in ( 'Voice over IP' )
   AND OP.resultingaction <> 2
   AND OP.status = 10
   AND ACC.status = 1
   AND ismaxopid.id IS NULL
   AND NOT EXISTS ( SELECT 1
                      FROM OSQUESTION OSQ2
                         , ORDEREDSERVICE OS1
                         , OSPARAMDATACAPABILITY OSPARAMCAP1
                         , OSQUESTIONGROUP OSQGRP1
                         , ORDEREDPRODUCT OP3
                     where OSQ2.EXT_QUEST_ID = PD.ID
                       AND OSQ2.QUESTION_ID > OSQ.QUESTION_ID
                       AND OSQ2.PROVISIONED_VALUE = OSQ.CURRENT_VALUE
                       AND OSQ2.CURRENT_VALUE != OSQ.CURRENT_VALUE
                       AND PD.NAME IN ( 'Primary TN'
                                      , 'VoIP MTA MAC address'
                                      , 'HSDS CM MAC address' )
                       AND OSQGRP1.QG_ID = OSQ2.QG_ID
                       AND OSPARAMCAP1.CAP_ID = OSQGRP1.CAP_ID
                       AND OS1.ID = OSPARAMCAP1.OS_ID
                       AND OP.ROOTPRODUCT_ID = OP3.ROOTPRODUCT_ID
                       AND OP3.ORDSERVICE_ID = OS1.ID
                       AND OP3.STATUS = 10 )
 ORDER BY ACC.EXTERNALBILLID
        , OP.ROOTPRODUCT_ID
        , CO.ID

The NOT EXISTS part still remains to be improved.

OTOH the "LEFT JOIN orderedproduct ismaxopid ON ismaxopid.product_id" can be rewritten to a "NOT EXISTS" subquery, which at least reduces the rangetable in the outer select. The other "NOT EXISTS" thing seems like a common subexpression with (part of) the outer query (which could -for clarity- be condensed into a view) — wildplasser, Oct 05 '11 at 11:08
@wildplasser: I have experienced that left-join with nonnull check, most of the time, results in a faster execution time (I had a query which took 4 seconds and rewriting that not exists part made it run in 3 μs). — Benoit, Oct 05 '11 at 11:13
I see that "LEFT JOIN+NULL check" idiom a lot here. It seems to be a {oracle, mysql} thing. In Postgres, they result in exactly the same query plan. "NOT EXISTS" is clearer, and more readable, IMHO. The MAX() thing could be done as an aggregate, too (+- the nullability of the aggregate) — wildplasser, Oct 05 '11 at 11:19

score 0 · Answer 2 · answered Oct 05 '11 at 10:27

0

First of All Identify all the PK fields in all the tables of the query. Then change your where condition according to the sequence of PK fields created in the oracle db table.

answered Oct 05 '11 at 10:27

Waqas Mehmood

135
1
1
7

Oracle query optimization (with joins and subselects)

2 Answers2