Re: Index performance

deangup_at_admin.ci.seattle.wa.us wrote:
>
> > njs3_at_doc.ic.ac.uk (Niall Smart) writes:
> > Naren Chintala <naren_at_mink.att.com> wrote:
> >
> >
> > >> We are having performance trouble with a batch process and we've
> > >> narrowed it down to index performance.
> >
>
> My question too. The fact that they're inserting 1000 rows, not 1 row, may be part of the answer. While the
> table insert can just go to the next unoccupied block(s) in the tablespace, the index insert has to be
> logicaly attached to the index's tree structure. If there isn't room for it where it belongs, previously-created
> index leaves and branches have to be rearranged. I understand that this happens for each individual insert,
> so that one particular branch may be moved repeatedly in response to successive inserts, adding to the I-O
> requierd to create the new index entries. I suggest dropping the index, doing the mass insert, and
> re-creating the index to reduce the effect of this "secondary" I-O requirement. Of course, if you need the
> index to enforce integrity during the inserts, that might not be such a good idea!
>
> I also notice that only one tablespace on one drive is mentioned. If it isn't already being done, I suggest
> placing the index in a tablespace whose files are on a physically separate disk drive from those of the
> tablespace which contains the table being inserted to. This will get more heads on the job and reduce wait
> time for arm movement.
>
> I still have the feeling that there is something peculiar about this application; huge rows, or enormous index
> keys, or...?
>
> -- Paul de Anguera, City of Seattle / HRIS
>
> >
> >
> >>>>If you are inserting 1000 rows in a single transaction as in

insert as select ...

try using the new UNRECOVERABLE option in 7.3. Received on Mon Aug 19 1996 - 00:00:00 CEST