Re: How to rebuild unusable index in parallel?

"Jaap W. van Dijk" <j.w.vandijk.removethis_at_hetnet.nl> wrote in message news:8mejs0ta0ca60ohk2lt011r6kmhf9l5pt4_at_4ax.com...
> On Wed, 22 Dec 2004 16:55:31 +0100, "Access"
> <idmwarpzone_NOSPAM__at_yahoo.com> wrote:
>
> >
> >"Jaap W. van Dijk" <j.w.vandijk.removethis_at_hetnet.nl> wrote in message
> >news:8jfis01ctg6jlit3qs69qrsfhv653nr57s_at_4ax.com...
> >> Hi,
> >>
> >> In Oracle 9.2.0.4 on Solaris I have a table with degree DEFAULT and an
> >> UNUSABLE bitmap index defined on the table, also with degree DEFAULT.
> >> Parallel ddl has been enabled for the session.
> >>
> >> If I do a count on the table which requires a full table scan, the
> >> table is being read in parallel. If I execute
> >>
> >> alter index so_and_so rebuild;
> >>
> >> the table is being read noparallel. Why, and how can I force reading
> >> in parallel?
> >>
> >> Jaap.
> >
> >Alter index ... rebuild parallel ?
> >
> >
> I don't think this will do the job, because the SQL Reference on the
> 'ALTER INDEX' statement states:
>
> "parallel_clause
> Use the PARALLEL clause to change the default degree of parallelism
> for queries and DML on the index."
>
> If I am interpreting this correctly this clause changes the
> degree-property of the index which will change behaviour of
> *subsequent* DML and queries on the index and not the DDL-statement
> 'alter index ... rebuild'. But as I mentioned before the index already
> has degree DEFAULT.
>
> But if nobody comes up with another solution I will try this anyway:
> docs don't always speak the truth.
>
> Jaap.

If you do : "alter index ... rebuild parallel", it will rebuild the index in parallel + it will change the degree of the index to "parallel". So you could do "alter index ... noparallel" after the rebuild to reset the degree value.

Matthias Received on Wed Dec 22 2004 - 16:46:31 CST