Re: Index on different tablespace

dnguyeb_at_site.gmu.edu (Dong Nguyen (INFT801)) wrote:
> Hello,
> I have heard that indexes are read faster if stored in different
>table space. Is this true. If so why?
>
> Doug g

Quick answer: physics.

Long answer: Disks are mechanical devices which supply information to the computer at rates which are fixed by the path to memory (rotation delay, seek time, transfer time, potential bottlenecks on controller/bus/etc). If you place data and indexes on one disk, all I/O must occur on that disk. If its maximum i/o rate is 100 xfers per second that is the limit to the i/o on that disk. If you spread data on one disk and indexes on another, the maximum throughput (theoretically) goest to 200 xfers per second. The more the merrier. This is the BIG PICTURE. There are many other factors that come into play, such as the read-to-write ratio, the number of index reads required to perform a data read, etc,etc,etc. Disks are (relatively) cheap. If you data tablespace was big enough to justify it, and you were able to talk the powers-that-be into spending the-$$$-required to spread the data/indexes over 10 disks: now you i/o rate is 1000 xfers per second. WAG $$$ for disk: $2000. Guestimated cost of disk/cabinet/power/yadda-yadda-yadda for 1 yead: $20000 (9 new disk $2000 each and the rest ...). With a multi-user system, the payback would be QUICK. Easy to justify with return on investment analysis. Received on Thu Jan 25 1996 - 00:00:00 CET