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 .
 
Very Interesting.
 
Thanks for Information Our Online-Training-Informatica proven expert in all Hyperion Modules like Hyperion Financial Management, Hyperion Financial Data Quality, Hyperion Financial Reporting, Hyperion Essbase, Hyperion Planning, Smart view and Data Relationship management.Hyperion Essbase Online Training
 
Thanks for Information Oracle Online Training
 
Thanks for that..hadoop training in chennai...I have to learn to lot of information...hadoop training in chennai....
 
It was really a nice article and i was really impressed by reading this article We are also giving all software Course Online Training.The oracle Online Training is one of the leading Online Training institute in the world.
 
Hi,i hope to your information really understand.

Besant Technologies
&
LoadRunnerTraining in Chennai

 
This is Valuable information for who wants to learns software courses, thanks for sharing.

Besant Technologies
&
QTP Training in chennai

 
Besant Technologies Reviews and complaints...

Besant Technologies Reviews
&
Besant Technologies Reviews and Complaints

 
Our Android Training in Chennai aims to teach beginners and
employees.Android is the fastest growing smart phone OS in the world
today..To know more,follow the below link

http://www.androidtraininginchennai.in
 
I got a job by saying this answer in my last interview. thanks for awesome help. I got more idea about Java from Besant Technologies. If anyone wants to get Java

Training in Chennai visit Besant Technologies.

Java Training in Chennai

Java Training Institutes in Chennai
 
Thanks to review,
http://www.besanttechnologies.com/training-courses/mobile-applications-training/ios-training-institute-in-chennai
 
Our Android Training in Chennai aims to teach beginners and employees.Android is the fastest growing smart phone OS in the world today..To know more,follow the below link
http://www.androidtraininginchennai.in/#
 
Thanks to review,
Best ios
training institute in chennai

 
Our Android Training in Chennai aims to teach beginners and
employees.Android is the fastest growing smart phone OS in the world
today..To know more,follow the below link
Best android training
in chennai


 
best oracle apps finance training in chennai
oracle apps finance training in chennai

&

oracle apps finance training institute in chennai
 
best oracle apps finance training in chennai:

oracle apps finance training in chennai

&

oracle apps finance training

institute in chennai

 

Besant Technologies Reviews,

This page is dedicated for our Besant Technologies Reviews by our students. Please give your reviews here,

Besant Technologies Reviews
 
This page is dedicated for our Besant Technologies Reviews by our students. Please give your
reviews here,
Besant Technologies
Reviews Complaints Testimonials


 

Besant Tech Reviews,

This page is dedicated for our Besant Technologies Reviews by our students. Please give your reviews here,

Besant Technologies Reviews
 
Awesome post thanks for sharing Oracle training
 
Your blog is really useful for me.

Regards

Oracle Training in Chennai
 
this is very nice article and very good information for Oracle ADF Learners. our Cubtraining also provide all Oracle Courses
 
Really awesome blog. Thanks for sharing this informative blog. Suppose if anyone want to learn Software Testing Training in Chennai at a reputed training institutes. If you are looking for best Software Testing Training Institutes in Chennai reach FITA located at Chennai, India. Rated as No.1 placement and training center in Chennai.





 
I get a lot of great information from this blog. Recently I did oracle certification course at a leading academy. If you are looking for best Oracle Course in Chennai visit FITA IT training and placement academy which offer PL SQL Training in Chennai.

 
Excellent article!!! LoadRunner is popular automation testing tool used for validating a software application/system under load. It delivers most precise information about the performance, functionality and behavior of the software product. Loadrunner training institute in Chennai
 
Hi I am Emi lives in Chennai. I am a technology freak. Recently I did Java Training in Chennai at a leading Java Training Center in Chennai. This is really helpful for me to make a bright carrer in IT industry.

 
Thanks for your wonderful post.It is really very helpful for us and I have gathered some important information from this blog.If anyone wants to get Dot Net Training in Chennai reach FITA, rated as No.1 Dot Net Training Institute in Chennai.

 
Hi, Thanks for sharing this valuable blog.I was really impressed by reading this blog. I did HTML5 Courses in Chennai at reputed HTML5 Training Institutes in Chennai. This is reslly useful for me to make a bright future in designing field.


 
Hi, I wish to be a regular contributor of your blog. I have read your blog. Your information is really useful for beginner. I did Selenium Training in Chennai at Fita training and placement academy which offer best Software Testing Training in Chennai with years of experienced professionals. This is really useful for me to make a bright career.

 
Thanks for your informative article on ios mobile application development. Your article helped me to explore the future of mobile apps developers. Having sound knowledge on mobile application development will help you to float in mobile application development. IOS Training in Chennai | IOS Training Institutes in Chennai
 
