You are right that if all your SQL statements address single rows only, then I/O
separation is relatively unimportant. However, the premise that OLTP
applications only do single row operations is not normally sound. There are some
operations that work with large amounts of data even in OLTP, and OLTP queries
often fetch small sets of rows, rather than individual rows.

The potential for I/O contention arises when you do repeated alternating access
between related segments that reside on the same disks. There are more such
scenarios than the simple separation of tables from indexes would cater for.
Some of these are mentioned in the "Planning Tablespaces" tip on the Ixora web
site. For example, it is mentioned that "a nested loops join normally involves
repeated access to first the outer table, then the index on the inner table, and
then the inner table itself. If any 2, or all 3, of these segments reside on the
same disks, head thrashing will occur ..."

To answer the question directly, if you don't know the application well enough
to plan the layout properly, then it is not a bad idea to merely rely on the
striping. This is certainly not the best solution, but it is normally adequate.
However, I would never choose just one broad stripe set, because that would make
it difficult to add more disks later on. In a case like this, I would probably
use 5 stripe sets with a uniform stripe breadth of two disks. Tables and index
can still be mixed together pseudo randomly. This gives you most of the
potential benefit of (broader) striping, reasonable probability of good I/O
separation, and the ability to grow the database without major disk
reconfiguration.


-----Original Message-----
Sent: Saturday, 26 August 2000 6:58


In all database design related books and web sites it is advised that index
tablespaces should sit on separated onto different disks from the data
tablespaces.

My understanding was that in most OLTP based applications with index and rowid
based table lookups, these accesses happen sequentially, i.e first the index
leaf block is looked up to get the rowid of the data block and then a rowid
based access is done to the data block.  Thus I fail to understand the grounds
of I/O contention.

Now say we are required to layout the data and index tablespace for a high
concurrency OLTP database.  If 10 mirrored disks are available (20 disks in
total), wouldn't it make more sense to create one logical volume striped across
all these 10 disks and layout all the data and index tablespaces on this logical
volume and thus depend on the striping to take care of the I/O contention.

The other option would be to get an estimate of the required I/O bandwidth for
index accesses and data access, and say based on this estimate we find that 6
disks are sufficient for data tablespace and 4 disks are sufficient for index
tablespace.  Then we build 2 striped volumes, 1 consisting of 6 disks for the
data tablespace and one consisting of 4 disks for index tablespace. This is the
traditional design.  Out of these 2 which one would you chose and why.