| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Select count(*)
Hi Carlos.
If you use this command first you will maybe find out why there is a defrence between the two tables.
set autotrace traceonly;
It will show you the explain plan it uses when running the query. It will also show how many consistent gets and physical reads is used. A rule is that 1 physical reads is equal to 10000 consistent gets. they should both be as low as possible
Another posible reason is that you have remembered to run analyze on the table but not on the index.
Please mail me and tell if you can use this info.
If you don't understand the explain plan you can also mail me that and I will try to analyze it.
Use this mail adress. flipper1_at_jubiimail.dk
Best regards,
Jens
"Carlos Alberto" <calberto2312_at_hotmail.com> wrote in message
news:72954535.0207080654.57d75337_at_posting.google.com...
> Hi Tom,
>
> I´m not using both tables in queries, I´m using one table and
> comparing the execution time with the other table. The SQL´s are the
> same, the only difference is the table in the FROM clause.
>
> Regards,
> Carlos
>
> Thomas Kyte <tkyte_at_oracle.com> wrote in message
news:<ag6qsg02gau_at_drn.newsguy.com>...> >
> > In article <72954535.0207051011.1058d2be_at_posting.google.com>,
> > calberto2312_at_hotmail.com says...
> > >
> > >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
> >
 |
 |