创建分区表
CREATE TABLE CUSTOMER
(CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY,
FIRST_NAME VARCHAR2(30) NOT NULL,
LAST_NAME VARCHAR2(30) NOT NULL,
PHONE VARCHAR2(15) NOT NULL,
EMAIL VARCHAR2(80),
STATUS CHAR(1)
)
PARTITION BY RANGE (CUSTOMER_ID)
(
PARTITION CUS_PART1 VALUES LESS THAN (100000) tablespace ts1 ,
PARTITION CUS_PART2 VALUES LESS THAN (200000) tablespace ts2
);
--指定分区表查询
select * from CUSTOMER partition(CUS_PART2) where customer_id = '100001' ;
--不指定分区表查询
select * from CUSTOMER where customer_id = '100001' ;
注:
对于已有的大表,可以采取新建分区表再insert in ... select ...方法,导入之后再rename;
优点:
a、每个分区的数据独立存放,且可单独备份和恢复;
b、提升大表查询性能;