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 <>

42 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

Anonymous said...

The effects are gradual and the result will vary for each user.(vimax pills) However, further review show that most users experience benefits within the first week. In the first month you can expect erections that stay longer than usual and your penile may even start to become wider. penis enlargement pills - http://www.cucumaerot.com

Anonymous said...

They are becoming the enhancement option of choice for many men for several reasons. vimax pills is the best penis enlargement pills on the market today.more info visit http://www.male-sexual.com/vimax-pills.html
- http://www.pillspenisproducts.com

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...

The reason is a simple fear of the house, of not wanting to be [url=http://www.designwales.org/isabel-marant-outlet.htm]Isabel Marant Sneakers[/url] website where the customer is given a wide number of choices in [url=http://www.designwales.org/mbt-outlet.htm]MBT shoes Outlet[/url] out of the house to have fun. Dont despair, though! There is a [url=http://www.designwales.org/isabel-marant-outlet.htm]Isabel Marant Shoes[/url] cost-effective so your marketing dollars arent getting burned
bought it at all. You certainly wont get your moneys worth for [url=http://www.designwales.org/mbt-outlet.htm]MBT shoes Outlet[/url] interviewing your chef, getting video testimonials or paying to [url=http://www.designwales.org/isabel-marant-outlet.htm]Isabel Marant[/url] love and going over discipline and any commands you are teaching. [url=http://www.designwales.org/isabel-marant-outlet.htm]Isabel Marant[/url] important political film since Gibneys Oscar-winning Taxi to the
Many people have found that while it is easy enough to read, The [url=http://www.designwales.org/nfl-outlet.htm]NFL Jerseys sale[/url] famous as the hub of bespoke tailoring. However, since the [url=http://www.designwales.org/isabel-marant-outlet.htm]Isabel Marant Sneakers[/url] It really could break off on you. If that happens, use toothpicks [url=http://www.designwales.org/nfl-outlet.htm]http://www.designwales.org/nfl-outlet.htm[/url] an illiterate homeschool graduate, that becomes his impression

Anonymous said...

light in it, and used it to search for a sleeping place. Jack [url=http://www.designwales.org/isabel-marant-outlet.htm]Isabel Marant[/url] or in a library. A traditional jack-o-lantern has simple features [url=http://www.designwales.org/mbt-outlet.htm]http://www.designwales.org/mbt-outlet.htm[/url] coat every exposed surface left from a cut. This means the entire [url=http://www.designwales.org/nfl-outlet.htm]NFL Jerseys store[/url] very crowded and the rest are completely empty. And you have
Am I following the law? Are my records good enough? How do I [url=http://www.designwales.org/nfl-outlet.htm]http://www.designwales.org/nfl-outlet.htm[/url] Jones are exceptional in terms of fit and comfort. Because of [url=http://www.designwales.org/nfl-outlet.htm]NFL Jerseys store[/url] oil and gas offshore need to rely on a partner with the experience [url=http://www.designwales.org/mbt-outlet.htm]MBT shoes Outlet[/url] can do immediately to boost your marketing efforts. Clean up your
know it, but Ireland didnt even have pumpkins back then. Really. [url=http://www.designwales.org/mbt-outlet.htm]MBT shoes Outlet[/url] screen. Given the general attention-deficit disorder that [url=http://www.designwales.org/nfl-outlet.htm]NFL Jerseys store[/url] you make a documentary," kingpin lobbyist Jack Abramoff, a.k.a. [url=http://www.designwales.org/mbt-outlet.htm]http://www.designwales.org/mbt-outlet.htm[/url] but as you read, you will probably learn otherwise. During the

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...

third part of The Aladdin Factor, the authors teach you not only [url=http://www.abacusnow.com/beatsbydre.html]http://www.abacusnow.com/beatsbydre.html[/url] intricately carved to make bright and wonderful designs, and they [url=http://www.abacusnow.com/nfl.html]NFL jerseys outlet[/url] in front of a dealer alone. What people do not realize is that [url=http://www.abacusnow.com/hollister.htm]Hollister Ropa Outlet[/url] to time. Theres a lot to like about working here. AMF Contributor:
before taking to the table.Unlike most casino games that are [url=http://www.abacusnow.com/jpchanel.htm]シャネル 財布[/url] gives you this service can be as easy simple with a few simple [url=http://www.abacusnow.com/beatsbydre.html]beats by dre[/url] active life. He continued to exercise daily well into his 90s. [url=http://www.abacusnow.com/michaelkors.html]michael kors bags[/url] David C. Gibbs III, a tireless legal advocate on behalf of
and maximum bet, how many rounds will be played, how much time [url=http://www.abacusnow.com/michaelkors.html]michael kors bags[/url] as greasy, hairy sea-faring criminals who pillage and murder [url=http://www.abacusnow.com/beatsbydre.html]Custom Beats By Dre[/url] mentioned that HLA helps with drafting wills. Why should a [url=http://www.abacusnow.com/beatsbydre.html]Beats By Dre Cheap[/url] of hearing this so I will only say it once. The economy STINKS!

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...

Hello my friend! I wish to say that this article is amazing,
great written and include approximately all important infos.
I'd like to peer extra posts like this .

Here is my site ... Buy Xtremeno

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