Skip to Main Content
  • Questions

  • can I rebuild an index online in oracle 8i

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: September 21, 2003 - 2:26 pm UTC

Last updated: July 15, 2005 - 8:36 pm UTC

Version: 8.1.7.4

Viewed 1000+ times

You Asked

In 8i can I rebuild an index online? Are there any restictions on others using the table at the same time? I am not using a partitioned table or parallel server.

and Tom said...

ops$tkyte@ORA817DEV> alter index EMP_PK rebuild online;

Index altered.


yes you can...


are their restrictions? well -- depends on how you define that work. here are some things to consider:

o there is a brief need for a lock at the beginning of this command. the rebuild online will wait for all outstanding transactions to complete and then it'll start. so, it would be best to wait for a time of "low use"

o it'll take 2x or thereabouts the space PLUS room to hold the modifications so we can sync the two indexes for after a bit.



Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

is this only for enterprise edition?

Tina McAllsiter, September 22, 2003 - 9:21 am UTC

I ran and got error ORA-00439: feature not enabled: Online Index Build. So is this a feature for enterprise edition only? We have standard edition.

Tom Kyte
September 22, 2003 - 9:24 am UTC

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96531/ch5_edit.htm#89333 <code>

it is EE and PE (enterprise/personal)

Problems with not using online

A reader, July 15, 2005 - 4:07 pm UTC

Hi. I've got a beginning level question -
If you don't use online - what happens? Let's say you're in a warehouse environment with no DML - do you need to use the online feature?

Tom Kyte
July 15, 2005 - 8:36 pm UTC

you never "need" to use online, online permits modifications while the rebuild takes place. if you don't need that, you don't need that.

rebuilding an index in a warehouse could be a bad idea if you have lots of long running queries. The queries that start at time T0 and use the index will be "upset" perhaps at time T2 after you rebuild their index at time T1 -- the index they were using "goes away"

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.