主页 / dba / 问题 / 344579
Accepted
Pranithan T.
Asked: 2025-01-07 18:22:04 +0800 CST

如何调优复杂的Oracle存储过程？

  • 772

我有一个 Oracle 存储过程，它接受一堆参数并且大量执行左外连接，如下所示：

create or replace PROCEDURE "SOME_PROCEDURE" (
    cRECORDSET OUT SYS_REFCURSOR,
    iPHY_YEAR IN SOME_PROCEDURE_TYPE.PHYSICAL_YEAR % TYPE,
    iPHYSICAL_DOC_TYPE IN SOME_PROCEDURE_TYPE.PHYSICAL_DOC_TYPE % TYPE,
    iPHYSICAL_DOC_TYPE_NONSAP IN SOME_PROCEDURE_TYPE.PHYSICAL_DOC_TYPE_NONSAP % TYPE,
    sREFERENCE_NO IN SOME_PROCEDURE_TYPE.REFERENCE_NO % TYPE,
    sPHYSICAL_NO IN SOME_PROCEDURE_TYPE.PHYSICAL_NO % TYPE,
    iLOCATION_ID IN SOME_PROCEDURE_TYPE.LOCATION_ID % TYPE,
    iCONTAINER_ID IN SOME_PROCEDURE_TYPE.CONTAINER_ID % TYPE,
    sCONTAINER_NUMBER IN SOME_PROCEDURE_TYPE.CONTAINER_NUMBER % TYPE,
    dREGISTERED_DATE_FROM IN SOME_PROCEDURE_TYPE.REGISTERED_DATE_FROM % TYPE,
    dREGISTERED_DATE_TO IN SOME_PROCEDURE_TYPE.REGISTERED_DATE_TO % TYPE,
    iPHYSICAL_STATUS_ID IN SOME_PROCEDURE_TYPE.PHYSICAL_STATUS_ID % TYPE,
    sDRAFT_FLAG IN SOME_PROCEDURE_TYPE.DRAFT_FLAG % TYPE,
    sCANCEL_FLAG IN SOME_PROCEDURE_TYPE.CANCEL_FLAG % TYPE,
    sPHYSICAL_REMARK IN SOME_PROCEDURE_TYPE.PHYSICAL_REMARK % TYPE,
    sPHYSICAL_DOC_TITLE IN SOME_PROCEDURE_TYPE.PHYSICAL_DOC_TITLE % TYPE,
    sFI_DOC_NO_FROM IN SOME_PROCEDURE_TYPE.FI_DOC_NO_FROM % TYPE,
    sFI_DOC_NO_TO IN SOME_PROCEDURE_TYPE.FI_DOC_NO_TO % TYPE,
    iCOMPANY_ID IN SOME_PROCEDURE_TYPE.COMPANY_ID % TYPE,
    iFI_DOC_YEAR IN SOME_PROCEDURE_TYPE.FI_DOC_YEAR % TYPE,
    sFI_DOC_TYPE IN SOME_PROCEDURE_TYPE.FI_DOC_TYPE % TYPE,
    sIS_ASSET IN SOME_PROCEDURE_TYPE.IS_ASSET % TYPE,
    sWORKFLOW_STATUS_CODE IN SOME_PROCEDURE_TYPE.WORKFLOW_STATUS_CODE % TYPE,
    sPO_NO IN SOME_PROCEDURE_TYPE.PO_NO % TYPE,
    sVENDOR_NAME IN SOME_PROCEDURE_TYPE.VENDOR_NAME % TYPE,
    sCUSTOMER_NAME IN SOME_PROCEDURE_TYPE.CUSTOMER_NAME % TYPE,
    dPOSTING_DATE_FROM IN SOME_PROCEDURE_TYPE.POSTING_DATE_FROM % TYPE,
    dPOSTING_DATE_TO IN SOME_PROCEDURE_TYPE.POSTING_DATE_TO % TYPE,
    sCLEARING_DOC_NO IN SOME_PROCEDURE_TYPE.CLEARING_DOC_NO % TYPE,
    iCLEARING_DOC_YEAR IN SOME_PROCEDURE_TYPE.CLEARING_DOC_YEAR % TYPE,
    dCLEARING_DOC_DATE IN SOME_PROCEDURE_TYPE.CLEARING_DOC_DATE % TYPE,
    sPAYMENT_BLOCK IN SOME_PROCEDURE_TYPE.PAYMENT_BLOCK % TYPE,
    iAMOUNT_INC_VAT IN SOME_PROCEDURE_TYPE.AMOUNT_INC_VAT % TYPE,
    sFI_DOC_REFNO IN SOME_PROCEDURE_TYPE.FI_DOC_REFNO % TYPE,
    sSEND_TO IN SOME_PROCEDURE_TYPE.SEND_TO % TYPE,
    sSAP_REVERSE_FLAG IN SOME_PROCEDURE_TYPE.SAP_REVERSE_FLAG % TYPE,
    sCLEARING_FLAG IN SOME_PROCEDURE_TYPE.CLEARING_FLAG % TYPE,
    iPAGE_SIZE IN SOME_PROCEDURE_TYPE.PAGE_SIZE % TYPE,
    iPAGE_INDEX IN SOME_PROCEDURE_TYPE.PAGE_INDEX % TYPE,
    iUSERID IN USR_DETAIL.USER_ID % TYPE
) IS
LAST_INDEX NUMBER;
USR_PERM_TYPE GET_USR_PERM_TYPE;
FIRST_INDEX NUMBER;
BEGIN
    LAST_INDEX := iPAGE_SIZE * iPAGE_INDEX;
    FIRST_INDEX := LAST_INDEX - iPAGE_SIZE + 1;
    SELECT FN_GET_USR_PERM(iUSERID) INTO USR_PERM_TYPE FROM DUAL;
    OPEN cRECORDSET FOR 
    
    WITH CTE_PHD_DETAIL AS (
    SELECT
        *
    FROM
        PHD_DETAIL
    WHERE
        USR_PERM_TYPE.VIS_MODE = 'ALL'
        OR (
            USR_PERM_TYPE.VIS_MODE = 'UNT'
            AND (UNIT_ID = USR_PERM_TYPE.USUB_ID)
        )
        OR (
            USR_PERM_TYPE.VIS_MODE = 'SUB'
            AND SUB_UNIT_ID = USR_PERM_TYPE.USUB_ID
        )
)
    
    SELECT
    PHD_ITEMS.RECORD_NUMBER,
    PHD.PHYSICAL_ID,
    PHD.PHYSICAL_DOC_TYPE,
    PHD.PHYSICAL_NO,
    PHD.REGISTERED_DATE,
    PHD.CANCEL_FLAG,
    PHD.DRAFT_FLAG,
    CASE
            WHEN PHD.REQUESTED_NO IS NOT NULL THEN
            PHD.REQUESTED_NO ELSE PHD.TEXT_FILE_NAME
        END REFERENCE_NO,
    PHDS.PHYSICAL_STATUS_ID,
    PHDS.PHYSICAL_STATUS_NAME,
    CNT.CONTAINER_ID,
