0

I have been trying to fine tune a SQL Query that takes 1.5 Hrs to process approx 4,000 error records. The run time increases along with the number of rows.

I figured out there is one condition in my SQL that is actually causing the issue

AND (DECODE (aia.doc_sequence_value, 
               NULL, DECODE(aia.voucher_num, 
                              NULL, SUBSTR(aia.invoice_num, 1, 10), 
                              aia.voucher_num) , 
               aia.doc_sequence_value) ||'_' ||
     aila.line_number ||'_' ||
     aida.distribution_line_number ||'_' || 
     DECODE (aca.doc_sequence_value, 
               NULL, DECODE(aca.check_voucher_num, 
                              NULL, SUBSTR(aca.check_number, 1, 10), 
                              aca.check_voucher_num) , 
               aca.doc_sequence_value)) = " P_ID"

(P_ID - a value from the first cursor sql) (Note that these are standard Oracle Applications(ERP) Invoice tables)

P_ID column is from the staging table that is derived the same way as above derivation and compared here again in the second SQL to get the latest data for that record. (Basically reprocessing the error records, the value of P_ID is something like "999703_1_1_9995248" )

Q1) Can I create a function based index on the whole left side derivation? If so what is the syntax. Q2) Would it be okay or against the oracle standard rules, to create a function based index on standard Oracle tables? (Not creating directly on the table itself) Q3) If NOT what is the best approach to solve this issue?

Improve this question
3
  • 1
    Have you run an explain plan and a trace on the query first? There may be other ways to structure your query. Also, be aware that stating, "Kind of Urgent" is not going to get you some sort of "priority help." Commented May 6, 2015 at 19:11
  • What is " P_ID"? A VARCHAR2 looks typically like ' P_ID'. In addition: how should your expression ever be equal to ' P_ID', if you concatenate three _? A function based index might help to improve the performance .... but some more information could help to find the best approach :) Commented May 6, 2015 at 19:18
  • Thank you OldProgrammer, ! I have the explain plan, but I do know the exact SQL condition (as above) thats taking time, so I am guessing I need to work around a solution : Either to create new columns with above ids to compare and fetch the unique records or something else.. Commented May 6, 2015 at 19:52

2 Answers 2

Reset to default
1

Briefly, no you can't place a function-based index on that expression, because the input values are derived from four different tables (or table aliases).

What you might look into is a materialised view, but that's a big and potentially difficult to solve a single query optimisation problem with.

You might investigate decomposing that string "999703_1_1_9995248" and applying the relevant parts to the separate expressions:

 DECODE(aia.doc_sequence_value, 
           NULL,
           DECODE(aia.voucher_num, 
                NULL, SUBSTR(aia.invoice_num, 1, 10), 
                aia.voucher_num) , 
           aia.doc_sequence_value)  = '999703' and
 aila.line_number                   = '1'      and
 aida.distribution_line_number      = '1'      and
 DECODE (aca.doc_sequence_value, 
           NULL,
           DECODE(aca.check_voucher_num, 
                NULL, SUBSTR(aca.check_number, 1, 10), 
                aca.check_voucher_num) , 
           aca.doc_sequence_value)) = '9995248'

Then you can use indexes on the expressions and columns.

You could separate the four components of the P_ID value using regular expressions, or a combination of InStr() and SubStr()

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks much David, very good thinking, this is helpful, but isn't it against Oracle standards to create function based indexes directly on the base tables, I guess Oracle wouldnt support it, please let me know.
Well you can create them against the base table, and technically it will work. Or you can query a view that you're placed over the base table with the expression as a new column in the view, and be sure that your index expression matches that expression. Or add a virtual column and index that. If you can't do the latter then try one of the others. there's not a problem with Oracle support, more a matter of making it easy to specify exactly the correct expression in the query.
1

Ad 1) Based on the SQL you've posted, you cannot create function based index on that. The reason is that function based indexes must be:

  1. Deterministic - i.e. the function used in index definition has to always return the same result for given input arguments, and
  2. Can only use columns from the table the index is created for. In your case - based on aliases you're using - you have four tables (aia, aila, aida, aca).

Req #2 makes it impossible to build a functional index for that expression.

Improve this answer

1 Comment

Thank you for clarifying npe ! Very helpful info !

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.