Benchmarks I have carried out one year ago on Oracle 8.1.7 point to
slightly better (but of the same order of magnitude) results.
If the number of blocks accessed when inserting into a 'naked' table was
100, the definition of a (sequence based) primary key was adding 240,
and each additional index about 257, whether it was based on a number or
a string. In terms of elapsed time however the picture was a little bit
brighter, as the PK was adding around 85 and each index around 135. The
number of distinct keys for a non-unique index doesn't seem to make any
difference. I also noticed that adding a foreign key was adding 190 in
terms of accessed blocks (non-indexed FK, just the cost of checking the
constraint), but nothing measurable in terms of elapsed time.
Interestingly, a trigger to maintain a total in a different table was
costing hardly more than an index.
> "Jamadagni, Rajendra" wrote:
>
> I don't remember where but the results of the study were like
> following ...
>
> if the cost of inserting one row to a table is 1 unit and if you have
> 5 indexes on the table than total cost of inserting 1 row to the table
> is
>
> 1 (cost of inserting a row)
> + 3*5 (5 indexes)
> ---------
> 16
>
> So total cost is about 16 units.
>
> Raj
--
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 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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 Thu Sep 05 2002 - 16:38:44 CDT
