Oracle 如何避免重复扫描同一张表

嗨，汤姆

此查询一次又一次地扫描下面的同一表，因此
表SALES_CASE在此查询中被引用5次。这张表有300万行。
表MDM_KC_ORGANIZATION被引用5次。这个表有300,000条记录。
表sor.person被引用3次。rowcount 4.5万
表sucus被引用2次，行计数为1.8 M。
表supcus属性使用2次，行计数为3.5 M。

您能否提供一种开发方法来扫描一次。请参阅下面的查询-

查询开始-

SELECT /*+ PARALLEL(4) */ /* SL_SALES_CASE_S Base sales case data (type S) */
CAST(PREKEY.BEG_CAL_KEY AS NUMBER(38,0)) AS BEG_CAL_KEY,
CAST(PREKEY.END_CAL_KEY AS NUMBER(38,0)) AS END_CAL_KEY,
CAST('W' AS CHAR(1 CHAR)) AS SUMMARY_TYPE,
CAST('S' AS CHAR(1 CHAR)) AS LINE_TYPE,
CAST(SORSCA.SRCSYS_PRIMARY_ID AS VARCHAR2(50 CHAR)) AS SALES_CASE_ID,
CAST('0' AS VARCHAR2(50 CHAR)) AS SALES_CASE_ITEM_ID,
CAST(COMDIV.DIVISION_CODE AS VARCHAR2(50 CHAR)) AS DIVISION_CODE,
CAST(CASE
  WHEN SORLEG.MDM_KC_ORGANIZATION_KEY>0 THEN SORLEG.MDM_KC_ORGANIZATION_ID
  ELSE '0'
END AS VARCHAR2(50 CHAR)) AS LEGAL_COMPANY_CODE,
CAST(CASE
  WHEN SORSAT.MDM_KC_ORGANIZATION_KEY<>0 THEN SORSAT.MDM_KC_ORGANIZATION_ID
  WHEN SORBRA.MDM_KC_ORGANIZATION_KEY<>0 THEN SORBRA.MDM_KC_ORGANIZATION_ID
  WHEN SORDIS.MDM_KC_ORGANIZATION_KEY<>0 THEN SORDIS.MDM_KC_ORGANIZATION_ID
  WHEN SORBUS.MDM_KC_ORGANIZATION_KEY<>0 THEN SORBUS.MDM_KC_ORGANIZATION_ID
  ELSE '0'
END AS VARCHAR2(50 CHAR)) AS ORGANIZATION_CODE,
CAST(SORBUS.MDM_KC_ORGANIZATION_ID AS VARCHAR2(50 CHAR)) AS BUSINESS_ORGANIZATION_CODE,
CAST(SORPER.SRCSYS_PRIMARY_ID AS VARCHAR2(50 CHAR)) AS OWNER_PERSON_ROW_ID,
CAST(NVL(SORPER.AD_ID,'0') AS VARCHAR2(50 CHAR)) AS OWNER_PERSON_AD_ID,
CAST(NVL(ATTPERINA.ATTR_FLAG,0) AS NUMBER(38,0)) AS OWNER_PERSON_INACT_FLAG,
CAST(SORCUS.SRCSYS_PRIMARY_ID AS VARCHAR2(50 CHAR)) AS CUSTOMER_ROW_ID,
CAST(SORCPT.SRCSYS_PRIMARY_ID AS VARCHAR2(50 CHAR)) AS COMPETITOR_ROW_ID,
CAST(DESCTY.COUNTRY_CODE AS CHAR(2 CHAR)) AS DESTINATION_COUNTRY_CODE,
CAST(CUSCTY.COUNTRY_CODE AS CHAR(2 CHAR)) AS CUSTOMER_COUNTRY_CODE,
CAST(SCASPC.SP_CODE AS VARCHAR2(10 CHAR)) AS SCASE_SP_CODE,
CAST('N/A' AS VARCHAR2(10 CHAR)) AS PLINE_SP_CODE,
CAST(CUSSMA.SMA_CODE AS VARCHAR2(30 CHAR)) AS CUSTOMER_SMA_CODE,
CAST(SCASMA.SMA_CODE AS VARCHAR2(30 CHAR)) AS SCASE_SMA_CODE,
CAST('0' AS VARCHAR2(30 CHAR)) AS PLINE_SMA_CODE,
CAST(SORSCA.SRCSYS_CREATE_CAL_KEY AS NUMBER(38,0)) AS CREATED_CAL_KEY,
CAST(SORSCA.OFFER_UPDATE_CAL_KEY AS NUMBER(38,0)) AS UPDATED_CAL_KEY,
CAST(SORSCA.DECLINE_CAL_KEY AS NUMBER(38,0)) AS DECLINE_CAL_KEY,
CAST(SORSCA.CANCEL_CAL_KEY AS NUMBER(38,0)) AS CANCEL_CAL_KEY,
CAST(SORSCA.LOST_CAL_KEY AS NUMBER(38,0)) AS LOST_CAL_KEY,
CAST(SORSCA.DECISION_CAL_KEY AS NUMBER(38,0)) AS DECISION_CAL_KEY,
CAST(SORSCA.INQUIRY_CAL_KEY AS NUMBER(38,0)) AS INQUIRY_CAL_KEY,
CAST(SORSCA.CHECK_CAL_KEY AS NUMBER(38,0)) AS CHECK_CAL_KEY,
CAST(SORSCA.VALID_UNTIL_CAL_KEY AS NUMBER(38,0)) AS VALID_UNTIL_CAL_KEY,
CAST(SORSCA.EXPECTED_DELIVERY_CAL_KEY AS NUMBER(38,0)) AS EXPECTED_DELIVERY_CAL_KEY,
CAST(SORSCA.INSTALL_CAL_KEY AS NUMBER(38,0)) AS INSTALL_CAL_KEY,
CAST(SORSCA.EXPECTED_OFFER_CAL_KEY AS NUMBER(38,0)) AS EXPECTED_OFFER_CAL_KEY,
CAST(SORSCA.ORDER_RECEIVED_CAL_KEY AS NUMBER(38,0)) AS ORDER_RECEIVED_CAL_KEY,
CAST(NVL(OFFDAY.STAGE_OFFER_CAL_KEY,0) AS NUMBER(38,0)) AS STAGE_OFFER_CAL_KEY,
CAST(NVL(HOTDAY.STAGE_HOT_OFFER_CAL_KEY,0) AS NUMBER(38,0)) AS STAGE_HOT_OFFER_CAL_KEY,
CAST(SORSCA.CHECK_CAL_KEY AS NUMBER(38,0)) AS NEXT_CUST_COMM_CAL_KEY,
CAST(SORSCA.CLOSED_CAL_KEY AS NUMBER(38,0)) AS CLOSED_CAL_KEY,
CAST(SORSCA.REOPENED_CAL_KEY AS NUMBER(38,0)) AS REOPENED_CAL_KEY,
CAST(SORSCA.HIDDEN_FLAG AS NUMBER(38,0)) AS HIDDEN_FLAG,
CAST(1 AS NUMBER(38,0)) AS MAIN_SCASE_LINE_FLAG,
CAST(CASE
  WHEN SORSAT.CATEGORY='Alpha' THEN 1
  WHEN SORBRA.CATEGORY='Alpha' THEN 1
  WHEN SORDIS.CATEGORY='Alpha' THEN 1
  WHEN SORBUS.CATEGORY='Alpha' THEN 1
  ELSE 0
END AS NUMBER(38,0)) AS ALPHA_FLAG,
CAST(SORSCA.REOPENED_FLAG AS NUMBER(38,0)) AS REOPENED_FLAG,
CAST(SORSCA.LLE_SALES_CASE_FLAG AS NUMBER(38,0)) AS LLE_SALES_CASE_FLAG,
CAST(COMDIV.DIVISION_NAME AS VARCHAR2(200 CHAR)) AS DIVISION_NAME,
CAST(CASE
  WHEN SORSAT.MDM_KC_ORGANIZATION_KEY<>0 THEN SORSAT.MDM_KC_ORGANIZATION_NAME
  WHEN SORBRA.MDM_KC_ORGANIZATION_KEY<>0 THEN SORBRA.MDM_KC_ORGANIZATION_NAME
  WHEN SORDIS.MDM_KC_ORGANIZATION_KEY<>0 THEN SORDIS.MDM_KC_ORGANIZATION_NAME
  WHEN SORBUS.MDM_KC_ORGANIZATION_KEY<>0 THEN SORBUS.MDM_KC_ORGANIZATION_NAME
  ELSE ' '
END AS VARCHAR2(200 CHAR)) AS ORGANIZATION_NAME,
CAST(NVL(SORPER.FIRST_NAME,' ') AS VARCHAR2(50 CHAR)) AS OWNER_PERSON_FIRST_NAME,
CAST(NVL(SORPER.LAST_NAME,' ') AS VARCHAR2(50 CHAR)) AS OWNER_PERSON_LAST_NAME,
CAST(SORCUS.SUPCUS_NUMBER AS VARCHAR2(50 CHAR)) AS CUSTOMER_NUMBER,
CAST(SORCUS.SUPCUS_NAME AS VARCHAR2(100 CHAR)) AS CUSTOMER_NAME,
CAST(SORCPT.SUPCUS_NUMBER AS VARCHAR2(50 CHAR)) AS COMPETITOR_NUMBER,
CAST(SORCPT.SUPCUS_NAME AS VARCHAR2(100 CHAR)) AS COMPETITOR_NAME,
CAST(DESCTY.COUNTRY_NAME AS VARCHAR2(50 CHAR)) AS DESTINATION_COUNTRY_NAME,
CAST(CUSCTY.COUNTRY_NAME AS VARCHAR2(50 CHAR)) AS CUSTOMER_COUNTRY_NAME,
CAST(SCASTA.STATUS AS VARCHAR2(20 CHAR)) AS SCASE_STATUS,
CAST(STGSTA.STATUS AS VARCHAR2(20 CHAR)) AS STAGE_STATUS,
CAST(STGOST.STATUS AS VARCHAR2(20 CHAR)) AS STAGE_STATUS_ORIG,
CAST(' ' AS VARCHAR2(20 CHAR)) AS PLINE_STATUS,
CAST(SCASTG.STAGE_NAME AS VARCHAR2(50 CHAR)) AS STAGE,
CAST(SCOSTG.STAGE_NAME AS VARCHAR2(50 CHAR)) AS STAGE_ORIG,
CAST(SORSCA.STAGE_ORDNUM AS NUMBER(38,0)) AS STAGE_ORDNUM,
CAST(SORSCO.STAGE_ORDNUM AS NUMBER(38,0)) AS STAGE_ORDNUM_ORIG,
CAST(NVL(SCAPHA.PHASE_NAME,' ') AS VARCHAR2(50 CHAR)) AS PHASE,
CAST(SCAREA.REASON AS VARCHAR2(20 CHAR)) AS REASON,
CAST(SCALSO.LEAD_SOURCE AS VARCHAR2(200 CHAR)) AS LEAD_SOURCE,
CAST(SCATYP.TYPE AS VARCHAR2(20 CHAR)) AS SCASE_TYPE,
CAST(RNWTYP.TYPE AS VARCHAR2(20 CHAR)) AS RENEWAL_TYPE,
CAST(NVL(SCASPC.DESCRIPTION,' ') AS VARCHAR2(250 CHAR)) AS SCASE_SP_DESCRIPTION,
CAST(' ' AS VARCHAR2(250 CHAR)) AS PLINE_SP_DESCRIPTION,
CAST(' ' AS VARCHAR2(10 CHAR)) AS PRODUCT_FAMILY_NAME,
CAST(CUSSMA.SMA_DESCRIPTION AS VARCHAR2(50 CHAR)) AS CUSTOMER_SMA_DESCR,
CAST(SCASMA.SMA_DESCRIPTION AS VARCHAR2(50 CHAR)) AS SCASE_SMA_DESCR,
CAST(' ' AS VARCHAR2(50 CHAR)) AS PLINE_SMA_DESCR,
CAST(SORSCA.PROJECT_PROBABILITY_PCT AS NUMBER(22,7)) AS PROJECT_PROBABILITY_PCT,
CAST(SORSCA.KC_PROBABILITY_PCT AS NUMBER(22,7)) AS KC_PROBABILITY_PCT,
CAST(SORSCA.CMII AS NUMBER(22,7)) AS SCASE_CMII,
CAST(0 AS NUMBER(22,7)) AS PLINE_CMII,
CAST(SORSCA.CURRENCY_CODE_TRN AS CHAR(3 CHAR)) AS SCASE_CURRENCY_CODE_TRN,
CAST(SORSCO.VALUE_TRN*NVL(EURRAT.EUR_RATE,0) AS NUMBER(22,7)) AS CREATION_VALUE_EUR,
CAST(SORSCO.VALUE_TRN AS NUMBER(22,7)) AS CREATION_VALUE_TRN,
CAST(SORSCA.VALUE_TRN*NVL(EURRAT.EUR_RATE,0) AS NUMBER(22,7)) AS SCASE_VALUE_EUR,
CAST(SORSCA.VALUE_TRN AS NUMBER(22,7)) AS SCASE_VALUE_TRN,
CAST(0 AS NUMBER(22,7)) AS PLINE_VALUE_EUR,
CAST(0 AS NUMBER(22,7)) AS PLINE_VALUE_TRN,
CAST('0' AS VARCHAR2(50 CHAR)) AS PLINE_NUMBER,
CAST(0 AS NUMBER(22,7)) AS PLINE_CAPACITY,
CAST(0 AS NUMBER(22,7)) AS PLINE_SPAN,
CAST(0 AS NUMBER(22,7)) AS PLINE_QTY,
CASE
  WHEN SORSAT.CATEGORY='Alpha' THEN CAST(SORSCA.PROJECT_DESC AS VARCHAR2(100 CHAR))
  WHEN SORBRA.CATEGORY='Alpha' THEN CAST(SORSCA.PROJECT_DESC AS VARCHAR2(100 CHAR))
  WHEN SORDIS.CATEGORY='Alpha' THEN CAST(SORSCA.PROJECT_DESC AS VARCHAR2(100 CHAR))
  WHEN SORBUS.CATEGORY='Alpha' THEN CAST(SORSCA.PROJECT_DESC AS VARCHAR2(100 CHAR))
  ELSE 'N/A'