Its really awesome blog..If anyone wants to get Software Testing Training in Chennai visit FITA IT academy located at Chennai. Rated as No.1 Software Testing Training Institutes in Chennai

 
Salesforce Course in Chennai


I have read your blog and i got a very useful and knowledgeable information from your blog.You have done a great job . If anyone want to get Salesforce Training in Chennai, Please visit FITA academy located at Chennai Velachery.

Salesforce Developer Training in Chennai

Salesforce Administrator Training in Chennai


 
hi, i have for more knowledge for this sites..we have to post excellent information..hadoop training in chennai
 
hi,this artice is really excellent..i have to learning to lot information...hadoop training in chennai
 
Really i got you understand you have to sharing the for this information..Thanks for tjhat...oracle training in chennai
 
you sharing now useful information..i hope to really understanding to this posts..Oracle training in chennai
 
we could be sharing for helpfulness information..i have to sharing you sites all my world freinds...selenium training in chennai
 
Hi, I have read your blog. Your information is really useful for me.Thanks for sharing this blog. I did QTP Training Chennai at Fita training and placement academy which offer best Selenium Training Chennai with years of experienced professionals. This is really useful for me to make a bright career.

Regards..........

Software Testing Training Institutes in Chennai

 
Hadoop Training Chennai

Hi, I am Christina lives in Chennai. I am technology freak. I did Hadoop Training in Chennai at FITA. This is useful for me to make a bright career in IT field.

Hadoop Course in Chennai
 
PHP Training Chennai

I get a lot of great information from this blog. Thank you for your sharing this informative blog. Recently I did PHP course at a leading academy. If you are looking for best PHP Training Institute in Chennai visit FITA IT training academy which offer real timePHP Training in Chennai.

PHP Course in Chennai
 
Thanks for the discussion.Its very helpful.to know about lots of things
Software Testing Training in Chennai

 
I know something have to be in discussion. Later on i found that it would be precise to schedule the team as much of time calculation..

Android training in chennai


 
This comment has been removed by the author.
 
Oracle Training Institutes in Chennai

I get a lot of great information from this blog. Recently I did oracle certification course at a leading academy. If anyone interested to learn best Oracle Training in Chennai visit FITA academy which offer PL SQL Training in Chennai.

Regards...

Oracle Training Chennai


 
Hi,i would like for your information..i learning for lot informative.Thanks a lot...
hadoop training in chennai | hadoop training in chennai | hadoop training in chennai
 
HTML5 Training

Hi, Thanks for sharing this valuable blog.I was really impressed by reading this blog. I did HTML5 Training in Chennai at reputed HTML5 Training Institutes in Chennai. This is really useful for me to make a bright future in designing field.

HTML Training in Chennai

 
Nice blog to read.thank you.
Software Testing Training in Chennai | QTP Training in Chennai | Software Testing Training in Chennai

 
hi this is the wonderful blogs!!! . i have learn about lot of information..you shring to valuable information..oracle training in chennai
 
Dot Net Training Chennai

Thanks for your wonderful post.It is really very helpful for us and I have gathered some important information from this blog.If anyone wants to get Dot Net Training in Chennai reach FITA, rated as No.1 Dot Net Training Institute in Chennai.

Dot Net Course in Chennai



 
Just cant imagine the entire feature about synthetic issue.

Java training in Chennai | Android training in chennai | IOS training in chennai
 
I too know about major problems but still critical to know it.


 
SEO Training in Chennai

Thanks for sharing this information. SEO is one of the digital marketing techniques which is used to increase website traffic and organic search results. If anyone wants to get SEO Training Chennai visit FITA Academy located at Chennai. Rated as No.1 SEO Institutes in Chennai.

SEO Training in Chennai | SEO Training Center in Chennai



 
Testing Training in Chennai

Its really awesome blog..If anyone wants to get Software Testing Training in Chennai visit FITA IT academy located at Chennai. Rated as No.1 Software Testing Training Institutes in Chennai

Software Testing Course in Chennai

 
HTML5 Training in Chennai

Your blog is really awesome. Thank you for your sharing this informative blog. Recently I did PHP course at a leading academy. If you are looking for best PHP Training Institute in Chennai visit FITA IT training academy which offer real time PHP Training in Chennai.

PHP Course in Chennai





 

SAP by definition is also name of the ERP (Enterprise Resource Planing) software as well the name of the company. SAP system comprises of a number of fully integrated modules, which covers virtually every aspect of the business management.
Success factor training in chennai | sap training in chennai | sap training in chennai | Workday training in chennai
 

