Início / dba / Perguntas / 344579
Accepted
Pranithan T.
Asked: 2025-01-07 18:22:04 +0800 CST

Como ajustar o procedimento armazenado complexo do Oracle?

  • 772

Tenho um procedimento armazenado Oracle que recebe vários parâmetros e faz muita junção externa esquerda, assim:

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;

Esta consulta leva cerca de 1:30 minutos antes de emitir o resultado da consulta de ~4000 registros. É difícil extrair o plano de execução para esta consulta longa. Que sugestão ou estratégia ou devo fazer algo para obter o plano?

Na minha opinião, atualmente tenho 3 opções:

Consulta dinâmica

A primeira opção é escalar o procedimento para ser uma declaração ORM com alguma declaração if para compor a declaração adequada com junção interna em vez de junção externa esquerda (se o parâmetro for nulo, sem junção). Acho que essa opção é inteligente quando penso sobre isso à primeira vista. No entanto, pode levar muito tempo para eu realizar a tarefa.

Visão materializada

Uma das opções que penso é puxar as cláusulas left join para serem reescritas como Materialized View. Infelizmente, esse procedimento de armazenamento está envolvido na aplicação, então a Materialized View será atualizada frequentemente quando combinada com ON COMMITou ON STATEMENT. Essa opção é considerada perigosa para deadlock ou desempenho ruim?

Remova alguns critérios de pesquisa (parâmetros de procedimento armazenado)

Suponha que eu possa remover alguns critérios, isso significa que posso remover algumas cláusulas left outer join. No entanto, essa opção não é possível depois que eu discuto com o colega de equipe.

Como devo fazer para lidar com o problema de desempenho ou quais outras estratégias ou opções eu tenho? Quaisquer conselhos são bem-vindos.

oracle

1 respostas

  1. Best Answer

    Alguns pontos menores e mais gerais.

    Em vez de

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

    Use a cláusula row_limiting_clause

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

    Verifique cuidadosamente se você realmente precisa DISTINCTna subconsulta.

    Condições como esta

    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 ) >= ...

    desacelere sua consulta. Tente também evitar condições UPPER(column_name) = ...e LIKE %...%.

    Tente escrever sua consulta como

    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 ...

    Isso é mais fácil de ler (na minha opinião) e a Oracle pode adotar um plano de execução melhor.

    Condição como esta

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

    é inútil. Pule WHERE ROWNUM = 1a condição e a ORDER BYcláusula.

    Usar Materialized Views pode ser uma opção. Você pode tentar criar Materialized Views apenas para um sup-set de tabelas, por exemplo, a parte

    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

    aparece duas vezes na sua consulta. Pelo menos você pode tentar selecioná-lo apenas uma vez em um CTE (a WITH AS (...)cláusula), então o Oracle pode reutilizá-lo e pode criar uma tabela temporária ad-hoc para ele.

    Visualizações materializadas podem ser criadas com FAST REFRESH, veja https://stackoverflow.com/questions/41465445/what-is-the-difference-between-complete-refresh-and-fast-refresh-in-materialized/41465650#41465650 mas tem algumas limitações

    Compor um SQL dinâmico também pode melhorar sua consulta. Em vez de

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

    poderia ser

    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 $');

    Eu sei, essa é uma vulnerabilidade para SQL-Injection. Depois de otimizar a consulta, você pode dar uma olhada em DBMS_SQL e usar variáveis ​​bind para torná-la mais segura - mas faça isso passo a passo!

    E por último, mas não menos importante, verifique seus índices. Dê uma olhada em seu plano de execução se você vir qualquer FULL TABLE SCAN em tabelas maiores. Considere Índices Baseados em Função para condições em UPPER(...)ou TRUNC(...)colunas.

    • 2

relate perguntas