Message-Id: <10614.116584@fatcity.com> From: "Shevtsov Eduard" Date: Sat, 9 Sep 2000 20:54:19 +0400 Subject: Re: Index Creation question This is a multi-part message in MIME format. ------=_NextPart_000_00E4_01C01AA0.1FF93850 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable There is an option for online rebuilding which doesn't lock table = during the process. alter index my_index rebuild online; It works for 8i. But it's not = recommended to do an online rebuilding for heavy loaded tables. Ed I think you are right. When I do index rebuilds I have to be the only = one able to hold locks. =20 Allan ----- Original Message -----=20 From: Steve McClure=20 To: Multiple recipients of list ORACLE-L=20 Sent: Friday, September 08, 2000 6:40 PM Subject: Index Creation question I have a question about creating an index on a production OLTP = database. This type of work I usually schedule for weekends or late at = night when downtime would cause minimal problems. The client I am = currently working for, as a developer not a DBA--I learn about all = things DBA as I go--, is asking me to do this in the middle of the day. = The deal is I am going away for the weekend and won't be able to do it = until next week when I return. =20 Anyway my reservation has to do with incoming transactions during = the index build. First I have this thought in the back of my head that = creating an index will lock the table, but I can't really say where that = is coming from. Secondly I am certain that it will have a negative = impact on transactions during the index creation. We are talking about = 4 million rows in the table, and it is THE table read and written to = during OLTP. =20 Am I just being paranoid, or should I make them wait? =20 Steve McClure ------=_NextPart_000_00E4_01C01AA0.1FF93850 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

There is an option for online = rebuilding=20 which doesn't lock table during the process.

alter index my_index rebuild = online; It=20 works for 8i. But it's not recommended to do an online = rebuilding=20 for heavy loaded tables.

Ed

I think you are right. When I = do index=20 rebuilds I have to be the only one able to hold locks.

Allan

----- Original Message -----
From:=20 Steve = McClure=20
To: Multiple=20 recipients of list ORACLE-L

Sent: Friday, September 08, = 2000 6:40=20 PM

Subject: Index Creation = question

I have a question about creating an = index on a=20 production OLTP database. This type of work I usually schedule = for=20 weekends or late at night when downtime would cause minimal = problems. =20 The client I am currently working for, as a developer not a DBA--I = learn=20 about all things DBA as I go--, is asking me to do this in the = middle of the=20 day. The deal is I am going away for the weekend and won't be = able to=20 do it until next week when I return.

Anyway my reservation has to do = with incoming=20 transactions during the index build. First I have this thought = in the=20 back of my head that creating an index will lock the table, but I = can't=20 really say where that is coming from. Secondly I am certain = that it=20 will have a negative impact on transactions during the index = creation. =20 We are talking about 4 million rows in the table, and it is THE = table read=20 and written to during OLTP.

Am I just being paranoid, or should = I make them=20 wait?

Steve=20