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

ORACLE 创建 分区表

例子：

-- 删除 分区表
  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 ;