using parallel in Index creation
Creating an index on one of my large tables takes me arounf 2 Hours.
If i create the index using Parallel 8 which will of course reduce the index creation time, and then later on once the index has been created , say i reset it back to NOPARALLEL, will there be any problem in data fetch later on. i mean any unforseen problem in future ? for ex, the standard queries might not use the index etc. etc. ?
is it safe ? because i will be doing it on the Production database.
Some comments :
a) your affirmation "using parallel 8 which will ** of course ** reduce the index creation time..." is NOT necessarily true : see, parallel means "I have unused resources at the moment (CPUs, memory, I/O bandwith) , and I want to have various processes doing a part of the big job each one". OF COURSE, each process WILL consume CPU, RAM & I/O, *** so *** if right now you DONīT have available resources in the machine, having even MORE processes running WILL slow you in a BIG way.
b) yes, it is safe to do it in production, I always do using PARALLEL (and NOLOGGING, for the matter) , no probs.
before indexing you can set the
parameter to a higher value.
oracle will use this area for sorting while indexing.
once your indexing is over then reduce it back.
some version of oracle (eg.oracle 8i standard edition)
will not support parallel dml.
it will just ignore your parallel clause.
Click Here to Expand Forum to Full Width