Re: Index organized tables

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Thu, 25 May 2000 19:55:12 -0400
Message-Id: <10508.106801@fatcity.com>

This is a multi-part message in MIME format.

------=_NextPart_000_0016_01BFC683.23844670 Content-Type: text/plain;

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

You are right, if the index values will be reused. But, in my particular case I am purging historical data (column of = 'date' type is a prefix in PK), so the values will never be reused. And, unfortunately I'm stuck with Standard edition - no partitioning = option.

Igor

Original Message -----=20 From: Colin.Shaw_at_phs.com=20 To: Multiple recipients of list ORACLE-L=20 Sent: Thursday, May 25, 2000 5:55 PM Subject: RE: Index organized tables

  Igor:
=20

  ...empty space in the indexes will not be reused...
=20

  Not true - if you re-insert the same index values, they will go to the = "old" index nodes. Remember it's a sorted index - data *must* go in the = correct place.
=20

  Colin.
    -----Original Message-----
    From: Igor Neyman [mailto:ineyman_at_perceptron.com]     Sent: Wednesday, May 24, 2000 8:19 AM     To: Multiple recipients of list ORACLE-L     Subject: Index organized tables

My question is - how to reuse space in Index Organized Table (IOT) = after deleting substantial amount of rows.

When I deal with regular table, after deleting a lot of rows I = rebuild the indexes in order not to leave gaps in the index blocks, = because we know that this empty space in the indexes will not be reused = (unlike, when it is reused in table blocks after PCTUSED threshold met = in the block).

So, what could be done with IOT in order to prevent constant growth, = even after a lot of rows being deleted?

    Igor Neyman, OCP DBA
    Perceptron, Inc.
    (734)414-4627
    ineyman_at_perceptron.com
     =20

------=_NextPart_000_0016_01BFC683.23844670 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>
<DIV><FONT face=3DArial size=3D2>You are right, if the index values will =

be=20
reused.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>But, in my particular case I = am purging=20
historical data (column of 'date' type is a prefix in PK), so the values = will=20
never be reused.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>And, unfortunately I'm stuck with = Standard edition=20
- no partitioning option.</FONT></DIV>

<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Igor</FONT></DIV>
<BLOCKQUOTE=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:Colin.Shaw_at_phs.com"=20   title=3DColin.Shaw_at_phs.com>Colin.Shaw_at_phs.com</A> </DIV>   <DIV style=3D"FONT: 10pt arial"><B>To:</B> <A = href=3D"mailto:ORACLE-L_at_fatcity.com"=20
  title=3DORACLE-L_at_fatcity.com>Multiple recipients of list ORACLE-L</A> = </DIV>
  <DIV style=3D"FONT: 10pt arial"><B>Sent:</B> Thursday, May 25, 2000 = 5:55=20
PM</DIV>
  <DIV style=3D"FONT: 10pt arial"><B>Subject:</B> RE: Index organized = tables</DIV>
  <DIV><BR></DIV>
  <DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20   class=3D489293720-25052000>Igor:</SPAN></FONT></DIV>   <DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20   class=3D489293720-25052000></SPAN></FONT> </DIV>   <DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20   class=3D489293720-25052000>...empty space in the indexes will not be=20   reused...</SPAN></FONT></DIV>
  <DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20   class=3D489293720-25052000></SPAN></FONT> </DIV>   <DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20   class=3D489293720-25052000>Not true - if you re-insert the same = index=20
  values, they will go to the "old" index nodes.  Remember it's a = sorted=20
  index - data *must* go in the correct place.</SPAN></FONT></DIV>   <DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20   class=3D489293720-25052000></SPAN></FONT> </DIV>   <DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20   class=3D489293720-25052000>Colin.</SPAN></FONT></DIV>   <BLOCKQUOTE style=3D"MARGIN-RIGHT: 0px">     <DIV align=3Dleft class=3DOutlookMessageHeader dir=3Dltr><FONT = face=3DTahoma=20

size=3D2>-----Original Message-----<BR><B>From:</B> Igor Neyman [<A=20 =
href=3D"mailto:ineyman_at_perceptron.com">mailto:ineyman_at_perceptron.com</A>]= <BR><B>Sent:</B>=20

Wednesday, May 24, 2000 8:19 AM<BR><B>To:</B> Multiple recipients of = list=20

ORACLE-L<BR><B>Subject:</B> Index organized = tables<BR><BR></DIV></FONT>

<DIV><FONT size=3D2>My question is - how to reuse space in Index = Organized=20

Table (IOT) after deleting substantial amount of rows.</FONT></DIV> <DIV><FONT size=3D2>When I deal with regular table, = after deleting a lot=20

of rows I rebuild the indexes in order not to leave gaps in the = index=20

blocks, because we know that this empty space in the indexes will = not be=20

reused (unlike, when it is reused in table blocks after PCTUSED = threshold=20

met in the block).</FONT></DIV>
<DIV><FONT size=3D2>So, what could be done with IOT in order to = prevent=20

constant growth, even after a lot of rows being = deleted?</FONT></DIV>

    <DIV> </DIV>
    <DIV><FONT size=3D2>Igor Neyman, OCP DBA<BR>Perceptron,=20     Inc.<BR>(734)414-4627<BR><A=20
    =
href=3D"mailto:ineyman_at_perceptron.com">ineyman_at_perceptron.com</A><BR>&nbs= Received on Thu May 25 2000 - 18:55:12 CDT