This is purely subjective, but my gut feel has always
been that first_rows bends too heavily toward index
usage, and all_rows bengs too far away from it..
So we're stuck? Not really. You can find some useful
middle ground with some tweaking of the optimizer_...
parms in init.ora. Although "tweaking" sounds
hideous, what you are really doing is giving the
optimizer *more* accurate information..You're telling
it the probability of index blocks being cached, the
differential between a multiblock read and a single
block read
hth
connor
- Mark Richard <mrichard_at_transurban.com.au> wrote:
> 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
> <ORACLE-L_at_fatcity.com>
> <maa25681_at_GlaxoWel cc:
>
>
> lcome.com>
> Subject: What's your opinion: ALL_ROWS vs
> FIRST_ROWS
> Sent by:
>
>
> root_at_fatcity.com
>
>
>
>
>
>
>
>
> 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: maa25681_at_GlaxoWellcome.com
>
> 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: 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).
>
>
>
>
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
> 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: mrichard_at_transurban.com.au
>
> 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: 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).
Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
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: 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 Wed Oct 30 2002 - 03:12:12 CST
