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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
