• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 785
  • Last Modified:

Rebuild index

Hi
I have one  huge table which conists of  million rows , i that tabe i have 4 columns index is done,
i always when i run the archive script , its failling on the object id  for each column index giving  me the problem saying the index corrupted,we are using oracle 9i.

Please help me to resolve these.
Please suggest me the steps which i should follow.

Thanks in advanca
Raj
0
nrajasekhar7
Asked:
nrajasekhar7
  • 3
  • 3
6 Solutions
 
AkenathonCommented:
Just drop the index and re-create it. If the index is the only segment containing corrupted blocks you'll be fine. Rebuilding won't help because it uses the existing index data to build the new one, so you will hit the corrupted blocks and get an error.

0
 
PilouteCommented:
Hi,

If you are looking for the syntax to rebuild you index :

alter index <your_idx_name> rebuild;

I'd strongly suggest you to check also why your index gets corrupted. Rebuilding it is not a guarantee it won't become corrupted again....

Cheers,
P
0
 
PilouteCommented:
@Akenaton

Rebuilding mecanism builds a new index from the table data, then drops the former index. In other words does exactly what it should. No need to 'drop' the index and to 'create' it again.

@nrajasekhar7

Still you must check where's the corruption originating from. If it is your disks that are failing, you would rebuild somewhere else your indexes, for example....

Cheers,
P
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
slightwv (䄆 Netminder) Commented:
I should also throw out an online rebuild of an index so DML can continue on the table and existing queries can take advantage of the old one:

alter index index_name rebuild online;
0
 
AkenathonCommented:
@Piloute: Quoted from 9i's docs:

"When creating a new index that is a subset of an existing index or when rebuilding an existing index with new storage characteristics, Oracle might use the existing index instead of the base table to improve the performance of the index build."

"The REBUILD statement uses the existing index as the basis for the new one."

"Usually, ALTER INDEX ... REBUILD is faster than dropping and re-creating an index, because this statement uses the fast full scan feature. It reads all the index blocks using multiblock I/O, then discards the branch blocks."

If you want to learn when Oracle does a full table scan or a fast full scan on the existing index from someone who actually took the time to research it (using the same 9i as the author), please go to this page and find this string "When I rebuild an index, I see Oracle doing a sort". To save you the time: ONLY if he uses the ONLINE clause does Oracle appear to start from the table in a consistent fashion. However, even this is not documented (and not what some answers are saying anyway).

He can try using rebuild ONLINE, it does appear to use a FTS, but he CANNOT in ANY WAY use just "rebuild", because it DOES start from the existing index more often than not. Remember we're dealing with corruption here.

The bottom line is: IMHO, to be on the safe side, he'd better DROP and CREATE the index.
0
 
PilouteCommented:
@Akenathon

Thanks for the quote. Haven't read the docs since long time ;)  

When you say you'll save me the time, I feel like you're angry at me.
1) No need to. Really thought a rebuild will use table data under any circumtances.
2) Will take the time to carefully read the demo of the guy and will check it myself. Seems interesting.

Cheers,
P
0
 
AkenathonCommented:
@Piloute: Sorry for the wording, no ill intentions... I'm just too used to people not reading anything else but the posts :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now