Oracle SQL Optimization: SQL Query taking very long time

Question

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.

Answer 1

If you don't already have indexes on L_code and licensing_no columns, I would try that.

Answer 2

If there are many records with L_code = '1000' and the only additional test is for NOT NULL, you probably have a cardinality problem. Indexes have a hard time selecting on NULL or not.

The number of rows returned is unimportant - it's the number of rows examined that's the question.

What indexes are there?

Answer 3

Get rid of the DISTINCT.

Answer 4

Hmmm... getting rid of DISTINCT may help considering that code is the PRIMARY KEY. I don't think it is what is causing the major processing problems. If believe the RPAD, etc. is causing most of the query delay.

The indexes mainly ASCEND the CODE field. That's the only relevant indexes on the table.

Answer 5

You could prebuild the RECORD derived value in a secondary table, view or column using a trigger and query that instead of building it on the fly if the table is frequently queried.

It might help to know the size of the table. If you've got a large column in there, or a lot of records, it could be something IO or cache related.

Answer 6

I'm sorry to everyone looking at this SQL, but this is a mindboggling server problem or something. The scenario seems to have drawn itself out,and I believe it's a data availability problem as to where the DB resides, but somebody may be able to give me some insight.

On my Localhost, I run the code, works instantaneously. I export the data it gives me from a datatable to a textfile in less than a second... done.

On our development environment, the same page is in old ASP. Half our site is in classic ASP as we convert to .NET. The problem seems to be that on the DEV site, the classic ASP page works perfectly, quickly and done in less than a second. When I uploaded the newly converted ASPX file, it hung for about 30 seconds on that query.

On Localhost, the old classic ASP hangs for about 30 seconds.

So, I have a vice versa problem here in that the classic ASP doesn't hang on the DEV site, but on my machine while my own ASPX page hangs on the DEV site, but NOT on my machine. The difference is that I believe the data is being pulled in my own code on the DEV site, while the ASP page is pulling the data from code that resides on an old DEV site server that ports the results to the DEV site. So, technically, the code isn't being run on the same server. The classic ASP code is on our old site server.

I'm assuming there is some sort of speed issue or server issue between the two sites.

Answer 7

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.

Answer 8

As most of the answers here have indicated, your question sounds like an optimization question. Your later answer changes the nature of the question significantly. I suggest posting it as a new question or modifying the original question to ask what you really want to know.

I can't help you on the ASP/ASPX issue, but if this were an optimization question I'd suggest creating a function based index for a new WHERE clause as follows:

SELECT DISTINCT 
    'LRS-TECH  1' || rpad(code,7) || rpad('APPTYPE',30) || 
     rpad(licensing_no,30) || rpad(' ',300) AS RECORD 
FROM APPS
WHERE DECODE(L_code,'1000',licensing_no,NULL) IS NOT NULL;

A function based index on DECODE(L_code,'1000',licensing_no,NULL) would include all the records you want to return. If you needed even more speed you could create a materialized view on the results of the query, but that would be more of a last ditch effort.

Answer 9

I wonder if it is because the oracle is using a different index (or not at all) for the query from the aspx page.
I would suggest updating the statistics on the table to see if that makes any difference.
See this question for how to do it (and the comments that 'calculate statistics' is obsolete, replaced by a package instead)

Answer 10

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)