# Oracle SQL Code - Multiple Joins used

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,
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
FULL OUTER JOIN
LEFT JOIN
PO_DISTRIBUTIONS_ALL PDA
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

• it would be nice to see the db creation script as well, as it would answer questions as: are all the columns you are joining on indexed? are the columns you are joining on only integers? also its very hard to make a suggestion without the whole picture. – baot Jun 19 '18 at 16:32
• Apologies for posting an unhelpful question. I'm quite new to stackoverflow. Your answer prompted me to do a bit more digging though to understand what you mean and none of the above has indexed tables/columns (but it seems like that would be a useful thing to do to aid in performance) and also the columns aren't integers only. They consist of integer, varchar, string and datetime. – Matt Williams Jun 19 '18 at 21:25
• so first index on everything that you join on, that will give you quite the speedboost, some reading on indexes stackoverflow.com/questions/107132/…. in my battles with speed in sql i have found that joining on integers instead of strings (likes) is a lot faster. oracle should also have some query analysis tool that you could use to futher understand where the problems in the query lie. maybe my first comment was a bit harsh, if so: sorry for that (english isnt my first languish so it can be hard at time to set the right tone especially in text). – baot Jun 19 '18 at 23:11
• It's fine don't worry. You've helped none the less. I'll look to read up on Indexes and whether I can join on integers. I need to pull back data that would be in a string such as names and dates but not sure if I can join on an integer then have the output as a different format. Thanks again – Matt Williams Jun 20 '18 at 6:36
• Indexes can not be added to what looks like a FUSION environment. What you are looking for is beyond impossible. – pee2pee Jun 20 '18 at 20:51

SELECT DISTINCT
. Is this really necessary? This is a performance killer. Perhaps duplicates can only occur between certain tables. If that were the case, put the distinct as deep as you can in query, even if this means adding additional nested queries.