SAP by definition is also name of the ERP (Enterprise Resource Planing) software as well the name of the company. SAP system comprises of a number of fully integrated modules, which covers virtually every aspect of the business management.
Success factor training in chennai | sap training in chennai | sap training in chennai | Workday training in chennai
 
Java training in Chennai

Just made everything clear Android training in chennai I got into an awesome idea. Will it work IOS training in chennai I think so others may have opinion about that Android training in chennai
 
Java training in Chennai

Why not i should try Android training in chennai Its all in game Java training in Chennai
 
i got lot of information from your blog. I can predict it will be good in future.

Java training in Chennai | Android training in chennai | IOS training in chennai


 
I get a lot of great information from this blog. Thank you for your sharing this informative blog. Just now I have completed hadoop certification course at a leading academy. If you are interested to learn Hadoop Training in Chennai visit FITA IT training and placement academy which offer Big Data Training in Chennai.

 
Java training in Chennai

I read your blog abruptly.. Android training in chennai I can sort out my point clearly because i can support to all my friends IOS training in chennai
 
Nice blog to deal with row quries.its really helpful.Loadrunner Testing Training in Chennai | Software Testing Training in Chennai | QTP Training in Chennai | Selenium Training in Chennai

 
I have joined the oracle Trained in Training in Bangalore. Oracle Trainer will be teaching in practically Manner. So I can Easily able to understand the any Concept. This is very helpful for the interviews Purpose . I would refer my frnds to joined this institute. Visit Us, Oracle Training in Bangalore
 

I have joined the oracle Trained in Training in Bangalore. Oracle Trainer will be teaching in practically Manner. So I can Easily able to understand the any Concept. This is very helpful for the interviews Purpose . I would refer my frnds to joined this institute. Visit Us, Oracle Training in Bangalore
 
Good Collection Information all IT people, Visit Us, Oracle Training in Bangalore
 
Thanks to Share in Oracle FIRST_ROWS vs ALL_ROWS Concept for Freshers and Experience, Visit Us, Visit Us, Oracle Training in Bangalore
 
Java training in Chennai

How it shares to all region. Experiencing new forms Android training in chennai Quite interesting IOS training in chennai
 
Thanks for sharing this niche useful informative post of SAP HCM & ABAP tips to our knowledge, Actually SAP is ERP software that can be used in many companies for their day to day business activities it has great scope in future if anyone wants to take sap training in Chennai get here.
SAP ABAP Training In Chennai | SAP MM Training In Chennai
 
hadoop training in chennai

How it imparts to oracle training in chennai all locale. Encountering new structures oracle dba training in chennai Quite intriguing.
 
Greating from , hadoop training in chennai

How it presents oracle training in chennai to all area. Encountering new structures Quite intriguing.Since we oracle dba training in chennai have made the new tech to plan the expenses hadoop training in chennai yet it coordinates a dab of concordant.
 
This blogs giving the nice and valuable information. Thanks for sharing.
Sap training in chennai | SAP Success factor training in chennai | Workday training in chennai | Success factor training in chennai

 
The information you posted here is useful to make my career better keep updates...If anyone want to get Oracle Training in Chennai, Please visit Training in Bangalore Located at Bangalore which offer best Oracle Training in Bangalore.Visit Us, Oracle Training in Bangalore
 
Thanks for sharing this Information blog. Recently I have completed Oracle Training Courses in Bangalore at a leading Company. It's really useful for me to make a bright Future career. Visit Us, Oracle Training in Bangalore
 
I have joined the oracle Trained in Training in Bangalore. Oracle Trainer will be teaching in practically Manner. So I can Easily able to understand the any Concept. This is very helpful for the interviews Purpose . I would refer my frnds to joined this institute. Visit Us, Oracle Training in Bangalore
 
i am looking for webdesign traning in chennai
Php training in chennai
 
Oracle Training

The information you posted here is useful to make my career better keep updates..If anyone want to become an oracle certified professional reach FITA Oracle Training Institutes in Chennai, which offers Best Oracle Training in Chennai with years of experienced professionals.

 
Your blog is really useful for me, and I gathered some information from this blog. I did SAP Training in Chennai, at FITA Academy. It's really useful for me to make a bright career in IT industry.


 
Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing. PHP Training in chennai | PHP Training chennai | PHP course in chennai | PHP course chennai

 
Hi, I am Martina from Chennai. I am technology freak. I did Android mobile application development course at Fita academy, this is very useful for me to make a bright career in IT industry. So If you looking for best Android Training Institutes in Chennai please visit fita academy.

 
Really awesome blog. Your blog is really useful for me. Thanks for sharing this informative blog. Keep update your blog.

