Re: Fundamental database design question

Peter,

Comments inline....

> 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

If in fact, the index lookup and the table index happen sequentially, then you are correct. There should be no noticeable difference in putting the indexes and tables on the same volume or splitting them. But the only way you can guarantee this sequential access is if you have only one user executing queries at a time. What if two users are executing this same code? If User A is reading from the table, the User B will have to wait for the disk head to finish before it can read the index. In this scenario, User B will wait on User A. But if the index was on a different disk, then User B can be reading the index while User A is reading the table.

The other point that should be mentioned is that your scenario is only proper with the assumption that ever piece of code will access one, and only one row of data. Does a query in your code return multiple rows? If so, then it would be better to split the indexes from the data similar to the example I gave above.

> 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.

Your optimal configuration depends on your data access. While the OFA document gives good guidelines on how to split up your db's files, I can't say that it is an absolute. Your data access patterns and usage will determine your best method of using your available disk volumes. If you are really returning only one row at a time in your query, then striping the data across multiple volumes won't benefit you very much. Striping (RAID 0) is really beneficial when the data being queried is bigger than the stripe size. For instance, assume the stripe size is 8K (just for this example). Further assume that my table is 16K. Then the table is striped across two volumes in two stripes. If I'm doing a full table scan, then the two stripes are read in unison and I get half the response time of the entire 16K table on one disk. But if you are only query one row (say 2K for this example), then you won't realize any benefit if that row is entirely contained in one stripe.

HTH,
Brian

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================