Databases Reference
In-Depth Information
One significant drawback of rebuilding indexes using this command is that if you have many
indexes on your table, the index partitions are built serially—one index partition at a time. This can slow
the rebuild process and can become prohibitive if this operation occurs as part of a regular process. One
way to alleviate the serial limitation of the
ALTER INDEX...REBUILD UNUSABLE LOCAL INDEXES
command is
to parallelize the rebuild of each affected partition.
There are several ways to accomplish the parallelization of the rebuild operations. For example, this
may be especially useful if you have a subpartitioned table with locally-subpartitioned indexes. One
example, shown next, is a korn shell script, which submits many index partition rebuild operations in
the background. The following specific shell script is an example of rebuilding many subpartitioned
index partitions for many indexes:
#!/bin/ksh
typeset -Z4 YEAR=${1:-2011}
typeset -Z2 MM=${2:-08}
YY=`echo $YEAR|cut -c3-4`
LOGDIR=$HOME/logs
DT=`date +%Y%m%d.%H%M`
LOG="$LOGDIR/`echo \`basename $0\`|cut -d'.' -f1`_${YEAR}${MM}_${DT}"
IDXFILE=/tmp/bf_i.$$.out
PARTFILE=/tmp/bf_p.$$.out
# Get list of subpartitions for an index
sqlplus -s $CONNECT_STRING@$ORACLE_SID <<EOT > /dev/null
set echo off
set pages 0
set head off
set feedback off
spool $PARTFILE
select subpartition_name from user_ind_subpartitions
where index_name = 'BILLING_FACT_PK'
and subpartition_name like '%${YY}_${MM}%'
order by 1;
quit;
EOT
# Get list of indexes for a table
sqlplus -s $CONNECT_STRING@$ORACLE_SID <<EOT > /dev/null
set echo off
set pages 0
set head off
set feedback off
spool $IDXFILE
