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.
good...
ReplyDeleteRead all the information that i've given in above article. It'll give u the whole idea about it.
ReplyDeleteangularjs-Training in annanagar
angularjs Training in chennai
angularjs Training in chennai
angularjs Training in online
angularjs Training in bangalore
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.
ReplyDeletepython interview questions and answers | python tutorials
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.
ReplyDeleteJava training in Marathahalli | Java training in Btm layout
Java training in Marathahalli | Java training in Btm layout
Very good brief and this post helped me alot. Say thank you I searching for your facts. Thanks for sharing with us!
ReplyDeleteData Science course in rajaji nagar | Data Science with Python course in chenni
Data Science course in electronic city | Data Science course in USA
Data science course in pune | Data science course in kalyan nagar
thanks for your post sql course
ReplyDeleteAre 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
ReplyDeleteWant 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.
ReplyDeletehttps://infycletechnologies.com/oracle-dba-training-in-chennai
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.
ReplyDeleteIf salary is your only income means. Simply think to make it HIGH by getting into AWS training institute in Chennai, Infycle. It will be so easier for you because, past 15 years software industry Infycle leads a 1 place for giving a best students in IT industry.
ReplyDeleteworld777 betting
ReplyDeleteflats under 15 lakh
best coaching classes for class 12 in gurgaon
kurti pant set
kurti pant set with duppatta
azure firewall
azure blueprints
azure resource group
azure application gateway
azure express route
perde modelleri
ReplyDeletesms onay
VODAFONE MOBİL ÖDEME BOZDURMA
nft nasıl alınır
ANKARA EVDEN EVE NAKLİYAT
trafik sigortası
dedektör
KURMA WEB SİTESİ
ASK KİTAPLARİ
smm panel
ReplyDeletesmm panel
iş ilanları
instagram takipçi satın al
Https://www.hirdavatciburada.com
beyazesyateknikservisi.com.tr
servis
tiktok jeton hilesi
Good content. You write beautiful things.
ReplyDeletekorsan taksi
hacklink
mrbahis
vbet
sportsbet
taksi
sportsbet
hacklink
vbet
Success Write content success. Thanks.
ReplyDeletebetmatik
kıbrıs bahis siteleri
deneme bonusu
canlı poker siteleri
betturkey
canlı slot siteleri
betpark
aydın
ReplyDeletebayrampaşa
bilecik
bingöl
çekmeköy
USA2