| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: When to rebuild Indexes
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_01C06C2F.D3A1E3F0
Content-Type: text/plain;
charset="iso-8859-1"
What I am doing is I have all tables originally analyzed and in monitoring (8.1.6) and every night cron job checks view dba_tab_modifications and dba_tables. If there is more then 10% updates+deletes+inserts all indexes for this table are rebuilt and table is analyzed (for table for all indexes for all indexed columns). I prefere to use dba_tab_modifications view and not dbms_stats...gather_stale because there are couple of bugs left and also I can change if needed percentage of changes when to reanalyze the table and rebuild indexes.
Alex Hillman
-----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_01C06C2F.D3A1E3F0 Content-Type: text/html; charset="iso-8859-1" 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=3Diso-8859-1"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2448.0"> <TITLE>RE: When to rebuild Indexes</TITLE> </HEAD> <BODY> <P><FONT SIZE=3D2>What I am doing is I have all tables originally = analyzed and in monitoring (8.1.6) and every night cron job checks view = dba_tab_modifications and dba_tables. If there is more then 10% = updates+deletes+inserts all indexes for this table are rebuilt and = table is analyzed (for table for all indexes for all indexed columns). = I prefere to use dba_tab_modifications view and not = dbms_stats...gather_stale because there are couple of bugs left and = also I can change if needed percentage of changes when to reanalyze the = table and rebuild indexes.</FONT></P> <P><FONT SIZE=3D2>Alex Hillman</FONT> </P> <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 > 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 </FONT> <BR><FONT SIZE=3D2> Ruth Gramolini wrote:</FONT> <BR><FONT SIZE=3D2>>I believe the command it 'alter index = schema.my_index rebuild;'</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>>Ruth</FONT> <BR><FONT SIZE=3D2>>it is more important to be human than to be = important</FONT> <BR><FONT SIZE=3D2>>----- Original Message -----</FONT> <BR><FONT SIZE=3D2>>To: "Multiple recipients of list = ORACLE-L" <ORACLE-L_at_fatcity.com></FONT> <BR><FONT SIZE=3D2>>Sent: Thursday, December 21, 2000 4:03 PM</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>>In version 7.3 and above tablespaces are = automatically coalesced, but if you</FONT> <BR><FONT SIZE=3D2>>mean to defragment the index, use the</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>>alter index schema.my_table rebuild</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>>Which will recreate the index by reading the = previous index. You can also</FONT> <BR><FONT SIZE=3D2>>change the storage sizes and tablespace if you = want to during the rebuild.</FONT> <BR><FONT SIZE=3D2>>The default is to rebuild with the same storage = and tablespace as the</FONT> <BR><FONT SIZE=3D2>>previous index.</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>>>>> Pablo ksksksk = <p_rodri99_at_yahoo.es> 12/21/00 02:50PM >>></FONT> <BR><FONT SIZE=3D2>>Hi list:</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>>I'd like to know when should I colaesce an = index?</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>>When should I rebuild it?</FONT> <BR><FONT SIZE=3D2>>What views should I be looking at?</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>>thank you</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT = SIZE=3D2>>___________________________________________________________= ____</FONT> <BR><FONT SIZE=3D2>>Do You Yahoo!?</FONT> <BR><FONT SIZE=3D2>>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>>--</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: = =3D?iso-8859-1?q?Pablo=3D20ksksksk?=3D</FONT> <BR><FONT SIZE=3D2>> INET: p_rodri99_at_yahoo.es</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>>Fat City Network Services -- = (858) 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=3D2>>San Diego, = California -- 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 = 'ListGuru') and in</FONT> <BR><FONT SIZE=3D2>>the message BODY, include a line containing: = UNSUB ORACLE-L</FONT> <BR><FONT SIZE=3D2>>(or the name of mailing list you want to be = removed from). You may</FONT> <BR><FONT SIZE=3D2>>also send the HELP command for other information = (like subscribing).</FONT> <BR><FONT SIZE=3D2>></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: William Beilstein</FONT> <BR><FONT SIZE=3D2>> INET: BeilstWH_at_OBG.com</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>>Fat City Network Services -- = (858) 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=3D2>>San Diego, = California -- 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 = 'ListGuru') and in</FONT> <BR><FONT SIZE=3D2>>the message BODY, include a line containing: = UNSUB ORACLE-L</FONT> <BR><FONT SIZE=3D2>>(or the name of mailing list you want to be = removed from). You may</FONT> <BR><FONT SIZE=3D2>>also send the HELP command for other information = (like subscribing).</FONT> <BR><FONT SIZE=3D2>></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: Ruth Gramolini</FONT> <BR><FONT SIZE=3D2>> INET: rgramolini_at_tax.state.vt.us</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>>Fat City Network Services -- = (858) 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=3D2>>San Diego, = California -- 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 = 'ListGuru') and in</FONT> <BR><FONT SIZE=3D2>>the message BODY, include a line containing: = UNSUB ORACLE-L</FONT> <BR><FONT SIZE=3D2>>(or the name of mailing list you want to be = removed from). You may</FONT> <BR><FONT SIZE=3D2>>also send the HELP command for other information = (like subscribing).</FONT> <BR><FONT SIZE=3D2>></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> INET: theoracledba_at_lycos.com</FONT> </P> <P><FONT SIZE=3D2>Fat City Network Services -- (858) = 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=3D2>San Diego, = California -- 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 = 'ListGuru') and in</FONT> <BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB =Received on Fri Dec 22 2000 - 09:57:03 CST
 |
 |