有下面的代码:
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已经试过了,貌似没有用耶
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已经试过了,貌似没有用耶
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 );