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.
--
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.
