分区表创建与截断

创建：

CREATE TABLE EVENT_TABLE
(
  NAME               VARCHAR2(100 BYTE)         NOT NULL,
  OCCUR_TIME         DATE                       NOT NULL,
  MSECOND            NUMBER(10)                 NOT NULL,
  CONTENT            VARCHAR2(160 BYTE)
)
NOCOMPRESS 
TABLESPACE HISTORY_TABLESPACES
RESULT_CACHE (MODE DEFAULT)
PCTUSED    40
PCTFREE    1
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64M
            NEXT             64M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      KEEP
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOLOGGING
PARTITION BY RANGE (OCCUR_TIME)
INTERVAL( NUMTOYMINTERVAL(1,'MONTH'))
(  
  PARTITION EVENT_TABLE_1 VALUES LESS THAN (TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    NOLOGGING
    NOCOMPRESS 
    TABLESPACE HISTORY_TABLESPACES
    PCTUSED    40
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                INITIAL          1280K
                NEXT             1280K
                MAXSIZE          UNLIMITED
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                FREELISTS        1
                FREELIST GROUPS  1
                BUFFER_POOL      KEEP
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               )
)
CACHE
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT )
MONITORING;

CREATE INDEX PK_EVENT_TABLE ON EVENT_TABLE
(OCCUR_TIME)
NOLOGGING
LOCAL
TABLESPACE HISTORY_TABLESPACES_INDEX
PCTFREE    1
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64M
            NEXT             64M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      KEEP
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT );

需要注意的两点：

1. NUMTOYMINTERVAL后面只能跟年或者月，没有“周”之类的选项。

2. 索引创建必须加Local，否则索引仍使用全局分区，查询效率得不到提升。

截断分区：

ALTER TABLE <table_name> TRUNCATE PARTITION <partition_name>;