Re: Is this too slow?

Thanks for your comments. I guess I wasn't too concerned about that particular query. It was just that a lot of things seemed too slow on that database, and that query was a simpler example of that. The dba made some adjustment (I don't know what), and now it takes a few seconds rather than 5 minutes.

But there are still some things that take too long. What I'm most concerned about right now is with my data upload. The final step involves a stored procedure that copies rows over from a scratch table (which has no constraints or indices) to the real table (which has both). For data sets containing 10,000 rows it takes a moderate amount of time (maybe 2 minutes or so). For data sets of 20,000 rows it takes much longer than twice that. To me, that's a sign that resources aren't being adequately distributed.

I will look into tkprof, it looks like that should be useful.

To answer these questions:

For the comments about the join with the datum table, this reply was correct:
> 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.

I want to know how many data points per experiment for each experiment by scientist 4. Otherwise I just get an experiment count. Hence the 'group by experiment_id'

>o tables analyzed or not?

Sorry, I don't know what that is, though it sounds good.

>o optimizer goal (but tkprof will tell us that)

Don't know again. I did explain plan on that particular query, and it just did index scans, so that looked right.

>o sigh, the database version.

8.1.5, though this needs to work with 8.0 too.

>I don't know how many scientists that you have,

It's a small number. Around thirty.

>but when joining a very
>large table to a very small table the /*+ star */ hint will often give a
>very good result.

Can someone point me too information about using these optimization hints?

--