Re: Tuning "INSERT" for small table

My suggestion would be to stop doing the inserts. They care completely unnecessary in Oracle. That should speed things up substantially.

Strikes me that likely you come from a SQL Server or Sybase background where that kind of workaround is necessary ... here it is not.

Daniel Morgan

Lee M Horowitz wrote:

> 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 - 14:22:39 CDT