Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how to check and disable degree of parallelism of partitioned index

qyqgpowerMar 12 2019 — edited Mar 12 2019

I know I can create or rebuild a non-partitioned index using parallel_clause like

alter index TEST_INDEX rebuild parallel 8;

Then, I could check the degree of parallelism by query dba_indexes, result would be 8.

select degree from dba_indexes where index_name = 'TEST_INDEX';

I can disable the parallelism by issue following statement:

alter index TEST_INDEX noparallel;

--following result would be 1

select degree from dba_indexes where index_name = 'TEST_INDEX';

But when dealing with partitioned index, I can't find any view to check the degree of parallelism.

for example, assuming TEST_INDEX_PART is a partitioned index, created with noparallel option, and have two partitions P1, P2.

--degree of the index is 1

select degree from dba_indexes where index_name = 'TEST_INDEX_PART';

--rebuild P1 in parallel 8

alter index TEST_INDEX_PART rebuild partition P1 parallel 8;

--degree of the index is still 1,

select degree from dba_indexes where index_name = 'TEST_INDEX_PART';

--rebuild P2 in parallel 4

alter index TEST_INDEX_PART rebuild partition P2 parallel 4;

--degree of the index is still 1,

select degree from dba_indexes where index_name = 'TEST_INDEX_PART';

--and I can't find valid clause to disable parallelism on these partitioned index.

so, the question is, how can I check and disable degree of parallelism of partitioned index?

Or there is no such thing for partitioned index, the parallel_clause for alter index rebuild partition is only for speed boost and won't affect execution plan at all?

Thanks in advance.

This post has been answered by Mustafa KALAYCI on Mar 12 2019
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 9 2019
Added on Mar 12 2019
1 comment
1,580 views