CASE
    WHEN PHD.PHYSICAL_STATUS_ID = 2 THEN
    NULL ELSE CNT.CONTAINER_NUMBER
    END CONTAINER_NUMBER,
    CNT.CONTAINER_TYPE,
    CNTU.IS_DOC_HOUSE CONTAINER_IS_DOC_HOUSE,
    CNTU.UNIT_ABBR CONTAINER_UNIT_ABBR,
    LOC.LOCATION_ID,
CASE
    WHEN PHD.PHYSICAL_STATUS_ID = 2
    OR PHD.PHYSICAL_STATUS_ID = 12
    OR PHD.PHYSICAL_STATUS_ID = 14 THEN
    (
    SELECT
        SEND_TO
    FROM
        ( SELECT SEND_TO, PHYSICAL_ID FROM PHD_STATUS_LOGS T1 WHERE T1.PHYSICAL_STATUS_ID = 2 ORDER BY CREATE_DATE DESC )
    WHERE
        ROWNUM = 1
        AND PHYSICAL_ID = PHD_ITEMS.PHYSICAL_ID
    ) --DECODE( PHDLG.SENT_TO,NULL , '',PHDLG.SENT_TO )
    WHEN CNT.CONTAINER_TYPE = 1 THEN
CASE
        WHEN CNT.CONTAINER_STATUS_ID = 2 THEN
    CASE
            WHEN CNT.CURRENT_UNIT_ID IS NOT NULL THEN
            CNTU.UNIT_FULL_NAME ELSE CCOM.COMPANY_CODE || ' - ' || CCOM.COMPANY_NAME
        END ELSE LOC.LOCATION_NAME
    END ELSE LOC.LOCATION_NAME -- LOC.LOCATION_NAME
    END LOCATION_NAME,
    FIDS.WORKFLOW_STATUS_NAME,
    FID.FI_DOC_ID,
    FID.FI_DOC_NO,
    FID.FI_DOC_YEAR,
