Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: can clustering help INSERTS ?

Re: can clustering help INSERTS ?

From: Stephane Faroult <sfaroult_at_oriole.com>
Date: Sun, 27 Oct 2002 07:33:19 -0800
Message-ID: <F001.004F4C85.20021027073319@fatcity.com>


DENNIS WILLIAMS wrote:
>
> Stephane
> You mentioned "each additional index costs about 2.5 times the cost of
> inserting into a non-indexed table". I just wanted to point out that Kevin
> Loney has done some performance tests involving the number of indexes. I
> don't know if he has published these anywhere. In a nutshell, the results
> were that a single index really hurts insert performance, and each
> additional index increases the hurt, but by a decreasing amount. The
> conclusions were:
> - If you can drop all indexes, that will really help inserts.
> - If you have one index, adding a second index will really hurt, but not
> as bad.
> - If the table already has 15 indexes, adding one more index probably
> won't be noticed.
>
> Dennis Williams
> DBA, 40%OCP
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>

Dennis,

   I have also benched it, and what I gave are my results. Note that we totally agree in _relative_ terms. If you have 15 indexes, I estimate the cost to be about 100 + 15 * 250, so in truth at this stage the cost of an index is about 7% ...
Note also that the costs I gave are relative to the number of logical reads. I have met a number of cases when a significant increase in logical reads was hardly noticeable in terms of elapsed time.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Sun Oct 27 2002 - 09:33:19 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US