Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Index Creation question

Re: Index Creation question

From: Allan Nelson <anelson_at_houston.rr.com>
Date: Sat, 9 Sep 2000 10:16:36 -0500
Message-Id: <10614.116583@fatcity.com>


This is a multi-part message in MIME format.

------=_NextPart_000_002B_01C01A47.0915D5C0 Content-Type: text/plain;

        charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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

Allan

  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.

  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.

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

  Steve McClure

------=_NextPart_000_002B_01C01A47.0915D5C0 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 http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 5.50.4134.600" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>I think you are right.&nbsp; 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>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Allan</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20

style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; = BORDER-LEFT: #000000 2px solid; MARGIN-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 title=3Dsteve_at_pactr.com href=3D"mailto:steve_at_pactr.com">Steve =
McClure</A>=20
</DIV>
<DIV style=3D"FONT: 10pt arial"><B>To:</B> <A =
title=3DORACLE-L_at_fatcity.com=20
  href=3D"mailto:ORACLE-L_at_fatcity.com">Multiple recipients of list = ORACLE-L</A>=20
</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.&nbsp; This type of work I usually schedule = for=20
  weekends or late at night when downtime would cause minimal = problems.&nbsp;=20
  The client I am currently working for, as a developer not a DBA--I = learn about=20
  all things DBA as I go--, is asking me to do this in the middle of the =

  day.&nbsp; The deal is I am going away for the weekend and won't be = able to do=20
  it until next week when I return.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Anyway my reservation has to do with =
incoming=20
  transactions during the index build.&nbsp; First I have this thought = in the=20
  back of my head that creating an index will lock the table, but I = can't really=20
  say where that is coming from.&nbsp; Secondly I am certain that it = will have a=20
  negative impact on transactions during the index creation.&nbsp; We = are=20
  talking about 4 million rows in the table, and it is THE table read = and=20
  written to during OLTP.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</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 - 10:16:36 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US