可不可以强制让优化器进行index unique scan？

[善良xiaomifeng]

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

[chenjianthree]

百度一下hint

[善良xiaomifeng]

hint百度过了，没用的

[sych888]

修改成表链接看看效率如何？

[善良xiaomifeng]

引用 3 楼 sych888 的回复:

修改成表链接看看效率如何？

将UNION改为表连接？

[善良xiaomifeng]

引用 3 楼 sych888 的回复:

修改成表链接看看效率如何？

怎么改为表连接？

[sych888]

--这样改写一下试试 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 );