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: When to rebuild Indexes

RE: When to rebuild Indexes

From: Koivu, Lisa <lkoivu_at_qode.com>
Date: Tue, 26 Dec 2000 09:43:44 -0500
Message-Id: <10722.125318@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_01C06F4A.3F968FCE
Content-Type: text/plain

Hello Earl,

I can understand 3, 1, well... if you can afford the time. But what about #2 below? Is it because the b-tree may be off balance? I guess I just don't see having blevel higher than 2 as a problem. ?? Thinking about the structure of the b-tree and back to my days as a dumb programmer in college, a b-tree with more than a couple of levels made for a quicker path to the item(s) you were looking for.

I'm looking forward to your comments. Please enlighten me.

Thanks

Lisa Rutland Koivu
Oracle Database Administrator
Qode.com
4850 North State Road 7
Suite G104
Fort Lauderdale, FL 33319

V: 954.484.3191, x174
F: 954.484.2933 
C: 954.658.5849

http://www.qode.com

"The information contained herein does not express the opinion or position of Qode.com and cannot be attributed to or made binding upon Qode.com."

-----Original Message-----
From: The Oracle DBA [mailto:theoracledba_at_lycos.com] Sent: Friday, December 22, 2000 9:07 AM
To: Multiple recipients of list ORACLE-L Subject: Re: When to rebuild Indexes

As far as when, I use these rules:

1) regularly 2 ro 3 times a year
2) when blevel is > 3
3) after major loads or lots of delete action

Cheers,

Earl

On Fri, 22 Dec 2000 04:56:06
 Ruth Gramolini wrote:
>I believe the command it 'alter index schema.my_index rebuild;'
>
>Ruth
>it is more important to be human than to be important
>----- Original Message -----
>To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
>Sent: Thursday, December 21, 2000 4:03 PM
>
>
>In version 7.3 and above tablespaces are automatically coalesced, but if
you
>mean to defragment the index, use the
>
>alter index schema.my_table rebuild
>
>Which will recreate the index by reading the previous index. You can also
>change the storage sizes and tablespace if you want to during the rebuild.
>The default is to rebuild with the same storage and tablespace as the
>previous index.
>
>>>> Pablo ksksksk <p_rodri99_at_yahoo.es> 12/21/00 02:50PM >>>
>Hi list:
>
>I'd like to know when should I colaesce an index?
>
>When should I rebuild it?
>What views should I be looking at?
>
>thank you
>
>
>
>_______________________________________________________________
>Do You Yahoo!?
>Consiga gratis su direccisn @yahoo.es en http://correo.yahoo.es
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: =?iso-8859-1?q?Pablo=20ksksksk?=
> INET: p_rodri99_at_yahoo.es
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: William Beilstein
> INET: BeilstWH_at_OBG.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Ruth Gramolini
> INET: rgramolini_at_tax.state.vt.us
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
>

Get FREE Email/Voicemail with 15MB at Lycos Communications at http://comm.lycos.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: The Oracle DBA
  INET: theoracledba_at_lycos.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

------_=_NextPart_001_01C06F4A.3F968FCE
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3DUS-ASCII">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2650.12">
<TITLE>RE: When to rebuild Indexes</TITLE>
</HEAD>
<BODY>

<P><FONT SIZE=3D2>Hello Earl, </FONT>
</P>

<P><FONT SIZE=3D2>I can understand 3, 1, well... if you can afford the =
time.&nbsp; But what about #2 below?&nbsp; Is it because the b-tree may =
be off balance?&nbsp; I guess I just don't see having blevel higher =
than 2 as a problem.&nbsp; ??&nbsp; Thinking about the structure of the =
b-tree and back to my days as a dumb programmer in college, a b-tree =
with more than a couple of levels made for a quicker path to the =
item(s) you were looking for. </FONT></P>

<P><FONT SIZE=3D2>I'm looking forward to your comments.&nbsp; Please =
enlighten me. </FONT>
</P>

<P><FONT SIZE=3D2>Thanks</FONT>
</P>

<P><FONT SIZE=3D2>Lisa Rutland Koivu</FONT>
<BR><FONT SIZE=3D2>Oracle Database Administrator</FONT>
<BR><FONT SIZE=3D2>Qode.com</FONT>
<BR><FONT SIZE=3D2>4850 North State Road 7</FONT>
<BR><FONT SIZE=3D2>Suite G104</FONT>
<BR><FONT SIZE=3D2>Fort Lauderdale, FL&nbsp; 33319</FONT>
</P>

<P><FONT SIZE=3D2>V: 954.484.3191, x174</FONT>
<BR><FONT SIZE=3D2>F: 954.484.2933 </FONT>
<BR><FONT SIZE=3D2>C: 954.658.5849</FONT>
<BR><FONT SIZE=3D2><A HREF=3D"http://www.qode.com" =
TARGET=3D"_blank">http://www.qode.com</A></FONT>
</P>

