0

Can anyone help me with proofs and details ?
I was taught that count(*) and count (1) are not the same because count( * ) selects all the columns in a table however count (1) select only 1 column from a table.
But asktom explain that they are the same.
such information mad me confused so i wrote this query

select * from ( select count(1) from tab1 union select count(1) from tab2 )

gave me faster results than

select * from ( select count(*) from tab1 union select count( * ) from tab2 )

Moudiz
  • 7,211
  • 22
  • 78
  • 156
  • does `select count(1) from tab1` return the proper number(s)? – Axel Amthor May 14 '13 at 07:01
  • Did you tried to use `EXPLAIN PLAN` and verify what's happen? – DonCallisto May 14 '13 at 07:01
  • Maybe not a duplicate actually if the question is really how to measure relative performance of two queries with those specific queries just being examples. – Martin Smith May 14 '13 at 07:01
  • @MartinSmith i didnt find it while i was searching, i will read it carefully. if its the same as they are refering why am having different result time ? – Moudiz May 14 '13 at 07:19
  • @doncaliisto i am somehow biginner dont know how to *explain plan* – Moudiz May 14 '13 at 07:20
  • @axel amthor yes return the same number but with different result time – Moudiz May 14 '13 at 07:21
  • What order did you run those two queries? I would expect the one you ran second to be sightly faster because it's more likely to have data cached in memory, reducing disk I/O. – Alex Poole May 14 '13 at 07:29
  • Add details about your testing methodology to the question. How much faster? How many times did you try? What did you use to measure the times? What was the output of Explain Plan for both. – Martin Smith May 14 '13 at 07:46
  • @alex i run them in such order select * from ( select count(1) from tab1 union select count(1) from tab2 ) the first with (1) is way faster and explained what might be the re ason in my question – Moudiz May 14 '13 at 07:52
  • @martin smith not experienced with performance but i will try test more and give you all the informations – Moudiz May 14 '13 at 07:55
  • 1
    As a general rule, Tom Kyte is almost entirely perfectly correct in everything. Also, why would the Oracle kernel developers choose to harm the performance of their system by reading an entire row unnecessarily? A count(*) doesn't have to access the table at all under most circumstances -- the benefit of count(1) is a myth I'm afraid, and you might like to reconsider everything you learned from the source that told you about this. Especially if they said that you should rebuild indexes frequently or try to keep the Buffer Cache Hit Ratio high (two other depressingly common myths). – David Aldridge May 14 '13 at 09:20
  • I don't agree that this is a duplicate (despite casting the first vote as dupe). Your question is "why does count(1) have greater performance in this specific case". However without much more detail about exactly what and how you are testing it would be closed as Not a Real Question. If you edit your question and supply much more detail I will vote to reopen. – Martin Smith May 14 '13 at 09:27
  • The phrase 'count (1) select only 1 column from a table' ought to make you wonder *which* column that would supposedly access, since `1` is an expression not a column. Is it possible you were taught that `select count(some_column)` - i.e. counting one column - is different, which can be true? That might be faster if the column is nullable (and has significant null values) and indexed, simply because it's reading a smaller number of rows from that index than `count(*)` would. But then you'd also get a different result as nulls wouldn't be included. – Alex Poole May 14 '13 at 09:44
  • @martin what made me to post this question is when i selected the first querry it gave me down in plsql in the result set 0.137 seconds then when i ran the second one it game me 0.414 seconds so i thoughs the was a difference between the two. i asked my team they told me there are difference between them and in the research here i didnt find count(*) and count (1) question and anwer. – Moudiz May 14 '13 at 11:13

0 Answers0