Re: Is this too slow?

gene wrote:

> 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?
>
> --

I can understand not putting constraints on a staging table. But no indexes? Why?

Daniel A. Morgan Received on Wed Jul 18 2001 - 12:00:38 CDT