oracle分区测试

最新推荐文章于 2024-08-21 23:28:12 发布

congbinlu9935

最新推荐文章于 2024-08-21 23:28:12 发布

阅读量119

点赞数

文章标签：数据库

分区表维护的常用命令：

ALTER TABLE
-- DROP -- PARTITION
-- ADD |
-- RENAME |
-- MODIFITY |
-- TRUNCATE |
-- SPILT |
-- MOVE |
-- EXCHANGE |

分区索引的常用维护命令：

ALTER INDEX
-- DROP -- PARTITION
-- REBUILD |
-- RENAME |
-- MODIFITY |
-- SPILT |
-- PARALLEL
-- UNUSABLE

相关的数据字典
USER/ALL/DBA_PART_TABLES
USER/ALL/DBA_PART_TINDEXES
USER/ALL/DBA_PART_KEY_COLUMNS
USER/ALL/DBA_TAB_PARTITIONS
USER/ALL/DBA_IND_PARTITIONS
USER/ALL/DBA_PART_COL_STATISTICS
USER/ALL/DBA_TAB_COL_STATISTICS
USER/ALL/DBA_PART_HISTOGRAMS
USER/ALL/DBA_TAB_HISTOGRAMS
USER/ALL/DBA_OBJECTS
USER/ALL/DBA_TABLES
USER/ALL/DBA_INDEXES
USER/ALL/DBA_TAB_COLUMNS

1. 创建测试使用分区表

create table test_part(id number,comm varchar(100)) partition by range(id) (partition p1 values less than (10),partition p2 values less than (20))

2. 插入测试数据

insert into test_part values(1,'aa')
insert into test_part values(2,'aa')
insert into test_part values(11,'aa')
insert into test_part values(15,'aa')

3.新增分区，存储大于20的id记录

alter table test_part add partition pmax values less than (maxvalue)

insert into test_part values(22,'aa')
insert into test_part values(25,'aa')
insert into test_part values(33,'aa')
insert into test_part values(35,'aa')
insert into test_part values(40,'aa')

4.分裂pmax分区，拆成多个子分区

alter table test_part split partition pmax at(40) into (partition p4,partition pmax)

5. exchange交换分区

create table testnormal as select * from test_part

alter table test_part exchange partition p2 with table testnormal

select * from testnormal

select * from test_part partition(p2)

6.创建分区索引

create index test_part_idx on test_part(id) local

SQL> select owner,table_name,index_name,partition_count from dba_part_indexes where table_name='TEST_PART';

OWNER TABLE_NAME INDEX_NAME PARTITION_COUNT
------------------------------ ------------------------------ ------------------------------ ---------------
MONITOR TEST_PART TEST_PART_IDX 5

SQL> select index_owner,index_name,partition_name,status,tablespace_name from dba_ind_partitions where index_name='TEST_PART_IDX';

INDEX_OWNER                    INDEX_NAME                     PARTITION_NAME                 STATUS   TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ -------- ------------------------------
MONITOR                        TEST_PART_IDX                  P1                             USABLE   INTERFACE
MONITOR                        TEST_PART_IDX                  P2                             USABLE   INTERFACE
MONITOR                        TEST_PART_IDX                  P3                             USABLE   INTERFACE
MONITOR                        TEST_PART_IDX                  P4                             USABLE   INTERFACE
MONITOR                        TEST_PART_IDX                  PMAX                           USABLE   INTERFACE

7. truncate partition，分区索引状态正常

SQL> alter table test_part truncate partition p2;

Table truncated.

SQL> select index_owner,index_name,partition_name,status,tablespace_name from dba_ind_partitions where index_name='TEST_PART_IDX';

8. drop partition，分区索引自动删除

SQL> alter table test_part drop partition p3;

Table altered.

SQL> select index_owner,index_name,partition_name,status,tablespace_name from dba_ind_partitions where index_name='TEST_PART_IDX';

INDEX_OWNER                    INDEX_NAME                     PARTITION_NAME                 STATUS   TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ -------- ------------------------------
MONITOR                        TEST_PART_IDX                  P1                             USABLE   INTERFACE
MONITOR                        TEST_PART_IDX                  P2                             USABLE   INTERFACE
MONITOR                        TEST_PART_IDX                  P4                             USABLE   INTERFACE
MONITOR                        TEST_PART_IDX                  PMAX                           USABLE   INTERFACE

9.重命名分区

SQL> alter table test_part rename partition pmax to pnew;

Table altered.

SQL> select table_owner,table_name,partition_name from dba_tab_partitions where table_name='TEST_PART';

TABLE_OWNER                    TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
MONITOR                        TEST_PART                      P1
MONITOR                        TEST_PART                      P2
MONITOR                        TEST_PART                      PNEW
MONITOR                        TEST_PART                      P4

10.维护分区会导致全局索引不可用

创建一个global的索引
SQL> create index test_part_g on test_part(id,comm);

Index created.

SQL> select table_owner,table_name,partition_name from dba_tab_partitions where table_name='TEST_PART';

SQL> select index_owner,index_name,partition_name,status from dba_ind_partitions where index_name='TEST_PART_IDX';

INDEX_OWNER                    INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ ------------------------------ --------
MONITOR                        TEST_PART_IDX                  P1                             USABLE
MONITOR                        TEST_PART_IDX                  P2                             USABLE
MONITOR                        TEST_PART_IDX                  P4                             USABLE
MONITOR                        TEST_PART_IDX                  PMAX                           USABLE

SQL> select owner,index_name,table_owner,table_name,status from dba_indexes where index_name='TEST_PART_G';

OWNER INDEX_NAME TABLE_OWNER TABLE_NAME STATUS
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------
MONITOR TEST_PART_G MONITOR TEST_PART VALID

对分区表进行维护，truncate分区
SQL> alter table test_part truncate partition p1;

Table truncated.

分区索引状态正常
SQL> select index_owner,index_name,partition_name,status from dba_ind_partitions where index_name='TEST_PART_IDX';

全局索引变为不可用了
SQL> select owner,index_name,table_owner,table_name,status from dba_indexes where index_name='TEST_PART_G';

11.合并分区

select table_owner,table_name,partition_name from dba_tab_partitions where table_name='TEST_PART';

TABLE_OWNER                    TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
MONITOR                        TEST_PART                      P3
MONITOR                        TEST_PART                      P4
MONITOR                        TEST_PART                      P5
MONITOR                        TEST_PART                      PMAX
MONITOR                        TEST_PART                      PNEW
SQL> select owner,index_name,table_owner,table_name,status from dba_indexes where index_name='TEST_PART_G';

合并分区p3，p4到分区p1中，同时更新global索引

SQL> alter table test_part merge partitions p3,p4 into partition p1 update global indexes;

Table altered.

SQL> select table_owner,table_name,partition_name from dba_tab_partitions where table_name='TEST_PART';

TABLE_OWNER                    TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
MONITOR                        TEST_PART                      P1
MONITOR                        TEST_PART                      P5
MONITOR                        TEST_PART                      PMAX
MONITOR                        TEST_PART                      PNEW

SQL> select owner,index_name,table_owner,table_name,status from dba_indexes where index_name='TEST_PART_G';

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

转载于:http://blog.itpub.net/665930/viewspace-694327/

congbinlu9935

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle分区测试

分区表维护的常用命令：ALTER TABLE-- DROP -- PARTITION-- ADD |-- RENAME |-- MODIFITY |-- TRUNCATE |-- SPILT |-- MOVE |-- EXC...
复制链接

扫一扫