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.
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, 2022Comments
-
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 over 15 yearsGenerally an index won't help when testing for NULLness.
-
cagcowboy over 15 yearsIt can help when checking for NOT NULLness
-
Dave Costa over 15 yearsI'd suggest you get an execution plan for the query and post it, that will allow people to give you better responses.
-
dkretz over 15 yearsIn 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 over 15 yearsoops - s/testing for NULLness/testing for NOT NULLness/
-
-
dkretz over 15 yearsAnd you're assuming he doesn't want DISTINCT?
-
Dave Costa over 15 yearsIf 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 over 15 yearsCheck the execution plans on the two environments