There is one advantage of using count(*) instead of count(1). The advantage is 'clarity of meaning'. count(1) would seem to imply that somehow the value of 1 is involved. It would also seem to imply that count(2) is somehow different than count(1). This often leads to the type of confusion involved in the original post. 'count(*)' carries no such implication is is therefor less likely to lead people down the wrong path. It's too bad there has to be a parameter at all. Why could they not have simply used 'COUNT'? I suppose it's too late now to change it. Mike
On Fri, Mar 19, 2010 at 9:36 AM, Rob Wolfe <[email protected]> wrote: > > > On Mar 19, 9:50 am, Tom Alspaugh > <[email protected]> wrote: > > Dual is a Psuedo-table, it doesnt have 1 column or any set number of > > columns, Evidenced by the follows > > > > select 'this should appear' col1, 'this shouldnt appear' col2 from > > dual; > > > > COL1 COL2 > > ------------------ -------------------- > > this should appear this shouldnt appear > > > > 1 row selected. > > > > I did check on the rest of the information and will just point > > everyone to this link of asktom (no not me) where he goes over ALL of > > the intricacies of count(?) > > > > http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID... > > > > On Mar 19, 8:58 am, ddf <[email protected]> wrote: > > > > > > > > > On Mar 18, 4:30 pm, Tom Alspaugh > > > > > <[email protected]> wrote: > > > > Couple of minor tweaks to the information you provided here, > > > > When you say count(1) you are actually using a row ordinal in this > > > > case not a numeric value as you state. > > > > > Sorry to burst your bubble but that is incorrect: > > > > > SQL> select count(1) FROM DUAL; > > > > > COUNT(1) > > > ---------- > > > 1 > > > > > SQL> c/1/2 > > > 1* select count(2) FROM DUAL > > > SQL> / > > > > > COUNT(2) > > > ---------- > > > 1 > > > > > SQL> c/2/3 > > > 1* select count(3) FROM DUAL > > > SQL> / > > > > > COUNT(3) > > > ---------- > > > 1 > > > > > SQL> c/3/4 > > > 1* select count(4) FROM DUAL > > > SQL> / > > > > > COUNT(4) > > > ---------- > > > 1 > > > > > SQL> c/4/5 > > > 1* select count(5) FROM DUAL > > > SQL> / > > > > > COUNT(5) > > > ---------- > > > 1 > > > > > SQL> > > > > > How can I be selecting the 5th column from a single-column table? > > > > > > for example > > > > select count(1) from USER_TABLES; selects the nonnull values in the > > > > first row and returns that count. > > > > > Again, sorry that's incorrect as illustrated above. > > > > > > select count(2) from USER_TABLES; selects the nonnull values in the > > > > second row and returns that count. otherwise this second query would > > > > return a number twice as large as the first query. > > > > > Your logic is flawed in that COUNT() returns a count, not a sum. > > > Substituting 2 for each row rather than a 1 still will return the same > > > count as before. > > > > > > Your performance tuning tip does have one more issue, it is true that > > > > in MSSQL the count(1) vs Count(*) will give a better performance in > > > > the 1st scenario, > > > > > I'll take your word on that but I expect that only applies to versions > > > of SQL Server prior to 2005. > > > > > > However in Oracle even if you were to use count(0) > > > > (which would use the virtual rownum column) > > > > > Nope, it uses the same mechanism as count(1), count(2), count(12) and > > > count(743) and all of those will work and return results on a single- > > > column table: > > > > > SQL> select count(743) from dual; > > > > > COUNT(743) > > > ---------- > > > 1 > > > > > > the execution plan would > > > > be the exact same for all of these PLSQL statements.- Hide quoted > text - > > > > > - Show quoted text - > > Tom, > > That link says nothing at all like your statement. there are no > "intricacies of count(?)" he explains it is one very short paragraph. > > Not sure how you came up with your theory. > > His bottom line is that count(*) and count(<insert random number > here>) are the same now (they WERE once different, long ago). Nothing > to do with column ordinals or anything like that though. > > Fun discussion though > > Rob > > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to [email protected] > To unsubscribe from this group, send email to > [email protected] > For more options, visit this group at > http://groups.google.com/group/Oracle-PLSQL?hl=en > > To unsubscribe from this group, send email to oracle-plsql+ > unsubscribegooglegroups.com or reply to this email with the words "REMOVE > ME" as the subject. > -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en To unsubscribe from this group, send email to oracle-plsql+unsubscribegooglegroups.com or reply to this email with the words "REMOVE ME" as the subject.
