I'm currently working on some SQL code within Oracle Fusion BIP but it's not very optimised as it's using multiple LEFT,RIGHT and FULL OUTER joins to link tables together.
Unfortunately this is making the query timeout as there's roughly 80k lines and this will be the norm over a rolling 12 month period give or take a few thousand.
Is there any tips on how to optimise the below code to run quicker when ran.
SELECT DISTINCT
AID.INVOICE_ID,
LINES.AMOUNT,
AID.PERIOD_NAME,
GCC.SEGMENT1 AS Organisation,
GCC.SEGMENT2 AS ServiceArea,
GCC.SEGMENT3 AS AccountCode,
GCC.SEGMENT4 AS SubAccount,
INV.INVOICE_NUM,
INV.CREATION_DATE,
PO.SEGMENT1 as PO_Number,
SUP.VENDOR_NAME AS Supplier_Name,
--AID.LINE_TYPE_LOOKUP_CODE,
LINES.LINE_NUMBER,
LINES.DESCRIPTION,
SUP.SEGMENT1 as Supplier_Number,
FVV1.DESCRIPTION As OrganisationDesc,
FVV2.DESCRIPTION As ServiceAreaDesc,
FVV3.DESCRIPTION As AccountCodeDesc,
FVV4.DESCRIPTION As SubAccountDesc
FROM
AP_INVOICES_All INV
INNER JOIN
AP_INVOICE_LINES_ALL LINES
ON INV.INVOICE_ID = LINES.INVOICE_ID
INNER JOIN
AP_INVOICE_DISTRIBUTIONS_ALL AID
ON INV.INVOICE_ID = AID.INVOICE_ID
--LEFT JOIN AP_INVOICE_LINES_ALL LINES
--ON PO_HEADERS_ALL.PO_HEADER_ID = LINES.PO_HEADER_ID
FULL OUTER JOIN
PO_HEADERS_ALL PO
ON LINES.PO_HEADER_ID = PO.PO_HEADER_ID
LEFT JOIN
PO_DISTRIBUTIONS_ALL PDA
ON PO.PO_HEADER_ID = PDA.PO_HEADER_ID
INNER JOIN
GL_CODE_COMBINATIONS GCC
ON GCC.CODE_COMBINATION_ID = nvl(PDA.CODE_COMBINATION_ID, AID.DIST_CODE_COMBINATION_ID)
INNER JOIN
POZ_SUPPLIERS_V SUP
ON INV.VENDOR_ID = SUP.VENDOR_ID
LEFT JOIN
FND_VS_VALUES_VL FVV1
ON GCC.SEGMENT1 = FVV1.VALUE
LEFT JOIN
FND_VS_VALUES_VL FVV2
ON GCC.SEGMENT2 = FVV2.VALUE
LEFT JOIN
FND_VS_VALUES_VL FVV3
ON GCC.SEGMENT3 = FVV3.VALUE
LEFT JOIN
FND_VS_VALUES_VL FVV4
ON GCC.SEGMENT4 = FVV4.VALUE
WHERE
AID.LINE_TYPE_LOOKUP_CODE NOT IN
(
'REC_TAX',
'NONREC_TAX'
)
AND LINES.LINE_TYPE_LOOKUP_CODE NOT IN
(
'TAX'
)
AND trim(TRANSLATE(FVV3.DESCRIPTION, '0123456789-,.', ' ')) is not null
ORDER BY
AID.INVOICE_ID,
LINES.LINE_NUMBER
