Wednesday, August 11, 2010

FIRST_ROWS vs ALL_ROWS

A colleague asked me some questions about FIRST_ROWS and ALL_ROWS, but I'm hesitant to blog about it because it's already been done so well by others -- the best example would probably be Sachin Arora.

Nevertheless, it never hurts to lend another voice to the Oracle choir, so here's everything I know on the topic.

FIRST_ROWS and ALL_ROWS are values for the optimizer setting OPTIMIZER_MODE. You can set it in your init.ora file, or you can set it on a session-by-session basis (using alter session). You can also set it on a query-by-query basis by using hints (although such hints may be ignored in certain cases, like where all rows must be accessed, as in a GROUP BY).

Your basic choices for OPTIMIZER_MODE include CHOOSE by default, RULE, ALL_ROWS, FIRST_ROWS and its variant FIRST_ROWS_N (where N is 1, 10, 100 or 1000). So far I'm not telling you anything that wouldn't be obvious with a glance at the documentation, but let me explain a little bit about the latter two.

ALL_ROWS and FIRST_ROWS have a lot in common. They're the only two settings that force the use of the CBO (Cost-Based Optimizer), and therefore they're collectively referred to as cost-based, since the concept of cost is directly related to the CBO.

If your statistics are up to date -- which they should be!! -- then the CHOOSE option will generally use the CBO as well. Possibly always, but I'd rather not use the word "always" unless I were certain. If you turn tracing on, you can see the optimizer setting that was used, and if you see "Cost=" then you know the CBO came into play.

The most important thing to keep in mind when using CHOOSE or a cost-based optimizer setting is that your statistics are up to date, otherwise it might default to RULE (if it's set to the default value of CHOOSE), or will use default statistics.

"The CBO without stats is like a morning without coffee. A really bad idea."
- Tom Kyte (though I'm sure Jon Emmons would agree).


The difference between FIRST_ROWS and ALL_ROWS is exactly what you'd think - the former instructs the CBO to get the first rows as quickly as possible even if the overall query takes longer, whereas the latter simply gets you all the data as quickly as possible.

As you would probably guess, FIRST_ROWS will likely be better in an interactive client/server situation where there is a benefit to getting some of the results before the rest. FIRST_ROWS is also found to use index scans instead of full table scans more often than otherwise - but for more detail on its peculiarities, check Sachin's article or search the Oracle forums.

As far as I know, the only real difference between ALL_ROWS and CHOOSE is that ALL_ROWS will use the CBO regardless of whether you have up-to-date statistics. Basically ALL_ROWS doesn't make as much sense to me, because if you care about performance to the point where you're modifying optimizer settings, then you probably care enough to have up-to-date statistics, in which case you'd leave it on the default value of CHOOSE.

That's about all I can really say about these two settings. If you've read this far and you'd like to know more, there are two obvious things you can do:

- You can Ask Tom, and/or
- You can read the Oracle Documentation

Each of those sources has pretty much everything I've said, plus more. If you've got more to ask or to offer on this topic, I invite you to leave a comment.

Comments:
I have also the post that you have recommended. I agree with you and also think the same, its the best explanation give on this topic. The usage of the both the features of optimizer is very well explained using appropriate examples. Even some important facts are also given for both. Thanks for sharing the link to such a informative post.
 
First row is fetch quickly as compared to all rows because When we write the query to find all row it take lot of time to fetch all row but if the query is just write to fetch the first row it take less time.
 
Hi Robert, I really liked your articles. If you are interested in sharing your knowledge by delivering Tech webinars, please send us your contact details to jobsmips@gmail.com More details will be shared once we hear from you. This is not scam :-)..
 
Hi

I read this post two times.

I like it so much, please try to keep posting.

Let me introduce other material that may be good for our community.

Source: What have you done to improve your knowledge in the last year?

Best regards
Henry

 
Thanks for taking this opportunity to discuss this, I feel fervently about this and I like learning about this subject
 
This comment has been removed by the author.
 
Your given most of the usefull information..The Oracle consists of all master data.
It includes the following information and processes.ORACLE 11g TRAINIING IN A SIMPLE WAY.
ORACLE11g ONLINE TRAINING


 
Thanks for taking this opportunity to discuss this, I feel fervently about it and I am learning about this topic
oracleconnections
 
This is such a nice post, actually I'am new in Oracle industry and I don't know much about so that's why I'am looking on the web for tutorials and Oracle Consulting and Oracle Services to gain my knowledge about Oracle. Thank you so much for sharing such a nice post with us.
 
Nice informative information blog is this......

Thanks and Regards :
Qadir Shaikh.
Visit at http://www.oratc.com
 
Well list appreciated keep updates see oracle community the management consulting.
 
I am Sridevi Koduru, Oracle Apps Techno Functional Faculty with 6 Years Training Experience on Oracle Apps Technical, Oracle Apps Financial Functional, Oracle Financial for Indian Localization, Oracle Apps SCM, Oracle Apps HRMS, SQL, PL/SQL and D2K including 3+ Years of Real Time Experience. I have Provided Training for 500+ Professionals Most of them are Working in Real Time now.

Contact Me for Offline and Online Training on Oracle Apps Technical, Oracle Apps Financial Functional, Oracle Financial for Indian Localization, Oracle Apps SCM, Oracle Apps HRMS, SQL, PL/SQL and D2K at sridevioracleapps@gmail.com | +91-9581017828 | http://www.oracleappstechnical.com

My Linkedin profile - http://in.linkedin.com/pub/sridevi-koduru/8b/76a/9b8/
 
Thanks for sharing such an interesting post. It is really worth following your blog. Keep sharing.

Oracle training in chennai
 
Ofcourse , It's nice posting about oracle recruitment jobs .
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?