We are having a performance issue with "order by" and "contains". If I remove the "order by" from the query, the query returns less than 1 second, but the query takes 10 seconds with "order by". The number of rows returned is only around 100.
Any suggestion would be greatly appreciated.
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.