Re: Index usage in order by clause

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 15 Jun 2004 14:13:50 +0100
Message-ID: <009101c452da$99ac2380$7102a8c0@Primary>

It's a defect in first_rows optimization; if an "order by" can use an index to
produce an "order by (no sort)", then
Oracle will take no matter how much
more expensive the execution plan is.
(This changes in 10g - but first_rows
is deprecated, if not desupported
in 10g).

You can hack the hidden parameter
_sort_elimination_cost_ratio to change
the behaviour.

If you set the parameter to N, then the
no-sort plan (i.e. the one using the index for the order by) is only taken if it cost less than N times the cost of the plan that does the sort. There is no 'correct' value for N, as the whole thing depend on the
optimizer costing both plans realistically anyway, but a value in the range of 5 to 15 may be fairly reasonable.

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:10:43 CDT