Re: create index (rebuild)

From: Anurag Varma <avdbi_at_hotmail.com>
Date: Tue, 2 Jul 2002 16:33:41 -0400
Message-ID: <6aoU8.5288$68.155101@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 - 15:33:41 CDT