RE: ORDER BY increases consistent gets?
- From: DENNIS WILLIAMS <DWILLIAMS@xxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 3 Apr 2004 16:20:56 -0600
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@xxxxxxxxxxxxx
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Thomas A. La Porte
Sent: Saturday, April 03, 2004 12:25 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
