Oracle getting started Tutorial: place tables and indexes in different tablespaces

Oracle does not distinguish whether tables or indexes are stored in tablespaces. When the data volume is small, you can place tables and indexes in the same tablespace, but as the data volume increases

Oracle does not distinguish whether tables or indexes are stored in tablespaces. When the data volume is small, you can place tables and indexes in the same tablespace, but as the data volume increases

Oracle does not distinguish whether tables or indexes are stored in tablespaces. When the data volume is small, you can place tables and indexes in the same tablespace. However, as the data volume increases, it is best to separate the table and index and store them in different tablespaces, because:

1) Performance Improvement: Try to store tablespaces of tables and indexes on different disks and split the two types of data with different IO properties. This will improve the overall I/O performance of the disk;

2) Ease of management: if the index data file is damaged, you only need to create an index, which will not cause data loss.

The following statement is used to move the index tablespace:

Alter index INDEX_OWNER.INDEX_NAME rebuild tablespace NEW_TBS;

You can also use the following statement to obtain all statements for moving the index tablespace in a schema:

Select 'alter Index' | owner | '.' | index_name | 'rebuild tablespace NEW_TBS ;'
From dba_indexes
Where table_owner = 'schema _ name'
Order by index_name;

Note that once the table and index are placed in different tablespaces, when creating a new table, if the table has a primary key or unique constraint, remember to specify the index tablespace, see the following example:

Create table tmp (
MON VARCHAR2 (6 ),
IDNO VARCHAR2 (10 ),
Constraint pk_name primary key (MON, IDNO)
Using index tablespace index_tbs,
CONSTRAINT uk_name UNIQUE (MON)
Using index tablespace index_tbs
);