Re: Index usage in order by clause
- From: "Michael McMullen" <ganstadba@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 15 Jun 2004 09:03:04 -0400
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@xxxxxxxxxxx
----- Original Message -----
From: <jaysingh1@xxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts: