oracle分区及分区索引partition_partition index_维护(二)

最新推荐文章于 2022-04-15 18:06:07 发布

clg10051

最新推荐文章于 2022-04-15 18:06:07 发布

阅读量123

点赞数

文章标签：数据库

http://space.itpub.net/9240380/viewspace-751835

续上继续测试:

----------------------------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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-751842/