Re: create index (rebuild)

Perfectly fine statements Howard. I would definitely advise anyone following what
I'm showing, only after they know their application well and after understanding
exactly what the script is doing.

The example below was for an application which uses indexes on sequenced columns (i.e. my actual
script has a clause "WHERE index_name IN (.... list of indexes built on sequenced columns ...)"
The deleted keys in these indexes is never/rarely ever re-used in our application, thus it was prudent for me to rebuild these indexes. However, I take the statement back which gives an idea, that the script below
is "generic". I fully agree on Howard's warnings ... indexes usually do not need rebuilding ... even in our
application I have identified only 3-4 indexes out of a thousand which needed frequent rebuilding
due to the **nature** of insert/delete activity on them ... :)

To give you an idea as to what I mean by indexes on sequence requiring rebuilding:
avarma> create table t1 (a number(10), b varchar2(10)); Table created.

avarma> create index t1_a on t1(a);
Index created.

avarma> insert into t1 select rownum, 'x' from all_tables; 2988 rows created.

avarma> analyze index t1_a validate structure; Index analyzed.

avarma> select br_rows_len, lf_rows_len, del_lf_rows_len from index_stats; BR_ROWS_LEN LF_ROWS_LEN DEL_LF_ROWS_LEN
----------- ----------- ---------------

         55 44692 0

avarma> delete from t1 where a < 400;
399 rows deleted.

avarma> analyze index t1_a validate structure; Index analyzed.

avarma> select br_rows_len, lf_rows_len, del_lf_rows_len from index_stats; BR_ROWS_LEN LF_ROWS_LEN DEL_LF_ROWS_LEN
----------- ----------- ---------------

         55 44692 5883

avarma> insert into t1 select rownum + 4000 , 'y' from all_tables; 2988 rows created.

avarma> analyze index t1_a validate structure; Index analyzed.

avarma> select br_rows_len, lf_rows_len, del_lf_rows_len from index_stats; BR_ROWS_LEN LF_ROWS_LEN DEL_LF_ROWS_LEN
----------- ----------- ---------------

        121 89483 5883

Anurag

"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:aft533$d8b$1_at_lust.ihug.co.nz...
> 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:56:47 CDT