Re: Dummies guide to fast inserts ?

"Richard Shea" <richardshea_at_fastmail.fm> wrote in message news:282f826a.0308131623.69207485_at_posting.google.com...
> Hi - I'm interested in creating a table into which I can insert rows
> with as little overhead as possible. Could the group suggest key
> points to cover to do this as well as possible ? Just off the top of
> my head I'm thinking ...
>
> + No indexing
> + Some sort of restriction on the choice of column types
> + As little data as possible per row
> + Table in own tablespace
>
>
> ... however I'm sure there are plenty of other more sophisticated
> choices (and maybe the ones I've suggested are not significant ?).
>
> Any suggestions would be welcome.
>
> Just for background it's on Win32 and I'm replacing a diagnostic
> logfile which was previously implemented as an ASCII flatfile.
>
> Regards
>
> Richard.

In its own tablespace has nothing to do with it. As few indexes as possible, but you will probably need some.(eg a primary key) Use sqlLoader or an array insert to insert as many rows as possible at a time. Also use bind variables if you are not using sqlloader and just rebind and reexecute the sql statement. Have sufficient disk striping to give you high IO. I disagree about no constraints. If the data has constraints then have constraints. They don't slow things down that much. Oracle will do inserts very fast, but is (like anything else) dependent upon how fast the IO is on the machine.
Jim Received on Thu Aug 14 2003 - 16:34:48 CEST