Re: Index Creation question

From: Shevtsov Eduard <eshevtsov_at_flagship.ru>
Date: Sat, 9 Sep 2000 20:54:19 +0400
Message-Id: <10614.116584@fatcity.com>

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.

  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

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-1" =

http-equiv=3DContent-Type>

<META content=3D"MSHTML 5.00.2722.2800" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<BLOCKQUOTE dir=3Dltr=20

style=3D"BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: = 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px">
<DIV><FONT face=3D"ARIAL CYR" size=3D2>There is an option for online =
rebuilding=20
  which doesn't lock table during the process.</FONT></DIV>
<DIV><FONT face=3D"ARIAL CYR" size=3D2>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.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3D"Arial Cyr" size=3D2>Ed</FONT></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>I think you are right.  When I =
do index=20
  rebuilds I have to be the only one able to hold locks.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Allan</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20

  style=3D"BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; = MARGIN-RIGHT: 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px">

<DIV style=3D"FONT: 10pt arial">----- Original Message ----- </DIV> <DIV=20
style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: = black"><B>From:</B>=20

<A href=3D"mailto:steve_at_pactr.com" title=3Dsteve_at_pactr.com>Steve = McClure</A>=20

</DIV>
<DIV style=3D"FONT: 10pt arial"><B>To:</B> <A=20 href=3D"mailto:ORACLE-L_at_fatcity.com" = title=3DORACLE-L_at_fatcity.com>Multiple=20

recipients of list ORACLE-L</A> </DIV> <DIV style=3D"FONT: 10pt arial"><B>Sent:</B> Friday, September 08, = 2000 6:40=20

PM</DIV>
<DIV style=3D"FONT: 10pt arial"><B>Subject:</B> Index Creation = question</DIV>

<DIV><BR></DIV>
<DIV><FONT face=3DArial size=3D2>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.</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>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.</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Am I just being paranoid, or should = I make them=20

    wait?</FONT></DIV>
    <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
    <DIV><FONT face=3DArial size=3D2>Steve=20

Received on Sat Sep 09 2000 - 11:54:19 CDT