Creating Efficient Oracle/PL_SQL Query Help

Question

Hope you are having a great day. I came across a problem.

In database the use of distinct is not efficient and it poses numerous timeout problems.

So In a simple case I have...

select distinct first_value(e.error_message) over (order by create_date desc)
                  from database e

Which exactly one result which error message is ordered by latest and it is the first value of its kind, now when I run it it takes about .8 seconds which isn't bad, but the problem is, joining and making this query bigger and doing more than just retrieving errors will be a problem.

So if I do the following query...

select  first_value(e.error_message) over (order by create_date desc)
                  from database e

this query takes about .4 seconds, but the problem is only want the first item that is given. How do I do this, I do not know the row number that is specific with it.

Thanks everyone.

*EDIT

Just to let everyone know, using Rob's solution has made my huge query more efficient TRY NOT TO USE DISTINCT WHEN POSSIBLE!!!!

Rob van Wijk · Accepted Answer · 2014-10-19 14:45:19Z

5

select max(e.error_message) keep (dense_rank last order by create_date)
  from database e

EDIT: Here is the link to the documentation of the LAST function

It select the last error message when sorted by create_date.

edited Oct 19, 2014 at 14:45

user142162

answered Sep 16, 2011 at 14:35

Rob van Wijk

17.7k5 gold badges41 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kevin Over a year ago

works even better, can you explain this, I'm fairly new to writing queries thanks. I havent used keep or dense rank yet either

Rob van Wijk Over a year ago

I've added a link to the documentation and an explanation.

Rob van Wijk Over a year ago

It's explained in the link. It's all part of the syntax of the aggregate function LAST.

Ollie · Accepted Answer · 2011-09-16 13:36:01Z

2

You could wrap the select:

SELECT *
  FROM (
        select first_value(e.error_message) over (order by create_date desc)               from database e 
       )
 WHERE rownum < 2;

OR

SELECT UNIQUE 
       val
  FROM (
        select first_value(e.error_message) over (order by create_date desc) as val
          from database e 
       );

answered Sep 16, 2011 at 13:36

Ollie

17.6k8 gold badges50 silver badges61 bronze badges

4 Comments

Kevin Over a year ago

Ahhh,I didn't think about the first one. the first one only takes about .2-.3 seconds but the next one takes about .8 because i believe of the UNIQUE function, it cycles through the result and checks to see if its unique. Thanks a bunch the answer is amazing.

Allan Over a year ago

This is a good answer, but using a regular order by clause may be more efficient that using an analytic function: SELECT * FROM (select e.error_message from database e order by create_date desc) WHERE rownum = 1;.

Kevin Over a year ago

@Allan Allan, you are correct, maybe we over think it sometimes, but in the end a simple select query is in fact faster than the analytic function. Thank you for your help.

Kevin Over a year ago

Does anyone know a way to do this without wrapping it in a select out of curiosity, because I believe sql has a first function of some sort

Collectives™ on Stack Overflow

Creating Efficient Oracle/PL_SQL Query Help

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related