Re: should you seperate indexes from tables in seperate datafiles

• Original Message ----- From: "Gogala, Mladen" <MGogala_at_oxhp.com>

> I must say that I haven't actually seen the benefits myself but my faith
> is rock solid and I'll continue to separate data from indexes.

Don't want to debate faith... However, the technical side I can. ;)

The practice of separating indexes and tables into individual tablespaces comes from very early in the history of databases, as many here know. It made sense then in terms of performance, when databases were relatively small and the number of objects and users in a database was also small. Besides, in those days disks were discrete and controllers controlled each disk separately. It was easy to separate loads, for those who could be bothered and knew the arcane arts of disk partitioning and file system creation.

Nowadays with databases of thousands of tables and indexes, with disk farms and multiple disk strings and monster caches and disk arrays and logical volume managers, it makes no sense whatsoever to separate tables from indexes *FROM THE PURE PERFORMANCE POINT OF VIEW*. However from the maintenance and management point of view, it makes a lot of sense. If you ever want to separate indexes to a different disk, it's infinitely easier to just move a set of datafiles making up a tablespace than to have to create tablespaces, move indexes, blah blah. In addition indexes tend to have different allocation patterns than tables, even in LMT. It makes sense to separate them from that point of view.

There is another consideration which you brushed on: You normally don't want to mix the type of I/O for indexes (small, discrete blocks) from the potential "stringed" reads of a full table scan. They have different characteristics and cause all sorts of different I/O prioritization at controller and cache level. As such, they should not reside in the same logical device. This means in effect: not the same tablespace. Having said that, all sorts of tricks are possible at LVM level to avoid this even in single tablespace or single file system.

But once again: this is all very relative and highly dependent on the type of database and its use, type of hardware, etc.

Bottom line: separate if you feel comfortable doing so. You do NOT have to move the separate tablespaces into different disk devices: it all depends on I/O patterns, configuration, load management and so on. Analyze I/O patterns and loads, find the bottleneck (if there is one!) and solve it.

Do not do things just because someone says they "should/should not be done". And that includes the above. ;)

Try and apply a "one size fits all" policy to your work and sooner or later you'll be replaced by a program or outsourced. You are a DBA: think. Received on Tue Jul 15 2003 - 09:56:45 CDT