END  AS PROJECT_DESC,
CAST(SORSCA.NAME AS VARCHAR2(100 CHAR)) AS SCASE_NAME,
CAST(NVL(SORSCA.DESCRIPTION,' ') AS VARCHAR2(2000 CHAR)) AS SCASE_DESCRIPTION,
CAST(NVL(SORSCA.COMMENTS,' ') AS VARCHAR2(250 CHAR)) AS SCASE_COMMENTS,
CAST(' ' AS VARCHAR2(250 CHAR)) AS PLINE_DESCRIPTION,
CAST(SORHIP.SRCSYS_PRIMARY_ID AS VARCHAR2(50 CHAR)) AS HIS_OWNER_ROW_ID,
CAST(COMHSR.TYPE AS VARCHAR2(20 CHAR)) AS HIS_OWNER_SALES_ROLE,
CAST(SORCRP.SRCSYS_PRIMARY_ID AS VARCHAR2(50 CHAR)) AS CREATOR_ROW_ID,
CAST(NVL(CUSSSG.ATTR_STRING,'Unknown') AS VARCHAR2(20 CHAR)) AS HIS_CUSTOMER_SEGMENT,
CAST(NVL(CUSREL.ATTR_STRING,'Unknown') AS VARCHAR2(20 CHAR)) AS HIS_CUSTOMER_RELATION,
CAST(NVL(SORCMP.SRCSYS_PRIMARY_ID,'0') AS VARCHAR2(50 CHAR)) AS CAMPAIGN_ID,
CAST(NVL(CSMPSTS.ATTR_STRING,'Unknown') AS VARCHAR2(20 CHAR)) AS CAMPAIGN_STATUS,
CAST(NVL(COMORG.BRAND_CODE,'0') AS VARCHAR2(50 CHAR)) AS BRAND_CODE,
CAST(CASE WHEN SORSCA.LIST_PRICE_TOTAL_TRN=0 THEN 0 ELSE ((SORSCA.LIST_PRICE_TOTAL_TRN-SORSCA.VALUE_TRN)/SORSCA.LIST_PRICE_TOTAL_TRN*100) END AS NUMBER(22,7)) AS DISCOUNT_PCT,
CAST(PREKEY.SPLIT_WEEK_FLAG as NUMBER(38,0)) AS SPLIT_WEEK_FLAG,
CAST(CASE
  WHEN SCASTA.STATUS='Active' THEN SCASTG.STAGE_NAME
  WHEN SCASTA.STATUS='Won Offer' THEN SCASTG.STAGE_NAME
  ELSE SCASTA.STATUS
END AS VARCHAR2(50 CHAR)) AS SCASE_DERIVED_STATUS,
CAST(SORSCA.COMPETITOR_PRICE_TRN AS NUMBER(22,7)) AS COMPETITOR_PRICE_TRN,
CAST(SORSCA.COMPETITOR_PRICE_EUR AS NUMBER(22,7)) AS COMPETITOR_PRICE_EUR,
CAST(CASE
  WHEN SORSAT.MDM_KC_ORGANIZATION_KEY<>0 THEN SORSAT.INACTIVE_FLAG
  WHEN SORBRA.MDM_KC_ORGANIZATION_KEY<>0 THEN SORBRA.INACTIVE_FLAG
  WHEN SORDIS.MDM_KC_ORGANIZATION_KEY<>0 THEN SORDIS.INACTIVE_FLAG
  WHEN SORBUS.MDM_KC_ORGANIZATION_KEY<>0 THEN SORBUS.INACTIVE_FLAG
  ELSE 0
END AS NUMBER(38,0)) AS ORGANIZATION_INACT_FLAG,
CAST(NVL(COMDIV.INACTIVE_FLAG,0) AS NUMBER(38,0)) AS DIVISION_INACT_FLAG
-- Keys for this resolution
FROM
SALES.SL_SALES_CASE_TMPS_W PREKEY
-- The sales case
JOIN
SOR.SALES_CASE SORSCA ON
SORSCA.SALES_CASE_KEY=PREKEY.SALES_CASE_KEY
-- The original sales case
JOIN
SOR.SALES_CASE SORSCO ON
SORSCO.SALES_CASE_KEY=PREKEY.ORIGINAL_CASE_KEY
-- Current sales case
JOIN
SOR.SALES_CASE SORSCC ON
SORSCC.SALES_CASE_KEY=PREKEY.CURRENT_CASE_KEY
-- Division
JOIN
COMMON.DIVISION COMDIV ON
COMDIV.DIVISION_KEY=SORSCA.DIVISION_KEY
-- Legal company
JOIN
SOR.MDM_KC_ORGANIZATION SORLEG ON
SORLEG.MDM_KC_ORGANIZATION_KEY=SORSCA.LEGAL_COMPANY_KEY
-- Business organization
JOIN
SOR.MDM_KC_ORGANIZATION SORBUS ON
SORBUS.MDM_KC_ORGANIZATION_KEY=SORSCA.BUSINESS_ORGANIZATION_KEY
-- District organization
JOIN
SOR.MDM_KC_ORGANIZATION SORDIS ON
SORDIS.MDM_KC_ORGANIZATION_KEY=SORSCA.DISTRICT_KEY
-- Branch organization
JOIN
SOR.MDM_KC_ORGANIZATION SORBRA ON
SORBRA.MDM_KC_ORGANIZATION_KEY=SORSCA.BRANCH_KEY
-- Satellite organization
JOIN
SOR.MDM_KC_ORGANIZATION SORSAT ON
SORSAT.MDM_KC_ORGANIZATION_KEY=SORSCA.SATELLITE_KEY
-- Common organization for brand
LEFT JOIN
COMMON.ORGANIZATION COMORG ON
COMORG.ORGANIZATION_CODE=(CASE
  WHEN SORSAT.MDM_KC_ORGANIZATION_KEY<>0 THEN SORSAT.MDM_KC_ORGANIZATION_ID
  WHEN SORBRA.MDM_KC_ORGANIZATION_KEY<>0 THEN SORBRA.MDM_KC_ORGANIZATION_ID
  WHEN SORDIS.MDM_KC_ORGANIZATION_KEY<>0 THEN SORDIS.MDM_KC_ORGANIZATION_ID
  WHEN SORBUS.MDM_KC_ORGANIZATION_KEY<>0 THEN SORBUS.MDM_KC_ORGANIZATION_ID
  ELSE '0'
END)
-- Owner person
JOIN
SOR.PERSON SORPER ON
SORPER.PERSON_KEY=SORSCC.OWNER_PERSON_KEY
-- Owner Person inactive
LEFT OUTER JOIN
SOR.PERSON_ATTRIBUTE ATTPERINA ON
ATTPERINA.PERSON_KEY=SORPER.PERSON_KEY AND
ATTPERINA.ATTR_NAME='Inactive' AND
ATTPERINA.CURRENT_FLAG=1 AND
ATTPERINA.EFF_END_DATE IS NULL
-- Historic Owner person
JOIN
SOR.PERSON SORHIP ON
SORHIP.PERSON_KEY=SORSCA.OWNER_PERSON_KEY
-- Historic Owner Person Sales Role
JOIN
COMMON.TYPE_VALUE COMHSR ON
COMHSR.TYPE_KEY=SORSCA.SALES_ROLE_TYPE_KEY
-- Creator person
JOIN
SOR.PERSON SORCRP ON
SORCRP.PERSON_KEY=SORSCC.CREATOR_PERSON_KEY
-- Campaign
JOIN
SOR.CAMPAIGN SORCMP ON
SORCMP.CAMPAIGN_KEY=SORSCA.CAMPAIGN_KEY
-- Historic campaign status
LEFT OUTER JOIN (
SELECT
  CAMPAIGN_KEY,
  MAX(ATTR_STRING) AS ATTR_STRING,
  TO_NUMBER(TO_CHAR(EFF_START_DATE,'YYYYMMDD')) AS BEGIN_DAY,
  NVL(TO_NUMBER(TO_CHAR(EFF_END_DATE,'YYYYMMDD')),20301231) AS END_DAY
FROM
  SOR.CAMPAIGN_ATTRIBUTE
WHERE
  ATTR_NAME='STATUS'
GROUP BY
  CAMPAIGN_KEY,
  EFF_START_DATE,
  EFF_END_DATE
) CSMPSTS ON
CSMPSTS.CAMPAIGN_KEY=SORSCA.CAMPAIGN_KEY AND
CSMPSTS.BEGIN_DAY<=PREKEY.END_CAL_KEY AND
CSMPSTS.END_DAY>PREKEY.END_CAL_KEY
-- Customer
JOIN
SOR.SUPCUS SORCUS ON
SORCUS.SUPCUS_KEY=CASE
  WHEN SORSCA.SOLD_TO_KEY<>0 THEN SORSCA.SOLD_TO_KEY
  WHEN SORSCA.CUSTOMER_KEY<>0 THEN SORSCA.CUSTOMER_KEY
  ELSE 0
