And just in case...
August 13, 2001 - 11am Central time zone
Reviewer: Connor from UK (wish I wasn't)
And just before anyone jumps on the "count(primary key) is better" bandwagon, they should take a
look at the example on http://www.oracledba.co.uk/tips/count_speed.htm which shows (as Tom points out) that they all work the same nowadays...
GOOD ANSWER
August 13, 2001 - 12pm Central time zone
Reviewer: Ganesh A from Pune,India
a test result show difference between count(*) and count(1)
August 31, 2001 - 4am Central time zone
Reviewer: Dulimin from BJ,China
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>
Followup August 31, 2001 - 7am Central time zone:
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...
August 31, 2001 - 8am Central time zone
Reviewer: A reader
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
September 3, 2001 - 2am Central time zone
Reviewer: dulimin from BJ, China
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)
September 4, 2001 - 12am Central time zone
Reviewer: Mikito Harakiri from SF, CA US
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
September 4, 2001 - 1am Central time zone
Reviewer: Vadim Tropashko from Foster City, CA
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:-)
June 10, 2003 - 2pm Central time zone
Reviewer: Mikito Harakiri
select -1/2+1/2*sqrt(1+8*sum(rownum)) from emp
Could you please explain the query as well, Mikito?
June 12, 2003 - 10am Central time zone
Reviewer: Reader
Mikito is a show off.....
July 24, 2003 - 4pm Central time zone
Reviewer: Nick from NY
Ok, so which performs better ; )
Hmmmm...
August 4, 2003 - 8am Central time zone
Reviewer: A reader
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). "
Followup August 4, 2003 - 9am Central time zone:
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
August 4, 2003 - 9am Central time zone
Reviewer: A reader
<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!!!!!!!!
August 10, 2003 - 5pm Central time zone
Reviewer: Saeed Ahmed from India.
I am amazed to see such a basic things are discused here, This is a lovely forum!
Thank you very much.
August 13, 2003 - 5pm Central time zone
Reviewer: Su Baba
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
Followup August 13, 2003 - 7pm Central time zone:
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"
August 13, 2003 - 8pm Central time zone
Reviewer: Su Baba
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)
Followup August 13, 2003 - 9pm Central time zone:
do this now
delete from x;
commit;
don't analyze
compare the performance. see what happens when it accidently full scans
use first_rows.
August 13, 2003 - 9pm Central time zone
Reviewer: Su Baba
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)
Followup August 14, 2003 - 7am Central time zone:
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)
August 14, 2003 - 8am Central time zone
Reviewer: Helena Marková from Bratislava, Slovakia
I have found another opinion
October 20, 2003 - 4pm Central time zone
Reviewer: Sergej from Germany
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
Followup October 20, 2003 - 8pm Central time zone:
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!
October 21, 2003 - 7am Central time zone
Reviewer: Jens from Hamburg
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
October 21, 2003 - 8am Central time zone
Reviewer: freek from Belgium
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
October 21, 2003 - 9am Central time zone
Reviewer: Tony Andrews from London, UK
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...
March 16, 2004 - 1pm Central time zone
Reviewer: Sven Bleckwedel from Santos, SP - Brasil
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/ask/f?p=4950:8:1931342621091931282::NO::F4950_P8_DISPLAYID,F4950_P8_CRI
TERIA:6075151195522,
Maybe this can help something...
Rgds,
Sven
Followup March 16, 2004 - 1pm Central time zone:
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 ?
March 16, 2004 - 2pm Central time zone
Reviewer: Sven Bleckwedel from Santos, SP - Brasil
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
Followup March 16, 2004 - 2pm Central time zone:
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
March 17, 2004 - 12am Central time zone
Reviewer: Ben from Vancouver,Canada
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?
Followup March 17, 2004 - 7am Central time zone:
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
March 17, 2004 - 2am Central time zone
Reviewer: Tarun from India
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.
Followup March 17, 2004 - 7am Central time zone:
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
March 17, 2004 - 3am Central time zone
Reviewer: Srinivas from Hyderbad,AP,India
Hello Sir,
What is the use of "sample clause " in a sql select statement?
Pourpose of sample clause...
March 17, 2004 - 7am Central time zone
Reviewer: Sven Bleckwedel from Santos, SP - Brasil
Srinivas,
Sample clause is used to return randomly selected data.
>sample(10) is 10%
>
>See the SQL Reference for information on SQL statements!
>
>
> http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/state21b.htm#2065954 >
>sample(10) will return about 10% of the rows randomly.
Sven
Count with * or 1 parameter, that´s the question...
March 17, 2004 - 7am Central time zone
Reviewer: Sven Bleckwedel from Santos, SP - Brasil
Tarun,
q1) Doesn´t matter. See:
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/ask/f?p=4950:8:3678029641418332880::NO::F4950_P8_DISPLAYID,F4950_P8_CRI
TERIA:880343948514,
Sven
count(column_name)
March 18, 2004 - 5pm Central time zone
Reviewer: A reader
Is count(column_name) also the same as count(*) or count(1)
Followup March 18, 2004 - 6pm Central time zone:
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
March 19, 2004 - 4am Central time zone
Reviewer: Tarun Babu from Hyderabad, India
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
March 19, 2004 - 7am Central time zone
Reviewer: Ram from Bangalore,India
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.
Followup March 19, 2004 - 9am Central time zone:
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)
March 19, 2004 - 8am Central time zone
Reviewer: Tomas from Lithuania, Europe
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
May 12, 2004 - 7pm Central time zone
Reviewer: Maurice
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)
Followup May 13, 2004 - 9am Central time zone:
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
July 2, 2004 - 1pm Central time zone
Reviewer: Sreenivas Gangavarapu from Chicago, IL USA
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
Followup July 2, 2004 - 2pm Central time zone:
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"
July 2, 2004 - 4pm Central time zone
Reviewer: Sreenivas Gangavarapu from Chicago, IL USA
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.
Followup July 2, 2004 - 4pm Central time zone:
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.
July 2, 2004 - 5pm Central time zone
Reviewer: Arun Gupta from Harrisburg, PA USA
Tom,
How did you figure out that the indexes were almost as big as the table in the question by
Sreenivas Gangavarapu?
Thanks
Followup July 2, 2004 - 6pm Central time zone:
by looking at the consistent gets in his autotrace output above.
July 2, 2004 - 5pm Central time zone
Reviewer: Sreenivas Gangavarapu from Chicago, IL USA
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
Followup July 2, 2004 - 7pm Central time zone:
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.
July 2, 2004 - 6pm Central time zone
Reviewer: A reader
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!
July 6, 2004 - 8pm Central time zone
Reviewer: Mikito Harakiri
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.
Followup July 6, 2004 - 8pm Central time zone:
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
July 7, 2004 - 4pm Central time zone
Reviewer: anthony from baltimore, maryland
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:
http://ar.essortment.com/babydiaperrash_rtzj.htm
And how do I count distict combination of columns?
July 7, 2004 - 10pm Central time zone
Reviewer: Mikito
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.
Followup July 8, 2004 - 8am Central time zone:
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
July 7, 2004 - 10pm Central time zone
Reviewer: anthony from baltimore, maryland
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.
Followup July 8, 2004 - 8am Central time zone:
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(*)
July 8, 2004 - 9am Central time zone
Reviewer: Steve
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
July 8, 2004 - 9am Central time zone
Reviewer: Steve
Sorry, I mean I would do
select count(c1), count(c2), count(c3) ...
from t
Null issue
July 8, 2004 - 11am Central time zone
Reviewer: Mikito
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.
Followup July 8, 2004 - 12pm Central time zone:
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
July 9, 2004 - 7am Central time zone
Reviewer: Martin from Netherlands
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?
Followup July 9, 2004 - 8am Central time zone:
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
July 9, 2004 - 11am Central time zone
Reviewer: Steve
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 ?
July 9, 2004 - 1pm Central time zone
Reviewer: Mikito
"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.
Followup July 9, 2004 - 1pm Central time zone:
SQL> set ignore on;
July 9, 2004 - 2pm Central time zone
Reviewer: Jeff Hunter from greenwich.ct.us
ROTFL!!!
Great Reply!
July 9, 2004 - 2pm Central time zone
Reviewer: Bill from Vermont
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
July 9, 2004 - 3pm Central time zone
Reviewer: Dilip Patel from MD, USA.
BIG LOL.
July 15, 2004 - 3pm Central time zone
Reviewer: A reader
Difference is (*)
~
Great discussion
July 15, 2004 - 7pm Central time zone
Reviewer: Sachin from San Ramon, CA
This is the first Asktom article that I read wholly from top to bottom. Good one, and indeed
helpful.
Is table empty
October 22, 2004 - 3pm Central time zone
Reviewer: Eric Peterson from Issaquah, WA
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
Followup October 23, 2004 - 9am Central time zone:
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
October 27, 2004 - 11am Central time zone
Reviewer: Eric Peterson from Issaquah, WA
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.
Followup October 27, 2004 - 12pm Central time zone:
*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
October 27, 2004 - 12pm Central time zone
Reviewer: Catherine from OH,USA
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?
Followup October 27, 2004 - 12pm Central time zone:
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 not count the rows in a table
that counts the non-null occurences of N.
select count(*) from table -- that counts rows in the table
everything else, anything else -- counts the non-null occurences of the expression, not the
rows
one more agument that count function is goofy
November 17, 2004 - 6pm Central time zone
Reviewer: Mikito harakiri
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.
Followup November 18, 2004 - 10am Central time zone:
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
November 18, 2004 - 11am Central time zone
Reviewer: Bob B from Albany, NY
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
November 18, 2004 - 2pm Central time zone
Reviewer: mikito harakiri
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;
Followup November 18, 2004 - 2pm Central time zone:
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.
November 19, 2004 - 2am Central time zone
Reviewer: Dev Majmudar from Bombay, India
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)
December 7, 2004 - 1am Central time zone
Reviewer: Sanjay Talati from India
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
Followup December 7, 2004 - 10am Central time zone:
*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.
December 14, 2004 - 2pm Central time zone
Reviewer: KATI
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
Followup December 15, 2004 - 12pm Central time zone:
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(*)
December 15, 2004 - 2pm Central time zone
Reviewer: A reader
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
Followup December 15, 2004 - 6pm Central time zone:
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
January 25, 2005 - 11am Central time zone
Reviewer: A reader
Its helpful
February 10, 2005 - 4am Central time zone
Reviewer: devender from Hyderabad, AP ,India
Its very much helpful to the users and developers to use suitable queries in their applications
helpful but more help is needed
September 16, 2005 - 3am Central time zone
Reviewer: A reader
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
Followup September 16, 2005 - 8am Central time zone:
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
November 28, 2005 - 10am Central time zone
Reviewer: Gabriel from Montreal, Canada
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,
Followup November 28, 2005 - 1pm Central time zone:
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.
November 28, 2005 - 2pm Central time zone
Reviewer: David Aldridge http://oraclesponge.blogspot.com from Colorado Springs
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
November 28, 2005 - 6pm Central time zone
Reviewer: gabriel from montreal, canada
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,
Followup November 28, 2005 - 11pm Central time zone:
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?
November 29, 2005 - 9am Central time zone
Reviewer: Gabriel from Montreal, Canada
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?
Followup November 30, 2005 - 10am Central time zone:
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
November 30, 2005 - 4am Central time zone
Reviewer: Tracy Tupman from UK
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.
Followup November 30, 2005 - 11am Central time zone:
can you analyze table extractqueue validate structure cascade?
Confusion between IOT and index
November 30, 2005 - 12pm Central time zone
Reviewer: Bob B from Mechanicsburg, PA
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
December 1, 2005 - 12pm Central time zone
Reviewer: Tracy Tupman from UK
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?
December 1, 2005 - 9pm Central time zone
Reviewer: Gabriel from Montreal, Canada
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,
Followup December 2, 2005 - 10am Central time zone:
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
December 6, 2005 - 12pm Central time zone
Reviewer: A reader
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
Followup December 6, 2005 - 3pm Central time zone:
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!
December 6, 2005 - 12pm Central time zone
Reviewer: A reader
sorry!
December 6, 2005 - 3pm Central time zone
Reviewer: A reader
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);
Followup December 6, 2005 - 4pm Central time zone:
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
December 6, 2005 - 5pm Central time zone
Reviewer: A reader
Tom,
when I use this in the oracle forms it doesn't returns
1.
Followup December 7, 2005 - 1am Central time zone:
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
December 7, 2005 - 9am Central time zone
Reviewer: A reader
I was told that form doesn't understand count(*) but
just count...figures!
Followup December 8, 2005 - 1am Central time zone:
that is FALSE.
Forms
April 23, 2007 - 6am Central time zone
Reviewer: A.Varadarajan from Bangalore,India
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
May 24, 2007 - 10am Central time zone
Reviewer: A reader
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?
Followup May 26, 2007 - 11am Central time zone:
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
July 23, 2007 - 5am Central time zone
Reviewer: Sagar from Mumbai, India
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
Followup July 23, 2007 - 8am Central time zone:
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...
July 24, 2007 - 3am Central time zone
Reviewer: Sagar from Mumbai,India
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.
Followup July 24, 2007 - 9am Central time zone:
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
July 24, 2007 - 5am Central time zone
Reviewer: Sagar from Mumbai , India
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
July 24, 2007 - 3pm Central time zone
Reviewer: A reader
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]
Followup July 26, 2007 - 8am Central time zone:
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
July 24, 2007 - 9pm Central time zone
Reviewer: Loz
You need to delimit your data.
A B
- -
XX YY
X XYY
2 distinct rows but (A||B) would give you 1.
July 26, 2007 - 12am Central time zone
Reviewer: A reader
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 !!!
July 26, 2007 - 2am Central time zone
Reviewer: Sagar from Mumbai,India.
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
July 27, 2007 - 7am Central time zone
Reviewer: A reader
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)
Followup July 27, 2007 - 3pm Central time zone:
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
May 8, 2008 - 8am Central time zone
Reviewer: Rolfe Hare from Vienna, Austria
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
February 19, 2009 - 10am Central time zone
Reviewer: puslukita from TURKEY
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)
Followup February 19, 2009 - 1pm Central time zone:
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?
|