Re: Index usage in order by clause

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 15 Jun 2004 14:20:24 +0100
Message-ID: <009e01c452db$844cfd10$7102a8c0@Primary>

Just re-read the question, and realised my reply was totally irrelevant - effectively answering the exact opposite to the question you were asking.

The index should work (for precisely the reason given in my explanation) provided the lastupdatedate were declared to be NOT NULL.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st

Original Message ----- From: <jaysingh1_at_optonline.net> To: <oracle-l_at_freelists.org> Sent: Tuesday, June 15, 2004 1:54 PM 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
-----------------------------------------------------------------

Received on Tue Jun 15 2004 - 08:17:14 CDT