END
-- Historic customer segment
LEFT OUTER JOIN (
SELECT
  SUPCUS_KEY,
  MAX(ATTR_STRING) AS ATTR_STRING,
  TO_NUMBER(TO_CHAR(EFF_START_DATE,'YYYYMMDD')) AS BEGIN_DAY,
  NVL(TO_NUMBER(TO_CHAR(EFF_END_DATE,'YYYYMMDD')),20301231) AS END_DAY
FROM
  SOR.SUPCUS_ATTRIBUTE
WHERE
  ATTR_NAME='Customer Segment'
GROUP BY
  SUPCUS_KEY,
  EFF_START_DATE,
  EFF_END_DATE
) CUSSSG ON
CUSSSG.SUPCUS_KEY=SORSCA.CUSTOMER_KEY AND
CUSSSG.BEGIN_DAY<=PREKEY.END_CAL_KEY AND
CUSSSG.END_DAY>PREKEY.END_CAL_KEY
-- Historic customer relation
LEFT OUTER JOIN (
SELECT
  SUPCUS_KEY,
  MAX(ATTR_STRING) AS ATTR_STRING,
  TO_NUMBER(TO_CHAR(EFF_START_DATE,'YYYYMMDD')) AS BEGIN_DAY,
  NVL(TO_NUMBER(TO_CHAR(EFF_END_DATE,'YYYYMMDD')),20301231) AS END_DAY
FROM
  SOR.SUPCUS_ATTRIBUTE
WHERE
  ATTR_NAME='Relationship'
GROUP BY
  SUPCUS_KEY,
  EFF_START_DATE,
  EFF_END_DATE
) CUSREL ON
CUSREL.SUPCUS_KEY=SORSCA.CUSTOMER_KEY AND
CUSREL.BEGIN_DAY<=PREKEY.END_CAL_KEY AND
CUSREL.END_DAY>PREKEY.END_CAL_KEY
-- Competitor
JOIN
SOR.SUPCUS SORCPT ON
SORCPT.SUPCUS_KEY=SORSCA.COMPETITOR_KEY
-- Destination country
JOIN
COMMON.COUNTRY DESCTY ON
DESCTY.COUNTRY_KEY=SORSCA.DESTINATION_COUNTRY_KEY
-- Customer country
JOIN
COMMON.COUNTRY CUSCTY ON
CUSCTY.COUNTRY_KEY=SORCUS.COUNTRY_KEY
-- Sales case SP
JOIN
COMMON.SP SCASPC ON
SCASPC.SP_KEY=SORSCA.SP_KEY
-- Customer SMA
JOIN
COMMON.SMA CUSSMA ON
CUSSMA.SMA_KEY=SORSCA.SMA_KEY
-- Sales case SMA
JOIN
COMMON.SMA SCASMA ON
SCASMA.SMA_KEY=SORSCA.SALES_CASE_SMA_KEY
-- Sales case status
JOIN
COMMON.STATUS SCASTA ON
SCASTA.STATUS_KEY=SORSCA.OFFER_STATUS_KEY
-- Sales case original status
JOIN
COMMON.STATUS SCOSTA ON
SCOSTA.STATUS_KEY=SORSCO.OFFER_STATUS_KEY
-- Stage status
JOIN
COMMON.STATUS STGSTA ON
STGSTA.STATUS_KEY=SORSCA.STAGE_STATUS_KEY
-- Stage status original
JOIN
COMMON.STATUS STGOST ON
STGOST.STATUS_KEY=SORSCO.STAGE_STATUS_KEY
-- Stage
JOIN
COMMON.STAGE SCASTG ON
SCASTG.STAGE_KEY=SORSCA.STAGE_KEY
-- Original Stage
JOIN
COMMON.STAGE SCOSTG ON
SCOSTG.STAGE_KEY=SORSCO.STAGE_KEY
-- Phase
JOIN
COMMON.PHASE SCAPHA ON
SCAPHA.PHASE_KEY=SORSCA.PHASE_KEY
-- Reason
JOIN
COMMON.REASON SCAREA ON
SCAREA.REASON_KEY=SORSCA.REASON_KEY
-- Leas source
JOIN
COMMON.LEAD_SOURCE SCALSO ON
SCALSO.LEAD_SOURCE_KEY=SORSCA.LEAD_SOURCE_KEY
-- Sales case type
JOIN
COMMON.TYPE_VALUE SCATYP ON
SCATYP.TYPE_KEY=SORSCA.OFFER_TYPE_KEY
-- Renewal type
JOIN
COMMON.TYPE_VALUE RNWTYP ON
RNWTYP.TYPE_KEY=SORSCA.RENEWAL_TYPE_KEY
-- Euro rate
LEFT OUTER JOIN
COMMON.CURRENCY_RATE EURRAT ON
EURRAT.CALENDAR_DATE_KEY=PREKEY.BEG_CAL_KEY AND
EURRAT.CURRENCY_CODE=SORSCA.CURRENCY_CODE_TRN AND
EURRAT.RATE_TYPE='STANDARD'
-- First date when stage is offer
LEFT OUTER JOIN (
SELECT
  MIN(CASE
   WHEN SCA.OFFER_UPDATE_CAL_KEY>0 THEN OFFER_UPDATE_CAL_KEY
   WHEN SCA.SRCSYS_UPDATE_CAL_KEY>0 THEN SRCSYS_UPDATE_CAL_KEY
   ELSE TO_NUMBER(TO_CHAR(SCA.UPDATED_DATE,'YYYYMMDD'))
  END) AS STAGE_OFFER_CAL_KEY,
  SCA.SRCSYS_PRIMARY_ID AS SALES_CASE_ID
FROM
  SOR.SALES_CASE SCA
JOIN
  COMMON.STAGE STG ON
  STG.STAGE_KEY=SCA.STAGE_KEY AND
  STG.STAGE_NAME='05 - Offer'
GROUP BY
  SCA.SRCSYS_PRIMARY_ID
) OFFDAY ON
OFFDAY.SALES_CASE_ID=SORSCA.SRCSYS_PRIMARY_ID AND
OFFDAY.STAGE_OFFER_CAL_KEY<=PREKEY.END_CAL_KEY
-- First date when stage is hot offer
LEFT OUTER JOIN (
SELECT
  MIN(CASE
   WHEN SCA.OFFER_UPDATE_CAL_KEY>0 THEN OFFER_UPDATE_CAL_KEY
   WHEN SCA.SRCSYS_UPDATE_CAL_KEY>0 THEN SRCSYS_UPDATE_CAL_KEY
   ELSE TO_NUMBER(TO_CHAR(SCA.UPDATED_DATE,'YYYYMMDD'))
 END) AS STAGE_HOT_OFFER_CAL_KEY,
  SCA.SRCSYS_PRIMARY_ID AS SALES_CASE_ID
FROM
  SOR.SALES_CASE SCA
JOIN
  COMMON.STAGE STG ON
  STG.STAGE_KEY=SCA.STAGE_KEY AND
  STG.STAGE_NAME='06 - Hot Offer'
GROUP BY
  SCA.SRCSYS_PRIMARY_ID
) HOTDAY ON
HOTDAY.SALES_CASE_ID=SORSCA.SRCSYS_PRIMARY_ID AND
HOTDAY.STAGE_HOT_OFFER_CAL_KEY<=PREKEY.END_CAL_KEY
WHERE
-- Remove alternative sales cases
(
  SORSCA.GROUP_PROJECT_ID = SORSCA.SRCSYS_PRIMARY_ID OR --Changed the logic of alternative project sales case due to change in Siebel logic (DW-3170)
  (SORSCA.GROUP_PROJECT_ID IS NULL AND SORSCA.PRIMARY_GROUP_PROJECT_ID IS NULL) 
 )
-- Remove reopened cases
--AND SORSCA.REOPENED_FLAG=0
---Query Ends
复制

'\ _(ツ)_/''

说真的。

这是一个巨大的问题，有很多事情要做。我们不知道:

-它回答了什么问题
-数据的业务含义
-表之间的确切关系是什么 (1:1？1:M？)
-等等。

如果你不知道如何通过访问DDL、功能需求、业务知识等来合理化。，我们有什么机会？

例如，我不知道为什么查询有很多这样的案例语句:

case
  when sorsat.category = 'Alpha'   then 1
  when sorbra.category = 'Alpha'   then 1
  when sordis.category = 'Alpha'   then 1
  when sorbus.category = 'Alpha'   then 1
  else 0
end
复制

不知道你想做什么，我们怎么能提供有效的帮助？

所以我们可以帮助你，请:

-把问题分解成更小的块。把你坚持的部分给我们。把这个尽可能小
-给出查询应该做什么的整体书面解释
-为我们提供完整的测试用例。其中包括
-DDL (又名创建表)
-样本数据 (又名插入)
-基于这些的预期产出。理想情况下，有几个不同的例子

如果你能缩小这个问题，并带着上面列出的所有信息回来，我们会看看我们能做些什么来帮助你。