Creating Separate Tablespaces for Indexes- Tables and Constraints

For critical applications, you must give some thought to how much space tables and indexes will consume and how fast they grow. Space consumption and object growth have a direct impact on database availability. If you run out of space, your database will become unavailable. The best way to manage space in the database is by creating tablespaces tailored to space requirements and then creating objects in specified tablespaces that you have designed for those objects. With that in mind, I recommend that you separate tables and indexes into different tablespaces. Consider the following reasons:

• Doing so allows for differing backup and recovery requirements. You may want the flexibility of backing up the indexes at a different
  frequency than the tables. Or, you may choose not to back up indexes because you know that you can re-create them.
• If you let the table or index inherit its storage characteristics from the tablespace, when using separate tablespaces, you can tailor storage attributes for objects created within the tablespace. Tables and indexes often have different storage requirements (such as extent size and logging).
• When running maintenance reports, it is sometimes easier to manage tables and indexes when the reports have sections separated
  by tablespace.

If these reasons are valid for your environment, it is probably worth the extra effort to employ different tablespaces for tables and indexes. If you do not have any of the prior needs, then it is fine to put tables and indexes together in the same tablespace.

I should point out that DBAs often consider placing indexes in separate tablespaces for performance reasons. If you have the luxury of creating a storage system from scratch and can set up mount points that have their own sets of disks and controllers, you may see some I/O benefits from separating tables and indexes into different tablespaces.

Nowadays, storage administrators often give you a large slice of storage in a storage area network (SAN), and there is no way to guarantee that data and indexes will be stored physically, on separate disks (and controllers). Thus, you typically do not gain any performance benefits by separating tables and indexes into different tablespaces. Also, when using ASM, disks can be rebalanced for performance.

Establishing Naming Standards

When you are creating and managing indexes, it is highly desirable to develop some standards regarding naming. Consider the following motives:

•     Diagnosing issues is simplified when error messages contain information that indicates the table, index type, and so on.

•     Reports that display index information are more easily grouped and more readable, making it easier to spot patterns and issues.

Given those initial thoughts and needs, here are some sample index-naming guidelines:

•     Primary key index names should contain the table name and a suffix such as _PK.

•     Unique key index names should contain the table name and a suffix such as _UKN, where N is a number.

•     Indexes on foreign key columns should contain the foreign key table and a suffix such as _FKN, where N is a number.

•     Indexes that are not used for constraints should contain the table name and a suffix such as _IDXN, where N is a number.

•     Function-based index names should contain the table name and a suffix such as _FNXN, where N is a number.

•     Bitmap index names should contain the table name and a suffix such as _BMXN, where N is a number.

Some shops use prefixes when naming indexes. For example, a primary key index would be named PK_CUST (instead of CUST_PK). All these various naming standards are valid. It does not matter what the standard is, depending on groupings and making the names clearly understandable, as long as everybody on the team is following the standards set.