Notes for Using Parallel Indexing in Oracle

When creating an index, we add parallel indexes to build the index quickly. After adding parallel indexes, this column will be in parallel. When accessing an index with a degree of parallelism, CBO may consider parallel execution, which may cause some problems, such as using parallel execution when server resources are insufficient. When parallelism is used, you need to change the degree of parallelism back.

SQL> drop table test purge;

SQL> create table test as select * from dba_objects;
SQL> create index ind_t_object_id on test (object_id) parallel 4;
SQL> select s. degree
From dba_indexes s
Where s. index_name = upper ('ind _ t_object_id ');
DEGREE
----------------------------------------
4

SQL> Alter index ind_t_object_id noparallel;

SQL> select s. degree
From dba_indexes s
Where s. index_name = upper ('ind _ t_object_id ');
DEGREE
----------------------------------------
1