A reader, July 09, 2002 - 3:31 pm UTC
Say it ain't so!
Mark A. Williams, July 09, 2002 - 3:57 pm UTC
"A Reader" rates it 1 star...
Why can't people be bothered to use the search facility, do you think? It isn't exactly hard to find or use... same reason they don't read the Oracle docs?
Maybe you should put a link to the Oracle docs online on your main page? But, would it help?
Anyway, another unsolicited $0.02...
- Mark
Perfect Answer
Jan, July 09, 2002 - 8:26 pm UTC
to reader 1 and others like he:
This page is also a kind of Information System. It means - System about information, not just a storage of anything. Search before. If something is already responded, don`t ask again.
Use 'modular approach' - reusability.
Thank you Tom
Kiran, July 09, 2002 - 11:37 pm UTC
Hi Tom,
I have went through the series of Q/A's resulted due to the search which you suggested. I have found it very useful. Also, I realize that Searching the forum is important before I post.
I expected a different explaination, i.e in terms of the way count(*) and count(1) are executed i.e how blocks are read in both cases. Please do respond if you feel its worth.
Thanks
kiran
Some comments
Karl, July 10, 2002 - 9:21 am UTC
Tom,
Thank you for this very useful site.
As for the Original question, I think they were more interested in having the pleasure of posting it than really asking the question itself.
However, it's needless to say that some myths are almost unbreakable. And some are even written down and signed off by Oracle, for example in Oracle Press books. So, don't blame us poor mortals for being confused sometimes.
For example, Mr. Jason Couchman in his Oracle8i Certification Exam Guide objectively states:
- "Use COUNT(1) instead of COUNT(*) because of its performance benefits."
- "The ORDER BY columns have to be in the SELECT clause."
- "The password is encrypted and stored in the Oracle database"
... among others.
July 10, 2002 - 12:24 pm UTC
VBG
they are of course all wrong (and the second one is trivial to prove wrong, cannot imagine how that got past the editors!)
A reader, July 10, 2002 - 9:40 am UTC
In the above article, there is an unresponded post by Dulimin in China where he posted trace details of count(1) and count(*) on 8.1.7. Curious to know your response to it. Thanks.
July 10, 2002 - 12:39 pm UTC
write it off to a side effect of the cartesian product.
How is password stored in the database?
A reader, July 10, 2002 - 3:47 pm UTC
This is related to Karl's third observation.... How is the password stored then, I mean what is wrong in that statement?
A reader, July 10, 2002 - 3:50 pm UTC
Hi Tom,
I've seen in varous environments where, when a count(1) was changed to count(*), the performance improved dramatically, sometimes by a hundred fold. What would be the possible reasons for this?
thanks
July 10, 2002 - 4:12 pm UTC
i give up, use count(*) ;)
It is what I use.
The old "myth" was the count(1) was faster, better -- that you should avoid count(*).
Use count(*), it is more appropriate anyway.
Gee !
A reader, July 10, 2002 - 4:21 pm UTC
I don't believe... no slam on this last one ?
?
A reader, July 10, 2002 - 4:22 pm UTC
Didn't he read the top of the page ?
How about this?
Mr. 420, July 10, 2002 - 5:28 pm UTC
I am pretty sure that SELECT COUNT(ROWID) FROM a_very_large_tbl is faster than using COUNT(*). I would say, I am 99% sure. Tom, can you be kind enough to confirm this?
RE: How about this?
Mark A. Williams, July 10, 2002 - 8:53 pm UTC
PMFJI...
In regard to count(rowid) - just benchmark it!
I created a table (t) as select * from all_objects, and then repeated insert into t select * from t until I had 3330304 rows in the table.
Here's the relevant trace/tkprof results:
select count(*)
from
t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 4.34 13.88 40967 42239 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 4.35 13.88 40967 42240 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 23
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file scattered read 2605 0.07 10.44
db file sequential read 2 0.00 0.00
SQL*Net message from client 2 5.99 5.99
********************************************************************************
select count(rowid)
from
t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 4.22 13.71 40967 42239 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 4.24 13.71 40967 42240 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 23
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file scattered read 2605 0.07 10.29
db file sequential read 2 0.00 0.00
SQL*Net message from client 2 10.26 10.26
********************************************************************************
My laptop hard drive isn't terribly fast, so ignore the waits. The thing is, 13.88 and 13.77 don't seem too different. Do the same benchmark. Make the table bigger if you want. Play with it. Have fun. See what happens.
- Mark
Mark's response:
Mr. 420, July 24, 2002 - 9:50 am UTC
Mark, Thank you very much for that benchmark test, and clearing my doubts. I have to learn how tkprof/trace utilities work. They seem very useful! Thanks again.
PMFJI...????????
A reader, July 24, 2002 - 9:51 am UTC
Mark, what does that mean?
RE: Mark, what does that mean?
Mark A. Williams, August 17, 2002 - 3:43 pm UTC
PMFJI == (P)ardon (M)e (F)or (J)umping (I)n
Since I jumped into the middle of the thread, I was trying to be a bit polite about it... and this response is only a month late, but I just read the question!
HTH (Hope This Helps)...
- Mark
count(1) and count(field)....why NULL is being counted?
Bhavani, November 12, 2003 - 8:01 am UTC
Hi Tom,
ops@florida> create table t (f integer);
Table created.
ops@florida> insert into t values(1);
1 row created.
Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE
ops@florida> set autotrace off
ops@florida> insert into t values(1);
1 row created.
ops@florida> insert into t values(2);
1 row created.
ops@florida> insert into t values(3);
1 row created.
ops@florida> select count(1) from t;
/c cls COUNT(1)
----------
4
ops@florida> select count(f) from t;
/c cls COUNT(F)
----------
4
ops@florida> insert into t values(NULL);
1 row created.
ops@florida> commit;
Commit complete.
ops@florida> select count(f) from t;
/c cls COUNT(F)
----------
4
ops@florida> select count(1) from t;
/c cls COUNT(1)
----------
5
Why "select count(f) from t;" is not counting the row with NULL?
Thanks
Bhavani
November 12, 2003 - 10:37 am UTC
that is the very essence of count
count(*) = count all rows
count( <expression> ) = count all NON NULL rows
count( distinct <expression> ) = count unique non null values
count(1) is the wrong way to code that, count(*) is correct and proper (btw)...