And just in case...
Connor, August 13, 2001 - 11:44 am UTC
GOOD ANSWER
Ganesh A, August 13, 2001 - 12:43 pm UTC
a test result show difference between count(*) and count(1)
Dulimin, August 31, 2001 - 4:09 am UTC
Hi, tom:
Here is my test result, it show count(*) is much fast than count(1).
In other condition ( for example, a query with join), sometime i can find count(1) is fast than count(*), but i can't find the sample at present. When i find one, i will send to you.
SVRMGR> connect scott/tiger
Connected.
SVRMGR>
SVRMGR> drop sequence seq_r1000;
Statement processed.
SVRMGR> drop table r1000;
Statement processed.
SVRMGR> create sequence seq_r1000;
Statement processed.
SVRMGR> create table r1000 (id number);
Statement processed.
SVRMGR> insert into r1000 select seq2.nextval from all_objects where rownum<1001
;
1000 rows processed.
SVRMGR> commit;
Statement processed.
SVRMGR> set timing on
Timing ON
SVRMGR> select count(*) from r1000, r1000;
COUNT(*)
----------
1000000
1 row selected.
Parse 0.00 (Elapsed) 0.00 (CPU)
Execute/Fetch 0.43 (Elapsed) 0.00 (CPU)
Total 0.43 0.00
SVRMGR> select count(1) from r1000, r1000;
COUNT(1)
----------
1000000
1 row selected.
Parse 0.00 (Elapsed) 0.00 (CPU)
Execute/Fetch 0.70 (Elapsed) 0.00 (CPU)
Total 0.70 0.00
SVRMGR> select count(*) from r1000, r1000;
COUNT(*)
----------
1000000
1 row selected.
Parse 0.00 (Elapsed) 0.00 (CPU)
Execute/Fetch 0.41 (Elapsed) 0.00 (CPU)
Total 0.41 0.00
SVRMGR> select count(1) from r1000, r1000;
COUNT(1)
----------
1000000
1 row selected.
Parse 0.01 (Elapsed) 0.00 (CPU)
Execute/Fetch 0.69 (Elapsed) 0.00 (CPU)
Total 0.70 0.00
SVRMGR>
August 31, 2001 - 7:00 am UTC
I'll have to guess, since you don't say, that you are using 7.x and before when count(*) and count(1) were different (and count(1) was slower). In all releases of the databases for the last 4-5 years, they are the same.
My testing on 8.x with this test case:
drop sequence seq_r1000;
drop table r1000;
create sequence seq_r1000;
create table r1000 (id number);
insert into r1000 select seq_r1000.nextval from all_objects where rownum<1001;
analyze table r1000 compute statistics;
select count(*) from r1000, r1000;
select count(1) from r1000, r1000;
alter session set sql_trace=true;
declare
n number;
begin
for i in 1 .. 10
loop
select count(*) into n from r1000, r1000;
select count(1) into n from r1000, r1000;
end loop;
end;
/
shows:
SELECT COUNT(*)
FROM
R1000,R1000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10 0.00 0.00 0 0 0 0
Fetch 10 12.46 12.53 0 40 80 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 21 12.46 12.53 0 40 80 10
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 29 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
10 SORT AGGREGATE
10000000 MERGE JOIN CARTESIAN
10010 TABLE ACCESS FULL R1000
10000000 SORT JOIN
10000 TABLE ACCESS FULL R1000
********************************************************************************
SELECT COUNT(1)
FROM
R1000,R1000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 10 0.00 0.01 0 0 0 0
Fetch 10 12.38 12.38 0 40 80 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 21 12.38 12.40 0 40 80 10
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 29 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
10 SORT AGGREGATE
10000000 MERGE JOIN CARTESIAN
10010 TABLE ACCESS FULL R1000
10000000 SORT JOIN
10000 TABLE ACCESS FULL R1000
they are in effect the same...
A reader, August 31, 2001 - 8:28 am UTC
TOM WE ALREADY HAVE LOT'S OF DISCUSSION ABOUNT COUNT(*)
ETC.
LET'S JUST NOT WASTE TIME ANYMORE ON THIS TOPIC
My database is 8.1.5
dulimin, September 03, 2001 - 2:04 am UTC
I forget to say my database version in last post, it's Oracle 8.1.5 EE on Win NT 4.0.
And I have test it on 8.1.7 just now, the result is:
===========================
C:\>svrmgrl
Oracle Server Manager Release 3.1.7.0.0 - Production
Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
SVRMGR> connect scott/tiger
Connected.
SVRMGR> insert into r1000 select seq_r1000.nextval from all_objects where rownum<1001;
1000 rows processed.
SVRMGR> commit;
Statement processed.
SVRMGR> set timing on
Timing ON
SVRMGR> select count(*) from r1000, r1000;
COUNT(*)
----------
4000000
1 row selected.
Parse 0.00 (Elapsed) 0.00 (CPU)
Execute/Fetch 1.33 (Elapsed) 0.00 (CPU)
Total 1.33 0.00
SVRMGR> select count(1) from r1000, r1000;
COUNT(1)
----------
4000000
1 row selected.
Parse 0.02 (Elapsed) 0.00 (CPU)
Execute/Fetch 2.36 (Elapsed) 0.00 (CPU)
Total 2.38 0.00
SVRMGR> select count(*) from r1000, r1000;
COUNT(*)
----------
4000000
1 row selected.
Parse 0.01 (Elapsed) 0.00 (CPU)
Execute/Fetch 1.34 (Elapsed) 0.00 (CPU)
Total 1.35 0.00
SVRMGR> select count(1) from r1000, r1000;
COUNT(1)
----------
4000000
1 row selected.
Parse 0.00 (Elapsed) 0.00 (CPU)
Execute/Fetch 2.33 (Elapsed) 0.00 (CPU)
Total 2.33 0.00
SVRMGR>
============================
If the result is caused by some problem of my environment, what problem is it?
count is sum(1)
Mikito Harakiri, September 04, 2001 - 12:45 am UTC
Some extra info:
1. There is no need in a separate "count" function as
select sum(1) from emp
does the job (and could do more;).
2. "count" as an abbreviation for sum(1) doesn't really need an argument, for example
select count(1) from emp
and
select count(2) from emp
return the same data.
In short, "count" having an argument is counterintuitive, at least.
SQL quirks
Vadim Tropashko, September 04, 2001 - 1:12 am UTC
Responding to Mikito:
Except that
select count(distinct 1) from emp
demonstrates that count() does depend on the argument in some wierd way. Anyway, instead of the obscure syntax above, I would suggest to use nested queries like this:
select count(1) from (
select distinct 1 from emp
)
one more row count query:-)
Mikito Harakiri, June 10, 2003 - 2:46 pm UTC
select -1/2+1/2*sqrt(1+8*sum(rownum)) from emp
Could you please explain the query as well, Mikito?
Reader, June 12, 2003 - 10:18 am UTC
Mikito is a show off.....
Nick, July 24, 2003 - 4:35 pm UTC
Ok, so which performs better ; )
Hmmmm...
A reader, August 04, 2003 - 8:20 am UTC
Quoting a forum answer from an oracle employee on Metalink:
Subject: Re : Performance while using ROWNUM v/s COUNT
Hi,
If the intention is only to count the number of Records matching the criteria then you could as well use :
count(1) ..
This is the fastest as the numberical literal is the fastest to be summed up.
Max rownum will lead to implicit ordering while count(id) will also be effecient as the value is a primary key though not as good as count(1). "
August 04, 2003 - 9:10 am UTC
well, you could point out to them "hey, we are not summing -- we are COUNTING"
max(rownum) would be a bad idea.
count(id) is dandy if id is a NOT NULL column.
count(1) is just counter intuitive to me and is silently rewritten as count(*) internally...
Re: Counter-intuitive/Harakiri
A reader, August 04, 2003 - 9:33 am UTC
<quote>
In short, "count" having an argument is counterintuitive, at least.
<\quote>
Not if you need to count distinct values, or non-NULL ocurrances of some column.
Amazing!!!!!!!!
Saeed Ahmed, August 10, 2003 - 5:59 pm UTC
I am amazed to see such a basic things are discused here, This is a lovely forum!
Thank you very much.
Su Baba, August 13, 2003 - 5:09 pm UTC
Hi Tom,
What's the quickest way to find out if a table is empty? Obviously, select count(*) is not the quickest way. What about
select 'x' from <table> where rownum = 1;
This will still do a full table scan, wouldn't it.
What's the optimal solution?
thanks
August 13, 2003 - 7:25 pm UTC
use the CBO and
select /*+ FIRST_ROWS */ primary_key from table where rownum = 1;
ops$tkyte@ORA920LAP> create table emp as select * from scott.emp;
Table created.
ops$tkyte@ORA920LAP> alter table emp add constraint emp_pk primary key(empno);
Table altered.
ops$tkyte@ORA920LAP> analyze table emp compute statistics;
Table analyzed.
ops$tkyte@ORA920LAP> set autotrace traceonly explain
ops$tkyte@ORA920LAP> select /*+ FIRST_ROWS */ empno from emp where rownum = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=1 Card=1 Bytes=3)
1 0 COUNT (STOPKEY)
2 1 INDEX (FULL SCAN) OF 'EMP_PK' (UNIQUE) (Cost=1 Card=14 Bytes=42)
ops$tkyte@ORA920LAP> set autotrace off;
it'll read the index and stop at the first row. very fast on a big empty table (as the index is small and empty).
very fast on a big full table as the index is just read to find the first leaf node and then "stop"
Su Baba, August 13, 2003 - 8:20 pm UTC
Tom,
I tried it with and without /*+ FIRST_ROWS */ hint. In some case, the cost seems to be lower without the hint. Should I pay attention to the cost at all?
SQL> CREATE TABLE x (col1 NUMBER NOT NULL);
Table created.
SQL> CREATE UNIQUE INDEX x_u1 ON x(col1);
Index created.
SQL>
SQL> BEGIN
2 FOR i IN 1..14 LOOP
3 INSERT INTO x VALUES (i);
4 END LOOP;
5
6 commit;
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> ANALYZE TABLE x COMPUTE STATISTICS;
Table analyzed.
SQL>
SQL> set autotrace traceonly explain
SQL>
SQL> SELECT /*+ FIRST_ROWS */ col1
2 FROM x
3 WHERE rownum = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=1 Card=14
Bytes=28)
1 0 COUNT (STOPKEY)
2 1 INDEX (FULL SCAN) OF 'X_U1' (UNIQUE) (Cost=1 Card=14 Byt
es=28)
SQL>
SQL> set autotrace off
SQL>
SQL> BEGIN
2 FOR i IN 15..1000 LOOP
3 INSERT INTO x VALUES (i);
4 END LOOP;
5
6 commit;
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> ANALYZE TABLE x COMPUTE STATISTICS;
Table analyzed.
SQL>
SQL> set autotrace traceonly explain
SQL>
SQL> SELECT /*+ FIRST_ROWS */ col1
2 FROM x
3 WHERE rownum = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=3 Card=100
0 Bytes=3000)
1 0 COUNT (STOPKEY)
2 1 INDEX (FULL SCAN) OF 'X_U1' (UNIQUE) (Cost=3 Card=1000 B
ytes=3000)
SQL>
SQL> SELECT col1
2 FROM x
3 WHERE rownum = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1000 Bytes=30
00)
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (FULL) OF 'X' (Cost=2 Card=1000 Bytes=3000)
August 13, 2003 - 9:40 pm UTC
do this now
delete from x;
commit;
don't analyze
compare the performance. see what happens when it accidently full scans
use first_rows.
Su Baba, August 13, 2003 - 9:58 pm UTC
SQL> select count(*) from x;
COUNT(*)
----------
1000
SQL> DELETE FROM x;
1000 rows deleted.
SQL> commit;
Commit complete.
SQL> set autotrace traceonly explain
SQL>
SQL> SELECT /*+ FIRST_ROWS */ col1
2 FROM x
3 WHERE rownum = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=3 Card=100
0 Bytes=3000)
1 0 COUNT (STOPKEY)
2 1 INDEX (FULL SCAN) OF 'X_U1' (UNIQUE) (Cost=3 Card=1000 B
ytes=3000)
SQL>
SQL> SELECT col1
2 FROM x
3 WHERE rownum = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1000 Bytes=30
00)
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (FULL) OF 'X' (Cost=2 Card=1000 Bytes=3000)
August 14, 2003 - 7:40 am UTC
i said to BENCHMARK , not explain plan.
created a table t as select * from all_objects and then:
SELECT /*+ FIRST_ROWS */ object_id from t before_delete where rownum = 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 1 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 1 2 0 1
Rows Row Source Operation
------- ---------------------------------------------------
1 COUNT STOPKEY (cr=2 r=1 w=0 time=109 us)
1 INDEX FULL SCAN T_PK (cr=2 r=1 w=0 time=99 us)(object id 39935)
********************************************************************************
SELECT /*+ FULL(before_delete) */ object_id from t before_delete where rownum = 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 4 0 1
Rows Row Source Operation
------- ---------------------------------------------------
1 COUNT STOPKEY (cr=4 r=0 w=0 time=95 us)
1 TABLE ACCESS FULL T (cr=4 r=0 w=0 time=87 us)
so, far, so comparable. now -- delete from t; and commit
SELECT /*+ FIRST_ROWS */ object_id from t after_delete where rownum = 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 117 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 117 0 0
Rows Row Source Operation
------- ---------------------------------------------------
0 COUNT STOPKEY (cr=117 r=0 w=0 time=3121 us)
0 INDEX FULL SCAN T_PK (cr=117 r=0 w=0 time=3117 us)(object id 39935)
********************************************************************************
SELECT /*+ FULL(after_delete) */ object_id from t after_delete where rownum = 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.03 1.05 223 821 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.03 1.05 223 821 0 0
Rows Row Source Operation
------- ---------------------------------------------------
0 COUNT STOPKEY (cr=821 r=223 w=0 time=1054038 us)
0 TABLE ACCESS FULL OBJ#(39934) (cr=821 r=223 w=0 time=1054035 us)
Helena Marková, August 14, 2003 - 8:14 am UTC
I have found another opinion
Sergej, October 20, 2003 - 4:51 pm UTC
Hi Tom,
There is another opinion in:
OCP Introduction to Oracle 9i: SQL Exam Guide, p. 124
"Do not use count(*) to determine the number of rows in a table. Use count(1) or count(ROWID) instead. These options are faster because they bypass some unnecessary operations in Oracle's SQL pocessing mechanism."
Thanks,
Best,
Sergej
October 20, 2003 - 8:12 pm UTC
OPINIONS DON'T COUNT
FACTS -- FACTS COUNT.
first, count(1) internally is rewritten as count(*). That is a FACT.
second, a simple tkprof shows count(*) and count(rowid) are not significantly different (in fact, in this run, count(rowid) a little LESS efficient). the big difference is:
count(*) means something. It means literally COUNT THE ROWS
count(1) does not.
count(rowid) does not.
opinions -- they quite simplye DO NOT COUNT. Facts, backed up with supporting, reproducible evidence -- very much welcome here.
select count(*) from big_table
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.03 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 5.06 5.36 4327 4338 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 5.08 5.40 4327 4338 0 1
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=4338 r=4327 w=0 time=5367705 us)
1833792 INDEX FAST FULL SCAN BIG_TABLE_OBJECT_ID_IDX (cr=4338 r=4327 w=0 time=3160178 us)(object id 49917)
********************************************************************************
select count(rowid) from big_table
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 5.33 5.23 4322 4338 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 5.33 5.23 4322 4338 0 1
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=4338 r=4322 w=0 time=5234072 us)
1833792 INDEX FAST FULL SCAN BIG_TABLE_OBJECT_ID_IDX (cr=4338 r=4322 w=0 time=2898097 us)(object id 49917)
in fact, if you do it over and over and over:
select count(*) from big_table
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 7 0.00 0.00 0 0 0 0
Execute 7 0.00 0.00 0 0 0 0
Fetch 14 35.48 40.91 30254 30366 0 7
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 28 35.48 40.91 30254 30366 0 7
********************************************************************************
select count(rowid) from big_table
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 7 0.00 0.00 0 0 0 0
Execute 7 0.00 0.00 0 0 0 0
Fetch 14 37.61 42.36 30254 30366 0 7
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 28 37.61 42.36 30254 30366 0 7
but you decide. oh and count(1)
select count(*) from big_table
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 7 0.00 0.00 0 0 0 0
Execute 7 0.00 0.00 0 0 0 0
Fetch 14 35.35 35.87 30254 30366 0 7
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 28 35.35 35.87 30254 30366 0 7
********************************************************************************
select count(1) from big_table
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 7 0.00 0.00 0 0 0 0
Execute 7 0.00 0.00 0 0 0 0
Fetch 14 35.21 35.77 30254 30366 0 7
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 28 35.21 35.77 30254 30366 0 7
Great!
Jens, October 21, 2003 - 7:09 am UTC
Hi Tom,
you must publish something like "The 10 Oracle Myths". Before I found your side I read a lot of that COUNT(1) is better than COUNT(*) and that Explicit Cursors were better than Implicit Cursors (and I told it everbody because I found it in the literature) - I'm curious how many oracle consultants would say the same! And what is the worth of an OCP when they learn something like that?
Bye,
Jens
to Sergej
freek, October 21, 2003 - 8:06 am UTC
Sergej,
Just a warning: test everything you read in that book!
I have the same book and found one error after the other.
To give a nice example, on page 128 they clame that when you use a group by clause, all nongroup expressions in the column clause of the query must appear before the grouped expresson in the column clause.
Because of a mistake in their example (forgot a column in the group by clause) it seems to be true.
greetings
Freek
OCP Book Errors
Tony Andrews, October 21, 2003 - 9:50 am UTC
But bear in mind: if the OCP exam is based on the book, you better give the answer they WANT in the exam, not the RIGHT answer!
Another way to look to count(*) issues...
Sven Bleckwedel, March 16, 2004 - 1:11 pm UTC
Hi Tom,
Was in this site that I looked for some questions about count(*/1), and noted the link:
http://www.SITE-NOLONGER-EXISTS/tips/count_speed.htm
Looking around in SITE-NOLONGER-EXISTS site, found another interesting link, in:
http://SITE-NOLONGER-EXISTS/tips/sample_clause.htm
Made some tests with this function and found something very interesting, about this specific issue:
SQL> select count(*) from sys.source$;
COUNT(*)
----------
109502
Elapsed: 00:00:02.08
SQL> set autotrace on
SQL> select count(*) from sys.source$;
COUNT(*)
----------
109502
Elapsed: 00:00:02.09
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'SOURCE$'
Statistics
----------------------------------------------------------
0 recursive calls
39 db block gets
8996 consistent gets
8996 physical reads
0 redo size
393 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(*) from sys.source$ sample(99.9999999);
COUNT(*)
----------
109502
Elapsed: 00:00:01.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (SAMPLE FAST FULL SCAN) OF 'I_SOURCE1' (UNIQUE) (Cost=3 Card=734794)
Statistics
----------------------------------------------------------
14 recursive calls
6 db block gets
264 consistent gets
262 physical reads
0 redo size
393 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
The only thing that anyone must be warned about this is to use this "tip" carefully, knowing previously that EXIST at least an unique index for the target table and using the sample clause with the parameter "99.9999999", to return the right results, of course. But opened a great possibility for reducing LIOs and PIOs ! Using this "tip" with anothers published previously in this site, was possible to count registers when joining with other tables, too...
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6075151195522,
Maybe this can help something...
Rgds,
Sven
March 16, 2004 - 1:47 pm UTC
you don't need sample (which could return "the wrong answer"), just the use of the CBO.
the use of the SAMPLE feature was not the cause of the plan change in as much as the INVOCATION of the CBO in order to *use* the sample feature. The sample feature is "cbo only"
You can tell that in their example by the addition of the cost/card
Just add /*+ all_rows */ and you'd see the same (but always correct) answer
sys@ORA9IR2> select count(*) from source$;
COUNT(*)
----------
318845
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'SOURCE$'
sys@ORA9IR2> select /*+ all_rows */ count(*) from source$;
COUNT(*)
----------
318845
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=4 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'I_SOURCE1' (UNIQUE) (Cost=4 Card=1352050)
(eg: don't use SAMPLE for this -- dangerous!)
Another way to look to count(*) issues...is dangerous ?
Sven Bleckwedel, March 16, 2004 - 2:12 pm UTC
Hi,
I can understand that I made some "overutilization" here, but I can´t believe that is dangerous at all. The main reason for using the value "99.9999999" in sample clause parameter is the fact that this number is the nearest value to 100%, in this case. And noted that this could return correct values (when exist at least one UNIQUE index) and was possible to reduce LIOs and PIOs (autotrace showed this), too...
Rgds,
Sven
March 16, 2004 - 2:18 pm UTC
99.9999999 is not 100, the counts need not be the same!
the bottom line is:
a) sample was not the catalyst
b) the cbo was
c) there is a better, SAFER way to get the cbo involved without a 'trick'
use /*+ all_rows */ or /*+ first_rows */ and you'll get the same exact "effect" without the worry and restrictions of SAMPLE.
Ok
Ben, March 17, 2004 - 12:13 am UTC
Dear Tom,
My query finds the number of tables,views,procedures,func's
,packages etc which are owned by me.I used a query like
sql>select object_name,count(*)
from obj
group by object_name
order by object_name;
This works well.Is there any other ways that can be used and which may be better?
Please do reply.
p.s) Is an inline view possible here like
sql>select object_name,(select ..)cnt_tables,
(select..)cnt_views,(select ..)cnt_procs
from obj;
Is the above format possible?
March 17, 2004 - 7:15 am UTC
that finds the count of tables/views/procedures??
it seems to be getting object_names which won't tell you that which you say it does.
I don't understand the usage of object_name here (use the USER_ views, like USER_OBJECTS. the data dictionary views you should use start with DBA_ ALL_ and USER_ -- the ones like obj, tab -- they are for the "way back machine" -- backwards compatibility only)
count question
Tarun, March 17, 2004 - 2:59 am UTC
Hi tom,
Please verify these statements.
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> select count(1) from emp;
COUNT(1)
----------
14
SQL> select count(null) from emp;
COUNT(NULL)
-----------
0
1. Why count is displaying same result, when we pass 1 or *?
2. When passed null why it is 0 rows?
Please explain?
Thanks you.
March 17, 2004 - 7:37 am UTC
1) because count(*) says count the records and count(<expression>) says count the non-null occurrences of this expression (and of course 1 is "not null") so count(*) and count(1) return the same answer (but count(*) shows you know how to ask "how many records" properly)
2) because count(<expression>) counts NON NULL occurences. try count(sal), count(comm) and see what happens.
OK
Srinivas, March 17, 2004 - 3:55 am UTC
Hello Sir,
What is the use of "sample clause " in a sql select statement?
March 17, 2004 - 7:38 am UTC
Pourpose of sample clause...
Sven Bleckwedel, March 17, 2004 - 7:25 am UTC
Count with * or 1 parameter, that´s the question...
Sven Bleckwedel, March 17, 2004 - 7:41 am UTC
count(column_name)
A reader, March 18, 2004 - 5:40 pm UTC
Is count(column_name) also the same as count(*) or count(1)
March 18, 2004 - 6:09 pm UTC
no, look up just a little:
2) because count(<expression>) counts NON NULL occurences. try count(sal),
count(comm) and see what happens.
count(column_name) returns a count of NON-NULL occurences of column_name. count(*) returns the count of records. count(1) returns the count of records where the constant 1 exists (eg: it is a bad way of saying count(*))
Count rows
Tarun Babu, March 19, 2004 - 4:37 am UTC
Hi Tom,
I am trying to count the no of rows present in every table in my scott schema. Though I was able to write a pl/sql block for the follwoing out put..but, wondering how can we solve this with single query or subquery. Help will be appreciated. Thank U very much.
TABLE_NAME NO OF ROWS
---------- ----------
DEPT 4
EMP 14
SALGRADE 6
........ ..
........ ..
March 19, 2004 - 9:01 am UTC
Nice
Ram, March 19, 2004 - 7:09 am UTC
Dear Tom,
I used the following queries which should return the same
count but it is not so.Why is this happening?
SQL> select deptno,count(*)
2 from emp
3 group by deptno;
DEPTNO COUNT(*)
---------- ----------
10 3
20 5
30 6
SQL> select deptno,count(*) over(partition by deptno) from emp;
DEPTNO COUNT(*)OVER(PARTITIONBYDEPTNO)
---------- -------------------------------
10 3
10 3
10 3
20 5
20 5
20 5
20 5
20 5
30 6
30 6
30 6
DEPTNO COUNT(*)OVER(PARTITIONBYDEPTNO)
---------- -------------------------------
30 6
30 6
30 6
14 rows selected.
Please do reply.
March 19, 2004 - 9:28 am UTC
they are returning the same counts?
the first query says "aggregate the result set to return only distinct values of the group by and count the records"
that is 10->3, 20->5, 30->6
The second query says "don't aggregate, but by deptno assign the count of records to each record in the partition". the same result is apparent to me -- the count of records in deptno 10 is 3, every deptno 10 record gets "3"
tell me, what were you expecting to see? (you are comparing APPLES to Toaster Ovens however -- aggregates are not analytics, analytics are not aggregates)
Tomas, March 19, 2004 - 8:58 am UTC
Dear Ram,
in your second query you have used analytic function:
count(*) over(....)
so it returned count for each department (which is the same as in the query with group by clause) but for each and every row in emp table.
count(id) vs count(distinct id) when id is unique
Maurice, May 12, 2004 - 7:07 pm UTC
Hi Tom,
I'm working on Oracle 9.2.0.4 and noticed recently that Oracle makes a difference if a count() or a count(distinct) is executed against a column defined as unique (unique index + unique constraint + not null).
From my point of view a count() and count(distinct) against a unique column should take the same query time because Oracle should know that each column is unique.
However, as you can see below, when the distinct is used Oracle makes a group by and doesn't just aggregate the data.
So my question is: why is that so, is there any workaround so that Oracle is as fast with as without the distinct
SQL> select count( id) from sales3;
COUNT(ID)
----------
2032542
Abgelaufen: 00:00:03.00
Ausführungsplan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'I_RN' (UNIQUE) (Cost=3 Card=1
034313)
SQL> select count( distinct id) from sales3;
COUNT(DISTINCTID)
-----------------
2032542
Abgelaufen: 00:00:07.00
Ausführungsplan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=4)
1 0 SORT (GROUP BY)
2 1 INDEX (FAST FULL SCAN) OF 'I_RN' (UNIQUE) (Cost=3 Card=1
034313 Bytes=4137252)
May 13, 2004 - 9:50 am UTC
not as far as I know. subtle difference and only really applicable to this specific sort of query -- a simple count(column). (add any more columns or anything and the whole thing changes)
Help
Sreenivas Gangavarapu, July 02, 2004 - 1:56 pm UTC
Hi Tom, I'm doing a count(*) on a table that has 21 million records. The trace shows it does a FTS even though it has indexed columns and when I adjust the parameter db_file_multiblock_read_count it did chosse a Index full scan, although the results are same( I mean in terms of performance). I would like to know is this the best possible plan or there could be better way of doing it. Could you please explain when does the optimizer choose the index fast full scan or index range scan and why is not doing the same here. Thanks for your time. Here's what I did.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
PL/SQL Release 8.1.7.4.0 - Production
CORE 8.1.7.0.0 Production
TNS for Solaris: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
SQL> set autotrace on
SQL> select count(*) from gl_balances;
COUNT(*)
----------
21475220
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=61882 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'GL_BALANCES' (Cost=61882 Card=21
292441)
Statistics
----------------------------------------------------------
0 recursive calls
6 db block gets
407739 consistent gets
403924 physical reads
264 redo size
370 bytes sent via SQL*Net to client
426 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter session set db_file_multiblock_read_count=4;
Session altered.
SQL> select count(*) from gl_balances;
COUNT(*)
----------
21475220
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=85512 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN) OF 'GL_BALANCES_N2' (NON-UNIQUE) (Cost
=85512 Card=21292441)
Statistics
----------------------------------------------------------
0 recursive calls
6 db block gets
407730 consistent gets
406683 physical reads
0 redo size
370 bytes sent via SQL*Net to client
426 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
July 02, 2004 - 2:38 pm UTC
desc gl_balances
and tell me what columns are indexed.
you did notice your index is only 9 blocks smaller than the table right?
i'm suspecting "really skinny table" with "indexes that are as big, if not larger than the table itself"
Sreenivas Gangavarapu, July 02, 2004 - 4:02 pm UTC
First of all I was very surprised at your quick response considering how busy you are. I guess I was lucky to reach you at right time. Here's the details you asked for.
SQL> desc gl_balances
Name Null? Type
----------------------------------------- -------- ----------------------------
SET_OF_BOOKS_ID NOT NULL NUMBER(15)
CODE_COMBINATION_ID NOT NULL NUMBER(15)
CURRENCY_CODE NOT NULL VARCHAR2(15)
PERIOD_NAME NOT NULL VARCHAR2(15)
ACTUAL_FLAG NOT NULL VARCHAR2(1)
LAST_UPDATE_DATE NOT NULL DATE
LAST_UPDATED_BY NOT NULL NUMBER(15)
BUDGET_VERSION_ID NUMBER(15)
ENCUMBRANCE_TYPE_ID NUMBER(15)
TRANSLATED_FLAG VARCHAR2(1)
REVALUATION_STATUS VARCHAR2(1)
PERIOD_TYPE VARCHAR2(15)
PERIOD_YEAR NUMBER(15)
PERIOD_NUM NUMBER(15)
PERIOD_NET_DR NUMBER
PERIOD_NET_CR NUMBER
PERIOD_TO_DATE_ADB NUMBER
QUARTER_TO_DATE_DR NUMBER
QUARTER_TO_DATE_CR NUMBER
QUARTER_TO_DATE_ADB NUMBER
YEAR_TO_DATE_ADB NUMBER
PROJECT_TO_DATE_DR NUMBER
PROJECT_TO_DATE_CR NUMBER
PROJECT_TO_DATE_ADB NUMBER
BEGIN_BALANCE_DR NUMBER
BEGIN_BALANCE_CR NUMBER
PERIOD_NET_DR_BEQ NUMBER
PERIOD_NET_CR_BEQ NUMBER
BEGIN_BALANCE_DR_BEQ NUMBER
BEGIN_BALANCE_CR_BEQ NUMBER
TEMPLATE_ID NUMBER(15)
ENCUMBRANCE_DOC_ID NUMBER(15)
ENCUMBRANCE_LINE_NUM NUMBER(15)
SQL> select index_name, rpad(column_name,20,' ') column_name, column_position position from dba_ind_columns where table_name='GL_BALANCES';
INDEX_NAME COLUMN_NAME POSITION
------------------------------ -------------------- ----------
GL_BALANCES_N1 CODE_COMBINATION_ID 1
GL_BALANCES_N1 PERIOD_NAME 2
GL_BALANCES_N2 PERIOD_NAME 1
GL_BALANCES_N3 PERIOD_NUM 1
GL_BALANCES_N3 PERIOD_YEAR 2
GL_BALANCES_N4 TEMPLATE_ID 1
6 rows selected.
July 02, 2004 - 4:47 pm UTC
well, according to the stats above, your indexes are as big at the table -- lots of nulls in there or the indexes contain lots of whitespace.
In any case -- scanning the index was no more efficient than the table. it was a classic case of "either or, both are the same"
might want to look into why your indexes are as big as the table.
Arun Gupta, July 02, 2004 - 5:02 pm UTC
Tom,
How did you figure out that the indexes were almost as big as the table in the question by Sreenivas Gangavarapu?
Thanks
July 02, 2004 - 6:59 pm UTC
by looking at the consistent gets in his autotrace output above.
Sreenivas Gangavarapu, July 02, 2004 - 5:23 pm UTC
Yes. You are right. These white spaces I guess are due to some huge deletes. I am rebuilding those indexes and provide you with the latest results. I have some questions here.
1. Whats the difference between Index full scan and Index Fast Full scan?
2. When is Index range scan used?
3. What factors influence the optimizer to choose a particular index?
Thanks a lot
July 02, 2004 - 7:05 pm UTC
1) index full scan reads leaf blocks using single block io one by one. data is read "sorted" index fast full scan reads the entire index structure (root, branch and leaves) using multi-block io, it ignores all but leaf nodes, data is read not in sorted order
2) when you have a predicate that would facilitate it, eg: where x between 1 and 5 might range scan an index build on X
3) lots - mostly the predicate.
A reader, July 02, 2004 - 6:23 pm UTC
Here's the trace results after the index rebuild.
SQL> set autotrace on
SQL> select count(*) from gl_balances;
COUNT(*)
----------
21478445
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=49764 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN) OF 'GL_BALANCES_N2' (NON-UNIQUE) (Cost
=49764 Card=21478445)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
49775 consistent gets
49747 physical reads
1020 redo size
370 bytes sent via SQL*Net to client
426 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count (code_combination_id) from gl_balances;
COUNT(CODE_COMBINATION_ID)
--------------------------
21478445
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=49764 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN) OF 'GL_BALANCES_N2' (NON-UNIQUE) (Cost
=49764 Card=21478445)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
49758 consistent gets
49747 physical reads
0 redo size
388 bytes sent via SQL*Net to client
426 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
I still have these questions.
1. Whats the difference between Index full scan and Index Fast Full scan?
2. When is Index range scan used?
3. What factors influence the optimizer to choose a particular index?
Thanks a lot
It's ANSI SQL bug!
Mikito Harakiri, July 06, 2004 - 8:23 pm UTC
It's just a misconception that "count" should have any arguments at all. Indeed when we count
int count = 0;
for( int i = 0; i< 10; i++)
count++;
we use a single argument increment operator ++. Likewise, any "normal" aggregation
int sum = 0;
for( int i = 0; i< 10; i++)
sum = sum + element[i];
uses 2 argument operator -- "+", "max", "min", etc. Therefore, we need one argument for normal aggregates, and no arguments for the count.
As far as
select count(distinct ename) from emp
is concerned, this odd syntax has been invented for retards who are unable to grasp what inner view is and figure out that
select count from (
select distinct empno from emp
)
is much cleaner syntax.
July 06, 2004 - 8:43 pm UTC
Nice adult way of putting things. Appreciate that. I'll have to tell you about my sister-in-law one day. She was born with a certain birth defect.... Unfortunately, SQL is beyond her.
i find
select count(distinct empno) from emp
to be sort of clean myself. Once you learn the language anyway. I find it quite pleasant to read in english as "give me the count of distinct empno's from the emp table".
mikito - you're an idiot
anthony, July 07, 2004 - 4:49 pm UTC
Honestly,
You impress exactly zero people.
You come to this site and cry about Oracle...
Cry about Oracle's sql...
Cry about analytics...
Now you want to say,
"It's just a misconception that "count" should have any arguments at all"
LOL, so now that you are done crying you run around
regurgitating opinions you've read from C.J. Date's
books that are 20+ years old and pawn them off as your own "brilliant" conclusions?!
I know, I know, you've been doing this a while.
You do the same thing in the google groups, but no one
called you on it until now.
You are a charlatan at best.
You are altogether unimpressive and have never posted
anything productive or clever, just annoying.
You do alot of complaining for someone who posts some
really poor sql.
Please, go play at some other site.
oh and for the crying, this should help you
get all cleaned up:
</code>
http://ar.essortment.com/babydiaperrash_rtzj.htm <code>
And how do I count distict combination of columns?
Mikito, July 07, 2004 - 10:18 pm UTC
Neither
select count(distinct ename,distinct mgr) from emp
nor
select count(distinct ename,mgr) from emp
works. So this little syntax convenience doesn't scale at all.
July 08, 2004 - 8:09 am UTC
count is an aggregate function designed to count either all or distinct occurrences of an expression.
that is what it is spec'ed out to do, that is what it does.
an expression.
sorry you don't "like it", but it does exactly what it was spec'ed out to do. If you need a way to count distinct tuples -- well, you already know how to do that.
brilliant mikito... not
anthony, July 07, 2004 - 10:59 pm UTC
so, after trying to eat soup with a fork you'd deem the fork useless?
select count(distinct ename) from emp
is fine and scales as well as anything you think you can
write to answer that specific question.
now,
select count(distinct ename,distinct mgr) from emp
is obviously trying to answer a different question.
scalar subqueries answer your question easily.
select (select count (distinct ename) from emp), (select count(distinct mgr) from emp) from dual;
inline views using the "little syntax convenience" answers your question easily and does scale
select count(distinct ename), count(distinct mgr) from (select ename, mgr from emp);
ask a different question, get a different answer.
the point is,
select count(distinct ename) from emp
and
select count(*) from ( select distinct ename from emp )
are the same, so stop whining.
July 08, 2004 - 8:13 am UTC
I think he was trying to count distinct ENAME/MGR combos which would be done via:
select count(*)
from ( select distinct ename, mgr
from emp
where ename is not null
or mgr is not null );
count(*)
Steve, July 08, 2004 - 9:42 am UTC
Surely that "nice" Mikito is missing another point.
count(*) tells you how many not null values there are. I've had several occasions (typically analyzing the quality of data) when I want to know how many null values there are for each nullable column in a table.
count(*) does this fine without having to do lots of
"select count
from t
whwre c is nt null"
as Mikito would like
correction
Steve, July 08, 2004 - 9:43 am UTC
Sorry, I mean I would do
select count(c1), count(c2), count(c3) ...
from t
Null issue
Mikito, July 08, 2004 - 11:49 am UTC
Null is totally different story. In general, nulls are so non intuitive and inconsistent that a user is expected to get a surprising result anytime when null is involved. In the "count" case having explicit "where" clause IMO is much better programming style. For one thing, when you see
select count from emp where empno is not null
you don't have to consult documentation.
BTW, another ANSI SQL syntax quirk is why
select sum(sal) from emp
returns null on empty set. One don't have to be math genius to figure out that 0 is the correct answer.
July 08, 2004 - 12:12 pm UTC
everything is non-intuitive, until you learn about it.
what is non-intuitive to me, is obvious to someone else.
what is obvious to me, it clearly non-intuitive to others.
everything we do or have done in the past clouds how we view things.
To me the concept of nullality makes perfect mathematical sense, it was intuitively clear and obvious to me -- but then I never took a single computer science course, it was all abstract algebra for me in college.
Lots of things looked totally non-intuitive to me the first time I saw them. Then I learn about it, test it, break it, figure it out and -- well, who cares if it was initially non-intuitive (as most things are).
people who don't consult documentation are missing most of the functionality and proably are doing things *wrong* because they assume that "the way I think it *should* work is the way it must work"
I remember the developer who "thought" shared server should work 'pre-emptively'. Well, it doesn't, it didn't, it hasn't, it won't -- for over a decade that has been true -- but they "thought" it should work they way the would have built it, designed their application based on that false premise and failed miserably.
they should have read the documentation.
they should have bothered to learn the tool they were using.
they would have been happier.
Everything that has IMO in it is just that, opinion.
I found C to be very counter intuitive at first (I programmed with PL/I, that formed my opinion and basis of reality, C differed from my known universe).
I now find it obvious.
I found unix counter intuitive at first (I learned programming on a mainframe).
I now find it obvious (and windows to be counter intuitive).
On the empty set -- one would have to be "not smart" to figure this to be zero. To me, a math person, the empty set is the empty set -- ZERO doesn't figure into that equation *at all*.
Actually, taking that further, one should read the documentation and understand things -- like why does this first query:
ops$tkyte@ORA9IR2> select sum(null) from dual where 1=0;
SUM(NULL)
----------
actually return a row where as this one doesn't?
ops$tkyte@ORA9IR2> a group by 1;
1* select sum(null) from dual where 1=0 group by 1
ops$tkyte@ORA9IR2> /
no rows selected
if you don't understand set theory, nullality, *something* -- you'll be as efficient and effective in an RDBMS as a "programmer" without any formal training, mentor, or advice would be (eg: NOT at all).
You can choose to continue to believe things should work they way you envision them, but you'll continue to be really utterly unhappy.
Me, I'd rather understand how something actually works (rather then be mad when it doesn't work the way *I* personally would have coded it) so I can use it to its fullest extent.
But, that's a classic "IMO"
Set Theory and nullality
Martin, July 09, 2004 - 7:39 am UTC
ops$tkyte@ORA9IR2> select sum(null) from dual where 1=0;
SUM(NULL)
----------
To me this is non-intuitive :-)
I would think that this should never return a row because the predicate will never be true?
Could you please explain or point out the documentation that covers this?
July 09, 2004 - 8:08 am UTC
an Aggreate without a group by ALWAYS returns a single row. ALWAYS
it is the introduction of a group by that changes that. If there is nothing to group by -- no rows returned.
this is basically saying "the sum of NOTHING is NOTHING".
It is implied in the SQL reference manual:
<quote>
Aggregate Functions
Aggregate functions return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.
If you omit the GROUP BY clause, then Oracle applies aggregate functions in the select list to all the rows in the queried table or view. You use aggregate functions in the HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view.
</quote>
when you have a group by -- there is a group for each distinct group by set of values.
when you do not have a group by -- there is always one "group" with the aggregate functions applied to all rows in that group -- all rows, even if there are no rows that would be "all rows"
Explain please Mikito
Steve, July 09, 2004 - 11:03 am UTC
You say
"In general, nulls are so non intuitive and inconsistent"
Personally I have not found this to be the case. Have you got any examples?
Have you ever considered actually reading the documentation?
And the *count* of NOTHING is ?
Mikito, July 09, 2004 - 1:07 pm UTC
"count" aggregate is redundant as we have "sum" already. Then,
select count from dual where 1=0
should be equivalent to
select sum(1) from dual where 1=0
In general, it would be nice to maintain elementary constistency in SQL.
Returning to Steve's question, nulls were created out of desperation, and not because they have nice theoretical properties. The laws for 3 valued logic are neither obvious, nor consistent; therefore, the thesis: "Expect surprising results when nuls are involved".
An example of the the mess created by nulls is the difference between "not exist" and "not in". Common sence tells the user that both should be the same. And yet, the existence of nulls make them different. Not to mention that small SQL quirks like that make optimization, in general, and SQL transformation rules, in particular, a such a daunting task.
July 09, 2004 - 1:53 pm UTC
Jeff Hunter, July 09, 2004 - 2:37 pm UTC
ROTFL!!!
Great Reply!
Bill, July 09, 2004 - 2:54 pm UTC
BTW, If you have ten $10 bills, your count is 10 but your SUM is $100 - if you can't fathom the difference there, you have ALOT to learn!
set ignore on
Dilip Patel, July 09, 2004 - 3:03 pm UTC
BIG LOL.
A reader, July 15, 2004 - 3:24 pm UTC
Difference is (*)
~
Great discussion
Sachin, July 15, 2004 - 7:54 pm UTC
This is the first Asktom article that I read wholly from top to bottom. Good one, and indeed helpful.
Is table empty
Eric Peterson, October 22, 2004 - 3:57 pm UTC
Above you remark that to quickly find if a table has data or not is to use CBO and the primary key (i.e. select /*+ FIRST_ROWS */ empno from emp where rownum = 1;)
A co-worker asked me to identify why one of these counts is much faster than the other. So I thought I'd pass along the question. The first is the "normal" count, if there's data then return a 1. The second query stumps me as why it is much quicker. The first explain shows a STOPKEY. But the first appears to grab every row in SUBSCRIBER but is much quicker. Is the EXISTS stoping the sub-query when it find the first row?
Here's an example showing 1 minute versus 3 seconds for a 12+ million row table.
> SELECT COUNT(*) FROM us2208 WHERE ROWNUM = 1;
COUNT(*)
------------
1
1 row selected.
Elapsed: 00:01:67.12
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4485 Card=1)
1 0 SORT (AGGREGATE)
2 1 COUNT (STOPKEY)
3 2 INDEX* (FAST FULL SCAN) OF 'USAGE_22_H_PK' (UNIQUE) (Cost=4485 Card=12726980)
3 PARALLEL_TO_SERIAL
Statistics
----------------------------------------------------------
38 recursive calls
3 db block gets
18037 consistent gets
18040 physical reads
992 redo size
194 bytes sent via SQL*Net to client
248 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
> SELECT COUNT(*) FROM DUAL WHERE EXISTS ( SELECT 1 FROM us2208 );
COUNT(*)
------------
1
1 row selected.
Elapsed: 00:00:03.85
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'DUAL' (Cost=2 Card=1)
4 2 INDEX (FULL SCAN) OF 'USAGE_22_H_PK' (UNIQUE) (Cost=18722 Card=12726980)
Statistics
----------------------------------------------------------
471 recursive calls
0 db block gets
141 consistent gets
15 physical reads
0 redo size
209 bytes sent via SQL*Net to client
248 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
8 sorts (memory)
0 sorts (disk)
1 rows processed
And as a comparison to the method you descirbed. Interesting.
> SELECT /*+ FIRST_ROWS */ 1 FROM us2208 WHERE ROWNUM = 1;
1
------------
1
1 row selected.
Elapsed: 00:00:03.87
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=18722 Card=1)
1 0 COUNT (STOPKEY)
2 1 INDEX (FULL SCAN) OF 'USAGE_22_H_PK' (UNIQUE) (Cost=18722 Card=12726980)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
3 physical reads
0 redo size
202 bytes sent via SQL*Net to client
248 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
October 23, 2004 - 9:19 am UTC
parallel query kicked in the first case -- did you see that. setting up a query for parallel query takes a (relatively) long time.
but frankly -- if that last query took 3.87 seconds for real -- you must be running on an 8088 at 4.74mhz or something. that would be painfully slow. (or maybe you use a satellite link like me -- and that was the network latency at work)
Slowness of counts
Eric Peterson, October 27, 2004 - 11:42 am UTC
I wish that were the case. But no, HPUX 4-CPU machine. Gigabyte ethernet connection to the other HPUX boxes, but 100BaseT to the users. This could be the network as we do push a lot of data around our network.
I was hoping for a background description on why the two count queries perform differently.
October 27, 2004 - 12:33 pm UTC
*parallel query kicked in* did you see that.
PQ is something to use when you have a query that runs minutes, then the seconds it takes to set up the PQ sessions and get everything going "aren't visible" as you take a query that takes minutes and make it run in "less than minutes"
ok
Catherine, October 27, 2004 - 12:19 pm UTC
Hi Tom,
For Counting number of rows in a table,We sometimes issue a
statement as
SQL> select count(n) from emp;
What is this "n"?I thought it was the column number but it was not so.Could you please inform me what it is?
October 27, 2004 - 12:37 pm UTC
n had better be a column -- or it fails.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select count(n) from dual;
select count(n) from dual
*
ERROR at line 1:
ORA-00904: "N": invalid identifier
but that does <b>not count the rows in a table</b>
that counts the non-null occurences of N.
select count(*) from table -- <b>that counts rows in the table</b>
<b>everything else, anything else -- counts the non-null occurences of the expression, not the rows</b>
one more agument that count function is goofy
Mikito harakiri, November 17, 2004 - 6:17 pm UTC
emp with and without bonuses could be counted like this
select sum(case when comm is null then 1 else 0 end),
sum(case when comm is not null then 1 else 0 end)
from emp;
I challenge anybody to write a more intuitive query with the count function.
November 18, 2004 - 10:06 am UTC
Mikito --
your point is exactly?
select count( comm ), count( case when comm is null then 1 end )
from emp;
Now, since I have read the documentation
Now, since I understand how aggregates have been defined to work
Now, since I use SQL on a recurring basis
That to me is painfully intuitive.
count(comm) -- Hey, please count non-null occurrences of comm.
count( case when comm is null then 1 end ) -- Hey, when comm is null, return 1 (else null) and count the non-null occurrences of that expression.
It is a simple matter of actually reading the definition of what the aggregate function DOES
So, challenge accepted and challenge won.
You know, you don't have to like it,
but you do have to understand it -- and then use it properly.
More intuitive
Bob B, November 18, 2004 - 11:19 am UTC
Let's assume I don't know how count works. Here's a more intuitive query:
select count(*) cnt, 'Non-Comm' Type
from emp
where comm is null
union all
select count(*) cnt, 'Comm' Type
from emp
where comm is not null
No one said it had to perform better, just be more intuitive.
congratulations for winning the challenge
mikito harakiri, November 18, 2004 - 2:15 pm UTC
Tom,
Didn't you write that you don't like count(1) expression? And you used:
count( case when comm is null then 1 end )
Also, what about your economy principle "The more code I write, the more code I have to maintain"? Wouldn't "lazy programmer" enjoy similar idea applied to documentation: "The more compicated and non-intuitive feature is, the more documentation I have to read"?
Summarizing, the "count" function seems to distinguish between nulls and nonnulls in a column. It could also sum distinct values. That is quite limiting. In my previous challenge I was unfortunate to give you a leverage of a predicate with null. What if I want to count all employees with salary 1000 versus everybody else:
select sum(case when sal=1000 then 1 else 0 end),
sum(case when sal<>1000 then 1 else 0 end)
from emp;
November 18, 2004 - 2:35 pm UTC
count(1) is the wrong way to count the number of rows in a table/result set.
count(*) is the right way to count the number of rows in a table/result set.
count( <expression> ) counts the number of NON NULL occurrences of <expression>, so I had an expression that returned a NON NULL value for every row I wanted to count.
Apples and toaster ovens here -- no comparision.
ALL AGGREGATES work this way. Using your analogy -- there should be no documentation. You should just be able to look at a screen and it would intuitively know what you want and the answer would appear. The screen would blink "42" and be done with it.
<quote>
Summarizing, the "count" function seems to distinguish between nulls and
nonnulls in a column.
</quote>
is an improper statement. correctly stated would be:
Summarizing, AGGREGATEs ignore nulls.
period (very short and to the point no?)
consider:
ops$tkyte@ORA9IR2> create table t ( x int );
Table created.
ops$tkyte@ORA9IR2> insert into t values ( 1 );
1 row created.
ops$tkyte@ORA9IR2> insert into t values ( null );
1 row created.
ops$tkyte@ORA9IR2> select sum(x) from t;
SUM(X)
----------
1
ops$tkyte@ORA9IR2> set NULL *****
ops$tkyte@ORA9IR2> select 1+null from t;
1+NULL
----------
*****
*****
So, why is sum(1 and null) = 1, but 1+null = null -- well that is because of the very short rule:
AGGREGATEs ignore nulls.
count does, sum does, min does, max does, avg does, <any of them> do.
As for your last challenge, your answer is technically inaccurate perhaps.
you forgot about NULLS :)
ops$tkyte@ORA9IR2> select sum(case when sal=1000 then 1 else 0 end) sal_1000,
2 sum(case when sal<>1000 then 1 else 0 end) not_sal_1000
3 from emp;
SAL_1000 NOT_SAL_1000
---------- ------------
1 1
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select COUNT(case when sal=1000 then 1 end) sal_1000,
2 COUNT(case when sal<> 1000 or sal is null then 1 end) not_sal_1000
3 from emp;
SAL_1000 NOT_SAL_1000
---------- ------------
1 2
select COUNT(case when sal=1000 then 1 end),
COUNT(case when sal<> 1000 or sal is null then 1 end)
from emp;
count the records that have sal=1000
count the records that have sal<>1000 or sal is null
Seems more intuitive to me than
sum up some fake number.
Thnx, A Great Misconception Removed.
Dev Majmudar, November 19, 2004 - 2:56 am UTC
Hey Tom,
What a Great Guru you are. Thanx for removing the misconception with evidence. This the reason why any serious Oracle DBA or Developer has to visit your site and read your Books (I certainly do) to keep in tune with the true and fair view of Oracle Server. Thanx again.
count(rowid)
Sanjay Talati, December 07, 2004 - 1:54 am UTC
hey tom,
i do agree that both count(*) and count(1) are almost the same. they also use the same amount of resources
but can u compare them with count(rowid)... cause i have found that count(rowid) is more faster than either of them. Is there any other mechanism that oracle uses in case of count(rowid)
thanks in advance
Sanjay Talati
December 07, 2004 - 10:27 am UTC
*almost* -- no, they are *exactly* the same (for the optimizer turns count(1) into count(*) for you, to fix your query)
as for the count(*) vs count(rowid) - you'd have to PROVE THAT.
Here is my example:
select count(*) from big_table
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.45 0.75 0 2102 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.45 0.75 0 2102 0 1
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=2102 pr=0 pw=0 time=752290 us)
1000000 INDEX FAST FULL SCAN BIG_TABLE_PK (cr=2102 pr=0 pw=0 time=71000110 us)(object id 49349)
select count(rowid) from big_table
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.70 0.88 0 2102 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.70 0.89 0 2102 0 1
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=2102 pr=0 pw=0 time=889688 us)
1000000 INDEX FAST FULL SCAN OBJ#(49349) (cr=2102 pr=0 pw=0 time=82000086 us)(object id 49349)
If you think about it -- conceptually, how could count(SOMETHING) - where they have to actually count SOMETHING, check to see if SOMETHING is null in order to count it -- be faster than "just please count the rows"
count(*) - please count the rows
count(rowid) - please count the non-null occurrences of rowid
bottom line, you want to count rows? count rows - that is count(*). anything else is wrong
Question about count(*) in Oracle 8i.
KATI, December 14, 2004 - 2:18 pm UTC
I have plsql code with loop.
In a loop I want to make sql statement like:
SELECT decode(COUNT(nwpr_date),NULL,NULL,
SUM(send_qty)/COUNT(nwpr_date))INTO
l_send_avg,
decode(COUNT(nwpr_date),NULL,NULL,
SUM(sale_qty)/COUNT(nwpr_date))INTO
l_sale_avg
FROM customers;
How many times does the count(*) function will be executed?
Is it better to do in a such way:
SELECT SUM(send_qty) INTO l_send_qty,
SUM(sale_qty) INTO l_sale_qty,
COUNT(nwpr_day) INTO l_count
FROM customers;
IF nvl(l_count,0)>0
THEN
l_send_avg:=l_send_qty/l_count;
l_sale_avg:=l_sale_qty/l_count;
ELSE
l_send_avg:=null;
l_sale_avg:=null;
END IF;
THANK'S
KATI
December 15, 2004 - 12:58 pm UTC
did you know that count(expression) will never return NULL?
decode(COUNT(nwpr_date),NULL,NULL,
SUM(send_qty)/COUNT(nwpr_date))INTO
l_send_avg
is not any different than
SUM(send_qty)/COUNT(nwpr_date)
but perhaps what you intended was:
decode(COUNT(nwpr_date),0,NULL,
SUM(send_qty)/COUNT(nwpr_date))INTO
l_send_avg
to avoid a zero divide? but if you are not worried about zero divides, the code is just
SUM(send_qty)/COUNT(nwpr_date)
because even if count(expression) did return NULL, then X/NULL is NULL already!
Let SQL do your work for you. do NOT add that procedural code, just use the decode with 0 or no decode at all.
CONTINUE :DIVIDE BY COUNT(*)
A reader, December 15, 2004 - 2:10 pm UTC
Hi,Tom!Thank's a lot!
My question also was:If i have
select sum(qty)/count(*),sum(qty1)/count(*) from customers
How many times the function count(*) will be executed?
Is it effective to write in a such way?
Thank you very much.
Kati
December 15, 2004 - 6:16 pm UTC
i know what the question was -- point was -- not relevant. if you can do it in SQL, do it. adding your own procedural code would be billions times slower.
big_table@ORA9IR2> alter session set events '10046 trace name context forever, l evel 12';
Session altered.
big_table@ORA9IR2>
big_table@ORA9IR2> select count(*) from big_table;
COUNT(*)
----------
1000000
big_table@ORA9IR2> select count(*) , count(*),
2 count(*) , count(*),
3 count(*) , count(*),
4 count(*) , count(*),
5 count(*) , count(*),
6 count(*) , count(*),
7 count(*) , count(*),
8 count(*) , count(*),
9 count(*) , count(*),
10 count(*) , count(*),
11 count(*) , count(*),
12 count(*) , count(*),
13 count(*) , count(*),
14 count(*) , count(*),
15 count(*) , count(*),
16 count(*) , count(*),
17 count(*) , count(*),
18 count(*) , count(*),
19 count(*) , count(*)
20 from big_table;
COUNT(*) COUNT(*) COUNT(*) COUNT(*) COUNT(*) COUNT(*) COUNT(*)
---------- ---------- ---------- ---------- ---------- ---------- ----------
COUNT(*) COUNT(*) COUNT(*) COUNT(*) COUNT(*) COUNT(*) COUNT(*)
---------- ---------- ---------- ---------- ---------- ---------- ----------
COUNT(*) COUNT(*) COUNT(*) COUNT(*) COUNT(*) COUNT(*) COUNT(*)
---------- ---------- ---------- ---------- ---------- ---------- ----------
COUNT(*) COUNT(*) COUNT(*) COUNT(*) COUNT(*) COUNT(*) COUNT(*)
---------- ---------- ---------- ---------- ---------- ---------- ----------
COUNT(*) COUNT(*) COUNT(*) COUNT(*) COUNT(*) COUNT(*) COUNT(*)
---------- ---------- ---------- ---------- ---------- ---------- ----------
COUNT(*) COUNT(*) COUNT(*)
---------- ---------- ----------
1000000 1000000 1000000 1000000 1000000 1000000 1000000
1000000 1000000 1000000 1000000 1000000 1000000 1000000
1000000 1000000 1000000 1000000 1000000 1000000 1000000
1000000 1000000 1000000 1000000 1000000 1000000 1000000
1000000 1000000 1000000 1000000 1000000 1000000 1000000
1000000 1000000 1000000
big_table@ORA9IR2>
big_table@ORA9IR2>
select count(*)
from
big_table
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 1.42 1.52 2093 2102 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.43 1.53 2093 2102 0 1
********************************************************************************
select count(*) , count(*),
count(*) , count(*),
count(*) , count(*),
count(*) , count(*),
count(*) , count(*),
count(*) , count(*),
count(*) , count(*),
count(*) , count(*),
count(*) , count(*),
count(*) , count(*),
count(*) , count(*),
count(*) , count(*),
count(*) , count(*),
count(*) , count(*),
count(*) , count(*),
count(*) , count(*),
count(*) , count(*),
count(*) , count(*),
count(*) , count(*)
from big_table
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 1.39 1.38 0 2102 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.39 1.39 0 2102 0 1
A reader, January 25, 2005 - 11:37 am UTC
Its helpful
devender, February 10, 2005 - 4:59 am UTC
Its very much helpful to the users and developers to use suitable queries in their applications
helpful but more help is needed
A reader, September 16, 2005 - 3:37 am UTC
Hi Tom,
good day to you, just if you can shed more light on whey count(null) returns zero, you said, count(<expression>) considers not null values agreed, but why does count(null) give zero even if we have null value in column, I may be sounding silly but if you can please explain this, I had created one table with one column and null value in that column.
Kindest Regards,
your fan
September 16, 2005 - 8:23 am UTC
count( <of expression> )
returns the count of NON-NULL expressions, therefore, by definition:
count(NULL)
where NULL is the expression must return 0 since none of the expressions are NON-NULL
select count (*) from index
Gabriel, November 28, 2005 - 10:28 am UTC
Hello Tom,
Can you please tell me why can't we do a select count (*) from index but we can do a select count (*) from a IOT. Aren't they the same basic structure? Is there a trick that can be used to do a select count (*) from index?
Thank you very much,
November 28, 2005 - 1:52 pm UTC
because an index organized TABLE is a TABLE.
and an index is an index.
indexes are not TABLES
but a table can be stored in an index structure.
David Aldridge http://oraclesponge.blogspot.com, November 28, 2005 - 2:23 pm UTC
I recall that Ingres used to allow you to query an index directly (and "truncate" could be rolled-back - that was a "gotcha" waiting to happen when you started using Oracle!).
In Oracle's case the optimizer will query an index if possible (and if cheaper to do so) anyway, so there's really no need for that syntax.
hmmmm
gabriel, November 28, 2005 - 6:14 pm UTC
Hello Tom,
I always liked your insightfull comments about the inner workings of Oracle, a view into the behind the scenes of Oracle and I also bought all of your books.
The followup to my later question though belongs in a different category, but I'll explain it with a case of "Mondays" because stating the obvious doesn't count in any argument.
My question above was not one of the how to questions, I'm not trying to optimize the select count (*) because when I am doing it in one of my database it is to "slow", I am rather happy with my select count (*) from table.
My question arose more from curiosity than anything else. So if you would like, maybe you could give a more detailed explanation than tables are tables and indexes are indexes, if not, that would be ok too.
Thank you very much,
November 28, 2005 - 11:48 pm UTC
I cannot say anything more than "tables are tables and indexes are indexes, we query tables - not indexes. The optimizer chooses to use an index when it wants to"
Let me turn this around: why would you want to query "an index".
whats in a name?
Gabriel, November 29, 2005 - 9:27 am UTC
Hello Tom,
I was thinking that because an index and an iot are basically the same thing there would be some operations that we traditionally perform on tables that we could perform on indexes. I was just explring the possibilities. So what is catch with the syntax, as soon as we do a DML Oracle looks in the dictionary and if the object is a table or view allows it otherwise it doesn't?
This approach looks very legitimate but if we remember that IOTs and indexes are the same structure isn't a bit limiting at the same time?
November 30, 2005 - 10:27 am UTC
SQL is all about logical structures. Indexes are "transparent", "not meant to be seen" in SQL. You don't specify what indexes to use (well, you shouldn't), indexes are physical structures.
SQL doesn't care about the physical storage. Just like you cannot query a "cluster", you can only query tables in the cluster (not all tables are segments, some tables live in other segments).
It just doesn't "make sense" to query an index. An index is a physical structure you put in place for optimizing response time. Just like it doesn't make sense to query a cluster, a cluster is something you use to optimizer query response time.
It is just what it is. Tables are the things we query. Indexes are things the optimizer makes use of to make queries against tables go faster (sometimes - sometimes it uses them to go slow too :)
Count(*) returns zero when there are rows present
Tracy Tupman, November 30, 2005 - 4:47 am UTC
How can the following behaviour be explained. There are 6 rows in the table yet count(*) returns zero. No other process is inserting or deleting rows into this table.
ID is the primary key on this table. Something odd is happening with the ID column it seems.
SQL> select * from extractqueue;
ID TYPEID PKID
---------- ---------- ----------
30657144 300 2641712600
31081977 300 2658011800
30769400 300 2646741000
30644077 300 2640686300
30772688 300 2646983500
31071066 300 2657114300
6 rows selected.
SQL> select count(*) from extractqueue;
COUNT(*)
----------
0
SQL> select count(PKID) from extractqueue;
COUNT(PKID)
-----------
0
SQL> select count(TYPEID) from extractqueue;
COUNT(TYPEID)
-------------
0
SQL>
SQL> select count(ID) from extractqueue;
COUNT(ID)
----------
0
SQL> select count(rowid) from extractqueue;
COUNT(ROWID)
------------
0
SQL> select rowid from extractqueue where id in (30657144,31081977,30769400,30644077,30772688,31071066);
no rows selected
SQL> select rowid from extractqueue where typeid = 300;
ROWID
------------------
AAAH1+AADAAAV/UAAB
AAAH1+AADAAAWArAAB
AAAH1+AADAAAWAuAAA
AAAH1+AADAAAWAuAAB
AAAH1+AADAAAWAuAAC
AAAH1+AADAAAWBJAAC
6 rows selected.
SQL> select * from extractqueue;
ID TYPEID PKID
---------- ---------- ----------
30657144 300 2641712600
31081977 300 2658011800
30769400 300 2646741000
30644077 300 2640686300
30772688 300 2646983500
31071066 300 2657114300
6 rows selected.
SQL> select typeid from extractqueue;
TYPEID
----------
300
300
300
300
300
300
6 rows selected.
SQL> select id from extractqueue;
no rows selected
SQL> select pkid from extractqueue;
PKID
----------
2641712600
2658011800
2646741000
2640686300
2646983500
2657114300
6 rows selected.
November 30, 2005 - 11:43 am UTC
can you analyze table extractqueue validate structure cascade?
Confusion between IOT and index
Bob B, November 30, 2005 - 12:01 pm UTC
I think the confusion a couple posts above lies in the word "Index" being in IOT. Maybe a better name would be B*Tree organized table. As opposed to Heap, Partitioned, and Hash Clustered organizations.
analyze table result
Tracy Tupman, December 01, 2005 - 12:14 pm UTC
SQL> analyze table extractqueue validate structure cascade;
analyze table extractqueue validate structure cascade
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file
SQL> alter index pk_extractqueue_id rebuild online;
Index altered.
SQL> analyze table extractqueue validate structure cascade;
Table analyzed.
SQL> select * from extractqueue;
no rows selected
Rebuilding the index seems to have fixed it.
Thanks.
Reversed IOT?
Gabriel, December 01, 2005 - 9:05 pm UTC
Hello Tom,
I tend to agree with Bob B also because as I realized recently there are no reversed IOT (like there are reversed indexes).
Are there any plans though to make reversed IOT available in any future release?
Thank you,
December 02, 2005 - 10:43 am UTC
not that I am aware of - but - I would question the need.
If I wanted to efficiently retrieve something with an equality predicate - a hash cluster pops into mind.
This type of query doesn't work in Forms
A reader, December 06, 2005 - 12:29 pm UTC
Tom,
this will compile but won't work in form. Any ideas how to
make it work?
select count(*)
from dual
where exists( select null
from tablea i
where tablea.id =: 104255
and rownum = 1
December 06, 2005 - 3:24 pm UTC
that query is missing a closing ) and the : after the = sign is not meaningful (an error?)
doesn't work, hmm, very ambigous, correct real query would help and the error you are receiving would be nice.
that's ORACLE FORMS ABOVE!
A reader, December 06, 2005 - 12:53 pm UTC
sorry!
A reader, December 06, 2005 - 3:59 pm UTC
Here is it...when I tried this query in ORACLE FORMS
it doesn't work. Any ideas. It works in sqlplus.
select count(*)
from dual
where exists( select i.id
from id_table i
where i.id = ID_IN
and rownum = 1);
December 06, 2005 - 4:11 pm UTC
what is id_in and what ERROR might you or might you not be getting
(the and rownum = 1 is not useful here at all
select count(*) from dual where exists ( select null from id_table where id = ID_IN)
is sufficient OR
select count(*) from id_table where id= ID_IN and rownum = 1;
thanks
A reader, December 06, 2005 - 5:00 pm UTC
Tom,
when I use this in the oracle forms it doesn't returns
1.
December 07, 2005 - 1:33 am UTC
what does it return.
perhaps the problem is ID_IN isn't what you presume it to be. Maybe a messagebox or some other debugging on your part would help figure it out.
Thanks
A reader, December 07, 2005 - 9:36 am UTC
I was told that form doesn't understand count(*) but
just count...figures!
December 08, 2005 - 1:01 am UTC
that is FALSE.
Forms
A.Varadarajan, April 23, 2007 - 6:08 am UTC
To revisit a question not answered a long time back
select count(*)
from dual
where exists( select i.id
from id_table i
where i.id = ID_IN
and rownum = 1);
will have to be written as
select count(*) INTO :var1
from dual
where exists( select i.id
from id_table i
where i.id = ID_IN
and rownum = 1);
for the code to work
why count(*) uses faull scan instead of fast full
A reader, May 24, 2007 - 10:28 am UTC
Hi
Sometimes when I count a table it uses index full scan and sometimes index fast full scan. Both have Primary Keys.
Do you know why?
May 26, 2007 - 11:33 am UTC
give us a "for example" and then tell us what is different about the two situations.
then, we can isolate our wild guesses down to something reasonable based on those inputs.
Count(1) is faster
Sagar, July 23, 2007 - 5:58 am UTC
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 14 18:21:14 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys as sysdba
Enter password: *****
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 92276068 bytes
Database Buffers 71303168 bytes
Redo Buffers 2945024 bytes
Database mounted.
Database opened.
SQL> conn scott/tiger@ocp10g ;
Connected.
SQL> set lines 10000
SQL> set autotrace on
SQL> set timing on
SQL> select count(*) from dept1;
COUNT(*)
----------
8388608
Elapsed: 00:07:00.79
Execution Plan
----------------------------------------------------------
Plan hash value: 1828124803
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1801 (4)| 00:00:22 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| DEPT1 | 2107K| 1801 (4)| 00:00:22 |
--------------------------------------------------------------------
Statistics
----------------------------------------------------------
208 recursive calls
0 db block gets
31678 consistent gets
31644 physical reads
0 redo size
414 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> conn sys/shree@ocp10g as sysdba ;
Connected.
SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> conn / as sysdba ;
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 88081764 bytes
Database Buffers 75497472 bytes
Redo Buffers 2945024 bytes
Database mounted.
Database opened.
SQL> conn scott/tiger@ocp10g ;
Connected.
SQL> set lines 10000
SQL> set autotrace on
SQL> set timing on
SQL> select count(1) from dept1;
COUNT(1)
----------
8388608
Elapsed: 00:04:26.39
Execution Plan
----------------------------------------------------------
Plan hash value: 1828124803
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1801 (4)| 00:00:22 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| DEPT1 | 2107K| 1801 (4)| 00:00:22 |
--------------------------------------------------------------------
Statistics
----------------------------------------------------------
212 recursive calls
0 db block gets
31678 consistent gets
31645 physical reads
0 redo size
414 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
July 23, 2007 - 8:41 am UTC
nope, your test is flawed. run the count(*) after the count(1) and then ask yourself what effect the file system buffer cache might possibly be having here...
Sagar, July 24, 2007 - 3:50 am UTC
The test is correct I think.
To clear caches I have restarted the database each time while doing the queries.Please see the example given more closely.
July 24, 2007 - 9:48 am UTC
FILE SYSTEM BUFFER CACHE.
I don't care about the database being restarted. Please do what I said - post your test case after reversing the order of the queries.....
count(1) is internally rewritten as count(*)
Test run
Sagar, July 24, 2007 - 5:50 am UTC
Thanks Tom,
If you meant OS level caching effect,
I will test run in reverse order and will post the results shortly.
Another way to do distinct count
A reader, July 24, 2007 - 3:37 pm UTC
I was reading this entire thread and I wanted to respond to a response from Mikito in 2004.
One way to do a count of distinct multiple columns is by using
select count(*)
from
(select distinct col1,col2
from my_table);
However, another way that I have used in the past is
select count(distinct col1||col2)
from my_table;
I think that the count(distinct col1||col2) is cleaner to read and faster then the first method
whb@xe>select count(distinct owner||object_type)
2 from x;
COUNT(DISTINCTOWNER||OBJECT_TYPE)
---------------------------------
54
[code]
Execution Plan
----------------------------------------------------------
Plan hash value: 2477198225
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 10 (0)| 00:00:01 |
| 1 | SORT GROUP BY | | 1 | 28 | | |
| 2 | TABLE ACCESS FULL| X | 5308 | 145K| 10 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
59 consistent gets
0 physical reads
0 redo size
436 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
[/code]
1 select count(*)
2 from
3 (select distinct owner,object_type
4* from x)
whb@xe>/
COUNT(*)
----------
54
[code]
Execution Plan
----------------------------------------------------------
Plan hash value: 399537838
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 55 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | | | |
| 2 | VIEW | | 5308 | | | 55 (4)| 00:00:01 |
| 3 | HASH UNIQUE | | 5308 | 145K| 392K| 55 (4)| 00:00:01 |
| 4 | TABLE ACCESS FULL| X | 5308 | 145K| | 10 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
29 consistent gets
0 physical reads
0 redo size
411 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
[/code]
July 26, 2007 - 8:47 am UTC
but count(col1||col2) is wrong, whereas count(*) from distinct col1, col2 is correct.
they return different answers.
ops$tkyte%ORA10GR2> create table t ( c1 int, c2 int );
Table created.
ops$tkyte%ORA10GR2> insert into t values ( 1, 11 );
1 row created.
ops$tkyte%ORA10GR2> insert into t values ( 11, 1 );
1 row created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select count(distinct c1||c2) from t;
COUNT(DISTINCTC1||C2)
---------------------
1
ops$tkyte%ORA10GR2> select count(*) from (select distinct c1, c2 from t);
COUNT(*)
----------
2
yes, I know, you can "fix that", but what remains is that creating a temporary like that with string concatenation is not going to be as cpu efficient as leaving the columns be - I think the second variant is more expressive, says what you are doing very clearly.
Almost
Loz, July 24, 2007 - 9:35 pm UTC
You need to delimit your data.
A B
- -
XX YY
X XYY
2 distinct rows but (A||B) would give you 1.
A reader, July 26, 2007 - 12:26 am UTC
scott@ORA10G> select count(1) from big_table;
COUNT(1)
----------
1015269
Elapsed: 00:00:00.37
Execution Plan
----------------------------------------------------------
Plan hash value: 1764098166
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 590 (5)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| BIG_TABLE_PK | 1018K| 590 (5)| 00:00:06 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2390 consistent gets
2377 physical reads
140816 redo size
426 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
scott@ORA10G> select count(*) from big_table;
COUNT(*)
----------
1015269
Elapsed: 00:00:00.28
Execution Plan
----------------------------------------------------------
Plan hash value: 1764098166
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 590 (5)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| BIG_TABLE_PK | 1018K| 590 (5)| 00:00:06 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2390 consistent gets
2377 physical reads
140816 redo size
426 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
You are absolutely right !!!
Sagar, July 26, 2007 - 2:17 am UTC
Hi Tom ,
Yes, You are absolutely right.I completely overlooked the OS caching.And the test is not a perfect benchmark.See output below for reverse order of queries under similar circumstances.
Another option was to restart the OS for each query and try to benchmark.But its not worth trying because even if in such scenario count(1) proves faster we will have to investigate reasons for count(1) being faster given the fact that count(1) is rewritten as count(*).Thanks for valuable inputs.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 79693156 bytes
Database Buffers 83886080 bytes
Redo Buffers 2945024 bytes
Database mounted.
Database opened.
SQL> conn scott/tiger@ocp10g
Connected.
SQL> set lines 10000
SQL> set timing on
SQL> set autotrace on
SQL> select count(1) from dept1;
COUNT(1)
----------
8388608
Elapsed: 00:03:57.35
Execution Plan
----------------------------------------------------------
Plan hash value: 1828124803
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6176 (5)| 00:01:15 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| DEPT1 | 9735K| 6176 (5)| 00:01:15 |
--------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
235 recursive calls
1 db block gets
53990 consistent gets
26982 physical reads
1939472 redo size
414 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 83887460 bytes
Database Buffers 79691776 bytes
Redo Buffers 2945024 bytes
Database mounted.
Database opened.
SQL> conn scott/tiger@ocp10g
Connected.
SQL> set lines 10000
SQL> set timing on
SQL> set autotrace on
SQL> select count(*) from dept1;
COUNT(*)
----------
8388608
Elapsed: 00:02:33.60
Execution Plan
----------------------------------------------------------
Plan hash value: 1828124803
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6176 (5)| 00:01:15 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| DEPT1 | 9735K| 6176 (5)| 00:01:15 |
--------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
index full scan
A reader, July 27, 2007 - 7:24 am UTC
Hello ,Mr. Tom!
Could you kindly give me some expain on this case below ,why oracle choose use IFS(INDEX FULL SCAN) ?I think I would be a FTS(FULL TABLE SCAN)?
SQL> create table emp1 as select * from emp nologging;
Table created.
SQL> create index idx_test on emp1(empno,ename,deptno);
Index created.
SQL> analyze table emp1 compute statistics for table for all indexes for all indexed columns;
Table analyzed.
SQL> select empno,ename,deptno from emp1 where ename='SCOTT';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=10)
1 0 INDEX (FULL SCAN) OF 'IDX_TEST' (NON-UNIQUE) (Cost=1 Card=1 Bytes=10)
July 27, 2007 - 3:06 pm UTC
why full scan a wide table, when you can full scan a skinny index?
and emp is teeny tiny, not very useful for this example.
why would you want it to full scan the table?
Create new small defaulted not null column indexed
Rolfe Hare, May 08, 2008 - 8:48 am UTC
Here is my contribution having read the whole thread (oh yes - I really mean "the whole thread". We have an application that is dying (thank god) and are trying to "keep it alive" just a few more months until its replacement kicks in and we all get to do an "rm -fR *" on each of the serveres that it runs on and get completely drunk to celebrate its long awaited demise.
Meanwhile we can do pretty much what we like to get it to process more than 1 Message per second and one problem identified was a "select count(*)" from a table that had 22 million rows and was about 1000 bytes wide max. Even after deleting rows down to 6.2 million rows the select count(*) was still taking 90 seconds (full table scan). The table has one primary index "uniqueid" NOT NULL VARCHAR2(32) but selecting a count(uniqueid) wasn't any faster either.
So I used some lateral thinking and added a new varchar2(1) column "COUNTME" default 1 not null, tried a count(countme) and got 60 physical reads instead of 14700 previously - .3 seconds instead of 90 seconds. Then to my joy the "select count(*)" also used the index (V 10.1.0.3.0) and we may have a super solution. AS LONG AS THE APPLICATION DOESN'T CREATE ERRORS ON INSERT BECAUSE OF THE NEW COLUMN !) (Testing for that now)
How should i use index to count a big table
puslukita, February 19, 2009 - 10:55 am UTC
Hello Tom,
I have large table, and i need to count rows in it. ¿t is a "users" table,
but when i use :select count(*) from users; it takes too long time. Should i use Index to make response time shorter.And also i don't know the way of Index usage to count for the all users in it , without any specialization for features of it. this is the table that i am deailing with:
SQL> desc users;
Name Null? Type
----------------------------------------- -------- ----------------------------
USER_NUMBER NOT NULL NUMBER(10)
USER_LOGINNAME NOT NULL VARCHAR2(253)
USER_GROUP_ID NOT NULL NUMBER(10)
VPN_ID NOT NULL NUMBER(4)
PW_LAST_UPDATE NOT NULL DATE
ADMINISTRATION_ID NUMBER(10)
PAYING_ADMINISTRATION_ID NUMBER(10)
FACTURATION_ID NUMBER(10)
USAGE NUMBER(5)
ACTIVATION_TIME DATE
DEACTIVATION_TIME DATE
HOLD_INDICATOR NUMBER(1)
FIX VARCHAR2(15)
CREDIT_UPDATE_TIME DATE
TIME_CREDIT_FREE NUMBER(10)
TIME_CREDIT_PAYED NUMBER(10)
PSW_OPER_STATE NUMBER(1)
BILLING_GROUP_ID NUMBER(10)
February 19, 2009 - 1:41 pm UTC
tell me first why you actually need to count rows in this table often?
do you have an index on any of the NOT NULL columns above? Are there any indexes that include any of those columns?
why we can't use count(2) for count(1) ?
mohan, May 11, 2010 - 7:12 am UTC
why we can't use count(2) for count(1) ?
count(2)
select dept_id,count(2) from abcdef
group by dept_id
having
count(2)=(select min(count(2)) from abcdef group by dept_id)
count(1)
elect dept_id,count(1) from abcdef
group by dept_id
having
count(1)=(select min(count(1)) from abcdef group by dept_id)
both query return same value
count(*) taking enough time than expected
Praveen Ray, October 10, 2011 - 6:44 am UTC
Hi Tom,
I ran the query: select count(*) from tabx; on my production database, and waited for more than 2 min. in order to see 44729982 rows. I created a same structured table with random data of same number and fired the same query in my personal database (laptop) to see the same result in few seconds. The server hardware components are of latest configuration. And, I had expected to see the query output in even lesser time. I need your opinion about this situation.
Ray
October 10, 2011 - 10:51 am UTC
Your laptop is single user (and probably has a much faster cpu for that single user than your big bad production machine, if your laptop is relatively current).
Your production machine is not.
So, I would suspect you were waiting for a shared resource (IO, Memory, CPU) on the production machine and no so much on your laptop.
trace it - see what you see, get a tkprof report like I always show and it'll likely become obvious.
Also, your production table could be much much much larger than your test table - due to deletes (it could have been 88million rows once, deleted down to 44million - it might be many times larger than your test table - a tkprof would show you that as well - you'd see more IO)
count one value
A reader, October 17, 2011 - 10:14 pm UTC
We have ONE_BIG_TABLE with millions of records.
There is a state column and we need to check if there is one record with state = "CA", can we run a count that would stop on the first "CA" record found instead of doing a FULL table or index scan.
select count(*) from ONE_BIG_TABLE where state = 'CA';
we just need to know if l_cnt = 0 or l_cnt > 0.
October 17, 2011 - 10:40 pm UTC
select count(*) from one_big_table where state = 'CA' and rownum = 1;
but I would actually say you probably don't need to do this, your code is probably:
count CA
if cnt >1 then do something end if
I say that should just be:
do something
do something will just return when it finds no data to process.
Order of "execution"
Kim Berg Hansen, October 18, 2011 - 1:51 am UTC
Tom, you gave this response:
select count(*) from one_big_table where state = 'CA' and rownum = 1;
That just sparked a thought :-)
Had I not "known better", I might have thought "OK, rownum is applied to output rows, so Oracle will first get the entire count which will be the one output row that rownum is applied to."
But no - it works out that rownum is applied before the aggregation. (Just as rownum is applied before the order by which is important to know in top-n queries :-)
Is there someplace (for example in concepts manual) where we can read a complete "order of execution" of the different parts of a SQL statement? Now-a-days a SQL statement can contain so many different parts, so sometimes one could wonder "is analytic before model clause?", "is pivot after rownum?", etc.
I guess it is not a very easy diagram to setup with possible optimizer rewrites :-) I do not remember having seen such a "flow diagram" of a SQL statement, but it may be tucked in a corner of the manual somewhere?
Thanks
(PS. Happy to hear you're visiting Denmark for the DOUG day :D )
October 18, 2011 - 2:18 am UTC
There is no "overview", each one would have its say where it is documented.
but in general, they work like this:
a row is selected by your predicate (minus any rownum predicates - and since analytics cannot appear in the where clause - so it doesn't count)
then grouping would take place (aggregation)
then any other set operations would happen (union, intersect, minus, etc)
and then sorting
You can alter this with inline views - for example you can "where" on analytics after they've been computed:
select * from (select a,b,c,row_number() over (order by x) rn from t) where rn <= 5;
works and because of the above list:
select * from t where rownum <= 5 order by x
is different from
select * from (select * from t order by x) where rownum <= 5;
the first one says "get five rows then sort them", the second one says "after sorting t by x, return the first five rows"
but analytics and rownum are very similiar in ordering.
EXISTS
Loz, October 18, 2011 - 7:14 am UTC
Wouldn't EXISTS be the "official" and most semantically obvious way of determining this?
October 18, 2011 - 4:51 pm UTC
you'd have to do something like:
select count(*) from dual where exists( select null from t where state='CA');
I personally don't like that syntax - or the semantics of it. It says to count the rows in DUAL (not T) such that there is a row in T where the state = 'CA'
the
select count(*) from t where state = 'CA' and rownum = 1;
says (to me anyway)
count the rows in T where the state is 'CA', stop counting after the first row.
To me that is more meaningful - since it is obviously counting rows in T (whereas the other has to count rows in some other unrelated table). For that reason by itself, I prefer this syntax.
You have to read some more "semantics" into rownum - you cannot think of it as a simple predicate - to get my reading, but to me - it is "more clear"
EXISTS
Michel Cadot, October 19, 2011 - 3:12 am UTC
In the case of EXISTS the following is clearer:
select 'Present' from dual
where exists (select null from t where state='CA');
And catch the NO_DATA_FOUND exception in PL/SQL.
Or if it is a plain SQL*Plus script you can use something like:
def flag=0
col flag new_value flag
select 1 flag from dual
where exists (select null from t where state='CA');
Then &flag is 0 or 1 depending if a row exists or not.
Regards
Michel
October 19, 2011 - 6:04 pm UTC
Not to me.
You are querying dual? Why dual? What is the purpose of dual in that query? I want to ask a question about T, not about dual - dual is confusing to me.
We just have to disagree on this one. I will continue to code:
select ... from t where .... AND ROWNUM = 1;
a matter of pure opinion on this one. The performance should be roughly equivalent (with a slight edge to the rownum=1 since it hits a single table... fast dual is faster than dual - but slower than "this space left intentionally blank"...
Loz, October 19, 2011 - 5:04 am UTC
Thanks Michel,
That's exactly what I was getting at. Next time I won't be so lazy and post some code for clarification. Unfortunately it will be SQL Server these days :(
Another Exists Query
Alan Stewart, October 20, 2011 - 8:07 am UTC
For "exists" queries, I have used the following
for some time.
select max(1)
from all_tables
where owner = 'SYS'
and rownum < 2;
Or this if I want a non-null result.
select nvl(max(1),0)
from all_tables
where owner = 'PUBLIC'
and rownum < 2;
This is similar to Tom's preferred query (which most
certainly gets the job done quite well). I do not need
to count rows, so count(*) seems like a clever coding
trick to me.
Prabaharan, July 02, 2014 - 12:00 pm UTC
I used ,
select * from emp;
select count(1) from emp;
Explain plan displays:
Plan
1 Every row in the table SCOTT.EMP is read.
2 Rows were returned by the SELECT statement.
Plan
1 Rows were retrieved by performing a sequential read of all records in index SCOTT.PK_EMP in ascending order.
2 The rows were sorted to support a group operation (MAX,MIN,AVERAGE, SUM, etc).
3 Rows were returned by the SELECT statement.
Diff b/n count(*) & Count(1)
Mohan, February 03, 2015 - 5:33 pm UTC
The major difference i see with count(*) & count(1) is in a RETURNING clause.
Count(*) will always returns '0' whereas count(1) returns the correct count in a Returning clause.
for example:
-------------
-------------
declare
v_cnt number;
begin
delete from emp where rownum<5 returning count(*) into v_cnt;
dbms_output.put_line (v_cnt);
end;
-- Output will always be '0'
declare
v_cnt number;
begin
delete from emp where rownum<5 returning count(1) into v_cnt;
dbms_output.put_line (v_cnt);
end;
-- Output is '4' in this case
Hope this helps someone....
I don't agree ...
Marcelo, May 26, 2015 - 1:16 pm UTC
Oracle 11.2.0.3 on AIX 6.1
SQL> set serveroutput on
SQL> declare
2 v_cnt number;
3 begin
4 delete from a where rownum<5 returning count(1) into v_cnt;
5 dbms_output.put_line (v_cnt);
6 end;
7 /
3 <<<<====
PL/SQL procedure successfully completed.
SQL> rollback;
Rollback complete.
SQL> declare
2 v_cnt number;
3 begin
4 delete from a where rownum<5 returning count(*) into v_cnt;
5 dbms_output.put_line (v_cnt);
6 end;
7 /
3 <<<<====
PL/SQL procedure successfully completed.
SQL> rollback;
Rollback complete.
SQL>
when count(*) WILL be faster than count(1)
Snehasish Das, October 05, 2015 - 3:05 pm UTC
Hi ,
Good day.
I just read the blog from Jonathan Lewis where he says sometimes count(*) is faster than count(1).
https://jonathanlewis.wordpress.com/2015/01/09/count-again/ Can you please help me to find scenarios where count(*) is faster so that we can make those adjustments.
Regards,
Snehasish Das
October 05, 2015 - 4:35 pm UTC
As Jonathan says in the post, this can occur if you have bitmap indexes and have set cursor_sharing = force.