Oracle 分区表使用和查询(转载)介绍
参考
https://blog.csdn.net/mzglzzc/article/details/46300645#
假设有一个CUSTOMER表,表中有数据200000行,我们将此表通过CUSTOMER_ID进行分区,每个分区存储100000行,我们将每个分区保存到单独的表空间中,这样数据文件就可以跨越多个物理磁盘。下面是创建表和分区的代码,
CREATE TABLE CUSTOMER
(
CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY,
FIRST_NAME<span style="white-space:pre"> </span>VARCHAR2(30) NOT NULL,
LAST_NAME<span style="white-space:pre"> </span>VARCHAR2(30) NOT NULL,
PHONE<span style="white-space:pre"> </span>VARCHAR2(15) NOT NULL,
EMAIL<span style="white-space:pre"> </span>VARCHAR2(80),
STATUS<span style="white-space:pre"> </span>CHAR(1)
)
PARTITION BY RANGE (CUSTOMER_ID)
(
PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01,
PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02
);
常用的
添加分区
-- range partitioned table
ALTER TABLE range_example ADD PARTITION part04 VALUES LESS THAN (TO_DATE('2008-10-1 00:00:00','yyyy-mm-ddhh24:mi:ss'));
--list partitioned table
ALTER TABLE list_example ADD PARTITION part04 VALUES('TE');
--Adding Values for a List Partition
ALTER TABLE list_example MODIFY PARTITION part04 ADD VALUES('MIS');
--Dropping Values from a List Partition
ALTER TABLE list_example MODIFY PARTITION part04 DROP VALUES('MIS');
--hash partitioned table
ALTER TABLE hash_example ADD PARTITION part03;
--增加subpartition
ALTER TABLE range_hash_example MODIFY PARTITION part_1 ADD SUBPARTITION part_1_sub_4;
注:hash partitioned table新增partition时,现有表的中所有data都有重新计算hash值,然后重新分配到分区中,所以被重新分配的分区的indexes需要rebuild 。
删除分区
ALTER TABLE SALES DROP PARTITION P3;
ALTER TABLE SALES DROP SUBPARTITION P4SUB1;
合并分区
ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2 UPDATE INDEXES;
重命名表分区
ALTER TABLE table_name RENAME PARTITION old_name TO new_name;
ALTER TABLE table_name RENAME SUBPARTITION old_name TO new_name;