Re: Fundamental database design question

Another issue is how big are your db blocks, the multiblock read count and the db_block_buffers.
Tie this to how the OS works.

That is, if you have your tablespace datafile on Unix in a single inode, many of the Unixes (Unixen?) will try to retrieve the entire file (inode). If your table and index are retained in the SGA (block buffers and read count), then the sequential read approach may be valid. If the OS has enough buffers such that the inode is in core, again the sequential read approach may be valid. But remember that for multiple users, or for files where the table requires several multi-block read count scans and the index is in the same file/tablespace, then the possibility that your reads are sequential are very low. THe table will be at one end of the file and the indexes at another, or intermixed, so the highest order probability will be that there will be head movement to get the second read to some other disk location than the next block (sequential read).

Given the speed of some disks and systems, or using massive storage arrays like EMC, this may not be as important as it once was, but that is an application specific quibble.

In article <39940F5C.3FE94C03_at_edcmail.cr.usgs.gov>,   Brian Peasland <peasland_at_edcmail.cr.usgs.gov> wrote:
> 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!
> ========================================
>

--
Joseph R.P. Maloney, CCP,CSP,CDP
MPiR, Inc.
502-451-7404
some witty phrase goes here, I think.


Sent via Deja.com http://www.deja.com/
Before you buy.