Re: Fundamental database design question

as always, specific advice is application dependant, but according to the oracle docs....

http://technet.oracle.com/doc/server.815/a67775/ch20_io.htm

<snip>
It is not necessary to separate a frequently used table from its index. During the course of a transaction, the index is read first, and then the table is read. Because these I/Os occur sequentially, the table and index can be stored on the same disk without contention. </snip>

yes, i know this is the second time today i have posted this url :)

hth,
tim hentzel

http://www.codegarden.com

In article <8mvec8$fb5$1_at_nnrp1.deja.com>,   peterl2000_at_my-deja.com wrote:
> 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.
>

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