[altibase]create partition table

• CREATE TABLE

CREATE TABLE TESTADM.TB_TEST_PART
(
    SEQ NUMBER(13) NOT NULL,
    NAME VARCHAR(20),
    AGE VARCHAR(20),
    --PARTITION_ID SMALLINT DEFAULT MOD(DATEDIFF(TO_DATE('20140501','YYYYMMDD'),SYSDATE,'MONTH'),60) NOT NULL
    PARTITION_ID SMALLINT DEFAULT MOD(DATEDIFF(TO_DATE('20140525','YYYYMMDD'),SYSDATE,'DAY'),14)  NOT NULL
)
PARTITION BY RANGE (PARTITION_ID)
(
    PARTITION P_00 VALUES LESS THAN  ( 1 ),
    PARTITION P_01 VALUES LESS THAN  ( 2 ),
    PARTITION P_02 VALUES LESS THAN  ( 3 ),
    PARTITION P_03 VALUES LESS THAN  ( 4 ),
    PARTITION P_04 VALUES LESS THAN  ( 5 ),
    PARTITION P_05 VALUES LESS THAN  ( 6 ),
    PARTITION P_06 VALUES LESS THAN  ( 7 ),
    PARTITION P_07 VALUES LESS THAN  ( 8 ),
    PARTITION P_08 VALUES LESS THAN  ( 9 ),
    PARTITION P_09 VALUES LESS THAN  ( 10 ),
    PARTITION P_10 VALUES LESS THAN  ( 11 ),
    PARTITION P_11 VALUES LESS THAN  ( 12 ),
    PARTITION P_12 VALUES LESS THAN  ( 13 ),
    PARTITION P_13 VALUES LESS THAN  ( 14 ),
    PARTITION P_DEF VALUES DEFAULT
)
TABLESPACE TS_TEST_DISK_DAT
;

• CREATE PK

ALTER TABLE TESTADM.TB_TEST_PART add constraint PK_TB_TEST_PART primary key(PARTITION_ID, SEQ) using index local
(
partition PK_TB_TEST_PART_P_00 on P_00 tablespace TS_TEST_DISK_IDX,
partition PK_TB_TEST_PART_P_01 on P_01 tablespace TS_TEST_DISK_IDX,
partition PK_TB_TEST_PART_P_02 on P_02 tablespace TS_TEST_DISK_IDX,
partition PK_TB_TEST_PART_P_03 on P_03 tablespace TS_TEST_DISK_IDX,
partition PK_TB_TEST_PART_P_04 on P_04 tablespace TS_TEST_DISK_IDX,
partition PK_TB_TEST_PART_P_05 on P_05 tablespace TS_TEST_DISK_IDX,
partition PK_TB_TEST_PART_P_06 on P_06 tablespace TS_TEST_DISK_IDX,
partition PK_TB_TEST_PART_P_07 on P_07 tablespace TS_TEST_DISK_IDX,
partition PK_TB_TEST_PART_P_08 on P_08 tablespace TS_TEST_DISK_IDX,
partition PK_TB_TEST_PART_P_09 on P_09 tablespace TS_TEST_DISK_IDX,
partition PK_TB_TEST_PART_P_10 on P_10 tablespace TS_TEST_DISK_IDX,
partition PK_TB_TEST_PART_P_11 on P_11 tablespace TS_TEST_DISK_IDX,
partition PK_TB_TEST_PART_P_12 on P_DEF tablespace TS_TEST_DISK_IDX
);

• CREATE SEQUENCE

CREATE SEQUENCE SEQ_TEST
START WITH 1
MINVALUE 1
MAXVALUE 9223372036854775806;

• INSERT TEST DATA

insert into TB_TEST_PART (seq, name, age)
select seq_test.nextval, 'aa', '33' from dual
CONNECT BY LEVEL <= 100
;

• RETRIEVE DATE USING SPEC. PARTITION TABLE

SELECT * FROM TB_TEST_PART PARTITION (P_01); -->

• RETRIEVE DATE USING SPEC PARTITION TABLE WITH WHERE CLAUSE

SELECT * FROM TB_TEST_PART
WHERE PARTITION_ID IN ('1','2')