对于经常有delete,insert 的表来说,其对应索引应该经常重建,以减小索引的大小,提高数据库的性能。
写了一个sql:
SELECT 'alter index '||t.index_name||' rebuild partition '||t.partition_name||';'
FROM user_ind_partitions t WHERE t.index_name='IDX2_EVT_BAG_MAIL_RELA_SEA_T'
AND t.partition_name
将拼出来的sql执行对索引经常重建报:
ORA-14257: cannot move partition other than a Range or Hash partition
发现原来此表是前几个月建的符合分区表,分区是按照range分区,子分区是hash分区。
因此这种写法是有问题的,应该直接重建子分区,重新调整上面的sql:
SELECT 'alter index '||t.index_name||' rebuild subpartition '||t.subpartition_name||';'
FROM user_ind_subpartitions t WHERE t.index_name='IDX2_EVT_BAG_MAIL_RELA_SEA_T'
AND t.partition_name
SQL> alter index IDX2_EVT_BAG_MAIL_RELA_SEA_T rebuild subpartition SYS_SUBP16612;
Index altered
SQL> alter index IDX2_EVT_BAG_MAIL_RELA_SEA_T rebuild subpartition SYS_SUBP16613;
Index altered
SQL> alter index IDX2_EVT_BAG_MAIL_RELA_SEA_T rebuild subpartition SYS_SUBP16614;
Index altered
SQL> alter index IDX2_EVT_BAG_MAIL_RELA_SEA_T rebuild subpartition SYS_SUBP16615;
Index altered
SQL> alter index IDX2_EVT_BAG_MAIL_RELA_SEA_T rebuild subpartition SYS_SUBP16616;
Index altered
SQL> alter index IDX2_EVT_BAG_MAIL_RELA_SEA_T rebuild subpartition SYS_SUBP16617;
Index altered
SQL> alter index IDX2_EVT_BAG_MAIL_RELA_SEA_T rebuild subpartition SYS_SUBP16618;
Index altered
SQL> alter index IDX2_EVT_BAG_MAIL_RELA_SEA_T rebuild subpartition SYS_SUBP16619;
Index altered
SQL> alter index IDX2_EVT_BAG_MAIL_RELA_SEA_T rebuild subpartition SYS_SUBP16620;
重建成功!
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12129601/viewspace-749785/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12129601/viewspace-749785/