Re: Index performance

> 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

>
>

>>>> Received on Sun Aug 18 1996 - 00:00:00 CEST