Oracle查询优化(连接和子选择)

Question

我想优化下面的查询。我没有优化技术方面的专业知识。请给我一些建议，可以帮助我优化下面的查询:

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

Answer 1

首先确定查询的所有表中的所有PK字段。然后根据oracle db表中创建的PK字段的顺序修改where条件

Answer 2

首先，您的查询相当于:

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

备注:

• 你的联系人和地址加了两次(没用的)。
• 如果你想要orderedproduct的最大id，你可以使用半连接(你会发现其他问题)。
根据联系人表检查CO.ID可以在JOIN部分完成。

我可以把第一部分重写为:

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

NOT EXISTS部分还有待改进