I am in the process of rebuilding one index on a table that has about 50 million records. The index is a primary key and only has one column assigned to it. I have created a seperate tablespace for the index but when I look at longops in DBA Studio it is telling me that it will be another 9 1/2 hours!!??
Any reccomendations? I have allocated extra space in the temp tablespace but this is just dripping slow.
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.
Thanks for all the responses to this, it has been a learning experience. Yesterday I altered the session an increased the sort area size and created the indexes with the parallel clause. It took about 20 minutes.
I noticed that the storage for the indexes is listed as parallel, the user rep for the application said that it is still slow. So, I just changed the parallel clause back to none, I am also re-analyzing, I assume this is a good idea after altering the index? Should I re-analyze the table as well?