range分区表 partition_position研究

不清楚range分区表的partition_position是一个固定的编号,还是记录各分区相对位置的动态值.基于实践出真知的目的,测试一下
测试步骤
-- Create table
create table US_USERCONSUME_TEST
(
  recordid        NUMBER(16) not null,
  msisdn          VARCHAR2(11) not null,
  mcpid           VARCHAR2(10),
  productid       VARCHAR2(20),
  bookid          VARCHAR2(20),
  spareaid        VARCHAR2(10),
  chapterid       VARCHAR2(20),
  promotionid     VARCHAR2(16),
  chargetime      DATE,
  sourcetype      VARCHAR2(2),
  basefee         NUMBER(6),
  infofee         NUMBER(6),
  realfee         NUMBER(6),
  uaid            VARCHAR2(255),
  recmsisdn       VARCHAR2(11),
  insideflag      VARCHAR2(1),
  auid            VARCHAR2(20),
  chargemode      VARCHAR2(3),
  ticketfee       NUMBER(6) default (0),
  productname     VARCHAR2(100),
  chaptername     VARCHAR2(1024),
  discounttype    VARCHAR2(2),
  discountreason  VARCHAR2(300),
  chargeticketfee NUMBER(9),
  paytype         VARCHAR2(5),
  paymsisdn       VARCHAR2(11),
  channelid       VARCHAR2(8)
)
partition by range (CHARGETIME)
(
  partition P1 values less than (TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace TBS_MREAD_DAT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition P_20140401 values less than (TO_DATE(' 2014-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace TBS_MREAD_DAT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition P_20140501 values less than (TO_DATE(' 2014-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace TBS_MREAD_DAT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition P_20140601 values less than (TO_DATE(' 2014-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace TBS_MREAD_DAT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition P_20140701 values less than (TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace TBS_MREAD_DAT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition P_20140801 values less than (TO_DATE(' 2014-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace TBS_MREAD_DAT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition P_20140901 values less than (TO_DATE(' 2014-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace TBS_MREAD_DAT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition P_20141001 values less than (TO_DATE(' 2014-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace TBS_MREAD_DAT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition P_20141101 values less than (TO_DATE(' 2014-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace TBS_MREAD_DAT
    pctfree 10
    initrans 1
    maxtrans 255
);

--查看并记录初始position
select t.partition_position, t.partition_name
                from all_tab_partitions t
               where t.table_name = 'US_USERCONSUME_TEST';

1    P1
2    P_20140401
3    P_20140501
4    P_20140601
5    P_20140701
6    P_20140801
7    P_20140901
8    P_20141001
9    P_20141101

--删除中间的分区
alter table US_USERCONSUME_TEST drop partition P_20140901;

--再次检查
1    P1
2    P_20140401
3    P_20140501
4    P_20140601
5    P_20140701
6    P_20140801
7    P_20141001
8    P_20141101

结论,当range 分区的partition_position记录的分区的相对位置,是可变的

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

转载于:http://blog.itpub.net/692830/viewspace-1260301/