Oracle 11g Interval分区

 

 

 

1.
   interval使用之后，就不用手工增加分区，来适应数据的增长。
2.
   根据年月 INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))
            INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
   根据天   INTERVAL(NUMTODSINTERVAL(1,'DAY'))

3.

 CREATE TABLE hr.int_par(
        id number primary key,
        join_date date )
      partition by range(join_date) interval (numtoyMinterval (1,'YEAR'))
      --store in (par01,par02,par03,par04,par05,par06,par07,par08,par09,par010,par011,par012)
      (
        partition inte_par1 VALUES LESS THAN (to_date('2000-9-9','YYYY-MM-DD'))
      )

4.
    BEGIN
         FOR i IN 1..10 LOOP
             EXECUTE IMMEDIATE 'INSERT INTO hr.int_par VALUES (:1,:2)'
             USING i, trunc(to_date('2000-9-9','YYYY-MM-DD'),'yyyy')+366*i  ;
         END LOOP;
         COMMIT;
    END;
    /

5.
TABLE_NAME  PARTITION_NAME  HIGH_VALUE                                                                       PARTITION_POSITION
------------------------------------------------------------------------------------------------------------ ------------------- --
  INT_PAR     SYS_P50         TO_DATE(' 2010-09-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                  11
  INT_PAR     SYS_P49         TO_DATE(' 2009-09-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                  10
  INT_PAR     SYS_P48         TO_DATE(' 2008-09-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                   9
  INT_PAR     SYS_P47         TO_DATE(' 2007-09-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                   8
  INT_PAR     SYS_P46         TO_DATE(' 2006-09-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                   7
  INT_PAR     SYS_P45         TO_DATE(' 2005-09-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                   6
  INT_PAR     SYS_P44         TO_DATE(' 2004-09-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                   5
  INT_PAR     SYS_P43         TO_DATE(' 2003-09-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                   4
  INT_PAR     SYS_P42         TO_DATE(' 2002-09-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                   3
  INT_PAR     SYS_P41         TO_DATE(' 2001-09-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                   2
  INT_PAR     INTE_PAR1       TO_DATE(' 2000-09-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                   1

6.
        SQL> SELECT * FROM hr.int_par partition(sys_p46);

                         ID JOIN_DATE
                   ---------- -----------
                         6 2006-1-5

         SQL> SELECT * FROM hr.int_par partition(sys_p50);

                         ID JOIN_DATE
                  ---------- -----------
                         10 2010-1-8

7.

 

 

create table BALANCE_X
(
  balid       VARCHAR2(64) not null,
  year        NUMBER(10),
  month       NUMBER(5),
  keptbcode   VARCHAR2(32),
  code00      VARCHAR2(32),
  code01      VARCHAR2(32),
  code02      VARCHAR2(32),
  code03      VARCHAR2(64),
  code04      VARCHAR2(4),
  code05      VARCHAR2(32),
  code06      VARCHAR2(32),
  code07      VARCHAR2(32),
  code08      VARCHAR2(32),
  code09      VARCHAR2(32),
  code10      VARCHAR2(128),
  code11      VARCHAR2(128),
  code12      VARCHAR2(128),
  code13      VARCHAR2(128),
  code14      VARCHAR2(128),
  code15      VARCHAR2(128),
  n6key       VARCHAR2(64),
  n6idx       VARCHAR2(64),
  n6flags     NUMBER(5),
  fcydebit    NUMBER(18,2),
  fcycredit   NUMBER(18,2),
  fcybalance  NUMBER(18,2),
  qtydebit    NUMBER(18,6),
  qtycredit   NUMBER(18,6),
  qtybalance  NUMBER(18,6),
  qtxdebit    NUMBER(18,6),
  qtxcredit   NUMBER(18,6),
  qtxbalance  NUMBER(18,6),
  scydebit    NUMBER(18,2),
  scycredit   NUMBER(18,2),
  scybalance  NUMBER(18,2),
  rcy1debit   NUMBER(18,2),
  rcy1credit  NUMBER(18,2),
  rcy1balance NUMBER(18,2),
  rcy2debit   NUMBER(18,2),
  rcy2credit  NUMBER(18,2),
  rcy2balance NUMBER(18,2),
  gattr1      VARCHAR2(128),
  gattr2      VARCHAR2(128),
  gattr3      VARCHAR2(128),
  vprepare    VARCHAR2(32),
  predate     DATE,
  gattr4      VARCHAR2(128),
  gattr5      VARCHAR2(128),
  modifier    VARCHAR2(32),
  modifydate  DATE,
  gattr6      VARCHAR2(128),
  gattr7      VARCHAR2(128),
  gattr8      VARCHAR2(128),
  gattr9      VARCHAR2(128),
  gattr10     VARCHAR2(128),
  code16      VARCHAR2(128),
  code17      VARCHAR2(128),
  code18      VARCHAR2(128),
  code19      VARCHAR2(128),
  code20      VARCHAR2(128),
  code21      VARCHAR2(128),
  code22      VARCHAR2(128),
  code23      VARCHAR2(128),
  code24      VARCHAR2(128),
  code25      VARCHAR2(128),
  code26      VARCHAR2(128),
  code27      VARCHAR2(128),
  code28      VARCHAR2(128),
  code29      VARCHAR2(128),
  code30      VARCHAR2(128),
  code31      VARCHAR2(128),
  code32      VARCHAR2(128),
  code33      VARCHAR2(128),
  code34      VARCHAR2(128),
  code35      VARCHAR2(128),
  code36      VARCHAR2(128),
  code37      VARCHAR2(128),
  code38      VARCHAR2(128),
  code39      VARCHAR2(128),
  code40      VARCHAR2(128),
  code41      VARCHAR2(128),
  code42      VARCHAR2(128),
  code43      VARCHAR2(128),
  code44      VARCHAR2(128),
  code45      VARCHAR2(128),
  code46      VARCHAR2(128),
  code47      VARCHAR2(128),
  code48      VARCHAR2(128),
  code49      VARCHAR2(128),
  code50      VARCHAR2(128)
)
tablespace FIN_TBS
partition by range(year) interval (1)
      (
        partition P_2005 VALUES LESS THAN (2005)
      )
 
alter table BALANCE_X
  add constraint BALANCE_PK_1 primary key (BALID)
  using index
  tablespace FIN_TBS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table BALANCE_X
  add constraint ACODE_BALANCE_1 foreign key (CODE00)
  references ACODE (ACODE);
-- Create/Recreate indexes
create index BALANCE_CODE00_1 on BALANCE_X (CODE00) local
  tablespace FIN_IDX_TBS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index BALANCE_CODE01_1 on BALANCE_X (CODE01) local
  tablespace FIN_IDX_TBS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index BALANCE_CODE02_1 on BALANCE_X (CODE02) local
  tablespace FIN_IDX_TBS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index BALANCE_CODE03_1 on BALANCE_X (CODE03) local
  tablespace FIN_IDX_TBS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index BALANCE_CODE04_1 on BALANCE_X (CODE04) local
  tablespace FIN_IDX_TBS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index BALANCE_CODE05_1 on BALANCE_X (CODE05) local
  tablespace FIN_IDX_TBS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index BALANCE_CODE06_1 on BALANCE_X (CODE06) local
  tablespace FIN_IDX_TBS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index BALANCE_CODE07_1 on BALANCE_X (CODE07) local
  tablespace FIN_IDX_TBS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index BALANCE_CODE08_1 on BALANCE_X (CODE08) local
  tablespace FIN_IDX_TBS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index BALANCE_CODE09_1 on BALANCE_X (CODE09) local
  tablespace FIN_IDX_TBS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index BALANCE_CODE10_1 on BALANCE_X (CODE10) local
  tablespace FIN_IDX_TBS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index BALANCE_KEPTBCODE_1 on BALANCE_X (KEPTBCODE) local
  tablespace FIN_IDX_TBS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index BALANCE_MONTH_1 on BALANCE_X (MONTH) local
  tablespace FIN_IDX_TBS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );