Re: Index usage in order by clause

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 15 Jun 2004 14:13:50 +0100

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.


Jonathan Lewis
The Co-operative Oracle Users' FAQ
Optimising Oracle Seminar - schedule updated May 1st

----- Original Message ----- 
From: <jaysingh1@xxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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         /*+ 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
: and e.userstatusid in ('5')
: order by e.lastupdatedate desc
: )
: /

Please see the official ORACLE-L FAQ:
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at

Other related posts: