> Hello Ari,
> 		I am a DBA trainee at Nokia. Am doing my final year 
> 		project on " Coalscing Free Spaces During Index
> 		Recreation". 
> 		Overhere, we use the DROP index CREATE index syntax.
> 		Am suppose to compare it to the "ALTER index REBUILD"
> 		My question is:
> 		(1) what is the differnce between these two syntax?.

The REBUILD syntax can be built in parallel, and can use UNRECOVERABLE to avoid
writing to REDO logs. So, it is faster.

The CREATE INDEX syntax needs to have storge parameters set if they differ from
the default for the tablespace. For instance, the TABLESPACE, INITIAL, NEXT,
PCT_FREE, PCT_USED, etc. do not need to be specified with REBUILD but do with

Also, the CREATE INDEX needs to have column names listed, whereas REBUILD does

> 		(2) which one is faster and more accurate.

REBUILD is faster with the UNRECOVERABLE clause, and if done in parallel.

Both are equal in accuracy.

> 		(3) which one eliminates fragmentation as we recreate
> 		    indexes for the large tables.

They both eliminate fragmentation

> 		(4) could you please expalin the "ALTER index REBUILD
> 		    command for me as I have just started using Oracle.

REBUILD is new for Oracle 7.3. It takes the description and storage parameters
of the original index and creates a new one in its place. The benefit is to
reduce fragmentation of the index due to updates, deletes, and inserts of the
underlying table.

> 	        (5) could you please, explain index recreation to me. 

Please see above.

-Ari Kaplan
Independent Oracle DBA Consultant

<-> For 115+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             www.arikaplan.com                                 <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
> 		Please Mr. Kaplan, help me as I have no means of 
> 		a book explaining it better to me.
> 		Thank you very much.
> 		Francis
> **********************************************************************
> " The mind of the beginner is empty, free of the habits of the expert,
>   ready to accept, to doubt, and open to all possibilites"	
> ********************************************************************
> Francis Ansah                      E-mail:ansah@shire.ntc.nokia.com        
> Nokia Telecommunications           Tel.:   +358-9-51123881
> P.O.Box 320,00045,Nokia Group      GSM:    +358-40-5324081

Back to Ari Kaplan's Home Page " height="18">Back to Ari Kaplan's Home Page