Databases Reference
In-Depth Information
select index_name
from user_ind_columns
where table_name = 'BILLING_FACT'
and index_name != 'BILLING_FACT_PK'
order by 1;
quit;
EOT
DT=`date +%Y%m%d.%H%M`
echo "Starting index rebuilds at $DT" >> $LOG
# Loop through each subpartition of every index and rebuild the index subpartitions.
# All indexes for table are done all at once, subpartition at a time (in the background)
for p in `cat $PARTFILE`
do
for i in `cat $IDXFILE`
do
DT=`date +%Y%m%d.%H%M`
sqlplus -s $CONNECT_STRING@$ORACLE_SID <<EOT >> $LOG &
prompt Rebuilding index $i, subpartition $p at $DT
$PROMPT alter index $i rebuild subpartition $p;
quit;
EOT
done
wait
done
DT=`date +%Y%m%d.%H%M`
echo "Completed index rebuilds at $DT" >> $LOG
Index Implications for Interval Partitioning
Interval partitioning, which is available as of Oracle 11g, is a wonderful feature that has Oracle
automatically create partitions on a table when incoming data doesn't match the partition boundaries
on a table. In early versions of Oracle, trying to insert new data would have generated an Oracle error
and the DML operation fail. With interval partitioning, Oracle now simply adds new partitions as needed
to match the incoming data.
In regards to indexes on interval-based table partitioning, similar rules apply as if you were using
other partitioning methods, such as locally partitioned indexes. On your interval-based partitioned local
indexes, if you want your new index partitions to be placed in a specific tablespace, you need to make
sure that each index points to that tablespace. As with table partitions, you can also modify the default
attributes for an index to accomplish the same task.
SQL> alter index test_i3 modify default attributes tablespace test09index_s;
