-
I have a table test:
table test:
COL1 COL2
-------- ---------
1 2
2 3
4
if use following statement,
SELECT count(1), count(rowid)
FROM test;
COUNT(1) COUNT(ROWID)
--------- ------------
3 3
What does "1" stand for? I only know in this statement
SELECT* FROM test ORDER BY 1 DESC;
1 stands for the first column. But if use
SELECT count(col1) FROM test;
COUNT(COL1)
-----------
2
looks like "1" in count(1) has different meaning with "1" in order by 1.
Many thanks.
-
u right,
""1" in count(1) has different meaning with "1" in order by 1"
in count(1) - this is only 1 as digit.
in order by 1 - this is number of fileld in select fld1, fld2 ... (select *) ...
on witch u going to sort result(rows in select).
-
Hi. Shestakov,
Thanks.
But I'm still confused. Why should we use SELECT count(1) FROM test?
If there are more 10,000 records, using SELECT count(1) FROM table_name is faster than SELECT count(*) FROM table_name. Could you please tell me about count(1) is from what kind idea?
-
Hi
Whenever you use "*" in the SELECT oracle will search for all the columns to get the count. If you use any integer or number for the count function it will not scan all the columns and that's why the result will be much faster.
when you use count(col) it will ignore the NULL values in that column and exclude that row in the output of count function..
Originally posted by jmniu
Hi. Shestakov,
Thanks.
But I'm still confused. Why should we use SELECT count(1) FROM test?
If there are more 10,000 records, using SELECT count(1) FROM table_name is faster than SELECT count(*) FROM table_name. Could you please tell me about count(1) is from what kind idea?
-
you should use count(*) there is no performance difference and if you have a PK then Oracle is clever enough to do a index fast full scan, if you are using 8i and it's fast and efficient
-
Incorrect:
Originally posted by DevangP
Whenever you use "*" in the SELECT oracle will search for all the columns to get the count. If you use any integer or number for the count function it will not scan all the columns and that's why the result will be much faster.
Correct:
Originally posted by DevangP
when you use count(col) it will ignore the NULL values in that column and exclude that row in the output of count function..
Absolutely correct:
Originally posted by pando
you should use count(*) there is no performance difference and if you have a PK then Oracle is clever enough to do a index fast full scan, if you are using 8i and it's fast and efficient
- Chris
[Edited by chrisrlong on 02-13-2002 at 10:02 AM]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|