分区表及分区索引创建示例

最新推荐文章于 2024-01-26 16:46:59 发布

cuixie2370

最新推荐文章于 2024-01-26 16:46:59 发布

阅读量93

点赞数

CREATE INDEX DFMS.AAAAA_TIME_IDX ON DFMS.AAAAA(in_station_time)
LOCAL
TABLESPACE BASE_IDX ;

创建完成之后在Toad中查看脚本如下, 可以看出， local 索引的分区完全继承表的分区的属性，包括分区类型，分区的范围值即不需指定也不能更改，这就是说： local 索引的分区维护完全依赖于其索引所在表。不过，分区名称，以及分区所在表空间等信息是可以自定义的。我们也可以这样写，

CREATE INDEX DFMS.AAAAA_TIME_IDX ON DFMS.AAAAA(in_station_time)   local (
partition   i_range_p1 tablespace tbspart01,
partition   i_range_p2   tablespace tbspart01,
partition i_range_p3   tablespace tbspart02,
partition i_range_pmax tablespace tbspart02
)    tablespace   tbspart00 ;

Toad中脚本如下：

CREATE TABLE DFMS.AAAAA
(
SERIAL_NUMBER     VARCHAR2(25 BYTE)           NOT NULL,
REPAIR_COUNT      INTEGER                     DEFAULT 0                     NOT NULL,
REWORK_COUNT      INTEGER                     DEFAULT 0                     NOT NULL,
IN_STATION_COUNT NUMBER(3),
IN_STATION_TIME   DATE,
SHIP_DATE         DATE,
PACK_SEQ_NO       INTEGER,
ORIGINAL_WO_NO    VARCHAR2(25 BYTE)
)
TABLESPACE BASE_DATA
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
LOGGING
PARTITION BY RANGE (IN_STATION_TIME)
(
PARTITION P200907 VALUES LESS THAN (TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_DATA
    PCTUSED    40
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                INITIAL          10M
                NEXT             10M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                FREELISTS        1
                FREELIST GROUPS 1
                BUFFER_POOL      DEFAULT
               ),
PARTITION P201001 VALUES LESS THAN (TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_DATA
    PCTUSED    40
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                INITIAL          10M
                NEXT             10M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                FREELISTS        1
                FREELIST GROUPS 1
                BUFFER_POOL      DEFAULT
               ),
PARTITION P201007 VALUES LESS THAN (TO_DATE(' 2010-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_DATA
    PCTUSED    40
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                INITIAL          10M
                NEXT             10M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                FREELISTS        1
                FREELIST GROUPS 1
                BUFFER_POOL      DEFAULT
               ),
PARTITION PMAX VALUES LESS THAN (MAXVALUE)
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_DATA
    PCTUSED    40
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                INITIAL          10M
                NEXT             10M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                FREELISTS        1
                FREELIST GROUPS 1
                BUFFER_POOL      DEFAULT
               )
)
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING
ENABLE ROW MOVEMENT;

CREATE INDEX DFMS.AAAAA_TIME_IDX ON DFMS.AAAAA
(IN_STATION_TIME)
TABLESPACE BASE_IDX
INITRANS   2
MAXTRANS   255
LOCAL (
PARTITION P200907
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_IDX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          5M
                NEXT             5M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                FREELISTS        1
                FREELIST GROUPS 1
                BUFFER_POOL      DEFAULT
               ),
PARTITION P201001
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_IDX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          5M
                NEXT             5M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                FREELISTS        1
                FREELIST GROUPS 1
                BUFFER_POOL      DEFAULT
               ),
PARTITION P201007
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_IDX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          5M
                NEXT             5M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                FREELISTS        1
                FREELIST GROUPS 1
                BUFFER_POOL      DEFAULT
               ),
PARTITION PMAX
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_IDX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          5M
                NEXT             5M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                FREELISTS        1
                FREELIST GROUPS 1
                BUFFER_POOL      DEFAULT
               )
)
NOPARALLEL;