Oracle: 创建分区表、分区索引

ORACLE 创建 分区表

例子：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62

-- 删除 分区表   DROP  TABLE CUX.CUX_PARTITION_TEST;    -- 创建 分区表，以GROUP_NAME字段的值进行分区    CREATE TABLE CUX.CUX_PARTITION_TEST (     TEST_ID NUMBER,     TEST_NAME VARCHAR2(50),     GROUP_NAME VARCHAR2(30),     CREATION_DATE DATE     )     PARTITION BY LIST(GROUP_NAME )     (     PARTITION PG1 VALUES( 'GROUP1'),     PARTITION PG2 VALUES( 'GROUP2'),     PARTITION PG3 VALUES( 'GROUP3'),     PARTITION PG4 VALUES( 'GROUP4')     ) TABLESPACE CUX_TS_TX_DATA ;           --  添加 表分区   ALTER TABLE CUX.CUX_PARTITION_TEST ADD PARTITION  PG5 VALUES ('GROUP5') ;                   INSERT INTO CUX.CUX_PARTITION_TEST(TEST_ID, TEST_NAME, GROUP_NAME, CREATION_DATE)    VALUES (1, 'NAME1','GROUP1',SYSDATE );   INSERT INTO CUX.CUX_PARTITION_TEST(TEST_ID, TEST_NAME, GROUP_NAME, CREATION_DATE)    VALUES (2, 'NAME2','GROUP2',SYSDATE );   INSERT INTO CUX.CUX_PARTITION_TEST(TEST_ID, TEST_NAME, GROUP_NAME, CREATION_DATE)    VALUES (3, 'NAME3','GROUP3',SYSDATE );    INSERT INTO CUX.CUX_PARTITION_TEST(TEST_ID, TEST_NAME, GROUP_NAME, CREATION_DATE)    VALUES (4, 'NAME4','GROUP4',SYSDATE );    INSERT INTO CUX.CUX_PARTITION_TEST(TEST_ID, TEST_NAME, GROUP_NAME, CREATION_DATE)    VALUES (11, 'NAME11','GROUP1',SYSDATE );   INSERT INTO CUX.CUX_PARTITION_TEST(TEST_ID, TEST_NAME, GROUP_NAME, CREATION_DATE)    VALUES (21, 'NAME21','GROUP2',SYSDATE );   INSERT INTO CUX.CUX_PARTITION_TEST(TEST_ID, TEST_NAME, GROUP_NAME, CREATION_DATE)    VALUES (31, 'NAME31','GROUP3',SYSDATE );    INSERT INTO CUX.CUX_PARTITION_TEST(TEST_ID, TEST_NAME, GROUP_NAME, CREATION_DATE)    VALUES (41, 'NAME41','GROUP4',SYSDATE );      SELECT * FROM CUX.CUX_PARTITION_TEST    ORDER BY TEST_ID;           SELECT * FROM SYS.ALL_TAB_PARTITIONS   WHERE TABLE_NAME LIKE 'CUX_PARTITION_TEST'      SELECT * FROM SYS.ALL_IND_PARTITIONS   WHERE INDEX_NAME LIKE 'CUX_PARTITION_TEST_N1'         SELECT IDX.*  FROM SYS.ALL_INDEXES IDX  WHERE IDX.TABLE_NAME ='CUX_PARTITION_TEST'   -- AND IDX.INDEX_NAME LIKE 'EBS_XLA_AE_HEADERS_N5'  ORDER BY IDX.TABLE_NAME, IDX.INDEX_NAME ;      SELECT IDXC.*  FROM SYS.ALL_IND_COLUMNS IDXC  WHERE IDXC.INDEX_NAME =  'CUX_PARTITION_TEST_N1'       -- 创建分区索引  CREATE INDEX  CUX.CUX_PARTITION_TEST_N1 ON CUX.CUX_PARTITION_TEST(TEST_ID, GROUP_NAME ) LOCAL INDEXING PARTIAL;

　

-- 向分区表添加新分区并指派表空间（XXCUX_IDX)
ALTER TABLE CUX.KL_PARTITION_TEST ADD PARTITION PG6 VALUES ('GROUP6') TABLESPACE XXCUS_IDX ;