| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Index usage in order by clause
Probably not. The difference in time comes from the rownum/order by combination (which, by the way, you are using correctly. Wish it always were the case). When you say 'ROWNUM <= 10' and you have no ORDER BY clause, Oracle just has to gather the very first ten rows (and your hint incites it to choose a path to do it as fast as possible). With the ORDER BY clause, you have to fetch ALL ROWS before being able to tell which are the first 10.
If I were you I would replace FIRST_ROWS with ALL_ROWS, I hope my explanations are clear enough to make you understand why.
Concerning your index on the sort column, I am ready to bet it would make things worse actually.
Regards,
Stephane Faroult
On Tue, 15 Jun 2004 08:54 , jaysingh1@optonline.net sent:
Dear All,
I have a question about index column in order by clause. The below query is taking 3 min to get the result but the sametime if I comment out ORDER BY clause it is taking only 3 secs.
Is it possible to improve the performance by creating an index on table2.lastupdatedate column
SELECT * FROM (
SELECT /*+ FIRST_ROWS */
p.processed_by,
p.last_name,
p.first_name,
p.company_name,
p.userid
FROM table1 p,table2 e
where p.business_country_id='GB'
and p.uk=e.pk
and e.userstatusid in ('5')
order by e.lastupdatedate desc
)
WHERE ROWNUM <=10
/
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
 |
 |