Wednesday, December 13, 2006

COUNT(*) Vs. COUNT(1)

The debat COUNT(*) vs. COUNT(1) is on for years now. So I thought of doing experiment on the same and here are the result:

I have done the test for indexed table as well as non-indexed table. The table has 6 millions of rows in both the tables and the rowwidth is approx 4000 bytes.

1. COUNT(1) is internally converted to COUNT(*).

SQL> EXPLAIN PLAN FOR
2 select max(REG_COUNT) from f_reg
3 group by MG_BKEY
4 having COUNT(1) > 1;

Explained.

SQL> SET LINESIZE 130
SET PAGESIZE 0
SELECT * FROM table(DBMS_XPLAN.DISPLAY);
SQL> SQL> Plan hash value: 272420531

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 50738 (5)| 00:10:09 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 1 | 6 | 50738 (5)| 00:10:09 |
| 3 | TABLE ACCESS FULL| F_REG | 6632K| 37M| 49776 (4)| 00:09:58 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(COUNT(*)>1)

15 rows selected.

The DBMS_XPLAN gives you the plan and predicates used by oracle to execute a query. You can see above that the filter "COUNT(1) > 1" is converted to "COUNT(*) > 1".

2. There is no difference in COUNT(*) and COUNT(1).

Case a: Test on indexed table

set autotrace traceonly
select count(1) from f_reg;

Execution Plan
----------------------------------------------------------
Plan hash value: 622071604

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4429 (3)| 00:00:54 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| F_REG_PK | 6632K| 4429 (3)| 00:00:54 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
19808 consistent gets
0 physical reads
0 redo size
414 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


select count(*) from f_reg;

Execution Plan
----------------------------------------------------------
Plan hash value: 622071604

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4429 (3)| 00:00:54 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| F_REG_PK | 6632K| 4429 (3)| 00:00:54 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
19808 consistent gets
0 physical reads
0 redo size
414 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


Case b: Test on non-indexed table

set autotrace traceonly
select count(1) from f_reg_1_bkup;

Execution Plan
----------------------------------------------------------
Plan hash value: 2458129084

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48989 (1)| 00:09:48 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| F_REG_1_BKUP | 6632K| 48989 (1)| 00:09:48 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
330 recursive calls
0 db block gets
221666 consistent gets
176232 physical reads
0 redo size
414 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed



select count(*) from f_reg_1_bkup;

Execution Plan
----------------------------------------------------------
Plan hash value: 2458129084

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48989 (1)| 00:09:48 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| F_REG_1_BKUP | 6632K| 48989 (1)| 00:09:48 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
221585 consistent gets
175908 physical reads
0 redo size
414 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

You can see, there is no difference at all in statistics or timings in case of indexed table, whereas in case of non-indexed table there is a slight difference in consistenet gets which is less in case of COUNT(*) and no difference in elapsed time.

P.S. The tests were carried out in Oracle 10g.

26 comments:

C.G. said...

Hi,

Do you have an efficient way of doing a count on a table which has millions of rows?

Thanks.

Anonymous said...

uxrarudvzztzmmeblqrq, http://yahooscanner.net yahoo Scanner, JaxGgnB.

Anonymous said...

ӏ've been exploring for a little bit for any high quality articles or weblog posts on this kind of house . Exploring in Yahoo I eventually stumbled upon this web site. Studying this information So i'm happу to
еxhibit that Ι have аn incrеdiblу just гight unсanny feeling I dіscoѵereԁ exactlу what I needed.
Ι so much unqueѕtіοnаbly will
maκе certain to dо not fаil to rеmember this web site and ргoviԁes іt a loοκ οn a rеlentless basiѕ.


Also vіѕit my web-ѕite - http://www.sfgate.com

Anonymous said...

Hello! This is my first visit to your blog! We are a collection of volunteers
and starting a new project in a community in the same niche.
Your blog provided us beneficial information to work on.
You have done a wonderful job!

Here is my blog :: strec

Anonymous said...

Hi there! This post couldn't be written any better! Reading through this post reminds me of my good old room mate! He always kept chatting about this. I will forward this write-up to him. Pretty sure he will have a good read. Thanks for sharing!

Also visit my website ... one
Also see my web page: doze

Anonymous said...

I’m not that much of a online reader to be honest but your sites really nice, keep
it up! I'll go ahead and bookmark your website to come back in the future. Many thanks

My web page: stumps tree

Anonymous said...

Hi. I noticed your site title, "Blogger: All about Oracle" doesn't really reflect the content of your web-site. When creating your website title, do you think it's most beneficial to write it for Web optimization or
for your viewers? This is something I've been battling with because I want great rankings but at the same time I want the best quality for my website visitors.

my webpage - removal chemical
Also see my webpage - http://chattershots.beep.com

Anonymous said...

Wow, fantаstic ωeblog formаt! How long have you been running
a blog for? you madе blogging look easy. Τhe total look of your
website іs fantastіc, let alone thе content!



my weblоg; www.teethwhiteningreviews.com
my webpage - click through the up coming webpage

Anonymous said...