CASE
    WHEN COM_FID.COMPANY_ID IS NOT NULL THEN
    COM_FID.COMPANY_CODE || ' - ' || COM_FID.COMPANY_NAME ELSE
CASE
        WHEN COM_PHD.COMPANY_ID IS NOT NULL THEN
        COM_PHD.COMPANY_CODE || ' - ' || COM_PHD.COMPANY_NAME ELSE ''
    END
    END AS COMPANY_FULL_NAME,
    FID.FI_DOC_REFNO,
CASE
        WHEN FID.FI_DOC_ID IS NULL THEN
        PTEMP.AMOUNT_INC_VAT ELSE FID.AMOUNT_INC_VAT
    END AMOUNT_INC_VAT,
CASE
    WHEN FID.FI_DOC_ID IS NULL THEN
    PTEMP.AMOUNT_CURRENCY ELSE FID.AMOUNT_CURRENCY
    END AMOUNT_CURRENCY,
    FID.IS_ASSET,
CASE
    WHEN PHD_ITEMS.FI_DOC_ID IS NULL THEN
CASE
        WHEN PTEMP.CUSTOMER_CODE IS NOT NULL THEN
        PTCUS.CUSTOMER_FULL_NAME ELSE PTVEN.VENDOR_FULL_NAME
END ELSE
CASE
        WHEN FID.FIRST_ITEMS = 'CUSTOMER' THEN
        FID.CUSTOMER_NAME ELSE FID.VENDOR_NAME
    END
    END AS CUSTOMER_VENDOR_NAME,
    PHD.PHYSICAL_REMARK
