| 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
If you need an order by, you have to use an order by. It's the only way
oracle will guarantee an ordered result. Some might say you can build a
descending index on lastupdatedate and then the order by will use that, but
definitely you need the order by.
Also, will first_rows hint at all? You're using an order by which will
require all the rows to come back before the sort takes place.
Mike
ganstadba_at_hotmail.com
----- Original Message -----
From: <jaysingh1_at_optonline.net>
To: <oracle-l_at_freelists.org>
Sent: Tuesday, June 15, 2004 8:54 AM
Subject: Index usage in order by clause
> 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_at_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
> -----------------------------------------------------------------
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Tue Jun 15 2004 - 08:04:20 CDT
 |
 |