another select count(*) post

Hi!

I've some question regarding select count(*) from blah. I understand that select count(*) from blah

                   where pk_column > 0
using index range while

                  select count(*) from blah using
full table scan.

I've 2 tables test1 and test2.

test1 (id int); primary key on id
There 10000 records in test1, and id begins from 1

test2 (id int, comments varchar2(80)); primary key on id and AVG_ROW_LEN is 42.
There 133820 records in test2, id begins from 1.

My optimizer_mode is choose.

Case1: (No statistics)
select count(*) from test1 --> Elapsed: 00:00:00.37 select count(*) from test1
 where id > 0 --> Elapsed: 00:00:00.54

Case2: (With statistics)
select count(*) from test1 --> Elapsed: 00:00:00.78 select count(*) from test1
 where id > 0 --> Elapsed: 00:00:01.16

Case3: (No statistics)
select count(*) from test2 --> Elapsed: 00:00:02.95 select count(*) from test2
 where id > 0 --> Elapsed: 00:00:00.70

Case4: (With statistics)
select count(*) from test2 --> Elapsed: 00:00:00.51 select count(*) from test2
 where id > 0 --> Elapsed: 00:00:01.73

Question 1:
Why does the query with where condition take more time than query without where condition except in case3?

Question 2:
Regarding statistics, only select count(*) from test2; takes less time after we analyzed the tables. That is: select count(*) from test1,
select count(*) from test1 where id > 0, and
select count(*) from test2 where id > 0 take more time after we analyzed the tables. Could someone explain what cause these?

Thanks in advance! Received on Wed Mar 19 1997 - 00:00:00 CST