Wednesday, October 18, 2006

Use hints like FIRST_ROWS(n)

Usually queries used for search fetch lots of records if proper search condition is not provided. If the query is fetching thousands of rows, that will be of no use for the end user. In such scenario, no of records should be limited by using ROWNUM <>/*+ FIRST_ROWS (n) */ should be used, where n is no of rows required.


Note: Hint /*+ FIRST_ROWS */ does not know the scope of the query and generally favours index access over full-table scans. Therefore /*+ FIRST_ROWS (n) */ should be used instead of /*+ FIRST_ROWS */.

Lets do a small experiment:

DROP TABLE t;

CREATE TABLE t AS SELECT ROWNUM id, ao.* FROM ALL_OBJECTS ao;

ANALYZE TABLE t
COMPUTE statistics;

SET timing on
SET autotrace traceonly
spool first_rows

prompt 'SELECT * FROM t;'
SELECT * FROM t;

prompt 'SELECT /*+ FIRST_ROWS */ * FROM t;'
SELECT /*+ FIRST_ROWS */ * FROM t;

prompt 'SELECT /*+ FIRST_ROWS_100 */ * FROM t;'
SELECT /*+ FIRST_ROWS_100 */ * FROM t;

prompt 'SELECT /*+ FIRST_ROWS(100) */ * FROM t;'
SELECT /*+ FIRST_ROWS(100) */ * FROM t;

prompt 'SELECT * FROM t WHERE ROWNUM <>
SELECT * FROM t WHERE ROWNUM <>

spool off
exit


And the results are:

CASE 1:
'SELECT * FROM t;'
88430 rows selected.
Elapsed: 00:00:03.04
Cost = 286

CASE 2:
'SELECT /*+ FIRST_ROWS */ * FROM t;'
88430 rows selected.
Elapsed: 00:00:03.03
Cost = 286

CASE 3:
'SELECT /*+ FIRST_ROWS_100 */ * FROM t;'
88430 rows selected.
Elapsed: 00:00:03.02
Cost = 286

CASE 4:
'SELECT /*+ FIRST_ROWS(100) */ * FROM t;'
88430 rows selected.
Elapsed: 00:00:03.02
Cost = 4

CASE 5:
'SELECT * FROM t WHERE ROWNUM <>
100 rows selected.
Elapsed: 00:00:00.00
cost = 4

After looking at above results, it is clear that the best option is using ROWNUM <>

36 comments:

Anonymous said...

CASE 5 IS WRONG!
Try this one:
SELECT * FROM t WHERE ROWNUM <= 100

Cba said...

Yes case 5 is wrong it only select 100 lines !

Anonymous said...

Yes ... I think you missed the point of the FIRST_ROWS hint. This hint optimizes the query to return the first (n) rows as quickly as possible and still return all the rows. The ROWNUM <= 100 approach you listed only returns the first 100 rows. Very different.

Anonymous said...

I am reading this article second time today, you have to be more careful with content leakers. If I will fount it again I will send you a link

free real incest stories said...

My mind found its own peace in a very pleasant dream, where Melvinwas shaking the bed, but this Melvin was six feet tall and built likea lumberjack. Julie whispered to him.
true fuck stories
free sex stories true
under age incest stories free
stories bisexual first time kristen
sexy romance stories
My mind found its own peace in a very pleasant dream, where Melvinwas shaking the bed, but this Melvin was six feet tall and built likea lumberjack. Julie whispered to him.

Anonymous said...

What's up, this weekend is fastidious designed for me, as this occasion i am reading this wonderful educational piece of writing here at my house.

My blog post: calories burned calculator

Anonymous said...

Wow! Finally I got a webpage from where I can actually get
useful facts concerning my study and knowledge.


Visit my blog - acne treatment

Anonymous said...

This information is invaluable. How can I find out more?


Here is my web-site :: maleextra online

Anonymous said...

I seldom write comments, however after reading a few of the remarks here
"Use hints like FIRST_ROWS(n)". I actually do have 2
questions for you if it's allright. Could it be simply me or does it give the impression like some of these remarks look like they are left by brain dead individuals? :-P And, if you are writing on additional online social sites, I would like to follow anything new you have to post. Would you make a list of all of all your social community pages like your Facebook page, twitter feed, or linkedin profile?

Here is my webpage; semenax

Anonymous said...

I have read so many posts regarding the blogger lovers however this post is in fact a fastidious post, keep it
up.

My blog buy genf20

Anonymous said...

I do agree with all the ideas you've introduced on your post. They're very convincing and will certainly work.
Still, the posts are very brief for beginners. May you please extend them a bit from next time?

Thanks for the post.

Also visit my homepage http://www.cosmicrew.com

Anonymous said...

I'm impressed, I have to admit. Rarely do I encounter a blog that's both equally educative
and engaging, and let me tell you, you've hit the nail on the head. The problem is something not enough people are speaking intelligently about. I am very happy I came across this in my hunt for something concerning this.

My page ... volume pills scam

Anonymous said...

hi!,I love your writing very a lot! proportion we be in contact extra approximately your post
on AOL? I require a specialist on this space to unravel my problem.
Maybe that is you! Taking a look ahead to see you.

my web page; maxoderm

Anonymous said...

Hurrah! After all I got a web site from where I
be capable of genuinely get helpful data regarding my
study and knowledge.

