oracle 分区举例

1.范围分区
     create table emp(
        empno number(5) primary key;
        ename varchar2(30)
     )
     partition by range(empno)
     (
        partition p1 values less than (3000),
        partition p2 values less than (5000)
     );
    insert into emp values(1001,'chen');
    insert into emp1 values (3000,'john');
    insert into emp1 values (3400,'john');
    insert into emp1 values (5000,'john');   --5000没有插入表中，5000不在任何分区中
   
    select * from emp partition(p1);  //查分区p1的数据
    增加一个分区:
     alter table emp1 add partition p3 values less than (maxvalue);   //这边是maxvalue,不是实际值

    按时间:
    partition by range(receipt_time)
      (partition p0 values less than (to_date('20050101','yyyymmdd')),
       partition p1 values less than (to_date('20050201','yyyymmdd')),

2.散列分区
    create table dept1(
      deptno number(2), --部门号
      dname varchar2(14), --部门名称
      loc varchar2(13)) --位置
    partition by hash(deptno)
      ( partition p1,partition p2);

3.复合分区
   create table salgrade(
     grade number,
     losal number,
     hisal number)
   partition by range(grade)
   subpartition by hash(losal,hisal)
    (partition p1 values less than (10)
      (subpartition sp1, subpartition sp2),
    (partition p2 values less than (20)
      (subpartition sp3, subpartition sp4));

4.列表分区
   create table sales(
     salsid number,
     salname varchar2(80),
     saldate date)
   partition by list(salname)
   (
     partition sal_east values('chen','kai')
     partition sal_west values('fj','fz')
   );

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/658698/viewspace-254563/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/658698/viewspace-254563/