oracle 优化 表分区示例

 

--范围分区 PARTITION BY RANGE(--)
/*create table fanwei(
       fid number(11) primary key,
       fdate date ,
       fname varchar2(12)     
)
PARTITION BY RANGE(fdate)
(
  PARTITION F1 VALUES LESS THAN(to_date('2011-1-01','YYYY-MM-DD')),
  PARTITION F2 VALUES LESS THAN(to_date('2012-1-01','YYYY-MM-DD')),
  PARTITION F3 VALUES LESS THAN(to_date('2013-1-01','YYYY-MM-DD')),
  PARTITION F4 VALUES LESS THAN(MAXVALUE)
);

drop table fanwei;
select * from fanwei;
select * from fanwei PARTITION (F2);
insert into fanwei values(1,to_date('2011-2-01','YYYY-MM-DD'),'12');*/

--散列分区 PARTITION BY HASH(COLUMN_NAME)

/*PARTITION BY HASH(column_name)
(
          partition d1 [TABLESPACE TBS1],
          partition d2 [TABLESPACE TBS2],
          partition d3 [TABLESPACE TBS2],
          partition d4
);*/
-----------------------------------------
/*CREATE TABLE Employee(
       E_ID NUMBER(11) PRIMARY KEY,
       E_NAME VARCHAR2(32),
       Department varchar2(10)
)
PARTITION BY HASH(Department)
(
          partition d1,
          partition d2,
          partition d3,
          partition d4
);

drop table Employee;

CREATE TABLE Employee(
       E_ID NUMBER(11) PRIMARY KEY,
       E_NAME VARCHAR2(32),
       Department varchar2(10)
)
PARTITION BY HASH(Department)
partitions 4;*/

--列表分区 PARTITION BY LIST(--)

/*CREATE TABLE EMPLOYEE1(E_ID NUMBER(4),E_ADDRESS VARCHAR2(16))
PARTITION BY LIST(E_ADDRESS)(
          PARTITION north values('北京'),
          PARTITION west values('新疆','青藏'),
          PARTITION east values('上海','浙江'),
          PARTITION south values('海南')
);

select * from employee1 partition (north);*/

 

--复合分区(范围与散列或者列表 相组合)
  CREATE TABLE SALES
 ( 
  P_ID number(11),
  SALES_DATE DATE NOT NULL,
  SALES_COST VARCHAR2(10)
 )
  PARTITION BY RANGE(SALES_DATE) --range 范围
  SUBPARTITION BY HASH(SALES_COST) --散列
  SUBPARTITIONS 5(
        PARTITION S1 VALUES LESS THAN (to_date('2011-1-01','YYYY-MM-DD')),
        PARTITION S2 VALUES LESS THAN (to_date('2012-1-01','YYYY-MM-DD')),
        PARTITION S3 VALUES LESS THAN (to_date('2013-1-01','YYYY-MM-DD')),
        PARTITION S4 VALUES LESS THAN (to_date('2014-1-01','YYYY-MM-DD')),
        PARTITION S5 VALUES LESS THAN (MAXVALUE)
  );
 
 
  --添加分区 --分区界限必须调整为大于最后一个分区界限
  ALTER TABLE EMPLOYEE1 ADD PARTITION nwes VALUES ('中部');
 
  --删除分区
  ALTER TABLE EMPLOYEE1 DROP PARTITION nwes;
  --截断分区
  ALTER TABLE EMPLOYEE1 TRUNCATE PARTITION nwes;
  --合并分区 MERGE-合并
  ALTER TABLE SALES MERGE PARTITIONS S1,S2 INTO PARTITION S2;
  --拆分分区
  ALTER TABLE SALES SPLIT PARTITION S2 AT (to_date('2011-1-01','YYYY-MM-DD')) INTO (PARTITION S1,PARTITION S2);

SELECT * from sales partition (s2);