oracle分割槽及分割槽索引partition_partition index_維護(二)

wisdomone1發表於2012-12-29
Oracle索引Index
 
 
續上繼續測試:
 
----------------------------truncate分割槽
SQL> select table_name,composite,partition_name from user_tab_partitions where
able_name='T_TEST_PARTITION_1';
TABLE_NAME                     COM PARTITION_NAME
------------------------------ --- ------------------------------
T_TEST_PARTITION_1             NO  P1
T_TEST_PARTITION_1             NO  P2
T_TEST_PARTITION_1             NO  P3
T_TEST_PARTITION_1             NO  P4
T_TEST_PARTITION_1             NO  P5
SQL> desc t_test_partition_1;
 名稱                                      是否為空? 型別
 ----------------------------------------- -------- --------------------------
 ID                                        NOT NULL NUMBER(38)
 REQTIME                                            TIMESTAMP(6)
 CALL_IP                                            VARCHAR2(200)
SQL> insert into t_test_partition_1 values(1,to_timestamp('2013-01-07 00:00:00
00000','yyyy-mm-dd hh24:mi:ss.ff')
  2  ,'1.1.1.1');
已建立 1 行。

---不符合分割槽表的資料不能插入分割槽表中
SQL> insert into t_test_partition_1 values(1,to_timestamp('2013-02-07 00:00:00
00000','yyyy-mm-dd hh24:mi:ss.ff'),'2,2,3,3');
insert into t_test_partition_1 values(1,to_timestamp('2013-02-07 00:00:00.0000
','yyyy-mm-dd hh24:mi:ss.ff'),'2,2,3,3')
            *
第 1 行出現錯誤:
ORA-14400: 插入的分割槽關鍵字未對映到任何分割槽
---違反分割槽表的主鍵約束
SQL> insert into t_test_partition_1 values(1,to_timestamp('2013-01-10 00:00:00
00000','yyyy-mm-dd hh24:mi:ss.ff'),'2,2,3,3');
insert into t_test_partition_1 values(1,to_timestamp('2013-01-10 00:00:00.0000
','yyyy-mm-dd hh24:mi:ss.ff'),'2,2,3,3')
*
第 1 行出現錯誤:
ORA-00001: 違反唯一約束條件 (SCOTT.PK_T_TEST_PARTITION_1)

SQL> insert into t_test_partition_1 values(2,to_timestamp('2013-01-10 00:00:00
00000','yyyy-mm-dd hh24:mi:ss.ff'),'2,2,3,3');
已建立 1 行。
SQL> insert into t_test_partition_1 values(3,to_timestamp('2013-01-16 00:00:00
00000','yyyy-mm-dd hh24:mi:ss.ff'),'2,2,3,3');
已建立 1 行。

SQL> insert into t_test_partition_1 values(4,to_timestamp('2013-01-29 00:00:00
00000','yyyy-mm-dd hh24:mi:ss.ff'),'2,2,3,3');
已建立 1 行。
SQL> commit;
提交完成。
SQL> select table_name,composite,partition_name,num_rows from user_tab_partiti
s where table_name='T_TEST_PARTITION_1';
TABLE_NAME                     COM PARTITION_NAME                   NUM_ROWS
------------------------------ --- ------------------------------ ----------
T_TEST_PARTITION_1             NO  P1                                      0
T_TEST_PARTITION_1             NO  P2                                      0
T_TEST_PARTITION_1             NO  P3                                      0
T_TEST_PARTITION_1             NO  P4                                      0
T_TEST_PARTITION_1             NO  P5                                      0

SQL> execute dbms_stats.gather_table_stats('scott','t_test_partition_1',cascad
>true);
PL/SQL 過程已成功完成。

----分析表後查詢分割槽表各個分表的資料分佈
SQL> select table_name,composite,partition_name,num_rows from user_tab_partiti
s where table_name='T_TEST_PARTITION_1';
TABLE_NAME                     COM PARTITION_NAME                   NUM_ROWS
------------------------------ --- ------------------------------ ----------
T_TEST_PARTITION_1             NO  P1                                      0
T_TEST_PARTITION_1             NO  P2                                      2
T_TEST_PARTITION_1             NO  P3                                      1
T_TEST_PARTITION_1             NO  P4                                      0
T_TEST_PARTITION_1             NO  P5                                      1
---------上述準備工作就緒,開始截斷分割槽p5
SQL> alter table t_test_partition_1 truncate partition p5;
表被截斷。
SQL> select table_name,composite,partition_name,num_rows from user_tab_partition
s where table_name='T_TEST_PARTITION_1';
TABLE_NAME                     COM PARTITION_NAME                   NUM_ROWS
------------------------------ --- ------------------------------ ----------
T_TEST_PARTITION_1             NO  P1                                      0
T_TEST_PARTITION_1             NO  P2                                      2
T_TEST_PARTITION_1             NO  P3                                      1
T_TEST_PARTITION_1             NO  P4                                      0
T_TEST_PARTITION_1             NO  P5                                      1

---分析分割槽表報錯,分割槽索引不可用
SQL> execute dbms_stats.gather_table_stats('scott','t_test_partition_1',cascade=
>true);
BEGIN dbms_stats.gather_table_stats('scott','t_test_partition_1',cascade=>true);
 END;
*
第 1 行出現錯誤:
ORA-20000: index "SCOTT"."PK_T_TEST_PARTITION_1"  or partition of such index is
in unusable state
ORA-06512: 在 "SYS.DBMS_STATS", line 20337
ORA-06512: 在 "SYS.DBMS_STATS", line 20360
ORA-06512: 在 line 1

------查詢分割槽表相關索引的狀態
SQL> select index_name,status from user_indexes where index_name='PK_T_TEST_PART
ITION_1';
INDEX_NAME                     STATUS
------------------------------ --------
PK_T_TEST_PARTITION_1          UNUSABLE

SQL> select index_name,status from user_indexes where index_name='IDX_T_TEST_PAR
TITION_1_ID';
INDEX_NAME                     STATUS
------------------------------ --------
IDX_T_TEST_PARTITION_1_ID      N/A
---重建索引
SQL> alter index pk_t_test_partition_1 rebuild;
索引已更改。
SQL> select index_name,status from user_indexes where index_name='IDX_T_TEST_PAR
TITION_1_ID';
INDEX_NAME                     STATUS
------------------------------ --------
IDX_T_TEST_PARTITION_1_ID      N/A

SQL> select index_name,status from user_indexes where index_name='PK_T_TEST_PART
ITION_1';
INDEX_NAME                     STATUS
------------------------------ --------
PK_T_TEST_PARTITION_1          VALID
SQL>

-------再次分析分割槽表成功
SQL> execute dbms_stats.gather_table_stats('scott','t_test_partition_1',cascade=
>true);
PL/SQL 過程已成功完成。
小結:截斷分割槽會使全域性索引處於不可用狀態,必須要重建
     本地分割槽索引仍為可用
 
--------------------split拆分分割槽
---
AT Clause The AT clause applies only to range partitions. Specify the new noninclusive upper bound
for the first of the two new partitions. The value list must compare less than the original partition
bound for current_partition and greater than the partition bound for the next lowest partition (if there is one).

----如下報錯是因為多了一個括號
alter table t_test_partition_1 split partition p1 at (to_timestamp('2013-01-03 0
0:00:00.000000','yyyy-mm-dd hh24:mi:ss.ff'))) into (partition p1_sub1,partition
p1_sub2)
                                            *
第 1 行出現錯誤:
ORA-14126: 只有 可遵循結果分割槽的說明

  1* alter table t_test_partition_1 split partition p1 at (to_timestamp('2013-01
-03 00:00:00.000000','yyyy-mm-dd hh24:mi:ss.ff'))) into (partition p1_sub1,parti
tion p1_sub2)
SQL> alter table t_test_partition_1 split partition p1 at (to_timestamp('2013-01
-03 00:00:00.000000','yyyy-mm-dd hh24:mi:ss.ff')) into (partition p1_sub1,partit
ion p1_sub2)
  2  /
表已更改。

----查詢原p1分割槽變成兩個分割槽,各為p1_sub1和p1_sub2
SQL> select table_name,composite,partition_name,num_rows from user_tab_partition
s  where table_name='T_TEST_PARTITION_1'
  2  /
TABLE_NAME                     COM PARTITION_NAME                   NUM_ROWS
------------------------------ --- ------------------------------ ----------
T_TEST_PARTITION_1             NO  P1_SUB1
T_TEST_PARTITION_1             NO  P1_SUB2
T_TEST_PARTITION_1             NO  P2                                      2
T_TEST_PARTITION_1             NO  P3                                      1
T_TEST_PARTITION_1             NO  P4                                      0
T_TEST_PARTITION_1             NO  P5                                      0
已選擇6行。

---查詢在拆分前如分割槽記錄為空,則拆分後其分割槽索引狀態為可用
SQL> select index_name,partition_name,status from user_ind_partitions where inde
x_name='IDX_T_TEST_PARTITION_1_ID';
INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_T_TEST_PARTITION_1_ID      P1_SUB1                        USABLE
IDX_T_TEST_PARTITION_1_ID      P1_SUB2                        USABLE
IDX_T_TEST_PARTITION_1_ID      P2_IDX2                        USABLE
IDX_T_TEST_PARTITION_1_ID      P3_IDX3                        USABLE
IDX_T_TEST_PARTITION_1_ID      P4_IDX4                        USABLE
IDX_T_TEST_PARTITION_1_ID      P5_IDX5                        USABLE
已選擇6行。
小結:拆分分割槽索引會使用全域性索引失效

-------重新命名分割槽
SQL> alter table t_test_partition_1 rename partition p1_sub1 to p1_sub1_newname;

表已更改。
SQL> select table_name,composite,partition_name,num_rows from user_tab_partition
s  where table_name='T_TEST_PARTITION_1';
TABLE_NAME                     COM PARTITION_NAME                   NUM_ROWS
------------------------------ --- ------------------------------ ----------
T_TEST_PARTITION_1             NO  P1_SUB1_NEWNAME                         0
T_TEST_PARTITION_1             NO  P1_SUB2                                 0
T_TEST_PARTITION_1             NO  P2                                      0
T_TEST_PARTITION_1             NO  P3                                      1
T_TEST_PARTITION_1             NO  P4                                      0
T_TEST_PARTITION_1             NO  P5                                      0
已選擇6行。

---變更分割槽表中分割槽某些資訊,下述為變更分割槽的預設表空間
SQL> alter table t_test_partition_1 move partition p1_sub1_newname tablespace tb
s_1;
表已更改。
SQL> select table_name,composite,partition_name,num_rows,tablespace_name from us
er_tab_partitions  where table_name='T_TEST_PARTITION_1';
TABLE_NAME                     COM PARTITION_NAME                   NUM_ROWS
------------------------------ --- ------------------------------ ----------
TABLESPACE_NAME
------------------------------
T_TEST_PARTITION_1             NO  P1_SUB1_NEWNAME                         0
TBS_1
T_TEST_PARTITION_1             NO  P1_SUB2                                 0
USERS
T_TEST_PARTITION_1             NO  P2                                      0
USERS

TABLE_NAME                     COM PARTITION_NAME                   NUM_ROWS
------------------------------ --- ------------------------------ ----------
TABLESPACE_NAME
------------------------------
T_TEST_PARTITION_1             NO  P3                                      1
USERS
T_TEST_PARTITION_1             NO  P4                                      0
USERS
T_TEST_PARTITION_1             NO  P5                                      0
USERS

已選擇6行。
SQL>
 

---合併分割槽
SQL> alter table t_test_partition_1 merge partitions p1_sub1_newname,p1_sub2 int
o partition p1_old
  2  ;
表已更改。
SQL> select table_name,composite,partition_name from user_tab_partitions  where
table_name='T_TEST_PARTITION_1';
TABLE_NAME                     COM PARTITION_NAME
------------------------------ --- ------------------------------
T_TEST_PARTITION_1             NO  P1_OLD
T_TEST_PARTITION_1             NO  P2
T_TEST_PARTITION_1             NO  P3
T_TEST_PARTITION_1             NO  P4
T_TEST_PARTITION_1             NO  P5
-----合併分割槽本地索引依舊有效,全域性索引失效
SQL> select index_name,partition_name,status from user_ind_partitions where inde
x_name='IDX_T_TEST_PARTITION_1_ID';
INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_T_TEST_PARTITION_1_ID      P1_OLD                         USABLE
IDX_T_TEST_PARTITION_1_ID      P2_IDX2                        USABLE
IDX_T_TEST_PARTITION_1_ID      P3_IDX3                        USABLE
IDX_T_TEST_PARTITION_1_ID      P4_IDX4                        USABLE
IDX_T_TEST_PARTITION_1_ID      P5_IDX5                        USABLE
 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-751842/，如需轉載，請註明出處，否則將追究法律責任。

相關文章