第一种方法：
一、创建分区表
SQL> CREATE TABLE ADDRESS(
2 ID VARCHAR2(20) NOT NULL,
3 AREACODE VARCHAR2(8)
4 )
5 PARTITION BY LIST(AREACODE)
6 (
7 PARTITION P1 VALUES ('333'),
8 PARTITION P_OTHER VALUES(DEFAULT)
9 );
Table created.
二、创建索引
SQL> create index idx_address_id on address(id)
2 local(
3 partition p1,
4 partition p_other
5 )
6 ;
Index created.
三插入测试数据
SQL> insert into address values ('1','333');
1 row created.
SQL> insert into address values ('2','444');
1 row created.
SQL> insert into address values ('3','555');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from address;
ID AREACODE
---------------------------------------- ----------------
1 333
2 444
3 555
SQL> select * from address partition (p1);
ID AREACODE
---------------------------------------- ----------------
1 333
SQL> select * from address partition (p_other);
ID AREACODE
---------------------------------------- ----------------
2 444
3 555
四、删除默认分区
SQL> alter table address drop partition p_other;
Table altered.
SQL> select * from address;
ID AREACODE
---------------------------------------- ----------------
1 333
SQL> select * from address partition (p1);
ID AREACODE
---------------------------------------- ----------------
1 333
SQL> select * from address partition (p_other);
select * from address partition (p_other)
*
ERROR at line 1:
ORA-02149: Specified partition does not exist
五，添加新分区
SQL> alter table address add partition p_444 values ('444') ;
SQL> insert into address values ('4','444');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from address partition (p_444);
ID AREACODE
---------------------------------------- ----------------
4 444
Table altered.
六、添加默认分区
SQL> alter table address add partition p_other values(default);
Table altered.
SQL> select T.TABLE_NAME,T.PARTITION_NAME From user_tab_partitions t where t.table_name='ADDRESS';
TABLE_NAME PARTITION_NAME
------------------------------------------------------------ ------------------------------------------------------------
ADDRESS P1
ADDRESS P_444
ADDRESS P_OTHER
七、对于局部索引，ORACLE会自动添加一个局部分区索引
SQL> SELECT D.index_name,D.table_name,D.partitioning_type,D.partition_count FROM USER_PART_INDEXES D WHERE D.index_name='IDX_ADDRESS_ID';
INDEX_NAME TABLE_NAME PARTITIONING_TYPE PARTITION_COUNT
------------------------------------------------------------ ------------------------------------------------------------ ------------------ ---------------
IDX_ADDRESS_ID ADDRESS LIST
SQL> SELECT D.INDEX_NAME,D.PARTITION_NAME FROM USER_IND_PARTITIONS D WHERE D.INDEX_NAME='IDX_ADDRESS_ID';
INDEX_NAME PARTITION_NAME
------------------------------------------------------------ ------------------------------------------------------------
IDX_ADDRESS_ID P1
IDX_ADDRESS_ID P_444
IDX_ADDRESS_ID P_OTHER
第二种访求：split分区拆分
一、拆分（注意values，如果是RANGE类型，用AT，如果是LIST类型，用VALUES）
SQL> alter table address split partition p_other values('666') into (partition p_666,partition p_other);
Table altered.
SQL> select T.TABLE_NAME,T.PARTITION_NAME From user_tab_partitions t where t.table_name='ADDRESS';
TABLE_NAME PARTITION_NAME
------------------------------------------------------------ ------------------------------------------------------------
ADDRESS P1
ADDRESS P_444
ADDRESS P_666
ADDRESS P_OTHER
SQL> SELECT D.INDEX_NAME,D.PARTITION_NAME FROM USER_IND_PARTITIONS D WHERE D.INDEX_NAME='IDX_ADDRESS_ID';
INDEX_NAME PARTITION_NAME
------------------------------------------------------------ ------------------------------------------------------------
IDX_ADDRESS_ID P1
IDX_ADDRESS_ID P_444
IDX_ADDRESS_ID P_666
IDX_ADDRESS_ID P_OTHER
ORACLE会自动维护局部分区索引，但是对于全局索引，则会失效，需要REBUILD.
阅读(1564) | 评论(0) | 转发(0) |
