1
\$\begingroup\$

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
\$\endgroup\$
  • 1
    \$\begingroup\$ 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. \$\endgroup\$ – baot Jun 19 '18 at 16:32
  • 1
    \$\begingroup\$ 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. \$\endgroup\$ – Matt Williams Jun 19 '18 at 21:25
  • \$\begingroup\$ 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). \$\endgroup\$ – baot Jun 19 '18 at 23:11
  • 1
    \$\begingroup\$ 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 \$\endgroup\$ – Matt Williams Jun 20 '18 at 6:36
  • \$\begingroup\$ Indexes can not be added to what looks like a FUSION environment. What you are looking for is beyond impossible. \$\endgroup\$ – pee2pee Jun 20 '18 at 20:51
1
\$\begingroup\$

You open with

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.

| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.