This content has been marked as final. Show 7 replies
1. In 10g there is no need to compute statistics for indexes since this is done automatically with creation.
2. As Justin already mentioned tracing might help you to find the reason
3. I experienced situations in which parallel processing slowed down DB activity massively, so try sequential processing.
Finally: In my 10g DB index creation is extremely fast: My table has 10 rows, my server has solid state devices and 16 CPUs. I can't watch as fast as the DB rebuilds the index. Do you notice what I want to say? :-)
If not, you'll find some hints here http://forums.oracle.com/forums/ann.jspa?annID=718
I am using a syntax like:alter index INDEX_NAME rebuild tablespace TABLESPACE_NAME online compute statistics parallel;
1. Why did are rebuilding the index.
2. You could have used
alter index INDEX_NAME rebuild tablespace TABLESPACE_NAME online parallel N; ---> where n is the CPU_COUNT parameter
3. Whats more going on the database.Check if any lock on the table whose index it is.
user8294047 wrote:In addition to the advices you've already got regarding index rebuilding in general:
We are trying to build indexes in Oracle 10g and its extremely slow. Any pointers to find out what the problem is and then fixing it?
I am using a syntax like:
alter index INDEX_NAME rebuild tablespace TABLESPACE_NAME online compute statistics parallel;
Should online and parallel together be an issue? (I don't necessarily need to do online, I just removed online and started again, but not sure how it will go)
If you're using the ONLINE rebuild option it might take a while if there are active transactions on the table, since it waits until no transactions are active to start the rebuild process, the same applies to the completion of the ONLINE operation.
Of course, you should get an "ORA-00054 resource busy" error when this is the case when using the normal (offline) rebuild option without the ONLINE keyword.
Oracle related stuff blog:
SQLTools++ for Oracle (Open source Oracle GUI for Windows):