ITPub博客

首页 > 数据库 > 数据库开发技术 > best practice of rebuild your partition table local index online by using: "update indexes"

best practice of rebuild your partition table local index online by using: "update indexes"

原创 数据库开发技术 作者:netbanker 时间:2007-11-16 09:25:20 0 删除 编辑

The following operations support the UPDATE INDEXES clause:

  • ADD PARTITION | SUBPARTITION

  • COALESCE PARTITION | SUBPARTITION

  • DROP PARTITION | SUBPARTITION

  • EXCHANGE PARTITION | SUBPARTITION

  • MERGE PARTITION | SUBPARTITION

  • MOVE PARTITION | SUBPARTITION

  • SPLIT PARTITION | SUBPARTITION

  • TRUNCATE PARTITION | SUBPARTITION

SQL> alter table SAPKBW."/BIC/FZSD_C71" move partition "/BIC/FZSD_C710000000123" tablespace PSAPKBWFACT update indexes;

Table altered.

SQL> set linesize 200
SQL> set pagesize 200
SQL> col table_owner for a10
SQL> col table_name for a15
SQL> col partition_name for a25
SQL> col type for a10
SQL> col column_name for a15
SQL> col sub_type for a10
SQL> col tablespace_name for a15
SQL> select a.index_owner,a.index_name,a.partition_name, b.PARTITIONING_TYPE,
2 b.SUBPARTITIONING_TYPE as sub_type, b.LOCALITY, a.status,a.TABLESPACE_NAME,
3 c.COLUMN_NAME as COLUMN_NAME
4 from sys.dba_ind_partitions a, sys.DBA_PART_INDEXES b, sys.DBA_PART_KEY_COLUMNS c
5 where (a.INDEX_NAME=b.INDEX_name and a.INDEX_owner=b.owner) and (a.INDEX_NAME=c.name and a.index_owner=c.owner)
6 and lower(a.index_name) like lower('%/BIC/FZSD_C71~900%')
7 and a.partition_name like '%/BIC/FZSD_C710000000123'
8 /

INDEX_OWNER INDEX_NAME PARTITION_NAME PARTITI SUB_TYPE LOCALI STATUS TABLESPACE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------- ------- ---------- ------ -------- --------------- ---------------
SAPKBW /BIC/FZSD_C71~900 /BIC/FZSD_C710000000123 RANGE NONE LOCAL USABLE PSAPKBWFACT KEY_ZSD_C71P

[@more@]

if you forget, here is the make-up plan:

1.
----------- -------------------- -------------------- -------------------- ------- ---------- ------ -------- --------------- -------------

SAPKBW /BIC/FZSD_C71~900 /BIC/FZSD_C71 /BIC/FZSD_C710000000 RANGE NONE LOCAL USABLE PSAPKBWFACT KEY_ZSD_C71P
123


2.
SQL> alter table SAPKBW."/BIC/FZSD_C71" move partition "/BIC/FZSD_C710000000123" tablespace PSAPKBWFACT ;

3.
SQL> host kla partidx /BIC/FZSD_C71~900

INDEX_OWNER INDEX_NAME TABLE_NAME PARTITION_NAME PARTITI SUB_TYPE LOCALI STATUS TABLESPACE_NAME COLUMN_NAME
----------- -------------------- -------------------- -------------------- ------- ---------- ------ -------- --------------- -------------

SAPKBW /BIC/FZSD_C71~900 /BIC/FZSD_C71 /BIC/FZSD_C710000000 RANGE NONE LOCAL UNUSABLE PSAPKBWFACT KEY_ZSD_C71P
123

4.

SQL> ALTER TABLE sapkbw."/BIC/FZSD_C71"
MODIFY PARTITION "/BIC/FZSD_C710000000123" REBUILD UNUSABLE LOCAL INDEXES; 2

Table altered.


or


SQL> alter index sapkbw."/BIC/FZSD_C71~900" rebuild partition "/BIC/FZSD_C710000000166" PARALLEL (DEGREE 2) online;

Index altered.

5.

SQL> select a.index_owner,a.index_name,a.partition_name, b.PARTITIONING_TYPE,
2 b.SUBPARTITIONING_TYPE as sub_type, b.LOCALITY, a.status,a.TABLESPACE_NAME,
3 c.COLUMN_NAME as COLUMN_NAME
4 from sys.dba_ind_partitions a, sys.DBA_PART_INDEXES b, sys.DBA_PART_KEY_COLUMNS c
5 where (a.INDEX_NAME=b.INDEX_name and a.INDEX_owner=b.owner) and (a.INDEX_NAME=c.name and a.index_owner=c.owner)
6 and lower(a.index_name) like lower('%/BIC/FZSD_C71~900%')
7 and a.partition_name like '%/BIC/FZSD_C710000000123%'
8 /

INDEX_OWNER INDEX_NAME PARTITION_NAME PARTITI SUB_TYPE LOCALI STATUS TABLESPACE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------- ------- ---------- ------ -------- --------------- ---------------
SAPKBW /BIC/FZSD_C71~900 /BIC/FZSD_C710000000123 RANGE NONE LOCAL USABLE PSAPKBWFACT KEY_ZSD_C71P

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/67/viewspace-982554/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-06-12

  • 博文量
    20
  • 访问量
    90138