Home » RDBMS Server » Server Administration » rebuild index and recreate them (hp ux 10.2.0.3)
rebuild index and recreate them [message #515369] Mon, 11 July 2011 03:18 Go to next message
sekar52
Messages: 141
Registered: May 2010
Location: Mumbai
Senior Member
Hi
What's the difference between rebuilding the index online and dropping and recreating them
Re: rebuild index and recreate them [message #515371 is a reply to message #515369] Mon, 11 July 2011 03:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
One obvious difference is the word ONLINE.
The second one is the duration of the operations.
The third one is the space used.

Doc: ALTER INDEX REUILD

Regards
Michel
Re: rebuild index and recreate them [message #515382 is a reply to message #515371] Mon, 11 July 2011 04:46 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
Also (a very nasty one) ONLINE is permitted only with an Enterprise Edition licence.
Re: rebuild index and recreate them [message #515383 is a reply to message #515382] Mon, 11 July 2011 04:50 Go to previous messageGo to next message
sekar52
Messages: 141
Registered: May 2010
Location: Mumbai
Senior Member
which will take more time and space

rebuild online or creating a new index
Re: rebuild index and recreate them [message #515384 is a reply to message #515383] Mon, 11 July 2011 04:59 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
Test it yourself:
conn / as sysdba
drop user jon cascade;
grant dba to jon identified by jon;
conn jon/jon
create table t1 as select * from all_objects;
create index i1 on t1(object_name);

alter index i1 rebuild online;


--in another session, before/during/after the index rebuild:
conn jon/jon
select segment_name,segment_type,bytes from user_segments;


Re: rebuild index and recreate them [message #515386 is a reply to message #515383] Mon, 11 July 2011 05:02 Go to previous message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
time: drop and create
space: rebuild

(But of course don't trust me, verify it.)

Regards
Michel
Previous Topic: can't change the parameters resource_manager_plan
Next Topic: changing archive log destination
Goto Forum:
  


Current Time: Sat Apr 27 18:50:06 CDT 2024