CODEWITHSUNDEEP
sqloracle-database
abhay kumar
abhay kumar

Reputation: 579

How to optimize my query so that I can use ORDER BY

I use ORDER BY in the below query but it takes the almost 20 sec to fetch the data.

SELECT id, 
       name, 
       o.map.subject, 
       o.map.xyz 
FROM   student o 
WHERE  test = 3553 
       AND o.map.identifier = 'Abhay' 
ORDER  BY o.id desc 
FETCH first 15 ROWS only;

When do not use ORDER BY, as shown below, it takes only the 0.2 sec response time which is what I want. The problem is - it is fetching the older data, I need to get the last 15 records inserted into the table student.

Is there any way to sort the data without using ORDER BY? If not, how can I speed up the query?

SELECT id, 
       name, 
       o.map.subject, 
       o.map.xyz 
FROM   student o 
WHERE  test = 3553 
       AND o.map.identifier = 'Abhay' 
FETCH first 15 ROWS only;

Upvotes: 0

Views: 239

Answers (1)

Connor McDonald
Connor McDonald

Reputation: 11591

The query

SELECT id, 
       name, 
       o.map.subject, 
       o.map.xyz 
FROM   student o 
WHERE  test = 3553 
       AND o.map.identifier = 'Abhay' 
ORDER  BY o.id desc 
FETCH first 15 ROWS only;

only runs slowly if either it is expensive to find the rows, or we get a huge number of rows and hence it is expensive to sort them.

If it is expensive to find then, then you probably need to consider indexing on TEST,MAP_IDENTIFIER. If, even with that index, the sorting cost is large, that is, you have a huge amount of rows that match the criteria, then you might want to consider adding ID to the index so that you can scan the index in a descending fashion and hence avoid the sorting cost.

Even if you do that, you must always have the ORDER BY clause. Without out, the results are always indeterminate.

Upvotes: 1

Related Questions