Sunday, March 27, 2016

FETCH FIRST N ROWS Vs OPTIMIZE FOR N ROWS in DB2

Very Often we use these two statements in our SQL query,ie, OPTIMIZE FOR N ROWS and FETCH FIRST N ROWS ONLY.
Although it may sound similar,but both of them have specific usage when it comes to the optimizer. This post we will try to see difference between the two.

As the name suggests FETCH FIRST 5 ROWS ONLY, it actually puts a limitation on the number of rows the query is returning.I am assuming here n=5. So, here user gets only 5 rows even though there can be 100 qualifying rows for that query.

OPTIMIZE FOR 5 ROWS will straight way influence the optimizer.It does not limit the number of rows SQL will return. So using this , we will get all the qualifying rows,may be incrementally.Intent here is to give priority to retrieve first few rows.Once optimizer understands this ,it will give preference to access plan that will minimize the response time.So,DB2 will send the rows in a single block. Consider the below scenario where this will come handy.

You have an online application where the screen can hold details of 5 customers at a time. Suppose you have the below query to fetch the customer details

Select Fname, Lname, Dept, Sal,Rollno
  From Dept table 
Order by Sal Desc;

We are assuming the index to be defined on Rollno. If a descending index also exists on SAL column, its likely to have low cluster ratio.
Without using the OPTIMIZE clause, DB2 will do a full table space scan and sort on sal column to fetch you the details.

Now on Adding  'ÓPTIMIZE FOR 5 ROWS' to the query, DB2 will use SAL index directly because it knows you need  the details of 5 highest paid employees.
So, it displays 5 rows to the screen and depending on user input will process the  next 5 or do something else.Here DB2 encourage matching index scan and would not prefer list or sequential prefetch. Access plan selected for 5 records may not be good for 100 rows.

Thus, this OPTIMIZE clause comes handy for queries where you can process incrementally.We can use it to get rows directly from table without going through any buffering operations like sorting. 

