How to place tables and indexes in different tablespaces in Oracle

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 tables and indexes and store them in different tablespaces because: 1) improve performance: Try to store the tablespaces of the tables and indexes on different disks to make them different.

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 tables and indexes and store them in different tablespaces because: 1) improve performance: Try to store the tablespaces of the tables and indexes on different disks to make them different.

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 better to store the table and index separately 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: imagine that if the index data file is damaged, you only need to create an index without causing data loss.

The following statement is used to move the index tablespace:

The Code is as follows:

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:

The Code is as follows:

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 tablespace to the index, see the following example:

The Code is as follows:

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
);