Re: What's your opinion: ALL_ROWS vs FIRST_ROWS

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).