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

最新推荐文章于 2024-08-31 21:57:21 发布

pkushinhwa

最新推荐文章于 2024-08-31 21:57:21 发布

阅读量5.5k

点赞数

分类专栏： DataBase 文章标签： oracle less date table 数据库 exchange

本文链接：https://blog.csdn.net/pkushinhwa/article/details/5047214

版权

本文详细介绍了在Oracle数据库中如何创建和管理分区表，包括范围分区、列表分区、哈希分区和组合分区的示例。通过分区可以提高大表的性能和可管理性，例如通过日期范围对数据进行分区，便于查询和维护。文章还提到了如何增加、删除和交换分区，以及将普通表转换为分区表的几种方法，并强调了在转换过程中需要注意的数据一致性问题。

摘要由CSDN通过智能技术生成

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里如果遇到特别大的表，可以使用分区的表来改变其应用程序的性能。

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