<P><FONT SIZE=3D2>&quot;The information contained herein does not =
express the opinion or position of Qode.com and cannot be attributed to =
or made binding upon Qode.com.&quot;</FONT></P>
<BR>

<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: The Oracle DBA [<A =
HREF=3D"mailto:theoracledba_at_lycos.com">mailto:theoracledba_at_lycos.com</A>=
]</FONT>
<BR><FONT SIZE=3D2>Sent: Friday, December 22, 2000 9:07 AM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>Subject: Re: When to rebuild Indexes</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>As far as when, I use these rules:</FONT>
<BR><FONT SIZE=3D2>1) regularly 2 ro 3 times a year</FONT>
<BR><FONT SIZE=3D2>2) when blevel is &gt; 3</FONT>
<BR><FONT SIZE=3D2>3) after major loads or lots of delete action</FONT>
</P>

<P><FONT SIZE=3D2>Cheers,</FONT>
</P>

<P><FONT SIZE=3D2>Earl</FONT>
</P>
<BR>
<BR>
<BR>

<P><FONT SIZE=3D2>On Fri, 22 Dec 2000 04:56:06&nbsp; </FONT>
<BR><FONT SIZE=3D2>&nbsp;Ruth Gramolini wrote:</FONT>
<BR><FONT SIZE=3D2>&gt;I believe the command it 'alter index =
schema.my_index rebuild;'</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT SIZE=3D2>&gt;Ruth</FONT>
<BR><FONT SIZE=3D2>&gt;it is more important to be human than to be =
important</FONT>
<BR><FONT SIZE=3D2>&gt;----- Original Message -----</FONT>
<BR><FONT SIZE=3D2>&gt;To: &quot;Multiple recipients of list =
ORACLE-L&quot; &lt;ORACLE-L_at_fatcity.com&gt;</FONT>
<BR><FONT SIZE=3D2>&gt;Sent: Thursday, December 21, 2000 4:03 PM</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT SIZE=3D2>&gt;In version 7.3 and above tablespaces are =
automatically coalesced, but if you</FONT>
<BR><FONT SIZE=3D2>&gt;mean to defragment the index, use the</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT SIZE=3D2>&gt;alter index schema.my_table rebuild</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT SIZE=3D2>&gt;Which will recreate the index by reading the =
previous index. You can also</FONT>
<BR><FONT SIZE=3D2>&gt;change the storage sizes and tablespace if you =
want to during the rebuild.</FONT>
<BR><FONT SIZE=3D2>&gt;The default is to rebuild with the same storage =
and tablespace as the</FONT>
<BR><FONT SIZE=3D2>&gt;previous index.</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT SIZE=3D2>&gt;&gt;&gt;&gt; Pablo ksksksk =
&lt;p_rodri99_at_yahoo.es&gt; 12/21/00 02:50PM &gt;&gt;&gt;</FONT>
<BR><FONT SIZE=3D2>&gt;Hi list:</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT SIZE=3D2>&gt;I'd like to know when should I colaesce an =
index?</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT SIZE=3D2>&gt;When should I rebuild it?</FONT>
<BR><FONT SIZE=3D2>&gt;What views should I be looking at?</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT SIZE=3D2>&gt;thank you</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT =
SIZE=3D2>&gt;___________________________________________________________=
____</FONT>
<BR><FONT SIZE=3D2>&gt;Do You Yahoo!?</FONT>
<BR><FONT SIZE=3D2>&gt;Consiga gratis su direccisn @yahoo.es en <A =
HREF=3D"http://correo.yahoo.es" =
TARGET=3D"_blank">http://correo.yahoo.es</A></FONT>
<BR><FONT SIZE=3D2>&gt;--</FONT>
<BR><FONT SIZE=3D2>&gt;Please see the official ORACLE-L FAQ: <A =
HREF=3D"http://www.orafaq.com" =
TARGET=3D"_blank">http://www.orafaq.com</A></FONT>
<BR><FONT SIZE=3D2>&gt;--</FONT>
<BR><FONT SIZE=3D2>&gt;Author: =
=3D?iso-8859-1?q?Pablo=3D20ksksksk?=3D</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp; INET: p_rodri99_at_yahoo.es</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT SIZE=3D2>&gt;Fat City Network Services&nbsp;&nbsp;&nbsp; -- =
(858) 538-5051&nbsp; FAX: (858) 538-5051</FONT>
<BR><FONT SIZE=3D2>&gt;San Diego, =
California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet =
access / Mailing Lists</FONT>
<BR><FONT =
SIZE=3D2>&gt;-----------------------------------------------------------=
---------</FONT>
<BR><FONT SIZE=3D2>&gt;To REMOVE yourself from this mailing list, send =
an E-Mail message</FONT>
<BR><FONT SIZE=3D2>&gt;to: ListGuru_at_fatcity.com (note EXACT spelling of =
'ListGuru') and in</FONT>
<BR><FONT SIZE=3D2>&gt;the message BODY, include a line containing: =
UNSUB ORACLE-L</FONT>
<BR><FONT SIZE=3D2>&gt;(or the name of mailing list you want to be =
removed from).&nbsp; You may</FONT>
<BR><FONT SIZE=3D2>&gt;also send the HELP command for other information =
(like subscribing).</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT SIZE=3D2>&gt;--</FONT>
<BR><FONT SIZE=3D2>&gt;Please see the official ORACLE-L FAQ: <A =
HREF=3D"http://www.orafaq.com" =
TARGET=3D"_blank">http://www.orafaq.com</A></FONT>
<BR><FONT SIZE=3D2>&gt;--</FONT>
<BR><FONT SIZE=3D2>&gt;Author: William Beilstein</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp; INET: BeilstWH_at_OBG.com</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT SIZE=3D2>&gt;Fat City Network Services&nbsp;&nbsp;&nbsp; -- =
(858) 538-5051&nbsp; FAX: (858) 538-5051</FONT>
<BR><FONT SIZE=3D2>&gt;San Diego, =
California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet =
access / Mailing Lists</FONT>
<BR><FONT =
SIZE=3D2>&gt;-----------------------------------------------------------=
---------</FONT>
<BR><FONT SIZE=3D2>&gt;To REMOVE yourself from this mailing list, send =
an E-Mail message</FONT>
<BR><FONT SIZE=3D2>&gt;to: ListGuru_at_fatcity.com (note EXACT spelling of =
'ListGuru') and in</FONT>
<BR><FONT SIZE=3D2>&gt;the message BODY, include a line containing: =
UNSUB ORACLE-L</FONT>
<BR><FONT SIZE=3D2>&gt;(or the name of mailing list you want to be =
removed from).&nbsp; You may</FONT>
<BR><FONT SIZE=3D2>&gt;also send the HELP command for other information =
(like subscribing).</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT SIZE=3D2>&gt;-- </FONT>
<BR><FONT SIZE=3D2>&gt;Please see the official ORACLE-L FAQ: <A =
HREF=3D"http://www.orafaq.com" =
TARGET=3D"_blank">http://www.orafaq.com</A></FONT>
<BR><FONT SIZE=3D2>&gt;-- </FONT>
<BR><FONT SIZE=3D2>&gt;Author: Ruth Gramolini</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp; INET: rgramolini_at_tax.state.vt.us</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT SIZE=3D2>&gt;Fat City Network Services&nbsp;&nbsp;&nbsp; -- =
(858) 538-5051&nbsp; FAX: (858) 538-5051</FONT>
<BR><FONT SIZE=3D2>&gt;San Diego, =
California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet =
access / Mailing Lists</FONT>
<BR><FONT =
SIZE=3D2>&gt;-----------------------------------------------------------=
---------</FONT>
<BR><FONT SIZE=3D2>&gt;To REMOVE yourself from this mailing list, send =
an E-Mail message</FONT>
<BR><FONT SIZE=3D2>&gt;to: ListGuru_at_fatcity.com (note EXACT spelling of =
'ListGuru') and in</FONT>
<BR><FONT SIZE=3D2>&gt;the message BODY, include a line containing: =
UNSUB ORACLE-L</FONT>
<BR><FONT SIZE=3D2>&gt;(or the name of mailing list you want to be =
removed from).&nbsp; You may</FONT>
<BR><FONT SIZE=3D2>&gt;also send the HELP command for other information =
(like subscribing).</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>Get FREE Email/Voicemail with 15MB at Lycos =
Communications at <A HREF=3D"http://comm.lycos.com" =
TARGET=3D"_blank">http://comm.lycos.com</A></FONT>
<BR><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Please see the official ORACLE-L FAQ: <A =
HREF=3D"http://www.orafaq.com" =
TARGET=3D"_blank">http://www.orafaq.com</A></FONT>
<BR><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Author: The Oracle DBA</FONT>
<BR><FONT SIZE=3D2>&nbsp; INET: theoracledba_at_lycos.com</FONT>
</P>

<P><FONT SIZE=3D2>Fat City Network Services&nbsp;&nbsp;&nbsp; -- (858) =
538-5051&nbsp; FAX: (858) 538-5051</FONT>
<BR><FONT SIZE=3D2>San Diego, =
California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet =
access / Mailing Lists</FONT>
<BR><FONT =
SIZE=3D2>---------------------------------------------------------------=
-----</FONT>
<BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an =
E-Mail message</FONT>
<BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of =
Received on Tue Dec 26 2000 - 08:43:44 CST

Original text of this message

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