CODEWITHSUNDEEP
sqloracle-databaseoracle10g
user7403515
user7403515

Reputation:

Query takes too much time for execution

I am using Oracle 10g. The following query takes too much time:

  SELECT invno, invdate, ccode
    FROM acct
   WHERE     invno IS NOT NULL
         AND invdate > '01-Feb-2018'
         AND invno LIKE '%' || :ppassingmn.dept || '%'
         AND invno NOT IN (SELECT DISTINCT (vdescript)
                             FROM genldgr
                            WHERE     vchrno LIKE 'IV%'
                                  AND trandate > '01-Feb-2018'
                                  AND vdescript LIKE
                                         '%' || :ppassingmn.dept || '%')
ORDER BY SUBSTR (invno, 12, 15);

Please optimize it.

Upvotes: 0

Views: 51

Answers (1)

Littlefoot
Littlefoot

Reputation: 142713

You didn't provide much info. Query itself is pretty much useless (as if you called the mechanic and said "my blue car is slow. Why?").

Anyway, a few hints; maybe they'll help.

  • '01-Feb-2018' is a string. If INVDATE is a DATE datatype column, you're forcing Oracle to perform implicit conversion between VARCHAR2 and DATE. Use a date literal instead, e.g. and invdate > date '2018-02-01'. The same goes for TRANDATE.
  • LIKE is slow. You use it twice. See if you can rewrite it to invno = :passingmn.dept. By the way, what is :passingmn.dept? Looks like a variable, but - what is that dot doing here? The same goes for VDESCRIPT.
  • DISTINCT requires you to select all rows, and then remove duplicates. It seems that you don't really care about it, so - remove DISTINCT.
  • As INVNO has to look like some parameter value, then it isn't NULL so you can probably remove invno is not null condition.
  • ORDER BY also affects performance. Moreover, it selects a substring. Do you need it? If not, remove it
  • are columns involved in the WHERE clause indexed? If not, do so

Finally, explain plan would help. Without it, people are partially blind.

Upvotes: 1

Related Questions