FROM
    (
    SELECT
        ROW_NUMBER ( ) over ( ORDER BY PHD.PHYSICAL_NO DESC, FID_ID.FI_DOC_NO, PHDTEMP.PHD_TEMP_DATA_ID DESC ) RECORD_NUMBER,
        PHD.PHYSICAL_ID,
        PHD.PHYSICAL_NO,
        FID_ID.FI_DOC_ID,
        FID_ID.FI_DOC_NO,
        PHDTEMP.PHD_TEMP_DATA_ID
    FROM
        (
        SELECT DISTINCT
            PHD.PHYSICAL_ID,
            PHD.PHYSICAL_NO,
            FID_ID.FI_DOC_ID,
            PHDTEMP.PHD_TEMP_DATA_ID
        FROM
            CTE_PHD_DETAIL PHD
            LEFT JOIN (
            SELECT
                t1.FI_DOC_ID,
                t1.PHYSICAL_ID,
                t2.fi_Doc_no,
                t2.fi_doc_year
            FROM
                PHD_FID_RELATE_DOC t1
                INNER JOIN FID_SAP_DETAIL t2 ON t1.FI_DOC_ID = t2.FI_DOC_ID UNION ALL
            SELECT
                t1.FI_DOC_ID,
                t1.PHYSICAL_ID,
                t2.fi_Doc_no,
                t2.fi_doc_year
            FROM
                FID_SAP_PHYSICAL_HISTORY t1
                INNER JOIN FID_SAP_DETAIL t2 ON t1.FI_DOC_ID = t2.FI_DOC_ID
            ) FID_ID ON FID_ID.PHYSICAL_ID = PHD.PHYSICAL_ID
            LEFT JOIN PHD_TEMP_DATA PHDTEMP ON PHD.PHYSICAL_ID = PHDTEMP.PHYSICAL_ID
            AND FID_ID.FI_DOC_ID
            IS NULL LEFT JOIN PHD_STATUS_LOGS PHDSL ON PHD.PHYSICAL_ID = PHDSL.PHYSICAL_ID
            LEFT JOIN PHD_TEMP_DOCTITLE PHDT ON PHD.PHYSICAL_ID = PHDT.PHYSICAL_ID
            LEFT JOIN CNT_DETAIL CNT ON PHD.CURRENT_CONTAINER_ID = CNT.CONTAINER_ID
            LEFT JOIN VEW_UNT_DETAIL CNTU ON CNT.CURRENT_UNIT_ID = CNTU.UNIT_ID
            LEFT JOIN LOC_DETAIL LOC ON CNT.LOCATION_ID = LOC.LOCATION_ID
            LEFT JOIN COM_DETAIL COM_PHD ON PHD.COMPANY_ID = COM_PHD.COMPANY_ID
            LEFT JOIN FID_SAP_DETAIL FID ON FID_ID.FI_DOC_ID = FID.FI_DOC_ID
            LEFT JOIN COM_DETAIL COM ON FID.COMPANY_CODE = COM.COMPANY_CODE
            LEFT JOIN FID_SAP_CUSTOMER FCUS ON FID.FI_DOC_ID = FCUS.FI_DOC_ID
            LEFT JOIN FID_SAP_VENDOR FVEN ON FID.FI_DOC_ID = FVEN.FI_DOC_ID
            LEFT JOIN FID_SAP_CLEARING_DOC CLR ON FID.FI_DOC_ID = CLR.FI_DOC_ID
            LEFT JOIN FID_SAP_PO PO ON FID.FI_DOC_ID = PO.FI_DOC_ID
            LEFT JOIN FID_SAP_STATUS FIDS ON FID.WORKFLOW_STATUS_CODE = FIDS.WORKFLOW_STATUS_CODE
            LEFT JOIN MAS_VENDOR PTVEN ON PHDTEMP.VENDOR_CODE = PTVEN.VENDOR_CODE
            AND COM_PHD.SERVER_ID = COM_PHD.SERVER_ID
            LEFT JOIN MAS_CUSTOMER PTCUS ON PHDTEMP.CUSTOMER_CODE = PTCUS.CUSTOMER_CODE
            AND PTCUS.SERVER_ID = COM_PHD.SERVER_ID
        WHERE
            PHD.PHYSICAL_NO IS NOT NULL
            AND (
                (
                    (
                        ( PHD.PHYSICAL_DOC_TYPE = 1 AND iPHYSICAL_DOC_TYPE = 1 )
                        OR ( ( PHD.PHYSICAL_DOC_TYPE = 2 OR PHD.PHYSICAL_DOC_TYPE = 4 ) AND iPHYSICAL_DOC_TYPE = 2 )
                    )
                    AND iPHYSICAL_DOC_TYPE_NONSAP IS NULL
                )
                OR ( PHD.PHYSICAL_DOC_TYPE = iPHYSICAL_DOC_TYPE_NONSAP AND iPHYSICAL_DOC_TYPE_NONSAP IS NOT NULL )
            )
            AND (
                sREFERENCE_NO IS NULL
                OR (
                    UPPER( PHD.REQUESTED_NO ) LIKE '%' || UPPER( sREFERENCE_NO ) || '%'
                    OR UPPER( PHD.TEXT_FILE_NAME ) LIKE '%' || UPPER( sREFERENCE_NO ) || '%'
                )
            )
            AND ( sPHYSICAL_NO IS NULL OR ( PHD.PHYSICAL_NO = sPHYSICAL_NO ) )
            AND ( iLOCATION_ID IS NULL OR ( LOC.LOCATION_ID = iLOCATION_ID ) )
            AND ( iPHYSICAL_STATUS_ID IS NULL OR ( PHD.PHYSICAL_STATUS_ID = iPHYSICAL_STATUS_ID ) )
            AND ( iCONTAINER_ID IS NULL OR ( CNT.CONTAINER_ID = iCONTAINER_ID AND CNT.CONTAINER_TYPE = 2 ) )
            AND (
                sCONTAINER_NUMBER IS NULL
                OR ( UPPER( CNT.CONTAINER_NUMBER ) LIKE '%' || UPPER( sCONTAINER_NUMBER ) || '%' AND CNT.CONTAINER_TYPE = 1 )
            )
            AND ( TRUNC( dREGISTERED_DATE_FROM ) IS NULL OR ( TRUNC( PHD.REGISTERED_DATE ) >= TRUNC( dREGISTERED_DATE_FROM ) ) )
            AND ( TRUNC( dREGISTERED_DATE_TO ) IS NULL OR ( TRUNC( PHD.REGISTERED_DATE ) <= TRUNC( dREGISTERED_DATE_TO ) ) )
            AND ( ( PHD.DRAFT_FLAG IS NULL AND sDRAFT_FLAG IS NULL ) OR ( PHD.DRAFT_FLAG = sDRAFT_FLAG ) ) --AND (PHD.CANCEL_FLAG              IS NULL
--OR (PHD.CANCEL_FLAG                = sCANCEL_FLAG))
            AND ( sPHYSICAL_REMARK IS NULL OR ( UPPER( PHD.PHYSICAL_REMARK ) LIKE '%' || UPPER( sPHYSICAL_REMARK ) || '%' ) )
            AND ( sPHYSICAL_DOC_TITLE IS NULL OR ( UPPER( PHDT.PHYSICAL_DOC_TITLE ) LIKE '%' || UPPER( sPHYSICAL_DOC_TITLE ) || '%' ) )
            AND ( iCOMPANY_ID IS NULL OR ( COM.COMPANY_ID = iCOMPANY_ID OR COM_PHD.COMPANY_ID = iCOMPANY_ID ) )
            AND ( iPHY_YEAR IS NULL OR ( PHD.PHYSICAL_YEAR = iPHY_YEAR ) )
            AND ( iFI_DOC_YEAR IS NULL OR ( FID_ID.FI_DOC_YEAR = iFI_DOC_YEAR OR FID_ID.FI_DOC_ID IS NULL ) ) /*    AND (
                sSEND_TO IS NULL
                OR ( PHDSL.SEND_TO LIKE '%' || sSEND_TO || '%' OR LOWER( PHDSL.SEND_TO ) LIKE '%' || sSEND_TO || '%' )
            )*/
            AND (
                sSEND_TO IS NULL
                OR (
                    iPHYSICAL_STATUS_ID = 2
                    AND EXISTS (
                    SELECT
                        PDSENT.SEND_TO
                    FROM
                        ( SELECT SEND_TO, PHYSICAL_ID FROM PHD_STATUS_LOGS T1 WHERE T1.PHYSICAL_STATUS_ID = 2 ORDER BY CREATE_DATE DESC ) PDSENT
                    WHERE
                        ROWNUM = 1
                        AND PDSENT.PHYSICAL_ID = PHD.PHYSICAL_ID
                        AND LOWER( PDSENT.SEND_TO ) LIKE '%' || LOWER( sSEND_TO ) || '%'
                    )
                )
            )
            AND ( sFI_DOC_NO_FROM IS NULL OR ( FID.FI_DOC_NO >= sFI_DOC_NO_FROM ) )
            AND ( sFI_DOC_NO_TO IS NULL OR ( FID.FI_DOC_NO <= sFI_DOC_NO_TO ) )
            AND ( iCOMPANY_ID IS NULL OR ( COM.COMPANY_ID = iCOMPANY_ID OR COM_PHD.COMPANY_ID = iCOMPANY_ID ) )
            AND ( iFI_DOC_YEAR IS NULL OR ( PHD.PHYSICAL_YEAR = iFI_DOC_YEAR OR FID.FI_DOC_YEAR = iFI_DOC_YEAR ) ) --AND ( sFI_DOC_TYPE              IS NULL
--OR ( UPPER(T1.FI_DOC_TYPE)       = UPPER(sFI_DOC_TYPE) ) )
            AND ( sIS_ASSET IS NULL OR ( sIS_ASSET IS NOT NULL AND FID.IS_ASSET IS NOT NULL ) )
            AND ( sWORKFLOW_STATUS_CODE IS NULL OR ( FID.WORKFLOW_STATUS_CODE = sWORKFLOW_STATUS_CODE ) )
            AND ( sPO_NO IS NULL OR ( UPPER( PO.PO_NO ) = UPPER( sPO_NO ) ) )
            AND (
                TRUNC( dPOSTING_DATE_FROM ) IS NULL
                OR ( TRUNC( FID.POSTING_DATE ) >= TRUNC( dPOSTING_DATE_FROM ) OR TRUNC( PHDTEMP.CREATE_DATE ) <= TRUNC( dPOSTING_DATE_TO ) )
            )
            AND (
                TRUNC( dPOSTING_DATE_TO ) IS NULL
                OR ( TRUNC( FID.POSTING_DATE ) <= TRUNC( dPOSTING_DATE_TO ) OR TRUNC( PHDTEMP.CREATE_DATE ) >= TRUNC( dPOSTING_DATE_FROM ) )
            )
            AND ( sCLEARING_DOC_NO IS NULL OR ( UPPER( CLR.CLEARING_DOC_NO ) LIKE '%' || UPPER( sCLEARING_DOC_NO ) || '%' ) )
            AND ( iCLEARING_DOC_YEAR IS NULL OR ( CLR.CLEARING_DOC_YEAR = iCLEARING_DOC_YEAR ) )
            AND ( dCLEARING_DOC_DATE IS NULL OR ( CLR.CLEARING_DATE = dCLEARING_DOC_DATE ) ) --AND ( sPAYMENT_BLOCK      IS NULL
-- OR ( UPPER(FID.PAYMENT_BLOCK) LIKE '%'
--   || UPPER(sPAYMENT_BLOCK)
--  || '%' ) )
            AND ( sFI_DOC_REFNO IS NULL OR ( UPPER( FID.FI_DOC_REFNO ) LIKE '%' || UPPER( sFI_DOC_REFNO ) || '%' ) )
            AND ( ( sSAP_REVERSE_FLAG IS NULL AND ( FID.SAP_REVERSE_FLAG IS NULL OR FID.FI_DOC_ID IS NULL ) ) OR ( sSAP_REVERSE_FLAG IS NOT NULL ) )
            AND ( iAMOUNT_INC_VAT IS NULL OR ( FID.AMOUNT_INC_VAT = iAMOUNT_INC_VAT OR PHDTEMP.AMOUNT_INC_VAT = iAMOUNT_INC_VAT ) )
            AND (
                sCUSTOMER_NAME IS NULL
                OR UPPER( PTCUS.CUSTOMER_FULL_NAME ) LIKE '%' || UPPER( sCUSTOMER_NAME ) || '%'
                OR UPPER( FCUS.CUSTOMER_CODE || ' ' || FCUS.CUSTOMER_NAME ) LIKE '%' || UPPER( sCUSTOMER_NAME ) || '%'
            )
            AND (
                sVENDOR_NAME IS NULL
                OR UPPER( PTVEN.VENDOR_FULL_NAME ) LIKE '%' || UPPER( sVENDOR_NAME ) || '%'
                OR UPPER( FVEN.VENDOR_CODE || ' ' || FVEN.VENDOR_NAME ) LIKE '%' || UPPER( sVENDOR_NAME ) || '%'
            ) --AND FID_ID.FI_DOC_ID IS NULL AND PHDTEMP.PHD_TEMP_DATA_ID IS NOT NULL
        ) PHD
        LEFT JOIN FID_SAP_DETAIL FID_ID ON FID_ID.FI_DOC_ID = PHD.FI_DOC_ID
        LEFT JOIN PHD_TEMP_DATA PHDTEMP ON PHD.PHD_TEMP_DATA_ID = PHDTEMP.PHD_TEMP_DATA_ID
        AND PHD.FI_DOC_ID IS NULL
    ) PHD_ITEMS
    INNER JOIN CTE_PHD_DETAIL PHD ON PHD_ITEMS.PHYSICAL_ID = PHD.PHYSICAL_ID
    LEFT JOIN PHD_STATUS PHDS ON PHD.PHYSICAL_STATUS_ID = PHDS.PHYSICAL_STATUS_ID
    LEFT JOIN CNT_DETAIL CNT ON PHD.CURRENT_CONTAINER_ID = CNT.CONTAINER_ID
    LEFT JOIN VEW_UNT_DETAIL CNTU ON CNT.CURRENT_UNIT_ID = CNTU.UNIT_ID
    LEFT JOIN LOC_DETAIL LOC ON CNT.LOCATION_ID = LOC.LOCATION_ID
    LEFT JOIN COM_DETAIL CCOM ON CNT.CURRENT_COMPANY_ID = CCOM.COMPANY_ID
    LEFT JOIN COM_DETAIL COM_PHD ON PHD.COMPANY_ID = COM_PHD.COMPANY_ID
    LEFT JOIN FID_SAP_DETAIL FID ON PHD_ITEMS.FI_DOC_ID = FID.FI_DOC_ID
    LEFT JOIN FID_SAP_STATUS FIDS ON FID.WORKFLOW_STATUS_CODE = FIDS.WORKFLOW_STATUS_CODE
    LEFT JOIN COM_DETAIL COM_FID ON COM_FID.COMPANY_CODE = FID.COMPANY_CODE
    LEFT JOIN PHD_TEMP_DATA PTEMP ON PHD_ITEMS.PHD_TEMP_DATA_ID = PTEMP.PHD_TEMP_DATA_ID
    LEFT JOIN MAS_VENDOR PTVEN ON PTEMP.VENDOR_CODE = PTVEN.VENDOR_CODE
    AND PHD_ITEMS.FI_DOC_ID IS NULL
    AND PTVEN.SERVER_ID = COM_PHD.SERVER_ID
    LEFT JOIN MAS_CUSTOMER PTCUS ON PTEMP.CUSTOMER_CODE = PTCUS.CUSTOMER_CODE
    AND PHD_ITEMS.FI_DOC_ID IS NULL
    AND PTCUS.SERVER_ID = COM_PHD.SERVER_ID