34 comments:

  1. Replies
    1. IEEE Final Year Project centers make amazing deep learning final year projects ideas for final year students Final Year Projects for CSE to training and develop their deep learning experience and talents.

      IEEE Final Year projects Project Centers in India are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes, while specialists like the enjoyment in interfering with innovation.

      corporate training in chennai corporate training in chennai

      corporate training companies in india corporate training companies in india

      corporate training companies in chennai corporate training companies in chennai

      I have read your blog its very attractive and impressive. I like it your blog. Digital Marketing Company in Chennai

      Delete
  2. Hello. This post couldn’t be written any better! Reading this post reminds me of my previous roommate. He always kept chatting about this. I will forward this page to him. Fairly certain he will have a good read. Thank you for sharing.
    AWS Training in Bangalore |Best AWS Training Institute in Bangalore BTM, Marathahalli
    AWS Training in Chennai | AWS Training Institute in Chennai Velachery, Tambaram, OMR

    ReplyDelete
  3. Nice post. By reading your blog, i get inspired and this provides some useful information. Thank you for posting this exclusive post for our vision. 
    python interview questions and answers | python tutorials

    ReplyDelete
  4. I found your blog while searching for the updates, I am happy to be here. Very useful content and also easily understandable providing.. Believe me I did wrote an post about tutorials for beginners with reference of your blog. 
    Devops Training courses
    Devops Training in Bangalore
    Best Devops Training in pune
    Devops interview questions and answers

    ReplyDelete
  5. A universal message I suppose, not giving up is the formula for success I think. Some things take longer than others to accomplish, so people must understand that they should have their eyes on the goal, and that should keep them motivated to see it out til the end.

    Java training in Marathahalli | Java training in Btm layout

    Java training in Marathahalli | Java training in Btm layout

    ReplyDelete
  6. This is most informative and also this post most user friendly and super navigation to all posts... Thank you so much for giving this information to me.. 

    best rpa training in chennai | rpa online training |
    rpa training in chennai |
    rpa training in bangalore
    rpa training in pune
    rpa training in marathahalli
    rpa training in btm

    ReplyDelete
  7. Have you been thinking about the power sources and the tiles whom use blocks I wanted to thank you for this great read!! I definitely enjoyed every little bit of it and I have you bookmarked to check out the new stuff you post

    devops online training

    aws online training

    data science with python online training

    data science online training

    rpa online training

    ReplyDelete
  8. Interesting Post. It is really Nice and lovely post. keep it up. keep blogging. otherwise anyone wants to learn SAS training course
    IBM Training in Bangalore

    ReplyDelete
  9. Are you looking for Big Data training in Chennai with placement opportunities? Then we, Infycle Technologies are with you to make your dream into reality. Infycle Technologies is one of the best Big Data Training Institute in Chennai, which offers various programs along with Big Data such as Oracle, Java, AWS, Hadoop, etc., in complete hands-on practical training with trainers, those are specialists in the field. In addition to the training, the mock interviews will be arranged for the candidates, so that they can face the interviews with the best knowledge. Of all that, 100% placement assurance will be given here. To have the words above in the real world, call 7502633633 to Infycle Technologies and grab a free demo to know more.Big Data Training in Chennai

    ReplyDelete
  10. Sharing the same interest, Infycle feels so happy to share our detailed information about all these courses with you all! Do check them out
    big data training in chennai & get to know everything you want to about software trainings.

    ReplyDelete
  11. Want to study an Oracle course with job opportunities? Infycle is with you for this! Infycle Technologies gives the most trustworthy and the best Oracle DBA training in Chennai, which will be guided by professional tutors in the field. Along with that, the mock interviews will be assigned for the candidates, so that they can meet the job interviews with full confidence. To transform your career to the next level, call 7502633633 to Infycle Technologies and grab a free demo to get more.
    https://infycletechnologies.com/oracle-dba-training-in-chennai

    ReplyDelete
  12. Learn Amazon Web Services for making your career as a shining sun with Infycle Technologies. Infycle Technologies is the best AWS training center in Chennai, providing complete hands-on practical training of professional specialists in the field. In addition to that, it also offers numerous programming language tutors in the software industry such as Oracle, Java, Python, AWS, Hadoop, etc. Once after the training, interviews will be arranged for the candidates, so that, they can set their career without any struggle. Of all that, 200% placement assurance will be given here. To have the best career, call 7502633633 to Infycle Technologies and grab a free demo to know more.

    ReplyDelete
  13. Grab the extraordinary Oracle Course with PLSQL from Infycle Technologies, the best software training institute in Chennai. Infycle offers the Best Oracle PLSQL Training in Chennai, with various IT demanding courses such as Big Data, Python, DevOps, Selenium, Full-Stack development, etc., in complete hands-on practical training with professional tutors in the field. In addition to that, the mock interviews will be done for the candidates so that they can face the interviews with total confidence. To have all these within your hands, call 7502633633 for having a free demo.

    ReplyDelete
  14. Train yourself in specific software modules to brush up your skills & shine in your career growth with the best software training institute in Chennai, Infycle Technologies. Infycle offers the Best Data Science Training in Chennai to serve the candidate's job profile requirements, including the top job placements in the MNC's. Rather than just teaching the theories, our fundamental aim is to make you a master by giving you live hands-on training. Therefore, individuals will be asked to work on the live tasks & real-time use cases that bring out the definite coder in you! To grab all these, call 7502633633 for a free demo.

    ReplyDelete
  15. Infycle Technologies, the best software training institute cum placement center in Chennai offers the No.1 Digital Marketing course in Chennai for freshers, students, and tech professionals at the best offers. In addition to the Digital Marketing Training, other in-demand courses such as AWS, DevOps, Data Science, Python, Selenium, Big Data, Java, Power BI, Oracle will also be trained with 100% practical classes. After the completion of training, the trainees will be sent for placement interviews in the top MNC's. Call 7504633633 to get more info and a free demo. No.1 Digital Marketing Course in Chennai | Infycle Technologies

    ReplyDelete
  16. Grab the Digital Marketing Training in Chennai from Infycle Technologies, the best software training institute, and Placement center in Chennai which is providing professional software courses such as Data Science, Artificial Intelligence, Cyber Security, Big Data, Java, Hadoop, Selenium, Android, and iOS Development, DevOps, Oracle etc with 100% hands-on practical training. Dial 7502633633 to get more info and a free demo and to grab the certification for having a peak rise in your career.

    ReplyDelete