Re: How do we know that an index need to be rebuilt.
The problem that rebuilding indexes is attempting to resolve is sparseness. Lots of folks think that indexes "become unbalanced" over time and that is the reason for rebuild, but Oracle B*Tree indexes are always balanced. Sparseness is the symptom, however, not the cause. The problem with sparsely-populated index structures is that it takes more I/O (both logical and ultimately physical) to process all four operations performed on indexes: unique scan, range scan, full scan, and fast full scan. What are the causes of sparseness? As Mladen indicated, deletions are one; index entries are not removed -- they are simply made unused and available for reuse. The other cause of sparseness is monotonically-ascending data values (i.e. values generated from a sequence or timestamp, etc). Oracle B*Tree indexes are optimized for randomly-generated data values and when a block in an index fills, it splits rather than overflows. This split activity will cause the index to gradually be comprised of half-filled blocks. If the data is not monotonically-ascending, then backfill will occur and the half-full blocks in the index will ultimately fill. If the data is monotonically-ascending, then the blocks will stay half-full forever. For the sparseness caused by deletion, rebuilds are often the only answer, unless the application can be "persuaded" to reuse data values in the indexed columns. :-) For the sparseness caused by monotonically-ascending data values, either rebuilds can be used periodically to improve the situation or REVERSE-key indexes can be used to solve the problem permanently. However, be aware that using REVERSE-key indexes comes with restrictions. on 9/22/03 1:04 AM, Mladen Gogala at [EMAIL PROTECTED] wrote: > There is a Metalink note 77574.1 entitled "Guidelines on When to Rebuild a B- > Tree Index" which explains how to use "validate structure" and index_stats > table. It is far too scientific to ever put into the script. Explanation > for the rule of thumb is simple: if number of index blocks is big that means > that the number of deleted entries is likely to be big, so let's rebuild it. > As I said, rebuilding indexes is not often necessary and is usually done for > all the wrong reasons. Quality of the rule of thumb, therefore, doesn't really > matter. > > On 2003.09.22 03:19, Prem Khanna J wrote: >> Read ur interesting reply for the query Mladen and learnt >> a new "rule of thumb" from u today. >> >> i sent the doc. to Raju ONLY adn NOT to the list. >> it's just the body of the mail u r seeing there. >> >> Regards, >> Jp. >> >> 22-09-2003 15:49:40, Mladen Gogala <[EMAIL PROTECTED]> wrote: >> >>> Attachments are stripped from the list messages. >> >>> On 2003.09.22 02:24, Prem Khanna J wrote: Hi Raju, Check the doc. i have attached. >> >> >> >> -- >> Please see the official ORACLE-L FAQ: http://www.orafaq.net >> -- >> Author: Prem Khanna J >> INET: [EMAIL PROTECTED] >> >> Fat City Network Services-- 858-538-5051 http://www.fatcity.com >> San Diego, California-- Mailing list and web hosting services >> - >> To REMOVE yourself from this mailing list, send an E-Mail message >> to: [EMAIL PROTECTED] (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). >> > > -- > Mladen Gogala > Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: How do we know that an index need to be rebuilt.
Thanks for all who responded. > -- > From: Prem Khanna J[SMTP:[EMAIL PROTECTED] > Reply To: [EMAIL PROTECTED] > Sent: Monday, September 22, 2003 1:59 PM > To: Multiple recipients of list ORACLE-L > Subject: Re: How do we know that an index need to be rebuilt. > > That's the doc. i mailed raju. > Thanx Mladen. > > can i add a question here : > > when is it appropriate to use BITMAP index ? > seems it has lot of restrictions. > read docs about it ...still confused. > > say, i have a big table with 50,000,000 records in a OLTP env. > it has column SEX which will have either MALE or FEMALE. > the table will have frequent insert/delete too. > often this SEX column is included in many queries. > > this being my case, can i use bitmap index on this column ? > > or can u give a simple example for when a bitmap index can be used ? > > Regards, > Jp. > > 22-09-2003 17:04:40, Mladen Gogala <[EMAIL PROTECTED]> wrote: > >There is a Metalink note 77574.1 entitled "Guidelines on When to Rebuild > a B- > >Tree Index" which explains how to use "validate structure" and > index_stats > >table. It is far too scientific to ever put into the script. Explanation > >for the rule of thumb is simple: if number of index blocks is big that > means > >that the number of deleted entries is likely to be big, so let's rebuild > it. > >As I said, rebuilding indexes is not often necessary and is usually done > for > >all the wrong reasons. Quality of the rule of thumb, therefore, doesn't > really > >matter. > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Prem Khanna J > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (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). > ** This email (including any attachments) is intended for the sole use of the intended recipient/s and may contain material that is CONFIDENTIAL AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or distribution or forwarding of any or all of the contents in this message is STRICTLY PROHIBITED. If you are not the intended recipient, please contact the sender by email and delete all copies; your cooperation in this regard is appreciated. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Veeraraju_Mareddi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: How do we know that an index need to be rebuilt.
That's the doc. i mailed raju. Thanx Mladen. can i add a question here : when is it appropriate to use BITMAP index ? seems it has lot of restrictions. read docs about it ...still confused. say, i have a big table with 50,000,000 records in a OLTP env. it has column SEX which will have either MALE or FEMALE. the table will have frequent insert/delete too. often this SEX column is included in many queries. this being my case, can i use bitmap index on this column ? or can u give a simple example for when a bitmap index can be used ? Regards, Jp. 22-09-2003 17:04:40, Mladen Gogala <[EMAIL PROTECTED]> wrote: >There is a Metalink note 77574.1 entitled "Guidelines on When to Rebuild a B- >Tree Index" which explains how to use "validate structure" and index_stats >table. It is far too scientific to ever put into the script. Explanation >for the rule of thumb is simple: if number of index blocks is big that means >that the number of deleted entries is likely to be big, so let's rebuild it. >As I said, rebuilding indexes is not often necessary and is usually done for >all the wrong reasons. Quality of the rule of thumb, therefore, doesn't really >matter. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: How do we know that an index need to be rebuilt.
set serveroutput on size 100
set pagesize 0
set feedback off
set echo off
set trimspool on
spool c:\reb_index1.sql
DECLARE
dbname varchar2(20);
wdayvarchar2(11);
BEGIN
dbms_output.put_line('set echo off');
dbms_output.put_line('set feedback off');
dbms_output.put_line('set head off');
dbms_output.put_line('spool c:\reb_index2.sql');
dbms_output.put_line('prompt set feedback on');
dbms_output.put_line('prompt set echo on');
dbms_output.put_line('prompt spool c:\reb_index2.log');
FOR t IN (select owner, index_name from dba_indexes where owner not in
('SYS','SYSTEM') order by owner,index_name) LOOP
dbms_output.put_line('prompt --Analizando
'||t.owner||'.'||t.index_name);
dbms_output.put_line('Analyze index '||t.owner||'.'||t.index_name||'
validate structure;');
dbms_output.put_line('select ' || '''' || 'Alter index ' || t.owner ||
'.' || t.index_name || ' rebuild online;' || ''''|| ' from index_stats where
(height > 2) or
(10<=decode(lf_rows_len,0,NULL,((del_lf_rows_len/lf_rows_len)*100)));');
END LOOP;
dbms_output.put_line('prompt spool off');
dbms_output.put_line('spool off');
dbms_output.put_line('@c:\reb_index2.sql');
END;
/
spool off
[EMAIL PROTECTED]:\reb_index1.sql
--------------------------------
-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de
Veeraraju_Mareddi
Enviado el: lunes, 22 de septiembre de 2003 8:00
Para: Multiple recipients of list ORACLE-L
Asunto: How do we know that an index need to be rebuilt.
Dear Friends,
Can somebody tell me how do we that an index needs to be rebuilt.. Different
scenarios / any documents will be helpful.
Thanks in advance.
Rajuvera
**
This email (including any attachments) is intended for the sole use of the
intended recipient/s and may contain material that is CONFIDENTIAL AND
PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or
distribution or forwarding of any or all of the contents in this message is
STRICTLY PROHIBITED. If you are not the intended recipient, please contact
the sender by email and delete all copies; your cooperation in this regard
is appreciated.
**
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Veeraraju_Mareddi
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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.net
--
Author: Juan Miranda
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
Re: How do we know that an index need to be rebuilt.
Anand, it's the same doc. mentioned by Mladen. i have sent it to your email address ONLY. Jp. 22-09-2003 16:20:21, "Anand Kumar" <[EMAIL PROTECTED]> wrote: > Prem, > > Can you mail me the same document -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: How do we know that an index need to be rebuilt.
There is a Metalink note 77574.1 entitled "Guidelines on When to Rebuild a B- Tree Index" which explains how to use "validate structure" and index_stats table. It is far too scientific to ever put into the script. Explanation for the rule of thumb is simple: if number of index blocks is big that means that the number of deleted entries is likely to be big, so let's rebuild it. As I said, rebuilding indexes is not often necessary and is usually done for all the wrong reasons. Quality of the rule of thumb, therefore, doesn't really matter. On 2003.09.22 03:19, Prem Khanna J wrote: Read ur interesting reply for the query Mladen and learnt a new "rule of thumb" from u today. i sent the doc. to Raju ONLY adn NOT to the list. it's just the body of the mail u r seeing there. Regards, Jp. 22-09-2003 15:49:40, Mladen Gogala <[EMAIL PROTECTED]> wrote: >Attachments are stripped from the list messages. >On 2003.09.22 02:24, Prem Khanna J wrote: >> Hi Raju, >> Check the doc. i have attached. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: How do we know that an index need to be rebuilt.
Read ur interesting reply for the query Mladen and learnt a new "rule of thumb" from u today. i sent the doc. to Raju ONLY adn NOT to the list. it's just the body of the mail u r seeing there. Regards, Jp. 22-09-2003 15:49:40, Mladen Gogala <[EMAIL PROTECTED]> wrote: >Attachments are stripped from the list messages. >On 2003.09.22 02:24, Prem Khanna J wrote: >> Hi Raju, >> Check the doc. i have attached. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: How do we know that an index need to be rebuilt.
Attachments are stripped from the list messages. On 2003.09.22 02:24, Prem Khanna J wrote: Hi Raju, Check the doc. i have attached. HTH. Jp. 22-09-2003 14:59:35, Veeraraju_Mareddi <[EMAIL PROTECTED]> wrote: >Dear Friends, > >Can somebody tell me how do we that an index needs to be rebuilt.. Different >scenarios / any documents will be helpful. > >Thanks in advance. >Rajuvera -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: How do we know that an index need to be rebuilt.
Oracle indexes do not need rebuilding on the regular basis, but only as an exceptional event. The warning sign is when queries that utilize nested loops slow down without any apparent reason. The only case when index needs to be rebuilt is the case of a table with frequent delete operations. You should consider rebuilding indexes if and only if the base table satisfies that crieria. How to decide? There are few rules of thumb, none satisfactory and decisive. I usually compare the number of blocks in the index with the number of blocks in the table. If the number of blocks in the index exceeds 50% of those in the table, the index is a candidate for rebuilding. How did I come to 50%? I have no clue. That value popped up at one of my previous jobs and I still use it, when I want to rebuild indexes. One of the most frequent reasons for index rebuilding is a magazine reading boss who has read that indexes need rebuilding. A script which rebuilds based on a criteria like 50% of the number of blocks usually satisfies damagement, especially if accompanied by a 3-page MS-Word document explaining this hocus-pocus in detail. Picture of a B*-tree scanned from N. Wirth's "Data Structures+Algorithms = Programs" is a mandatory requirement for such documents. On 2003.09.22 01:59, Veeraraju_Mareddi wrote: Dear Friends, Can somebody tell me how do we that an index needs to be rebuilt.. Different scenarios / any documents will be helpful. Thanks in advance. Rajuvera ** This email (including any attachments) is intended for the sole use of the intended recipient/s and may contain material that is CONFIDENTIAL AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or distribution or forwarding of any or all of the contents in this message is STRICTLY PROHIBITED. If you are not the intended recipient, please contact the sender by email and delete all copies; your cooperation in this regard is appreciated. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Veeraraju_Mareddi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: How do we know that an index need to be rebuilt.
Hi Raju, Check the doc. i have attached. HTH. Jp. 22-09-2003 14:59:35, Veeraraju_Mareddi <[EMAIL PROTECTED]> wrote: >Dear Friends, > >Can somebody tell me how do we that an index needs to be rebuilt.. Different >scenarios / any documents will be helpful. > >Thanks in advance. >Rajuvera -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
How do we know that an index need to be rebuilt.
Dear Friends, Can somebody tell me how do we that an index needs to be rebuilt.. Different scenarios / any documents will be helpful. Thanks in advance. Rajuvera ** This email (including any attachments) is intended for the sole use of the intended recipient/s and may contain material that is CONFIDENTIAL AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or distribution or forwarding of any or all of the contents in this message is STRICTLY PROHIBITED. If you are not the intended recipient, please contact the sender by email and delete all copies; your cooperation in this regard is appreciated. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Veeraraju_Mareddi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
