Re: Index performance

In article <4uu02p$l26_at_sjx-ixn2.ix.netcom.com> jbreynol_at_ix.netcom.com(James B. Reynolds ) writes:
>In <4usrpi$stc$1_at_mhadf.production.compuserve.com> Glen Nunes
><73044.1336_at_CompuServe.COM> writes:
>>
>>We are having performance trouble with a batch process and we've
>>narrowed it down to index performance.
>>
>>We have created a brand new tablespace on a new drive. Our
>>testing indicates a 1000 record insert that takes 6 second
>>non-indexed, takes 55 seconds or more when indexed in any of a
>>variety of ways.
>>
>>Can anybody give me an idea of what may be causing this?
>>
>>Paul Jacobs
>
>Paul,
>
>This may sound like a stupid reply but you are inserting a row and an
>index which generally consumes more resources than just inserting a
>row. This speeds up reads (selects), if the index is used, but slows
>down writes (inserts, deletes, etc.).
>
>If possible place the index in a separate tablespace, on a separate
>device (disk), and if the disk is on a separate controller so much the
>better.
>
>Milton Friedman once wrote a book that was titled "There Is No Such
>Thing As A Free Lunch."

To be a little more responsive, Oracle keeps indices as balanced b-trees, so some shuffling may need to take place to keep the balance, trading off slowness at add/insert for predictibility/speed at select. See the Oracle Concepts manual for a more in depth explanation, and you may want to run some tuning on your indices.

If you are going to be doing a lot of multi-record inserts, drop the index while inserting.

jg

>
>Regards,
>
>Jim Reynolds
>jbreynol_at_ix.netcom.com
>

-- 
Joel Garry               joelga_at_rossinc.com               Compuserve 70661,1534
These are my opinions, not necessarily those of Ross Systems, Inc.   <> <>
%DCL-W-SOFTONEDGEDONTPUSH, Software On Edge - Don't Push.            \ V /
panic: ifree: freeing free inodes...                                   O