Re: create index (rebuild)

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 - 17:48:10 CDT