索引重建实用程序

bysysnet

一次一个删除和重建数据库中的所有索引.
重建索引可以汇总磁盘,提高响应速度.必须在系统中无用户时运行.可以做成自动执行.
示例:
# idexextr
# This script creates 1 script whith 3 types of statements.
#        1. to drop all indexes on a database
#        2. to create all indexes on a database
#        3. audit trail statements
# The general idea is to make a dbaccess script to drop and
# re-create indexes one at a time. Simultaneously,a database.adt # file is  populated so that somebody monitoring can actually tell
# how far the script has progressed in execution.
# The database must be provided as an argument
usage="Usage : $0 DATABASE"
if [ $# -le 0 ]
          then echo
          $usage
          exit 1
fi
TEMPFILE=junk.$$
# echo working...
dbschema -d $1 >; $TEMPFILE
if [ $? != 0]
then
       echo Error occurred in schema extracion of $1
       exit 1
fi
# echo Schema extracted...
cat $TEMPFILE | sed "/{ /d" | sed "/} /d" \
                        | sed -f /usr/ron/bin/idxextr.f1 >; $1.cre
cat $1.cre | \
                 sed 's/\(.*}\)\(.*)\( on .*\)/\1 !echo \2 `date`>;>;  $1.adt;/' >; $1.msg
sed "1,4d" $TEMPFILE | sed -n "/create.* index/p" | \
                  sed "s/create.* index/drop index/" | sed "s/ on.*/;/" | \
                  sed "s/\ (.* index \)\(.*\);/{\2|} &/" >; $1.drp
cat $1.cre $1.drp $1.msg | sort -r | sed "s/.*\!echo/!echo/" >; $1.sql
#echo Script in $1.isql
rm $TEMPFILE $1.cre $1.drp $1.msg $i.adt
-----------------------------------------------------------------------------------
# idxextr.f1 : sed command file used by idxextr.
# Last 3 lines to make all 'create index' statement into a single line
#       (Ver. 6 spreads it across 2 lines if the stmt is long)
# Tabs Note : only the last line contains exactly 1 tab before
# the "}"
1,4d
/^create table/,/^ *);/d
/grant/d
/revoke/d
/update/,/;/d
/^ *$/d
s/\(.* index \)\(*.\) on/{\2} &/
/,${N
s/\n//
     }