RE: FIRST_ROWS vs. ALL

From: Carle, William T (Bill), NLCIO <wcarle_at_att.com>
Date: Mon, 03 Dec 2001 13:37:01 -0800
Message-ID: <F001.003D3D23.20011203132521@fatcity.com>

Hello again,

I want to thank everyone for responding to my query. I have a much better understanding of how this works now. I changed my query to remove the "rownum < 5" and the query returned 344066 rows. The output of the tkprof is below:

FIRST_ROWS call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 22939 29.99 145.98 42488 2708970 0 344066

ALL_ROWS call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse 1 0.01 0.01 0 0 0 0
Execute 2 0.01 0.00 0 0 2 0
Fetch 22939 49.33 474.71 221733 803265 3086 344066

It still seems like it takes much longer in the ALL_ROWS case, but the value in the query column is much less; however, the values in the disk and current columns are much bigger. When I actually ran it and put the output into a file, the FIRST_ROWS case took 1:17 minutes and the ALL_ROWS case took 8:53 minutes.

Bill Carle
AT&T
Database Administrator
816-995-3922
wcarle_at_att.com

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Carle, William T (Bill), NLCIO
INET: wcarle_at_att.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Mon Dec 03 2001 - 15:37:01 CST

RE: FIRST_ROWS vs. ALL_ROWS