Home » RDBMS Server » Server Administration » Impact of parallel index rebuild on partition index (Oracle 11gR2)
Impact of parallel index rebuild on partition index [message #625518] Thu, 09 October 2014 08:14 Go to next message
archana_av
Messages: 1
Registered: October 2014
Junior Member
I have query on impact of parallel clause in partition index rebuild . In case of non partitioned index what i come to know is ,Parallel index (re)build will persistently set the index parallel degree in data dictionary to the value used during build.This enables optimizer to consider parallel query plan for this object .Here is the DDL for non-partitioned index rebuild

alter index IX_RUN_ID rebuild parallel 4 nologging;

select degree from dba_indexes
where index_name ='IX_ALERT_CDR_RUN_ID'

output : 4

To change this ,alter index is required with no parallel clause
alter index IX_RUN_ID noparallel;

I would like to know the behavior of parallel clause in partition index rebuild , here is the DDL
alter index IX_AGG_VALUE rebuild partition P09 parallel 4 nologging;

After rebuilding all the index partition , i have checked dba_indexes for degree of parallelism and found its value as "1"
select degree from dba_indexes
where index_name ='IX_AGG_VALUE'

Pl clarify if there is different concept for normal index and partitioned index .

Re: Impact of parallel index rebuild on partition index [message #625522 is a reply to message #625518] Thu, 09 October 2014 08:29 Go to previous message
BlackSwan
Messages: 25848
Registered: January 2009
Location: SoCal
Senior Member
welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/
Previous Topic: Actual Database Size
Next Topic: Temp tablespace NOT shrinking in oracle 11.2.0.3
Goto Forum:
  


Current Time: Sat Feb 17 16:36:24 CST 2018

Total time taken to generate the page: 0.04185 seconds