Tuning "INSERT" for small table

I have some complicated queries which I'm handling by inserting partial results into a smallish table (bigest query result so far is about 13K records, but a more typical result is maybe 1K or even less records)

The table itself is just a numeric list of primary keys of the discovered records, plus a numerical "score". When I'm finished with the queries, I join the results to some tables (on that primary key) so as to set a ref cursor (ordered by score) which I return to the caller.

I have an index on the small tables primary key to help with the joins, but I'm not sure I'm buying much with it (and maybe thats a part of my INSERT problem).

I tried an "APPEND" hint for the insert, that may have helped some, but its not a magic bullet.

I also notice that there's such a thing as a temporary table (Oracle 8.1.7) ....At the moment I'm truncating the result table before each query (and commit-ing to avoid lock problems on the theory that committing would release all locks). I suppose a temporary table would eliminate the need to truncate before query....but would it do anything good (or bad?) to the insert time? (And what about causing contention "deadlocks" ? ) I figure that before I go mucking about with things I only half understand I would seek some advice.

This is a sort of "do-it-yourself, be-your-own-DBA 'cause thats all you get" kind of shop. On the one hand, a good opportunity to seek new worlds, on the other hand.....(bombs away!)

Should I be looking at the size of the initial exent? Or the number of initial extents? Or what other parameter (%used, %free , ????) might be egregiously out of whack?

Oh yeah, and ditto for the index storage parameters.

Any insight as to what might be making inserts take longer and longer as the table fills up, and what might be done to fix it?

THanx in advance. Received on Tue Apr 09 2002 - 10:23:09 CDT