Feel free to surf to my webpage ... meratol uk

Anonymous said...

I don't know if it's just me or if everybody else encountering issues with your site.

It looks like some of the written text within your posts are running off the
screen. Can someone else please comment and let me know if this is happening to
them too? This may be a issue with my web browser because I've had this happen before. Many thanks

Here is my website - buymiroverve.eklablog.com

Anonymous said...

I delight in, result in I found just what I used to be taking a look for.
You've ended my 4 day long hunt! God Bless you man. Have a nice day. Bye

Also visit my web blog: www.friendtrap.com

Anonymous said...

Hello colleagues, how is the whole thing, and what you wish for to say on the topic of this piece of writing, in my view its truly awesome designed for
me.

my site :: fungal toenail infections

Anonymous said...

Keep on writing, great job!

Also visit my weblog - volume pills review

Anonymous said...

Howdy, i read your blog from time to time and i
own a similar one and i was just curious if you get a lot of spam
responses? If so how do you prevent it, any plugin or
anything you can suggest? I get so much lately it's driving me mad so any help is very much appreciated.

my homepage Buy Semenax

Anonymous said...

Peculiar article, just what I was looking for.


Feel free to visit my web page - growth hormone treatment

Anonymous said...

Wow, this post is pleasant, my sister is analyzing such things, thus I am going to
convey her.

Look into my homepage http://www.tengensolar.com/

Anonymous said...

Stunning story there. What occurred after?
Good luck!

Here is my page - growmax male enhancement

Anonymous said...

Thank you for some other great article. The place
else could anyone get that kind of information in such a perfect manner of
writing? I have a presentation next week, and I'm on the look for such info.

my web-site - the best natural male enhancement pills

Anonymous said...

Hey there, You have done a fantastic job. I'll certainly digg it and personally suggest to my friends. I am confident they will be benefited from this site.

my blog post: http://undergroundcooking.blogspot.com/2008/11/get-job-pfffff.html

Anonymous said...

I've been surfing online more than 3 hours today, yet I never found any interesting article like yours. It is pretty worth enough for me. In my opinion, if all webmasters and bloggers made good content as you did, the web will be much more useful than ever before.

Here is my web site - thyromine

Anonymous said...

Occasionally seasons reasons some shoes is possibly[url=http://niketrainersuksale.webeden.co.uk]http://niketrainersuksale.webeden.co.uk[/url]
fleetingly will not press the chance to[url=http://tomscanadaoutlet.snappages.com]Toms Canada[/url]
survive such shoes we get to[url=http://addnikecanadastore.snappages.com]Nike Shoes Canada[/url]
rightly double-cross them through the[url=http://louboutintrainersuk.blog.co.uk]Christian Louboutin UK[/url]
right put in in to avoid superfluous wickedness to shoes[url=http://salelouisvuittonuk.webeden.co.uk]Louis Vuitton Bags[/url]

Influence be a insole is basically bad, this effect need to use a quiet bracken and sea water gently scrub. To be proper acclaimed that, don't manipulate chemical cleaning agents cleaning, or these individuals strength requite current the insole boundary of material off.[url=http://shoppradabagsuk.webeden.co.uk]Prada Outlet[/url]

Anonymous said...

Excellent blog you have got here.. It's hard to find high quality writing like yours these days. I really appreciate individuals like you! Take care!!

Also visit my web site: best home remedy for hemorrhoids

Anonymous said...

Superb, what a webpage it is! This webpage presents helpful facts to us,
keep it up.

Also visit my blog: virility ex

Anonymous said...

What's Happening i am new to this, I stumbled upon this I've found It
positively helpful and it has aided me out loads. I hope to give a contribution & aid different customers
like its helped me. Great job.

my blog post: http://comoaumentarpeniano.com/">maneiras

Anonymous said...

wonderful submit, very informative. I wonder why the opposite experts
of this sector do not notice this. You should continue your writing.
I am sure, you have a great readers' base already!

Feel free to visit my blog ... aumento peniano

Anonymous said...

This info is priceless. When can I find out more?

My webpage :: provillus treatment

Anonymous said...

Hey! Would you mind if I share your blog with my facebook group?
There's a lot of people that I think would really enjoy your content. Please let me know. Many thanks

Feel free to visit my web blog :: virility ex in faisalabad

Anonymous said...

I'm truly enjoying the design and layout of your blog. It's a very easy on the eyes which
makes it much more pleasant for me to come here and visit more often.

Did you hire out a designer to create your theme? Fantastic work!


my web page http://phen375truth.net

Anonymous said...

Hello! Would you mind if I share your blog with my myspace group?

There's a lot of people that I think would really appreciate your content. Please let me know. Thanks

Feel free to surf to my page; buy hgh enrgizer

Anonymous said...

Hello there, I found your web site via Google at the
same time as searching for a comparable subject, your web site got here up, it appears good.
I have bookmarked it in my google bookmarks.
Hi there, simply turned into aware of your weblog via Google, and located that it is truly informative.

I'm going to be careful for brussels. I will appreciate should you continue this in future. A lot of folks will likely be benefited from your writing. Cheers!

My web site: hoodia Diet Patches

Anonymous said...

Hey there! This is my first visit to your blog! We are a group
of volunteers and starting a new initiative in a community in the same niche.
Your blog provided us useful information to work on.
You have done a extraordinary job!

my webpage: proactol