Re: another select count(*) post

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. Received on Wed Mar 19 1997 - 00:00:00 CST