Re: Select count(*)

Carlos Alberto wrote:

> Hi,
>
> I have two tables (A and B), with the same structure, but in
> different tablespaces. One (A) has 50 million records and was created
> by the other one (B) (by INSERT ... SELECT ....), with 100 million
> records. After the data load, the indexes were created and 'analyze
> compute' executed. Then, after I made some performance tests, the
> results seem very strange :
>
> Test 1 :
>
> set timing on;
>
> select <columns>, ....
> from A, B
> where <indexed_column> = ....
>
> Table B is faster, although it has the double of records. As more
> times this SQL is executed, the time of execution of both tables are
> almost the same.
>
> Test 2 :
>
> set timing on;
>
> select count(*)
> from A, B
> where <indexed_column> = ....
>
> Table A is faster.
>
> Test 3 :
>
> set timing on;
>
> select count(*) from A, B
>
> Table A is faster.
>
> Why table A is just faster when I run select count(*)? I´m using
> Oracle 8.0.6.0.0.
>
> Thanks in advanced,
> Carlos

Are you sure table B has the indexes present on table A? Have you checked?

If they are ... run EXPLAIN PLAN and look for differences in how the CBO sees them.

Daniel Morgan Received on Fri Jul 05 2002 - 14:54:49 CDT