有下面的代碼：
SELECT DISTINCT A.OFFER_NAME,
A.OFFER_ID,
A.OFFER_CODE,
SUBS.ACC_NBR,
POSTAL_CODE.VALUE NC_POSTAL_CODE,
CUST_REF_ID.VALUE NC_CUST_REF_ID,
NC_LOCATION_TYPE.VALUE LOCATION_TYPE,
GENERIC_PROD.VALUE GENERIC_PROD,
NS.SERVICE_STATE PROD_STATE_NAME,
PROD.COMPLETED_DATE,
PROD.STATE_DATE,
PROD.PROD_STATE,
RSP.ORG_NAME RSP_NAME,
DEP.ORG_NAME AGENT_NAME,
DEP.ORG_ID AGENT_ID,
SUBS.SUBS_ID,
PROD_SPEC.STD_CODE,
PROD_SPEC.PROD_SPEC_NAME
FROM OFFER A,
SUBS,
PROD,
PROD_SPEC,
(SELECT B.PROD_ID, B.VALUE
FROM ATTR A, PROD_ATTR_VALUE B
WHERE A.ATTR_ID = B.ATTR_ID
AND A.ATTR_CODE = 'NC_POSTAL_CODE') POSTAL_CODE,
(SELECT B.PROD_ID, B.VALUE
FROM ATTR A, PROD_ATTR_VALUE B
WHERE A.ATTR_ID = B.ATTR_ID
AND A.ATTR_CODE = 'NC_CUST_REF_ID') CUST_REF_ID,
(SELECT B.PROD_ID, B.VALUE
FROM ATTR A, PROD_ATTR_VALUE B
WHERE A.ATTR_ID = B.ATTR_ID
AND A.ATTR_CODE = 'NC_LOCATION_TYPE') NC_LOCATION_TYPE,
(SELECT B.PROD_ID, B.VALUE
FROM ATTR A, PROD_ATTR_VALUE B
WHERE A.ATTR_ID = B.ATTR_ID
AND A.ATTR_CODE = 'NC_GENERIC_PRODUCT_ORDER') GENERIC_PROD,
NC_SERVICE NS,
ORG RSP,
ORG DEP
WHERE A.OFFER_ID = PROD.OFFER_ID
AND SUBS.SUBS_ID = PROD.PROD_ID
AND SUBS.AGENT_ID = DEP.ORG_ID
AND DEP.PARENT_ORG_ID = RSP.ORG_ID
AND PROD.PROD_ID = POSTAL_CODE.PROD_ID(+)
AND PROD.PROD_ID = CUST_REF_ID.PROD_ID(+)
AND PROD.PROD_ID = NC_LOCATION_TYPE.PROD_ID(+)
AND PROD.PROD_ID = GENERIC_PROD.PROD_ID(+)
AND SUBS.ACC_NBR = NS.SERVICE_ID(+)
AND PROD.Prod_Spec_Id = PROD_SPEC.Prod_Spec_Id
AND PROD.PROD_STATE IN ('A','B')
and subs.subs_id in
(SELECT B_.SUBS_ID
FROM SUBS B_
WHERE B_.ACC_NBR = 'FB0001-L3SP-030017'
union
SELECT A_.SUBS_ID
FROM SUBS_RELA A_
WHERE A_.PARENT_SUBS_ID =
(SELECT C_.SUBS_ID
FROM SUBS C_
WHERE C_.ACC_NBR = 'FB0001-L3SP-030017')
AND STATE='A');
上面代碼的執行計劃顯示PROD_ATTR_VALUE表會進行全表掃描，從而查詢速度非常慢。如果將加粗部分替換為具體的數，PROD_ATTR_VALUE表就會進行index unique scan，查詢非常快，所以可不可以讓優化器強制進行index unique scan？或者該怎樣修改上面的代碼避免全表掃描，hint已經試過了，貌似沒有用耶
uj5u.com熱心網友回復：
百度一下hintuj5u.com熱心網友回復：
hint百度過了，沒用的uj5u.com熱心網友回復：
修改成表鏈接看看效率如何？uj5u.com熱心網友回復：
將UNION改為表連接？
uj5u.com熱心網友回復：
怎么改為表連接？
uj5u.com熱心網友回復：
--這樣改寫一下試試SELECT DISTINCT A.OFFER_NAME,
A.OFFER_ID,
A.OFFER_CODE,
SUBS.ACC_NBR,
POSTAL_CODE.VALUE NC_POSTAL_CODE,
CUST_REF_ID.VALUE NC_CUST_REF_ID,
NC_LOCATION_TYPE.VALUE LOCATION_TYPE,
GENERIC_PROD.VALUE GENERIC_PROD,
NS.SERVICE_STATE PROD_STATE_NAME,
PROD.COMPLETED_DATE,
PROD.STATE_DATE,
PROD.PROD_STATE,
RSP.ORG_NAME RSP_NAME,
DEP.ORG_NAME AGENT_NAME,
DEP.ORG_ID AGENT_ID,
SUBS.SUBS_ID,
PROD_SPEC.STD_CODE,
PROD_SPEC.PROD_SPEC_NAME
FROM OFFER A,
SUBS,
PROD,
PROD_SPEC,
(SELECT B.PROD_ID, B.VALUE
FROM ATTR A, PROD_ATTR_VALUE B
WHERE A.ATTR_ID = B.ATTR_ID
AND A.ATTR_CODE = 'NC_POSTAL_CODE') POSTAL_CODE,
(SELECT B.PROD_ID, B.VALUE
FROM ATTR A, PROD_ATTR_VALUE B
WHERE A.ATTR_ID = B.ATTR_ID
AND A.ATTR_CODE = 'NC_CUST_REF_ID') CUST_REF_ID,
(SELECT B.PROD_ID, B.VALUE
FROM ATTR A, PROD_ATTR_VALUE B
WHERE A.ATTR_ID = B.ATTR_ID
AND A.ATTR_CODE = 'NC_LOCATION_TYPE') NC_LOCATION_TYPE,
(SELECT B.PROD_ID, B.VALUE
FROM ATTR A, PROD_ATTR_VALUE B
WHERE A.ATTR_ID = B.ATTR_ID
AND A.ATTR_CODE = 'NC_GENERIC_PRODUCT_ORDER') GENERIC_PROD,
NC_SERVICE NS,
ORG RSP,
ORG DEP
WHERE A.OFFER_ID = PROD.OFFER_ID
AND SUBS.SUBS_ID = PROD.PROD_ID
AND SUBS.AGENT_ID = DEP.ORG_ID
AND DEP.PARENT_ORG_ID = RSP.ORG_ID
AND PROD.PROD_ID = POSTAL_CODE.PROD_ID(+)
AND PROD.PROD_ID = CUST_REF_ID.PROD_ID(+)
AND PROD.PROD_ID = NC_LOCATION_TYPE.PROD_ID(+)
AND PROD.PROD_ID = GENERIC_PROD.PROD_ID(+)
AND SUBS.ACC_NBR = NS.SERVICE_ID(+)
AND PROD.Prod_Spec_Id = PROD_SPEC.Prod_Spec_Id
AND PROD.PROD_STATE IN ('A','B')
and exists(
select 1 from (SELECT B_.SUBS_ID
FROM SUBS B_
WHERE B_.ACC_NBR = 'FB0001-L3SP-030017'
union all
SELECT A_.SUBS_ID
FROM SUBS_RELA A_
WHERE A_.PARENT_SUBS_ID =
(SELECT C_.SUBS_ID
FROM SUBS C_
WHERE C_.ACC_NBR = 'FB0001-L3SP-030017')
AND STATE='A') c where c.SUBS_ID=subs.subs_id );
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/113619.html
標籤：開發
上一篇：二次更新問題，兩個行程同時修改一條資料造成一條資料的更新丟失
下一篇：查看臨時表空間的使用情況