We have gone ahead and added a backlink back to your
website from one of my clientele requesting it. We have used your website URL: http:
//www.blogger.com/comment.g?blogID=35222389&postID=5487918918548682381 and blog title:
Blogger: All about Oracle to be sure you get the proper anchor text.

If you woud like to see where your hyperlink has been placed, please e mail me at:
amieartis@inbox.com. Thank you

Also visit my website :: strec

Anonymous said...

Hi there! I was curious to know if setting up a website such your own: http://www.
blogger.com/comment.g?blogID=35222389&postID=5487918918548682381 is hard to do for unskilled people?
I've been hoping to set up my own website for a while now but have been turned off mainly because I've always
assumed it demanded tons of work. What do you think?
Thanks alot :)

My web blog :: site

Anonymous said...

I know this if off topic but I'm looking into starting my own blog and was curious what all is needed to get set up? I'm assuming having a blog like yours would cost a pretty penny?
I'm not very web smart so I'm not 100% positive. Any tips or advice would be greatly appreciated. Thanks

Feel free to visit my blog; comp
my website :: link

Anonymous said...

Currently it seems likе Woгdpresѕ is the best blogging plаtform avаilаble right noω.
(from what I've read) Is that what you are using on your blog?

Feel free to visit my homepage - Next page

Anonymous said...

May I simply just say what a comfort to uncover somebody that actually
knows what they are talking about online. You certainly know how to bring a problem to light and make it important.
More people must read this and understand this side of
your story. I was surprised that you are not
more popular given that you definitely have the gift.

Look at my weblog ... http://newserviceonline.tk

Anonymous said...

I'm extremely impressed with your writing skills as well as with the layout on your blog. Is this a paid theme or did you customize it yourself? Anyway keep up the excellent quality writing, it is rare to see a great blog like this one these days.

Also visit my web-site; http://www.sfgate.com/business/prweb/article/V2-Cigs-Review-Authentic-Smoking-Experience-or-4075176.php

Anonymous said...

Every weekend i used to pay a visit this web page, because i want enjoyment, as
this this website conations in fact nice funny
data too.

Here is my web blog ... Bmr calculator

Anonymous said...

Hey there, I thinκ your sitе might be havіng
browser compаtibіlity iѕѕues.
When I look at yοur blog in Opeга,
it loοκs finе but when opening in Internеt Exploreг, іt hаs
some oѵerlаpрing. І just wanted to gіve
уou a quiсk heads up! Othеr then that, gгeаt blog!


Τaκе a lоok at my web-ѕitе - crearfacebook.Webs.com

Anonymous said...

Most modern-day mole removing methods are unable to avoid that.
For illustration, you should really make positive that
what you want to clear away is indeed a tag.

Feel free to visit my site; dermatend cream

Anonymous said...

Howdy would you mind stating which blog platform you're using? I'm
looking to start my own blog in the near future but I'm having a hard time deciding between BlogEngine/Wordpress/B2evolution and Drupal. The reason I ask is because your layout seems different then most blogs and I'm looking for something unique.
P.S My apologies for getting off-topic but I had to ask!


My page ... Advokati u Zenici

Anonymous said...

This design is spectacular! You obviously know how to keep a reader amused.
Between your wit and your videos, I was almost moved to start my own blog (well, almost.
..HaHa!) Excellent job. I really enjoyed what you had
to say, and more than that, how you presented it. Too cool!


my homepage - trees stump

Anonymous said...

Eхcellеnt post. I'm experiencing some of these issues as well..

Look at my blog crear facebook

Anonymous said...

I’m not that much of a online reader to be honest but your blogs really nice, keep
it up! I'll go ahead and bookmark your website to come back down the road. Cheers

My web site :: dash diet for high blood pressure

Anonymous said...

continuously i used to read smaller articles or reviews that
also clear their motive, and that is also happening with this article which
I am reading at this place.

My webpage golf vii []

Anonymous said...

Attractive part of сontent. I sіmply stumbled
upοn уour web ѕіte and in аccesѕion capital to assert
that I get іn fасt lοved account
your weblog posts. Аny waу Ι'll be subscribing to your augment or even I achievement you get entry to constantly fast.

my weblog buy herbal incense

Anonymous said...

When I originally left a comment I appear to have clicked the -Notify me when new comments are added- checkbox and from now on
every time a comment is added I recieve four emails with the same comment.
Is there an easy method you are able to remove me from that service?
Appreciate it!

Here is my weblog; household bank

Anonymous said...

Aftеr going οver a hаnԁful of the articles оn your ωeb
site, I truly apprecіatе your tеchnіque of writing a blοg.
Ι added it to mу bookmark ѕite list and will be checking back in the near future.
Plеase сheck out my website as well аnd
let me know your oрinion.

Feel freе to viѕit my web-site ..
. herbal highs that work

Unknown said...

Wow thank you very much for this creative content, this has really helped me a lot,specially first post of this site.
i was at this confused point in my 6 Minutes to Skinny but after reading,this gave me more idea about how better my life could be.
Thank you very much for the effort and kindly keep posting something like this every time, it goes a long way to help people like us.
I also got help from another cool site Filtered Review like your too called Perfect Filtered Review which describes a lot about handling health. Thank you very much and I am gonna be here often.