Re: data/index on different disks

Niall has a good point. When you talk about text indexes you are not talking about normal indexes. The mechanics are very different.

I believe that separating tables and their indexes is basically a matter of preference and management style. I like to separate them because I find that the tables and the indexes on a set of tables have a tendency in OLTP environments to balance out in size and IO demand. So if I want to limit my tablespace size to 6G or 8G etc... then by separating the tables and indexes I find it easier to do just that.

There is a drawback if you want to use transportable tablespaces but since I have over 1400 tables in my application I am not going to have 1400 tablespaces. Every tablespace has a dozen to several hundred objects in it anyway making transportation of the tablespace unlikely anyway. We generally want data from specific tables so we will use exp/imp or insert select accross a database link instead of transportable tablespaces.

This last point being that the decision depends on your environment. Frank mentioned your backup strategy. This is very important if you are disk constrained or have a very small tape IO pipe then by separating the tables and indexes you could just backup the tables. On recovery you take the index datafiles out of your database and after recovering the table tablespaces recreate the index tablespaces and their contents. I would rather backup everything but it depends on your situation.

IMHO -- Mark D Powell -- Received on Thu Mar 03 2005 - 15:12:44 CST