Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> ORDER BY increases consistent gets?

ORDER BY increases consistent gets?

From: Thomas A. La Porte <tlaporte_at_yahoo.com>
Date: Sat, 3 Apr 2004 10:24:37 -0800 (PST)
Message-ID: <Pine.LNX.4.44.0403301107460.875-100000@sunset.anim.dreamworks.com>


Env: Oracle 8.1.7.4 on RedHat AS2.1 (2.4.9-e.35 kernel)

A developer brought me a query yesterday in which the introduction of an ORDER BY clause alters the performance substantially.

The query is as follows:

  SELECT reserve_host, usage,

         to_char(request_time, 'MM/DD/YYYY HH24:MI:SS')     FROM licadmin.licmgr_requests

   WHERE request_time > to_date('3/14/2004', 'MM/DD/YYYY')
     AND release_time < to_date('1970', 'YYYY')
ORDER BY request_time

Without the ORDER BY clause, the query requires about 100 consistent gets, while with the ORDER BY clause it takes over 50000 consistent gets. According to autotrace and tkprof the execution plans are the same [see below].

--

Thomas A. La Porte
<mailto:tlaporte_at_yahoo.com>



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 Sat Apr 03 2004 - 12:21:08 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US