表分区：范围分区、散列分区、列表分区、复合分区

范围分区：

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)

);