Databases Reference
In-Depth Information
Your results may vary depending on the number of records, the number of columns, the data types,
and the accuracy of statistics.
In addition to the initial sizing, keep in mind that the index will grow as records are inserted into the
table. You'll have to monitor the space consumed by the index and ensure that there's enough disk
space to accommodate future growth requirements.
Creating Separate Tablespaces for Indexes
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 has a direct impact on
database availability. If you run out of space, your database will become unavailable. The best way to
manage this is by creating tablespaces tailored to space requirements and explicitly creating objects
naming the tablespaces. With that in mind, we recommend that you separate tables and indexes into
separate tablespaces. Consider the following reasons:
•
This 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, logging, and so on).
•
When running maintenance reports, it's sometimes easier to manage tables and
indexes when the reports have sections separated by tablespace.
If these reasons are valid for your environment, it's probably worth the extra effort to employ
different tablespaces for tables and indexes. If you don't have any of the prior needs, then it's fine to put
tables and indexes together in the same tablespace.
We should point out that DBAs often consider placing indexes in separate tablespace for
performance reasons. If you have the luxury of setting up 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 by
separating tables and indexes into different tablespaces. Nowadays, storage administrators often give
you a large slice of storage in a SAN, and there's no way to guarantee that data and indexes will be stored
physically on separate disks (and controllers). Thus you typically don't gain any performance benefits by
separating tables and indexes into different tablespaces. In other words, the benefit isn't caused by
having separate tablespaces but by achieving evenly distributed I/O across all available devices.
The following code shows an example of building a separate tablespaces for tables and indexes. It
creates locally managed tablespaces using a fixed extent size and automatic segment space management
(ASSM).
create tablespace reporting_data
datafile '/ora02/DWREP/reporting_data01.dbf'
size 1G
extent management local
uniform size 1M
segment space management auto;
--
create tablespace reporting_index
