Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: Is this too slow?
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
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)
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.
![]() |
![]() |