The suggestions others have made should make the rebuild faster. Just be aware of a gotcha with the parallel clause.

1. After the rebuild do an alter index noparallel, to prevent the CBO using parallel options whenever this index is used in SQL (unless you want it to of course :-) ). If you don't you might find all your SQL execution plans on this table going crazy. If you use RBO then you MUST do this or things get very messy (been there, worn the Tshirt, caught the flak :-) ).

2. To get the best from parallel query option (PQO), you need to keep the reading and writing off the same disks. Ideally both should be on striped disk, or at the very least multiple concatenated disk. If not the the disk holding the read or write data (or both) becomes saturated long before the CPU. On a RAID-5 disk array I've seen PQO make no difference ( I'm guessing that your table is probably big enough that it's on multiple disks so this won't be an issue).

PQO is very powerful. I've seen a 55 million row table PK index build take just 5 minutes !!! on a 12 disk stripe on Sequent NumaQ with 12 procs.

Note the PQO also helps the sorting as this happens in smaller chunks that are then merged - much easier than sorting all the data.


Andy