Re: Why "Separating Data and Indexes improves performance" is a myth?

jonathan_at_jlcomp.demon.co.uk wrote:
> And an old truism - which I think I first ran up the flagpole
> during v6 days:
>
> 20 inserts might go into just one table block
>
> 20 inserts could easily mean 60 scattered index block
> updates because you have 3 indexes on the table.
>
> Ulimate Write ratio: 60 to 1
>
> The last thing you want in some heavy duty write systems
> is to separate index physical devices from table physical
> devices. (note the hair-splitting description).
>
>
> Regards
>
> Jonathan Lewis

Well, it kind of is in the V6 Performance Guide (and -unmodified- in the 7.3 version, too). And my guess is this is where it started: <quote>
Place heavily accessed database structures in separate data files on separate disks. To do this, you must know which of your database structures are used often. For example, separate an often used table from its index. This separation distributes the I/O to the table and index across separate disks.
</quote>
Then, the example is given, with code. Note, there is no general guideline about splitting indexes from tables in the above. Makes sense, actually, still does: distribute I/O of heavily used structures.

However: the layout of these books is such, that the emphasis is put on separating tables and indexes; actually, those 4 words are in the margin, asif these words are the crux, the essence, of the action. Those 4 words are badly chosen...

OK - enough of the old stuff for now - nice to see the SQLDBA screen dumps again, though :-)

-- 

Regards,
Frank van Bortel

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------