Re: another select count(*) post

You might want to also try

        select count (rowid) from test3

Kelly Young (young_at_maricopa.edu) wrote:
: Fengqing Zhou wrote:
: >
: > 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!
 

: You didn't mention if you flushed the shared pool before running each of
: your tests. If there were db blocks in the sga from a prior test, you
: would unintentionally improve the run time on any subsequent tests.
: Also, I'm not sure run times of less than 1 second are long enough to
: make any valid conclusions. I would increase the amount of test data.

--
This posting represents the personal opinions of the author. It is not the
official opinion or policy of the author's employer. Warranty expired when you
opened this article and I will not be responsible for its contents or use.