This content has been marked as final. Show 3 replies
Are you stating that without the `ORDER BY`, the query returns the first row in 1 second? Or the last row in 1 second?
What is the query plan of both queries? Are there differences outside of the presence or absence of a sort?
Thanks so much for you prompt response.
I mean the first row comes out in a second.
There is no difference between both execution plan except the presence or absent of order by sort.
Can we create indexes on the "order by" columns to improve the performance?
By the way, I am using Oracle 10g.
Edited by: 929380 on Aug 31, 2012 8:24 AM
Edited by: 929380 on Aug 31, 2012 8:57 AM
In 11g you can add an "order by" clause to the index, but not in 10g.
When you say "The number of rows returned is only around 100", is that the total number of rows returned by the query, or does that mean you're only fetching the first 100 rows? I really can't see why it should take 9 seconds to sort 100 rows.