Kevin
Kevin

Reputation: 3509

Creating Efficient Oracle/PL_SQL Query Help

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!!!!

Upvotes: 1

Views: 93

Answers (2)

Rob van Wijk
Rob van Wijk

Reputation: 17705

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.

Upvotes: 5

Ollie
Ollie

Reputation: 17538

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 
       );

Upvotes: 2

Related Questions