Oracle常用命令4（表分区）
表分区：范围分区、散列分区、列表分区、复合分区
范围分区：
PARTITION BY RANGE (column_name)
(
PARTITION part1 VALUE LESS THAN(range1),
PARTITION part2 VALUE LESS THAN(range2),
...
[PARTITION partN VALUE LESS THAN(MAXVALUE)]
);
查询分区的数据：
Select * from 表 partition(表分区名)
例1：
--创建商品表(商品编号、类别编号、名称、销售价、会员价、库存数量)
create table product(p_id number(4) primary key,
c_id number(4) not null,
p_name varchar2(10) not null,
p_price number(6,2)not null,
p_userprice number(6,2)not null,
p_quantity number(5) not null)
partition by range(p_id)
(partition p_product_1 values less than (1000),
partition p_product_2 values less than (5000),
partition p_product_3 values less than (maxvalue));
--创建序列
Create sequence seq_product start with 1 increment by 1;
--插入测试数据
insert into product values( seq_product.nextval, 1, 'java web', 15, 14, 10 );
--说明：插入的数据，一定要在表分区范围内的数据
insert into product values( seq_product.nextval, 1, 'java oop', 25, 24, 10 );
--查看某个表分区下的数据
select * from product partition(p_product_1);
例2：
--创建订单信息表(定单编号、用户编号、定单日期、总金额、收货人姓名、收货人电话、收货人地址)
create table orderTab(o_id number(4) primary key,
u_id number(4) not null,
o_date date not null,
o_money number(7,2) not null,
u_name varchar2(10) not null,
u_address varchar2(50),
u_tel varchar2(13))
partition by range(o_date)
(partition p_order_1 values less than ( to_date('2010-08-01','YYYY-MM-DD') ),
partition p_order_2 values less than ( to_date('2010-09-01','YYYY-MM-DD')),
partition p_order_3 values less than ( to_date('2010-10-01','YYYY-MM-DD') ),
partition p_order_5 values less than ( to_date('2011-04-01','YYYY-MM-DD') )
);
--创建序列
Create sequence seq_order start with 1 increment by 1;
--插入测试数据
insert into orderTab values( seq_order.nextval, 1, sysdate, 100, 'user1', '湖南长沙','13812345678' );
--根据月份，增加表分区
ALTER TABLE orderTab ADD PARTITION p_order4 VALUES LESS THAN (to_date('2010-11-01','YYYY-MM-DD'));
--删除表分区
ALTER TABLE orderTab DROP PARTITION P4;
--截断表分区
ALTER TABLE orderTab TRUNCATE PARTITION P4;
列表分区：
PARTITION BY LIST (column_name)
(
PARTITION part1 VALUES (values_list1),
PARTITION part2 VALUES (values_list2),
...
PARTITION partN VALUES (DEFAULT)
);
散列分区：
PARTITION BY HASH (column_name)
PARTITIONS number_of_partitions;
或
PARTITION BY HASH (column_name)
( PARTITION part1 [TABLESPACE tbs1],
PARTITION part2 [TABLESPACE tbs2],
...
PARTITION partN [TABLESPACE tbsN]);
复合分区：
PARTITION BY RANGE (column_name1)
SUBPARTITION BY HASH (column_name2)
SUBPARTITIONS number_of_partitions
(
PARTITION part1 VALUE LESS THAN(range1),
PARTITION part2 VALUE LESS THAN(range2),
...
PARTITION partN VALUE LESS THAN(MAXVALUE)
);