WHERE
    PHD_ITEMS.RECORD_NUMBER BETWEEN FIRST_INDEX
    AND LAST_INDEX
ORDER BY
    RECORD_NUMBER;
END;

此查询大约需要 1 分 30 秒，然后才会发出约 4000 条记录的查询结果。很难提取此长查询的执行计划。有什么建议或策略，或者我应该做些什么来获取计划？

我认为，我目前有 3 个选择：

动态查询

第一个选项是将过程升级为 ORM 语句，使用一些 if 语句来组成正确的语句，使用内连接而不是左外连接（如果参数为空，则不连接）。乍一看，我觉得这个选项很聪明。但是，我可能花了很长时间才完成这项任务。

物化视图

我想到的一个选项是将左连接子句重写为Materialized View。不幸的是，此存储过程涉及应用程序，因此当与ON COMMIT或结合时，物化视图将频繁更新ON STATEMENT。此选项是否被认为对死锁或性能不佳很危险？

删除一些搜索条件（存储过程参数）

假设我可以删除一些条件，这意味着我可以删除一些左外连接子句。但是，在我与队友讨论后，这个选项不可行。

我该如何解决性能问题，或者我有什么其他策略或选择？欢迎提出任何建议。

oracle

1 个回答

  1. Best Answer

    一些次要的和更普遍的观点。

    而不是

    SELECT ROW_NUMBER () over (...) RECORD_NUMBER