Regards..
Software Testing Training Institutes in Chennai

 
Pretty article! I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing.
Regards,
Salesforce training |Salesforce training in Chennai
 
Thanks for sharing this niche useful informative post to our knowledge, Actually PHP is an open source software that can be used in many companies for their day to day business activities it has great scope in future
Regards,
PHP Training in Chennai|PHP Training Chennai|Best PHP Training in Chennai
 
I ever had seen this information over the blog sites; actually I am looking forward for this information. Here I had an opportunity to read, it was crystal clear keep sharing because I have an expectation about your upcoming post.
Web design training Chennai|Web design training institute in Chennai|Web design courses in Chennai
 
Wow, brilliant article that I was searching for. Helps me a lot, using it in my work. Thanks a ton. Keep writing, would love to follow your posts.
Shashaa
HTML5 Training in Chennai

 
Brilliant article. The information I have been searching precisely. It helped me a lot, thanks. Keep coming with more such informative article. Would love to follow them.
Shashaa
Android Training in Chennai

 
Brilliant article. The information I have been searching precisely. It helped me a lot, thanks. Keep coming with more such informative article. Would love to follow them.
Shashaa
PHP Training in Chennai

 
Brilliant article. Found it helpful. We at fita, provide Java Training in Chennai, along with placement assurance. Reach us if you are interested in Java Training in Chennai. Thank you, Java Training in Chennai
 
Thanks for the article. It has given me some basic ideas of what I have been searching for. Come up with more such articles.
Rithika
Dot Net Training in Chennai | Dot Net Training in Chennai | Dot Net Training in Chennai

 
Really informative blog. Your blog is really useful for me.
 
Thank you for the informative post. It was thoroughly helpful to me. Keep posting more such articles and enlighten us.
Shashaa
Software testing training in Chennai | Software testing training in Chennai | Software testing training in Chennai

 
Hi I’m Rahul doing my final year BE in computer. I have not got placed in any campus recruitment, so planning to go for QTP Training in Chennai. So kindly help me by guiding on where I could do QTP Training in Chennai which also helps in placement services.
 
Thanks for sharing this niche useful informative post to our knowledge, Actually SAP is ERP software that can be used in many companies for their day to day business activities it has great scope in future so do your SAP Training in Chennai
Regards,
SAP course in chennai|SAP ABAP Training Institutes in Chennai|sap training in Chennai
 
Hello, Can anyone help me about the career path and chances in choosing Android? I have planned to do Android Training in Chennai. Someone suggest which is the Android Training Institute in Chennai where I could get placement services also?
 

Thanks for sharing this valuable post to my knowledge; SAS has great scope in IT industry. It’s an application suite that can change, manage & retrieve data from the variety of origin & perform statistical analytic on it
Regards,
sas training in Chennai|sas course in Chennai|sas training institute in Chennai
 
Hi, PHP is a server-side general purpose scripting language used for web development, which is used in almost all personal blogs. It is a very easy to understand language that can be easily learnt with a proper PHP Training in Chennai. You can join a course at FITA, where the best PHP Training in Chennai is taken, and excel as a web developer.
 
Well post, Thanks for sharing this to our vision. In recent day’s customer relationship play vital role to get good platform in business industry, Sales force crm tool helps you to maintain your customer relationship enhancement.
Regards,
Salesforce training institute in Chennai|Salesforce training |Salesforce course in Chennai
 
Thanks for the article. It has given me some basic ideas of what I have been searching for. Come up with more such articles.
Shashaa
Dot Net Training in Chennai | Dot Net Training in Chennai | Dot Net Training in Chennai

 
Thank you for the useful post. It helps a lot.
Raksha
Selenium Training in Chennai | Selenium Training in Chennai

 

The information you have given here is truly helpful to me. CCNA- It’s a certification program based on routing & switching for starting level network engineers that helps improve your investment in knowledge of networking & increase the value of employer’s network...
Regards,
ccna institutes in Chennai|ccna training center in Chennai|ccna training chennai
 
How to choose the Best Android Training Institute in Chennai?
 
Hello, Can anyone help me about the career path and chances in choosing PHP? I have planned to do PHP Training in Chennai. Someone suggest where to join for the best PHP Training in Chennai where I could also get placement services?
 
That is an informative post. Thank you so much.
Shashaa
HTML5 Training in Chennai | HTML5 Training in Chennai

 
How good is it to do an Android Course in Chennai? Can someone suggest?
 
Post a Comment

<< Home

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