Re: Is this too slow?

I have sat here thinking out this query. You have 700 experiments done by various scientists. For each experiment you can have many datums. You want to count all of the datums for all experiments done by scientist 4. I think I am correct so far.

The query should work. Now the question is how would it be most efficient.

I don't know how many scientists that you have, but when joining a very large table to a very small table the /*+ star */ hint will often give a very good result.

Give that 366,000 is much less than 10% of the 7,300,000 Oracle shouldn't be using a full scan on the datum table(you really should check though).

Also look into increasing your sort_area_size. Check you temp space size. Make sure that your initial extent is 10Xsort_area_size (helps with hash joins).

As for is that a reasonable time, it really depends on the amount of work that is being done. I would say the time isn't excessive, but I would guess that it really could be better.

More thoughts, you have 31 rows, so there are 31 experiments for scientist 4. Again less than 10% of 700. An index on (scientist_id,experiment_id) would be very useful here even though experiment_id is your key. Assuming that there are more than just those two fields in the table. Otherwise that table is too small to index, and you might want to add the hint /*+ full(experiment) */

--
Robert Fazio
Senior Technical Analyst
dbabob_at_yahoo.com

"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.