FROM ...
WHERE PHD_ITEMS.RECORD_NUMBER BETWEEN FIRST_INDEX AND LAST_INDEX

    使用row_limiting_clause

    SELECT ..
FROM ...
OFFSET FIRST_INDEX ROWS
FETCH FIRST LAST_INDEX ROWS ONLY

    仔细检查您是否真的需要DISTINCT子查询。

    像这样的条件

    OR UPPER( PTVEN.VENDOR_FULL_NAME ) LIKE '%' || UPPER( sVENDOR_NAME ) || '%'
OR UPPER( FVEN.VENDOR_CODE || ' ' || FVEN.VENDOR_NAME ) LIKE '%' || UPPER( sVENDOR_NAME ) || '%'
OR TRUNC( FID.POSTING_DATE ) >= ...

    减慢查询速度。同时尽量避免UPPER(column_name) = ...和LIKE %...%条件。

    尝试将您的查询写为

    WITH CTE_PHD_DETAIL AS (
  SELECT *
  FROM ...
),
FID_ID AS (
  SELECT ...
),
PHD AS (
  SELECT ...
  FROM FID_ID
     JOIN ...
)
-- Main query
SELECT *
FROM CTE_PHD_DETAIL
   JOIN FID_ID ON ...

    这更容易阅读（在我看来）并且 Oracle 可能采取更好的执行计划。

    像这样的情况

    AND EXISTS (
   SELECT 'x'
   FROM ( SELECT ... WHERE T1.PHYSICAL_STATUS_ID = 2 ORDER BY CREATE_DATE DESC ) PDSENT
   WHERE ROWNUM = 1
)

    没用。跳过WHERE ROWNUM = 1条件和ORDER BY子句。

    使用物化视图可能是一种选择。您可以尝试仅为一组表创建物化视图，例如部分

    LEFT JOIN CNT_DETAIL CNT ON PHD.CURRENT_CONTAINER_ID = CNT.CONTAINER_ID
