215

I have an big problem with an SQL Statement in Oracle. I want to select the TOP 10 Records ordered by STORAGE_DB which aren't in a list from an other select statement.

This one works fine for all records:

SELECT DISTINCT APP_ID, NAME, STORAGE_GB, HISTORY_CREATED, TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') AS HISTORY_DATE FROM HISTORY WHERE STORAGE_GB IS NOT NULL AND APP_ID NOT IN (SELECT APP_ID FROM HISTORY WHERE TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') = '06.02.2009')

But when I am adding 

AND ROWNUM <= 10 ORDER BY STORAGE_GB DESC

I'm getting some kind of "random" Records. I think because the limit takes in place before the order.

Does someone has an good solution? The other problem: This query is realy slow (10k+ records)

Improve this question
0

6 Answers 6

Reset to default
259

You'll need to put your current query in subquery as below :

SELECT * FROM ( SELECT DISTINCT APP_ID, NAME, STORAGE_GB, HISTORY_CREATED, TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') AS HISTORY_DATE FROM HISTORY WHERE STORAGE_GB IS NOT NULL AND APP_ID NOT IN (SELECT APP_ID FROM HISTORY WHERE TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') ='06.02.2009') ORDER BY STORAGE_GB DESC ) WHERE ROWNUM <= 10

Oracle applies rownum to the result after it has been returned.
You need to filter the result after it has been returned, so a subquery is required. You can also use RANK() function to get Top-N results.

For performance try using NOT EXISTS in place of NOT IN. See this for more.

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

5 Comments

NOT EXISTS is not working in this scenario (invalid relational operator) APP_ID NOT EXISTS (SELEC...)
Some may say that this is apt to turn people off to Oracle.
Check the FETCH NEXT N ROWS ONLY answer below.
@Padmarag : When is a rownum apply in a query like this one - Select * from SomeTable where someColumn = '123' and rownum <=3. Is it after selecting the results from [Select * from SomeTable where someColumn = '123' ]
try to use: select * from (select * from history order by storage_gb desc) where rownum < 10; --(modify inner query to fetch distinct records)
147

If you are using Oracle 12c, use:

FETCH NEXT N ROWS ONLY

SELECT DISTINCT APP_ID, NAME, STORAGE_GB, HISTORY_CREATED, TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') AS HISTORY_DATE FROM HISTORY WHERE STORAGE_GB IS NOT NULL AND APP_ID NOT IN (SELECT APP_ID FROM HISTORY WHERE TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') ='06.02.2009') ORDER BY STORAGE_GB DESC FETCH NEXT 10 ROWS ONLY

More info: http://docs.oracle.com/javadb/10.5.3.0/ref/rrefsqljoffsetfetch.html

Improve this answer

4 Comments

this is gold compared to other answer
If someone could say why it's better, that would be a good comment/edit.
If using simple query like SELECT * FROM TABLE1 WHERE ROWNUM<=10 and SELECT * FROM TABLE1 FETCH NEXT 10 ROWS ONLY, FETCH NEXT seems like more costly since it will do this predicate ROW_NUMBER() OVER ( ORDER BY NULL )<=10) if check from the Explain Plan.
@sanme98, Oracle said they fixed the performance issue: blogs.oracle.com/optimizer/post/…
66

try 

SELECT * FROM users FETCH NEXT 10 ROWS ONLY;
Improve this answer

Comments

23

With regards to the poor performance there are any number of things it could be, and it really ought to be a separate question. However, there is one obvious thing that could be a problem: 

WHERE TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') = '06.02.2009')

If HISTORY_DATE really is a date column and if it has an index then this rewrite will perform better:

WHERE HISTORY_DATE = TO_DATE ('06.02.2009', 'DD.MM.YYYY')

This is because a datatype conversion disables the use of a B-Tree index.

Improve this answer

Comments

16

You get an apparently random set because ROWNUM is applied before the ORDER BY. So your query takes the first ten rows and sorts them.0 To select the top ten salaries you should use an analytic function in a subquery, then filter that:

 select * from (select empno, ename, sal, row_number() over(order by sal desc nulls last) rnm from emp) where rnm<=10
Improve this answer

Comments

-2

you may use this query for selecting top records in oracle. Rakesh B

select * from User_info where id >= (select max(id)-10 from User_info);

Improve this answer

1 Comment

Hey Rakesh! I'm not sure this answer fully addresses the question posed. Check out some of the other responses that provide more detail and are well-formatted for some examples.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.