Michael,
I think you are correct... OLTP tends to go for FIRST_ROWS. OLAP tends to
go for ALL_ROWS. I say "tends" because I'm sure there are a multitude of
reasons for selecting the other option. I guess you need to look at the
queries being run against the database, and the applications using those
queries. Will they benefit by receiving a partial result first? If the
application can happily take the first few rows and display them to the
screen then FIRST_ROWS might be good, but if the application is going to
load them entire set into an array and then display the first few to the
screen then you might as well select ALL_ROWS. The difference can be that
subtle when you think about it.
I think the difference is often negligable as well - especially depending
on the query. If the query is only going to return a few rows then it
won't really matter. Similarly, if the query has to read a lot of rows and
perform some kind of sort / aggregate function then there is little
opportunity to return the first rows until every row has been sorted -
again, it won't really matter. The good news, therefore, is that unless
you fall into the "I have lots of queries that return large result sets
without performing sort operations" then it won't make a big difference to
you performance.
Cheers,
Mark.
"Armstead, Michael
A" To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
<maa25681@GlaxoWel cc:
lcome.com> Subject: What's your opinion:
ALL_ROWS vs FIRST_ROWS
Sent by:
[EMAIL PROTECTED]
30/10/2002 08:13
Please respond to
ORACLE-L
We're moving from RBO to CBO.
For those of you who use CBO, what mode do you use FIRST_ROWS or ALL_ROWS?
And why?
My thinking is if it's a database where most of the querying is done on
small sets of records, then we may want to use FIRST_ROWS. On the other
hand, if our database is used to generate sizable reports, we might use
ALL_ROWS.
I also understand that we can always change it per session (with alter
session) and per query (with hints).
Michael Armstead
Principal Database Administrator, OCP-Certified
World Wide Corporate IT Database Administration
GlaxoSmithKline
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Armstead, Michael A
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message
(or responsible for delivery of the message to such person),
you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
by reply e-mail or by telephone on (61 3) 9612-6999.
Please advise immediately if you or your employer does not consent to
Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message
that do not relate to the official business of
Transurban City Link Ltd
shall be understood as neither given nor endorsed by it.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mark Richard
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
