Re: ORDER BY increases consistent gets?

From: Juan Cachito Reyes Pacheco <jreyes_at_dazasoftware.com>
Date: Mon, 5 Apr 2004 09:34:29 -0400
Message-ID: <009d01c41b16$3b3647a0$2501a8c0@dazasoftware.com>

To improve performance, try creating an index based on the columns included in the order by clause (exactly with desc if it uses).

And if possible all the fields you query too, so Oracle will read only the index.

Juan Carlos Reyes Pacheco
OCP
Database 9.2 Standard Edition
----- Original Message -----

From: "DENNIS WILLIAMS" <DWILLIAMS_at_LIFETOUCH.COM> To: <oracle-l_at_freelists.org>
Sent: Saturday, April 03, 2004 6:20 PM
Subject: RE: ORDER BY increases consistent gets?

Thomas - Usually ORDER BY causes a sort to occur. A simple check would be to run EXPLAIN PLAN on both versions of the query.

Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Thomas A. La Porte Sent: Saturday, April 03, 2004 12:25 PM
To: oracle-l_at_freelists.org
Subject: ORDER BY increases consistent gets?

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


-----------------------------------------------------------------

----------------------------------------------------------------

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

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 Mon Apr 05 2004 - 08:59:10 CDT