oracle 创建分区表,增(追)加分区

CREATE TABLE T_BI_CurrentAccountDetailed
(
        OrderDate DATE,
        BranchFlag varchar2(3),
        SortId varchar2(11),
        OrderNo varchar2(15),
        CustomerId varchar2(11),
        AmtOfDebitSide NUMBER(14,2) DEFAULT 0,
        AmtOfCreditSide NUMBER(14,2) DEFAULT 0,
        Balance NUMBER(14,2) DEFAULT 0,
        GrossProfit NUMBER(14,2) DEFAULT 0,
        RepayWay varchar2(20),
        Remarks varchar2(100),
        PRIMARY KEY(OrderDate,BranchFlag,SortId,OrderNo)
)PARTITION BY RANGE(OrderDate)
  (PARTITION CAD2008_01 VALUES LESS THAN (TO_DATE('2008-01-01','YYYY-MM-DD')) ,
   PARTITION CAD2008_02 VALUES LESS THAN (TO_DATE('2008-02-01','YYYY-MM-DD')) ,
   PARTITION CAD2008_03 VALUES LESS THAN (TO_DATE('2008-03-01','YYYY-MM-DD')) ,
   PARTITION CAD2008_04 VALUES LESS THAN (TO_DATE('2008-04-01','YYYY-MM-DD')) ,
   PARTITION CAD2008_05 VALUES LESS THAN (TO_DATE('2008-05-01','YYYY-MM-DD')) ,
   PARTITION CAD2008_06 VALUES LESS THAN (TO_DATE('2008-06-01','YYYY-MM-DD')) ,
   PARTITION CAD2008_07 VALUES LESS THAN (TO_DATE('2008-07-01','YYYY-MM-DD')) ,
   PARTITION CAD2008_08 VALUES LESS THAN (TO_DATE('2008-08-01','YYYY-MM-DD')) ,
   PARTITION CAD2008_09 VALUES LESS THAN (TO_DATE('2008-09-01','YYYY-MM-DD')) ,
   PARTITION CAD2008_10 VALUES LESS THAN (TO_DATE('2008-10-01','YYYY-MM-DD')) ,
   PARTITION CAD2008_11 VALUES LESS THAN (TO_DATE('2008-11-01','YYYY-MM-DD')) ,
   PARTITION CAD2008_12 VALUES LESS THAN (TO_DATE('2008-12-01','YYYY-MM-DD')) ,
   PARTITION CAD2009_01 VALUES LESS THAN (TO_DATE('2009-01-01','YYYY-MM-DD')) ,
   PARTITION CAD2009_02 VALUES LESS THAN (TO_DATE('2009-02-01','YYYY-MM-DD')) ,
   PARTITION CAD2009_03 VALUES LESS THAN (TO_DATE('2009-03-01','YYYY-MM-DD')) ,
   PARTITION CAD2009_04 VALUES LESS THAN (TO_DATE('2009-04-01','YYYY-MM-DD')) ,
   PARTITION CAD2009_05 VALUES LESS THAN (TO_DATE('2009-05-01','YYYY-MM-DD')) ,
   PARTITION CAD2009_06 VALUES LESS THAN (TO_DATE('2009-06-01','YYYY-MM-DD')) ,
   PARTITION CAD2009_07 VALUES LESS THAN (TO_DATE('2009-07-01','YYYY-MM-DD')) ,
   PARTITION CAD2009_08 VALUES LESS THAN (TO_DATE('2009-08-01','YYYY-MM-DD')) ,
   PARTITION CAD2009_09 VALUES LESS THAN (TO_DATE('2009-09-01','YYYY-MM-DD')) ,
   PARTITION CAD2009_10 VALUES LESS THAN (TO_DATE('2009-10-01','YYYY-MM-DD')) ,
   PARTITION CAD2009_11 VALUES LESS THAN (TO_DATE('2009-11-01','YYYY-MM-DD')) ,
   PARTITION CAD2009_12 VALUES LESS THAN (TO_DATE('2009-12-01','YYYY-MM-DD'))
   );

-- Add comments to the table
comment on table T_BI_CurrentAccountDetailed
  is '往来账明细';
-- Add comments to the columns
comment on column T_BI_CurrentAccountDetailed.ORDERDATE
  is '日期';
comment on column T_BI_CurrentAccountDetailed.BRANCHFLAG
  is '分公司标识';
comment on column T_BI_CurrentAccountDetailed.sortid
  is '流水号';
comment on column T_BI_CurrentAccountDetailed.ORDERNO
  is '单据编号';
comment on column T_BI_CurrentAccountDetailed.CUSTOMERID
  is '客户内码';
comment on column T_BI_CurrentAccountDetailed.AMTOFDEBITSIDE
  is '借方金额';
comment on column T_BI_CurrentAccountDetailed.AMTOFCREDITSIDE
  is '贷方金额';
comment on column T_BI_CurrentAccountDetailed.BALANCE
  is '余额';
comment on column T_BI_CurrentAccountDetailed.GROSSPROFIT
  is '毛利';
comment on column T_BI_CurrentAccountDetailed.REPAYWAY
  is '还款方式';
comment on column T_BI_CurrentAccountDetailed.REMARKS
  is '备注';
 

 

增(追)加分区:

alter table T_BI_SUMOFCUSSALES add PARTITION SOCS2010_01
VALUES LESS THAN (TO_DATE('2010-01-01','YYYY-MM-DD'))
tablespace RPTFACT2010
 pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 10M
      minextents 1
      maxextents unlimited
    );
   

 

下面转载别人的:

在ORACLE里如果遇到特别大的表，可以使用分区的表来改变其应用程序的性能。

 

同事的分区表总结，转载一下。

1.1 分区表PARTITION table

在ORACLE里如果遇到特别大的表，可以使用分区的表来改变其应用程序的性能。

1.1.1 分区表的建立：

某公司的每年产生巨大的销售记录，DBA向公司建议每季度的数据放在一个分区内，以下示范的是该公司1999年的数据(假设每月产生30M的数据)，操作如下：

范围分区表：

CREATE TABLE sales

(invoice_no NUMBER,

...

sale_date DATE NOT NULL )

PARTITION BY RANGE (sale_date)

(PARTITION sales1999_q1

VALUES LESS THAN (TO_DATE(‘1999-04-01’,’YYYY-MM-DD’)

TABLESPACE ts_sale1999q1,

PARTITION sales1999_q2

VALUES LESS THAN (TO_DATE(‘1999-07-01’,’YYYY-MM-DD’)

TABLESPACE ts_sale1999q2,

PARTITION sales1999_q3

VALUES LESS THAN (TO_DATE(‘1999-10-01’,’YYYY-MM-DD’)

TABLESPACE ts_sale1999q3,

PARTITION sales1999_q4

VALUES LESS THAN (TO_DATE(‘2000-01-01’,’YYYY-MM-DD’)

TABLESPACE ts_sale1999q4 );

--values less than (maxvalue)

列表分区表：

create table emp (

empno number(4),

ename varchar2(30),

location varchar2(30))

partition by list (location)

(partition p1 values ('北京'),

partition p2 values ('上海','天津','重庆'),

partition p3 values ('广东','福建')

partition p0 values (default)

);

哈希分区：

create table emp (

empno number(4),

ename varchar2(30),

sal number)

partition by hash (empno)

partitions 8

store in (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);

组合分区：

范围哈希组合分区：

create table emp (

empno number(4),

ename varchar2(30),

hiredate date)

partition by rang