SELECT DISTINCT A.O FFER_NAME,
A.O FFER_ID,
A.O FFER_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.COM PLETED_DATE,
The PROD. STATE_DATE,
The 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
The FROM offers A,
SUBS,
PROD,
PROD_SPEC,
(SELECT p. ROD_ID, B.V ALUE
The FROM ATTR A, PROD_ATTR_VALUE B
WHERE A.A TTR_ID=B.A TTR_ID
AND A.A TTR_CODE='NC_POSTAL_CODE) POSTAL_CODE,
(SELECT p. ROD_ID, B.V ALUE
The FROM ATTR A, PROD_ATTR_VALUE B
WHERE A.A TTR_ID=B.A TTR_ID
AND A.A TTR_CODE='NC_CUST_REF_ID) CUST_REF_ID,
(SELECT p. ROD_ID, B.V ALUE
The FROM ATTR A, PROD_ATTR_VALUE B
WHERE A.A TTR_ID=B.A TTR_ID
AND A.A TTR_CODE='NC_LOCATION_TYPE) NC_LOCATION_TYPE,
(SELECT p. ROD_ID, B.V ALUE
The FROM ATTR A, PROD_ATTR_VALUE B
WHERE A.A TTR_ID=B.A TTR_ID
AND A.A TTR_CODE='NC_GENERIC_PRODUCT_ORDER) GENERIC_PROD,
NC_SERVICE NS,
ORG RSP,
ORG DEP
WHERE A.O FFER_ID=PROD. OFFER_ID
AND SUBS. SUBS_ID=PROD. PROD_ID
AND SUBS. AGENT_ID _ID=DEP.ORG
AND DEP. PARENT_ORG_ID _ID=RSP.ORG
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
The FROM SUBS B_
WHERE B_. ACC_NBR='FB0001 - L3SP - 030017
The union
The SELECT A_. SUBS_ID
The FROM SUBS_RELA A_
WHERE A_. PARENT_SUBS_ID=
(the SELECT C_. SUBS_ID
The FROM SUBS C_
WHERE C_. ACC_NBR='FB0001 - L3SP - 030017')
AND STATE='A' ).
Above code execution plan shows PROD_ATTR_VALUE table will be a full table scan, and query speed is very slow, if the bold part replaced with a specific number, PROD_ATTR_VALUE will table the index scan to chassis, queries very quickly, so can let the optimizer to enforce the index scan to chassis? Or how to modify the code to avoid a full table scan, hint has tried, seemingly useless
CodePudding user response:
Baidu a hintCodePudding user response:
Hint of baidu, it's no useCodePudding user response:
Modified into table links to see how efficiency?CodePudding user response:
The