Oracle : Move indexes to another tablespace

 

Considering the performance of an application, it is a good practice to store indexes and data in different tablespaces – each tablespace's datafiles on different physical disks. This will facilitate read operation to happen in parallel. As a result the data access will be faster.

During development, Developers often create the tables and indexes on the same tablespace. The DBA takes care when the schema changes of development schema are applied on the QA database. The indexes can be stored in a different tablespace using the following commands.

 alter index rebuild tablespace ;

Example: alter index pk_emp_idx rebuild tablespace tbs_index;

 

If you are using Oracle Enterprise Edition, including the option online, will help to rebuild the index without downtime on the application that uses the index. 

-- alter index rebuild tablespace

alter index pk_users_idx rebuild tablespace tbs_users online;

If you have to rebuild many indexes, the following SQL help you to generate ALTER statements for all the indexes present in the schema.

select distinct 'alter index '||index_name||' rebuild tablespace tbs_index' as cmd
from user_indexes
where INDEX_TYPE not in ('LOB','IOT – TOP','DOMAIN');

If you want to rebuild all indexes of a schema in one-execution, the following PLSQL script will help you to generate ALTER statements for all the indexes present in the schema and to execute them.

begin
for i in (
select distinct 'alter index '||index_name||' rebuild tablespace tbs_index' as cmd
from user_indexes
where INDEX_TYPE not in ('LOB','IOT - TOP','DOMAIN')
)
loop
dbms_output.put_line (i.cmd);
execute immediate i.cmd;
end loop;
end;
/

 

Read more about Performace Tuning Here