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.
