Re: create index (rebuild)

I see you're using the lf_rows/del_lf_rows ratio to determine when to rebuild.

The only validity that measure has is if what appears to be a deleted leaf row on Monday can be guaranteed to be there as a deleted leaf row next Friday, or next month. That is, if you know that the inserts on the underlying table will *never* want to re-use the spaces occupied by deleted entries. There *are* such tables/indexes: ones built on monotonically incrementing sequence numbers, for example, that haven't been reversed.

But generally, Oracle *will reuse* space occupied by deleted entries, without all the palaver of a rebuild. If you just wait long enough, the index efficiency will go up all on its own.

For example:

SQL> create index blah on emp(ename);
Index created.

SQL> delete from emp where ename='SMITH'; 1 row deleted.
SQL> commit;
Commit complete.

SQL> analyze index blah validate structure; Index analyzed.

SQL> select del_lf_rows from index_stats; DEL_LF_ROWS

          1

SQL> insert into emp (empno, ename)
  2 values (6218,'STETSON');
1 row created.
SQL> commit;
Commit complete.

SQL> analyze index blah validate structure; Index analyzed.
SQL> select del_lf_rows from index_stats; DEL_LF_ROWS

          0

...in other words, a fresh insert made the deleted leaf row go away. On its own, without an expensive rebuild.

Bear in mind that rebuilds ARE expensive. They take exclusive table locks (even the online variety do). They also cause the index to lose extents it had already gone to the effort of acquiring. As new DML takes place on the table, they are likely to have to re-acquire what you caused them to lose... none of which is terribly efficient.

Rebuild rarely, therefore, and use that ratio as a guide only, in conjunction with your knowledge about the application and how (and what) it inserts into tables.

Regards
HJR "Anurag Varma" <avdbi_at_hotmail.com> wrote in message news:6aoU8.5288$68.155101_at_news4.srv.hcvlny.cv.net...
> ... and go to www.ixora.com.au and search for
> "index rebuild"
>
> The approach below might also give you an idea as
> to whether an index needs to be rebuilt. I'm still
> working on refining this .. so would appreciate
> any comments from gurus out here:
>
> set termout off pagesize 0 heading off linesize
> 240 echo off feedback off trimspool on
> spool $DATA_DIR/rebuildind.tmp1
> SELECT 'ANALYZE INDEX '||index_name||' VALIDATE
> STRUCTURE;'||chr(10)||
> 'SELECT '||chr(39)||'ALTER INDEX
> '||lower(index_name)||' REBUILD
> ONLINE;'||chr(39)||
> ' FROM index_stats '||
> 'WHERE ((100 * (br_rows_len + lf_rows_len -
> del_lf_rows_len) / btree_space) < 70);'
> FROM user_indexes
> WHERE index_type = 'NORMAL'
> ;
> spool off
>
> spool $DATA_DIR/rebuildind.tmp2
> @$DATA_DIR/rebuildind.tmp1
> spool off
>
> set termout on pagesize 60 heading on linesize 120
> echo on feedback on trimspool off
> prompt NOTE: Running the Index Rebuild Script (No
> output means that no indexes needed rebuilding)
> @$DATA_DIR/rebuildind.tmp2
>
> *****************
>
> 'WHERE ((100 * (br_rows_len + lf_rows_len -
> del_lf_rows_len) / btree_space) < 70);'
> This line filters out indexes which are wasting
> more than 30% of their space.
>
> I have modified the above script to be generic ...
> however, I run this script only for some indexes I
> know
> that need rebuilding frequently.
>
> Read up the documentation stated by Daniel also
> for a better understanding of the above commands.
>
> Anurag
>
>
> "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote
> in message
> news:3D220548.FB39C6E6_at_exesolutions.com...
> > zeb wrote:
> >
> > > Hi,
> > >
> > > After creating an index,
> > > should I to rebuild it ?
> > >
> > > More generally, when should I to rebuild an
> Index ???
> > >
> > > Thanks for your help ....
> >
> > When it needs to be rebuilt. And only when it
> needs to be rebuilt.
> >
> > To learn how to determine when that is go to:
> >
> > http://technet.oracle.com
> > http://tahiti.oracle.com
> > http://otn.oracle.com
> > http://docs.oracle.com
> >
> > Or read a good book.
> >
> > Daniel Morgan
> >
>
>
Received on Tue Jul 02 2002 - 16:13:02 CDT