0

I have a performance issue on the following (example) select statement that returns the first row using a sub query:

SELECT ITEM_NUMBER,
       PROJECT_NUMBER,       
       NVL((SELECT DISTINCT
                   FIRST_VALUE(L.LOCATION) OVER (ORDER BY L.SORT1, L.SORT2 DESC) LOCATION
            FROM   LOCATIONS L
            WHERE  L.ITEM_NUMBER=P.ITEM_NUMBER
                   AND L.PROJECT_NUMBER=P.PROJECT_NUMBER
           ),
          P.PROJECT_NUMBER) LOCATION
FROM   PROJECT P

The DISTINCT is causing the performance issue by performing a SORT and UNIQUE but I can't figure out an alternative.

I would however prefer something akin to the following but referencing within 2 select statements doesn't work:

SELECT ITEM_NUMBER,
       PROJECT_NUMBER,       
       NVL((SELECT LOCATION
            FROM   (SELECT L.LOCATION LOCATION
                           ROWNUM RN
                    FROM   LOCATIONS L
                    WHERE  L.ITEM_NUMBER=P.ITEM_NUMBER
                           AND L.PROJECT_NUMBER=P.PROJECT_NUMBER
                    ORDER BY L.SORT1, L.SORT2 DESC
                   ) R
            WHERE RN <=1
           ), P.PROJECT_NUMBER) LOCATION
FROM   PROJECT P

Additionally: - My permissions do not allow me to create a function. - I am cycling through 10k to 100k records in the main query. - The sub query could return 3 to 7 rows before limiting to 1 row.

Any assistance in improving the performance is appreciated.

Improve this question
3
  • Use ROW_NUMBER instead of FIRST_VALUE Commented Jan 29, 2013 at 3:54
  • 1
    How do you know the DISTINCT is the cause of your performance issue? Just because you see a SORT / UNIQUE in your explain plan doesn't imply that your query is actually sorting, or sorting a lot of data, let alone getting a performance hit for it. Post your explain plan. Commented Jan 29, 2013 at 5:14
  • @JeffreyKemp, +1 you raise good points. I know there is no direct link (and don't want a debate) between the EXPLAIN PLAN and PERFORMANCE but the amount of extra work involved in getting the solution my way, I'm sure I've missed a simple solution. I can only go be the EXPLAIN to estimate the work the system is doing. Commented Jan 29, 2013 at 6:22

2 Answers 2

Reset to default
3

It's difficult to understand without sample data and cardinalities, but does this get you what you want? A unique list of projects and items, with the first occurrence of a location?

SELECT 
P.ITEM_NUMBER,
P.PROJECT_NUMBER,       
MIN(L.LOCATION) KEEP (DENSE_RANK FIRST ORDER BY L.SORT1, L.SORT2 DESC) LOCATION
FROM   
LOCATIONS L
INNER JOIN
PROJECT P
ON L.ITEM_NUMBER=P.ITEM_NUMBER
AND L.PROJECT_NUMBER=P.PROJECT_NUMBER
GROUP BY
P.ITEM_NUMBER,
P.PROJECT_NUMBER
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Yes you are right that is the output I need. I think your solution works well, certainty a lot simpler than mine. I'll reply tommorrow with an outcome.
You're missing the NVL but otherwise yes; this is what I would have answered; it saves the inline sub-query (which isn't always evil for those reading this) but more importantly it should be a lot more efficient.
Thanks my query went from in excess of 2 minutes down to 6 seconds!
Excellent. Those analytical functions are magic. Well not magic but oftentimes the closest thing to a silver bullet solution.
-2

I encounter similar problem in the past -- and while this is not ultimate solution (in fact might just be a corner-cuts) -- Oracle query optimizer can be adjusted with the OPTIMIZER_MODE init param.

Have a look at chapter 11.2.1 on http://docs.oracle.com/cd/B28359_01/server.111/b28274/optimops.htm#i38318

FIRST_ROWS

The optimizer uses a mix of cost and heuristics to find a best plan for fast delivery of the first few rows. Note: Using heuristics sometimes leads the query optimizer to generate a plan with a cost that is significantly larger than the cost of a plan without applying the heuristic. FIRST_ROWS is available for backward compatibility and plan stability; use FIRST_ROWS_n instead.

Of course there are tons other factors you should analyse like your index, join efficiency, query plan etc..

Improve this answer

1 Comment

-1 FIRST_ROWS is an optimizer hint. The OP is asking about a subquery which is supposed to return the first row - i.e. only the first row - I know it's confusing, but the two things are very different and have different purposes.

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.