RE: Index organized tables

From: <Colin.Shaw_at_phs.com>
Date: Thu, 25 May 2000 13:46:01 -0700
Message-Id: <10508.106777@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01BFC68A.3CAEAC10
Content-Type: text/plain;
charset=iso-8859-1
Content-Transfer-Encoding: 7bit

Igor:

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

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.

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 <mailto:ineyman_at_perceptron.com>

------_=_NextPart_001_01BFC68A.3CAEAC10
Content-Type: text/html;
charset=iso-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">

<META content="MSHTML 5.00.2314.1000" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=489293720-25052000>Igor:</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=489293720-25052000></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=489293720-25052000>...empty space in the indexes will not be reused...</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=489293720-25052000></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=489293720-25052000>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.</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=489293720-25052000></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=489293720-25052000>Colin.</SPAN></FONT></DIV>
<BLOCKQUOTE style="MARGIN-RIGHT: 0px">

  <DIV align=left class=OutlookMessageHeader dir=ltr><FONT face=Tahoma   size=2>-----Original Message-----<BR><B>From:</B> Igor Neyman   [mailto:ineyman_at_perceptron.com]<BR><B>Sent:</B> Wednesday, May 24, 2000 8:19   AM<BR><B>To:</B> Multiple recipients of list ORACLE-L<BR><B>Subject:</B> Index   organized tables<BR><BR></DIV></FONT>
  <DIV><FONT size=2>My question is - how to reuse space in Index Organized Table   (IOT) after deleting substantial amount of rows.</FONT></DIV>   <DIV><FONT size=2>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).</FONT></DIV>
  <DIV><FONT size=2>So, what could be done with IOT in order to prevent constant   growth, even after a lot of rows being deleted?</FONT></DIV>   <DIV> </DIV>
  <DIV><FONT size=2>Igor Neyman, OCP DBA<BR>Perceptron,   Inc.<BR>(734)414-4627<BR><A
  href="mailto:ineyman_at_perceptron.com">ineyman_at_perceptron.com</A><BR>    <BR></FONT></DIV></BLOCKQUOTE></BODY></HTML> Received on Thu May 25 2000 - 15:46:01 CDT