| 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: Rebuild Indexes
Hi,
Are the deletes followed by inserts? If so the rebuild may not be necessary
that often, if at all. We have a system running under Oracle 7.2.3 where
there are a set of tables that have a very high % of deletes and then
inserts, but we only need to rebuild the indexes on these tables at most
every 12 months and this is an old old version of the database. I am sure
that later versions of Oracle do an even better job at reusing space
allocated to the index.
Regards
Pete
-----Original Message-----
From: vidya kalyanaraman [mailto:vidya.kalyanaraman_at_oracle.com]
Sent: 13 February 2004 10:57
To: oracle-l_at_freelists.org
Subject: Re: Rebuild Indexes
Hi Jonathan
Thanks for the explanation. Here the customer wants to reclaim the
extra space. According to them they had massive deletes and they feel
that might have causeed the unbalanced structure and wastage of space.
Customer uses mainly B*Tree indexes.
what is your suggestion in this situation? Thanks a lot for answering
this question.
Best Regards
Vidya
Jonathan Lewis wrote:
>Is this the script that comes off Metalink ?
>
>There are a number of flaws that need to be
>highlighted - most significantly the warnings
>that point out
> the impact this will have on a production system,
> the idiocy of changing a b-tree to a bitmap on an OLTP system,
> the absence of any references to partitioned indexes,
> the possibility that the level is exactly what is ought to be for very
>large indexes,
> the importance of timing when considering deleted rows
>
>Note:
> The VALIDATE option effectively locks the underlying table.
> You can include the ONLINE option, but then the INDEX_STATS
> structure is not populated.
>
> The VALIDATE command takes a long time on a large index,
> and will do a lot of I/O.
>
> The suggestion that an index is a candidate for being
> turned into a bitmap in an OLTP system is remarkably
> stupid unless (a) the table is in a read-only tablespace,
> or (b) the table is going to have virtually no single row
> inserts or deletes, or updates to the indexed column.
> Bitmap indexes introduce massive concurrency and
> deadlock problems and tend to grow catastrophically
> if you are doing lightweight DML on the table.
>
> If you analyze a partitioned index, the only statistics you
> capture in INDEX_STATS are the statistics for the last
> partitioned (or sub-partition) analyzed.
>
> Although the script chooses height > 5 as a benchmark
> for rebuilds, and you have to have quite a large table for
> the 'correct' height for an index to be 5, an arbitrary
> choice of height is a bad idea. Think about this:
> if the index did not need to be rebuilt, then the mistake
> is a really BIG one, because it will start, and end, as
> a really BIG index. (In passing, for an 8K block size
> and an 80 byte key, you can get an index to height 6
> on just over 120,000,000 rows).
>
> If you rebuild regularly on 20% deleted rows, you could
> be rebuilding at just the right time for (a) wasting your
> effort, and (b) introducing a performance problem.
> For random data patterns, Oracle tends to re-use space
> from deleted entries quite effectively. You have to know
> your application to be certain that this 'magic' 20% really
> is space that won't be re-used, and that it really will
> improve performance. (There's an article of mine on
> www.dbazine.com that gives an example of the sort of
> thing that can make this a counter-productive operation).
> Another little drawback to the 20% rule is that it doesn't
> tell you whether you need to use the rebuild command,
> or the coalesce command, for the most effective way
> of cleaning things up.
>
>I've never got round to writing a script for it, but one
>starting point is simply: is the index much larger than
>it should be. To check this:
> For each B-TREE index
> check columns used in user_ind_columns
> check average length of columns in user_tab_columns
>
> sum the average column lengths,
> add the number of columns plus 10
>
> multiply by the number of rows in the index
>
> Multiply by 1/0.69, on the basis that the steady
> state for a random insertion b-tree index will be
> about 69% according to published papers on
> fringe analysis.
>
> Your b-tree index should be about this size
>
>This is very much a ball-park figure, and does not cater
>properly for globally partitioned indexes, cluster indexes,
>space taken by branch blocks (typically less than 2% on
>an 8K block size), compression, multi-column indexes where
>the distribution of nulls across the columns is very variable.
>
>For indexes which are much larger than this, you may want
>to spend a little time investigating how the index is used,
>and why it might be larger than expected, and whether a
>rebuild will (a) reclaim useful space or (b) improve performance.
>
>
>Regards
>
>Jonathan Lewis
>http://www.jlcomp.demon.co.uk
>
> The educated person is not the person
> who can answer the questions, but the
> person who can question the answers -- T. Schick Jr
>
>
>Next public appearances:
> March 2004 Hotsos Symposium - The Burden of Proof
> March 2004 Charlotte NC OUG - CBO Tutorial
> April 2004 Iceland
>
>
>One-day tutorials:
>http://www.jlcomp.demon.co.uk/tutorial.html
>
>
>Three-day seminar:
>see http://www.jlcomp.demon.co.uk/seminar.html
>____UK___February
>____UK___June
>
>
>The Co-operative Oracle Users' FAQ
>http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>----- Original Message -----
>From: "Senthil Kumar" <senthilkumard_at_summitworks.com>
>To: <oracle-l_at_freelists.org>
>Sent: Friday, February 13, 2004 8:59 AM
>Subject: RE: Rebuild Indexes
>
>
>Hi
>
>
>You can use this script.
>
>HTH
>Senthil Kumar.
>
>
>REM
>REM rebuild_indx.sql
>REM =============================================================
>
>prompt
>ACCEPT spoolfile CHAR prompt 'Output-file : ';
>ACCEPT schema CHAR prompt 'Schema name (% allowed) : ';
>prompt
>prompt
>prompt Rebuild the index when :
>prompt - deleted entries represent 20% or more of the current entries
>prompt - the index depth is more then 4 levels.
>prompt Possible candidate for bitmap index :
>prompt - when distinctiveness is more than 99%
>prompt
>spool &spoolfile
>
>set serveroutput on
>set verify off
>declare
> c_name INTEGER;
> ignore INTEGER;
> height index_stats.height%TYPE := 0;
> lf_rows index_stats.lf_rows%TYPE := 0;
> del_lf_rows index_stats.del_lf_rows%TYPE := 0;
> distinct_keys index_stats.distinct_keys%TYPE := 0;
> cursor c_indx is
> select owner, table_name, index_name
> from dba_indexes
> where owner like upper('&schema')
> and owner not in ('SYS','SYSTEM');
>begin
> dbms_output.enable (1000000);
> dbms_output.put_line ('Owner Index Name
>% Deleted Entries Blevel Distinctiveness');
> dbms_output.put_line
>('--------------- --------------------------------------- -----------------
>------ ---------------');
>
> c_name := DBMS_SQL.OPEN_CURSOR;
> for r_indx in c_indx loop
> DBMS_SQL.PARSE(c_name,'analyze index ' || r_indx.owner || '.' ||
> r_indx.index_name || ' validate
>structure',DBMS_SQL.NATIVE);
> ignore := DBMS_SQL.EXECUTE(c_name);
>
> select HEIGHT, decode (LF_ROWS,0,1,LF_ROWS), DEL_LF_ROWS,
> decode (DISTINCT_KEYS,0,1,DISTINCT_KEYS)
> into height, lf_rows, del_lf_rows, distinct_keys
> from index_stats;
>--
>-- Index is considered as candidate for rebuild when :
>-- - when deleted entries represent 20% or more of the current entries
>-- - when the index depth is more then 4 levels.(height starts counting
>from 1 so > 5)
>-- Index is (possible) candidate for a bitmap index when :
>-- - distinctiveness is more than 99%
>--
> if ( height > 5 ) OR ( (del_lf_rows/lf_rows) > 0.2 ) then
> dbms_output.put_line (rpad(r_indx.owner,16,' ') ||
>rpad(r_indx.index_name,40,' ') ||
> lpad(round((del_lf_rows/lf_rows)*100,3),17,' ')
||
> lpad(height-1,7,' ') ||
>lpad(round((lf_rows-distinct_keys)*100/lf_rows,3),16,' '));
> end if;
>
> end loop;
> DBMS_SQL.CLOSE_CURSOR(c_name);
>end;
>/
>
>spool off
>set verify on
>
>
>----------------------------------------------------------------
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>----------------------------------------------------------------
>To unsubscribe send email to: oracle-l-request_at_freelists.org
>put 'unsubscribe' in the subject line.
>--
>Archives are at http://www.freelists.org/archives/oracle-l/
>FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>-----------------------------------------------------------------
>
>
>----------------------------------------------------------------
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>----------------------------------------------------------------
>To unsubscribe send email to: oracle-l-request_at_freelists.org
>put 'unsubscribe' in the subject line.
>--
>Archives are at http://www.freelists.org/archives/oracle-l/
>FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>-----------------------------------------------------------------
>
>
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ________________________________________________________________________ This email has been scanned for all viruses by the MessageLabs Email Security System. For more information on a proactive email security service working around the clock, around the globe, visit http://www.messagelabs.com ________________________________________________________________________ ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Fri Feb 13 2004 - 05:35:00 CST
 |
 |