Oracle SQL Optimization: SQL Query taking very long time

13,198

Solution 1

The solution is simple.

Create an index on (code, licensing_no) and an index on (l_code, licensing_no) to fetch records faster. Do the 'beautification' piece later in the application or simply in external wrapper like this:

SELECT    'LRS-TECH  1'
       || RPAD (code, 7)
       || RPAD ('APPTYPE', 30)
       || RPAD (licensing_no, 30)
       || RPAD (' ', 300) AS RECORD
  FROM (SELECT DISTINCT code, licensing_no
                   FROM apps
                  WHERE l_code = '1000' AND licensing_no IS NOT NULL)

Solution 2

You cannot diagnose this problem unless you know how the query is being optimised.

Try this:

explain plan for SELECT DISTINCT 
'LRS-TECH  1' || rpad(code,7) || rpad('APPTYPE',30) || 
 rpad(licensing_no,30) || rpad(' ',300) AS RECORD 
FROM APPS
WHERE L_code = '1000' AND licensing_no IS NOT NULL
/

select * from table(dbms_xplan.display)
/

Now, try this also ... it will help you detect a statistics problem:

explain plan for SELECT /*+ dynamic_sampling(4) */ DISTINCT 
'LRS-TECH  1' || rpad(code,7) || rpad('APPTYPE',30) || 
 rpad(licensing_no,30) || rpad(' ',300) AS RECORD 
FROM APPS
WHERE L_code = '1000' AND licensing_no IS NOT NULL
/

select * from table(dbms_xplan.display)
/

Please update your original post with the results of those.

Share:
13,198
jbwharris
Author by

jbwharris

I'm a developer in the Boston area. I work primarily in Java, but have interests in Scala.

Updated on November 18, 2022

Comments

  • jbwharris
    jbwharris over 1 year
    SELECT DISTINCT 
        'LRS-TECH  1' || rpad(code,7) || rpad('APPTYPE',30) || 
         rpad(licensing_no,30) || rpad(' ',300) AS RECORD 
    FROM APPS
    WHERE L_code = '1000' AND licensing_no IS NOT NULL
    

    This seems to be the primary culprit in why I cannot export these records to a textfile in my development environment. Is there any way I can get this query to run quicker. It returns roughly 2000+ lines of text.

    • dkretz
      dkretz over 15 years
      Generally an index won't help when testing for NULLness.
    • cagcowboy
      cagcowboy over 15 years
      It can help when checking for NOT NULLness
    • Dave Costa
      Dave Costa over 15 years
      I'd suggest you get an execution plan for the query and post it, that will allow people to give you better responses.
    • dkretz
      dkretz over 15 years
      In my experience testing for NULLness is equivalent to testing for NULLness on an index. The optimizer is smart enough to know that; and based on experience, it doesn't work.
    • dkretz
      dkretz over 15 years
      oops - s/testing for NULLness/testing for NOT NULLness/
  • dkretz
    dkretz over 15 years
    And you're assuming he doesn't want DISTINCT?
  • Dave Costa
    Dave Costa over 15 years
    If you think it's the RPADs, try the same query just selecting the columns: SELECT code, licensing_no WHERE L_code = '1000' AND licensing_no IS NOT NULL. I seriously doubt that's it.
  • David Aldridge
    David Aldridge over 15 years
    Check the execution plans on the two environments