Re: Is this too slow?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 21 Jul 2001 21:36:50 GMT
Message-ID: <9innja02tbp@drn.newsguy.com>

In article <3b4f5032_4_at_corp-goliath.newsgroups.com>, "Adam" says...
>
>gene,
>I have a couple of things to say .. which may just show my ignorance :-)
>But don't worry, people here will correct me as necessary.
>
>Why are you joining with table datum? What's wrong with:
>SELECT count(*), experiment.experiment_id
>FROM experiment
> WHERE experiment.scientist_id = 4
>GROUP BY experiment.experiment_id;
>?

if he didn't, this would be the same as:

select 1, experiment_id
from experiment
where scientist_id = 4

from the problem definition:

>> The experiment table has about 700 rows, the datum table has about
>> 7,300,000 rows. As the names imply experiment_id is a primary key (not
>> null, number(8)) in
>> experiment and a foreign key in datum, and is an index in both.

he wants a count of the matching datum records for each experiment id done by scientist 4. We need to join to get the count.

>
>and the other point was,
>you can always use EXPLAIN PLAN technique to double check if you are
>actually doing a full table scan (although you seem pretty sure you're not).
>Otherwise, do a sql trace and see where the time is going (use tkprof or
>other tools to interpret trace)
>

tkprof would be the way to go here, more info then an explain plan

see
http://asktom.oracle.com/~tkyte/tkprof.html to get bounced to the doc on that.

We'd also need to know

o tables analyzed or not?
o optimizer goal (but tkprof will tell us that) o sigh, the database version.

for example, a query that *might* go better could be:

 SELECT ( select count(*)

              from datum d 
             where d.experiment_id = e.experiment_id ) cnt,
          e.experiment_id
 FROM      experiment e

 WHERE e.scientist_id = 4

but that'll work only in 8i and up.

>regards,
>adam
>
>gene <usenet_at_smalltime.com> wrote in message
>news:9a95c0dd.0107131134.1d68c75c_at_posting.google.com...
>> An oracle server that I'm using with some software I've written seems,
>> in my judgement, to be way too slow. Since I haven't worked with
>> tables of this size in the past, I want to solicit your opinions on
>> this.
>>
>> As an example, this particular query takes about 5 to 6 minutes to
>> run:
>>
>> SELECT count(*), experiment.experiment_id
>> FROM experiment, datum
>> WHERE experiment.experiment_id = datum.experiment_id and
>> experiment.scientist_id = 4
>> GROUP BY experiment.experiment_id;
>>
>> The experiment table has about 700 rows, the datum table has about
>> 7,300,000 rows. As the names imply experiment_id is a primary key (not
>> null, number(8)) in
>> experiment and a foreign key in datum, and is an index in both.
>>
>> The query above returns 31 grouped rows which represent 366,000
>> distinct rows.
>> As I said, it takes 5 to 6 minutes to run, which seems to me to be
>> very long even with the large table size since I am joining on indexed
>> columns.
>
>
>______________________________________________________________________________
>Posted Via Binaries.net = SPEED+RETENTION+COMPLETION = http://www.binaries.net

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Sat Jul 21 2001 - 16:36:50 CDT