Skip to Main Content
  • Questions
  • difference between count(1) and count(*)

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Ganesh.

Asked: August 12, 2001 - 11:01 pm UTC

Last updated: October 05, 2015 - 4:35 pm UTC

Version: 3.0

Viewed 100K+ times! This question is

You Asked

What is the difference between count(1) and count(*) in a sql query
eg.
select count(1) from emp;
and
select count(*) from emp;



and Tom said...

nothing, they are the same, incur the same amount of work -- do the same thing, take the same amount of resources.


You can see this via:

ops$tkyte@ORA817.US.ORACLE.COM> alter session set sql_trace=true;

Session altered.

ops$tkyte@ORA817.US.ORACLE.COM> select count(*) from all_objects;

COUNT(*)
----------
27044

ops$tkyte@ORA817.US.ORACLE.COM> select count(1) from all_objects
2 /

COUNT(1)
----------
27044


and the tkprof will show:



select count(*)
from
all_objects


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 5.56 5.56 0 234998 4 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 5.58 5.58 0 234998 4 1

select count(1)
from
all_objects


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 5.46 5.47 0 234998 4 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 5.48 5.49 0 234998 4 1

Same number of blocks read/written/processed, same cpu times (basically) same elapsed times (basically).

they are identical.


Anyone who thinks different (and I know you are out there) will have to post a test case like the above or some scientific proof otherwise to be taken seriously....



Rating

  (103 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

And just in case...

Connor, August 13, 2001 - 11:44 am UTC

And just before anyone jumps on the "count(primary key) is better" bandwagon, they should take a look at the example on </code> http://www.oracledba.co.uk/tips/count_speed.htm <code>which shows (as Tom points out) that they all work the same nowadays...

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>



Tom Kyte
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). "

Tom Kyte
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

Tom Kyte
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)          

 

Tom Kyte
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)

 

Tom Kyte
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

Tom Kyte
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.oracledba.co.uk/tips/count_speed.htm

Looking around in oracledba.co.uk site, found another interesting link, in:

http://oracledba.co.uk/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
 

Tom Kyte
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


Tom Kyte
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?



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

Tom Kyte
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?

Pourpose of sample clause...

Sven Bleckwedel, March 17, 2004 - 7:25 am UTC

Srinivas,

Sample clause is used to return randomly selected data.

>sample(10) is 10%
>
>See the SQL Reference for information on SQL statements!
>
>
></code> http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/state21b.htm#2065954 <code>
>
>sample(10) will return about 10% of the rows randomly.

Sven


Count with * or 1 parameter, that´s the question...

Sven Bleckwedel, March 17, 2004 - 7:41 am UTC

Tarun,

q1) Doesn´t matter. See:

</code> http://oracledba.co.uk/tips/count_speed.htm

q2) Maybe is the same case as:

>select count(0) is just like 
>
>select count(*)
>  from ( select 0 from t )
>/
>
>...oracle went to each block to find the rows to give >you a zero

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:880343948514, <code>

Sven


count(column_name)

A reader, March 18, 2004 - 5:40 pm UTC

Is count(column_name) also the same as count(*) or count(1)



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

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.
 

Tom Kyte
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) 

Tom Kyte
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

 

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

 

Tom Kyte
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

Tom Kyte
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

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


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


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


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

Tom Kyte
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?

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

Tom Kyte
July 09, 2004 - 1:53 pm UTC


SQL> set ignore on;

 

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


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

Tom Kyte
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?

 

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

Tom Kyte
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;


Tom Kyte
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

Tom Kyte
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


Tom Kyte
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

Tom Kyte
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

Tom Kyte
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,

Tom Kyte
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,

Tom Kyte
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?

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




 

Tom Kyte
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,

Tom Kyte
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

Tom Kyte
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);

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




Tom Kyte
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!




Tom Kyte
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?
Tom Kyte
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

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


Tom Kyte
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]

Tom Kyte
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)


Tom Kyte
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)

Tom Kyte
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
Tom Kyte
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.
Tom Kyte
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 )
Tom Kyte
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?
Tom Kyte
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

Tom Kyte
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
Chris Saxon
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.