核心逻辑：不等值关联转等值关联

使用场景：本案例适合满足以下条件的场景

1. 大表A不等值关联小表B
2. B的等值关联字段为主键

1.【原始语句】

SELECT
    T.CREATE_INVOICE_USER,
    T.PERIOD_ID,
    T.AP_INVOICE_ID,
    T.AP_INVOICE_NUM,
    T.AP_BATCH_NAME,
    EMP1.EMPLOYEE_NO,
    EMP1.EMPLOYEE_NAME
FROM DWACTDI.DWR_AP_GLOBAL_INVOICE_DETAIL_F_I T
LEFT JOIN DWRDIM_DW1.DWR_DIM_EMPLOYEE_D EMP1 ON (EMP1.SCD_ACTIVE_IND = 1 AND(T.CREATE_INVOICE_USER = EMP1.EMPLOYEE_NO OR SUBSTR(T.CREATE_INVOICE_USER, 2) = EMP1.EMPLOYEE_NO))

2.【性能分析】

原始语句执行超时(超过1h)，执行计划如下。可以看到执行语句存在大表NestLoop操作

分析发现表dwrdim_dw1.dwr_dim_employee_d是维度表，且关联列employee_no是主键

3【优化改写】

SELECT
    T.CREATE_INVOICE_USER,
    T.PERIOD_ID,
    T.AP_INVOICE_ID,
    T.AP_INVOICE_NUM,
    T.AP_BATCH_NAME,
    nvl(EMP1_0.EMPLOYEE_NO, EMP1_1.EMPLOYEE_NO)  AS EMPLOYEE_NO,
    nvl(EMP1_0.EMPLOYEE_NAME, EMP1_1.EMPLOYEE_NAME) AS ERP_ACCOUNTANT_ENAME
FROM DWACTDI.DWR_AP_GLOBAL_INVOICE_DETAIL_F_I T
LEFT JOIN DWRDIM_DW1.DWR_DIM_EMPLOYEE_D EMP1_0 ON (EMP1_0.SCD_ACTIVE_IND = 1 AND(T.CREATE_INVOICE_USER = EMP1_0.EMPLOYEE_NO))
LEFT JOIN DWRDIM_DW1.DWR_DIM_EMPLOYEE_D EMP1_1 ON (EMP1_1.SCD_ACTIVE_IND = 1 AND(SUBSTR(T.CREATE_INVOICE_USER, 2) = EMP1_1.EMPLOYEE_NO))

改写后执行信息如下