LEFT JOIN VEW_UNT_DETAIL CNTU ON CNT.CURRENT_UNIT_ID = CNTU.UNIT_ID
LEFT JOIN LOC_DETAIL LOC ON CNT.LOCATION_ID = LOC.LOCATION_ID
LEFT JOIN COM_DETAIL CCOM ON CNT.CURRENT_COMPANY_ID = CCOM.COMPANY_ID
LEFT JOIN COM_DETAIL COM_PHD ON PHD.COMPANY_ID = COM_PHD.COMPANY_ID
LEFT JOIN FID_SAP_DETAIL FID ON PHD_ITEMS.FI_DOC_ID = FID.FI_DOC_ID
LEFT JOIN FID_SAP_STATUS FIDS ON FID.WORKFLOW_STATUS_CODE = FIDS.WORKFLOW_STATUS_CODE
LEFT JOIN MAS_VENDOR PTVEN ON PTEMP.VENDOR_CODE = PTVEN.VENDOR_CODE
LEFT JOIN MAS_CUSTOMER PTCUS ON PTEMP.CUSTOMER_CODE = PTCUS.CUSTOMER_CODE

    在您的查询中出现两次。至少您可以尝试在 CTE（WITH AS (...)子句）中仅选择它一次，然后 Oracle 可以重复使用它，并可能为其创建一个临时临时表。

    可以使用创建物化视图FAST REFRESH，请参阅 https://stackoverflow.com/questions/41465445/what-is-the-difference-between-complete-refresh-and-fast-refresh-in-materialized/41465650#41465650 但它有一些限制

    编写动态 SQL 也可以改善你的查询。

    AND ( iCLEARING_DOC_YEAR IS NULL OR ( CLR.CLEARING_DOC_YEAR = iCLEARING_DOC_YEAR ) )

    可能是

    sqlStr := 'SELECT ... '||CHR(13);
sqlStr := sqlStr || 'FROM ...'||CHR(13);
sqlStr := sqlStr || 'WHERE '||CHR(13);

IF iCLEARING_DOC_YEAR IS NOT NULL THEN
   sqlStr := sqlStr || ' CLR.CLEARING_DOC_YEAR = '||iCLEARING_DOC_YEAR||' AND ';
END IF;
IF iCOMPANY_ID IS NOT NULL THEN
   sqlStr := sqlStr || iCOMPANY_ID ||' IN (COM.COMPANY_ID,COM_PHD.COMPANY_ID) AND ';
   -- yes, you can write it also this way around
END IF;
sqlStr := REGEXP_REPLACE(sqlStr, ' AND $');

    我知道，这是一个 SQL 注入漏洞。优化查询后，您可以查看DBMS_SQL并使用绑定变量使其更安全 - 但要一步一步来！

    And last but not least, check your indexes. Have a look at your execution plan if you see any FULL TABLE SCAN on bigger tables. Consider Function-Based Indexes for conditions on UPPER(...) or TRUNC(...) columns.

    • 2

相关问题