Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Why does index rebuild / creation fail?

Edward Mugerwa
Occasional Advisor

Why does index rebuild / creation fail?

Dear All;

I have a problem with my index. It is a partitioned index with 8 paritions. Some partitions of the corresponding table hold no data.

I have over 50Million rows in the table. Problems started when the index become unusable. So decided to rebuild the index and it was taking up alot of undo tablespace and took too long, I had to halt the process. So I decided to drop the index and re-create it!! Same problem resulted and process halted.

As a last resort I decided to start afresh, export data, drop table/index, create table, create index, import without index. This also failed.

So I used the recommended method, drop table/index, create table, import dump without index,create index after importation.
Again data was successfully imported but index creation just took too long and there was no apparent activity in the database. I let run overnight with no results in the morning. process forced to halt!!

Lo!! What is the problem with the index or process ?

Any help appreciated

Edward
6 REPLIES
Yogeeraj_1
Honored Contributor

Re: Why does index rebuild / creation fail?

hi edward,

did you try to rebuild it in parallel?
There is also the nologging option that you can use to accelerate the process.

if you need further help, let us know

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Steven E. Protter
Exalted Contributor

Re: Why does index rebuild / creation fail?

Shalom Edward,

Suggestions:
1) If possible build the Index on and idle database to see if that helps.
2) Make sure there is free space for the index tablespace to cut a new extent.
3) Make sure the database/tablespace is configured to cut a new extent.
4) See if anything can be done to enhance the i/o rate or relieve other pressures on the SAN.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Yogeeraj_1
Honored Contributor

Re: Why does index rebuild / creation fail?

hi again,

Allow me also to add the folllowing:
For instance if i have 5 CPUs, I would "alter index rebuild parallel 12" actually, with the NOLOGGING option if I could.

Oracle work better where you have more processes than CPUs. Especially with indexing. Indexing is IO intensive. We'll have lots of times where we are waiting on IO and not using the CPU. Having more processes then CPUs will help utilize them fully. This assumes of course you are doing this off hours.

The index will fundementally be the same after the parallel index rebuild.

Read also "Creating Indexes in Parallel" from the following site: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/tuningpe.htm#63440

hope this helps too!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Eric Antunes
Honored Contributor

Re: Why does index rebuild / creation fail?

Hi Edward,

Please post the commmand you are using to recreate the index and the following query result:

select *
from dba_extents
where segment_name = ''
order by extent_id desc;

select file_id, block_id, blocks,
owner||'.'||segment_name "Name"
from sys.dba_extents
where tablespace_name = upper()
union
select file_id, block_id, blocks,
'Free'
from sys.dba_free_space
where tablespace_name = upper()
order by 1,2,3;

Best Regards,

Eric Antunes
Each and every day is a good day to learn.
Patti Johnson
Respected Contributor

Re: Why does index rebuild / creation fail?

Edward,

One trick I learned to create large indexes faster is to put the DATA tablespace in read only mode. This disables all the work Oracle does for consistent read checking.
Also setting the sort_area_size for your session to a larger value can help.

When all is complete it's necessary to run a backup.

Patti
Simon Wickham_6
Regular Advisor

Re: Why does index rebuild / creation fail?

Hi Edward,

Are You receiving any ORA error message during ALTER INDEX REBUILD. If the answer is yes then you will have to increase the amount of available free space in the index tablespace either by adding another datafile or enabling autoextend on an existing datafile.

Regards,
Simon