Fundamental database design question

Hello everybody

I have a question that has confused me for a long time. Say I have a an OLTP database where 99% of the read/writes are may be single row updates/lookups and that all data access is happening via indexes, i.e we first look up the constraining key on the index to get the rowid of the actual data block and then do rowid based lookups of the data block. In this scenario I can't seem to understand the advantage of the common advise given regarding splitting indexes and tables to separate tablespaces and hence disks. As the access to the index and then the subsequent access to the data block happen sequentially, and not concurrently we can as well place them on the same tablespace and set of disks as the tables and rather depend on OS/hardware striping
to spread out the access of multiple Oracle processes among multiple available disks. I am assuming the database is laid out on raw devices and as such does not have reader/writer contention of filesystem based databases
A corollary to this question is that say in a perfect OLTP situation
(primarily index based single row lookups) we have 10 disks available
to us. Wouldn't it be better to spread
out the data and index tablespaces across all the 10 available disks in a RAID 0 configuration, rather than allocate say 6 disks to the data tablespace and 4 disks to the index tablespace
(of course striped etc. etc...).

Am I missing something obvious? Any insight would be appreciated.

Thanks

Peter Levi/Oracle DBA

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Aug 10 2000 - 00:00:00 CDT