Re: create index (rebuild)

:) I agree. Excellent reply. Of course, thats one of the reasons why reverse clause was introduced for indexes. I have been reluctant to use them for our production system (still 8.1.6) ...
pretty soon going to 9i ... after I saw the bugs associated with reverse PK indexes in that release. Will apply this solution on upgrade.

Thanks

Anurag

"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:aftalf$j0q$1_at_lust.ihug.co.nz...
> On thr grounds that it's an interesting topic, have you considered these
> alternatives:
>
> SQL> create table t1 (a number(10), b varchar2(10));
> Table created.
>
> SQL> create index t1_a on t1(a) reverse;
> Index created.
>
> SQL> insert into t1 select rownum, 'x' from all_tables;
> 827 rows created.
>
> SQL> analyze index t1_a validate structure;
> Index analyzed.
>
> SQL> select br_rows_len, lf_rows_len, del_lf_rows_len from index_stats;
> BR_ROWS_LEN LF_ROWS_LEN DEL_LF_ROWS_LEN
> ----------- ----------- ---------------
> 9 12298 0
>
> SQL> delete from t1 where a < 400;
> 399 rows deleted.
>
> SQL> analyze index t1_a validate structure;
> Index analyzed.
>
> SQL> select br_rows_len, lf_rows_len, del_lf_rows_len from index_stats;
> BR_ROWS_LEN LF_ROWS_LEN DEL_LF_ROWS_LEN
> ----------- ----------- ---------------
> 9 12298 5883
>
> SQL> insert into t1 select rownum + 4000 , 'y' from all_tables;
> 827 rows created.
>
> SQL> analyze index t1_a validate structure;
> Index analyzed.
>
> SQL> select br_rows_len, lf_rows_len, del_lf_rows_len from index_stats;
> BR_ROWS_LEN LF_ROWS_LEN DEL_LF_ROWS_LEN
> ----------- ----------- ---------------
> 27 18812 0
>
> Alternatively, try this:
>
> SQL> create table t1 (a number(10), b varchar2(10))
> 2 partition by hash(a) partitions 4 store in (blah, test, users,
tools);
> Table created.
>
> SQL> create index t1_a on t1(a) LOCAL
> 2 store in (blah, test, users, tools);
> Index created.
>
> SQL> insert into t1 select rownum, 'x' from all_tables;
> 827 rows created.
>
> SQL> analyze index t1_a validate structure;
> Index analyzed.
>
> SQL> select br_rows_len, lf_rows_len, del_lf_rows_len from index_stats;
> BR_ROWS_LEN LF_ROWS_LEN DEL_LF_ROWS_LEN
> ----------- ----------- ---------------
> 0 3168 0
>
> SQL> delete from t1 where a < 400;
> 399 rows deleted.
>
> SQL> analyze index t1_a validate structure;
> Index analyzed.
>
> SQL> select br_rows_len, lf_rows_len, del_lf_rows_len from index_stats;
> BR_ROWS_LEN LF_ROWS_LEN DEL_LF_ROWS_LEN
> ----------- ----------- ---------------
> 0 3168 1383
>
> SQL> insert into t1 select rownum + 4000 , 'y' from all_tables;
> 827 rows created.
>
> SQL> analyze index t1_a validate structure;
> Index analyzed.
>
> SQL> select br_rows_len, lf_rows_len, del_lf_rows_len from index_stats;
> BR_ROWS_LEN LF_ROWS_LEN DEL_LF_ROWS_LEN
> ----------- ----------- ---------------
> 0 4961 0
>
>
> In other words, there are various ways to ensure that even when the data
> coming into the table doesn't slot in between earlier entries, the
> associated index entries *will*. At which point, deleted leaf entry space
is
> re-used, and you again don't need to rebuild the index.
>
> Granted, reverse indexes have their associated problems, and partitioning
> isn't a particularly cheap (in dollars!) option. And I think in any case
we
> agree on the general principle involved here... I just thought it would be
> interesting to point out that there's more than one way to skin a possum.
> (Which are rather tasty, by the way).
>
> Best regards,
> HJR
>
>
>
> "Anurag Varma" <avdbi_at_hotmail.com> wrote in message
> news:0opU8.6844$68.183894_at_news4.srv.hcvlny.cv.net...
> > 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 - 18:09:30 CDT