Oracle的分区

一，自动列表分区，就是自动扩展列表分区

1，非自动扩展列表分区

 

SQL> create table t_t1(
  2  id number,
  3  name_code varchar2(10),
  4  GDP number,
  5  constraint t1_pk primary key(id)
  6  )
  7  partition by list(name_code)
  8  (
  9  partition p1 values('USA'),
 10  partition p2 values('China','Japan')
 11  );

Table created.

SQL> insert into t_t1 values(1,'USA',20);

1 row created.

SQL> insert into t_t1 values(2,'China',11);

1 row created.

SQL> insert into t_t1 values(3,'Japan',5);

1 row created.

SQL> insert into t_t1 values(4,'Korea',1);
insert into t_t1 values(4,'Korea',1)
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

2，自动扩展分区

 

 

SQL> create table t_t1(
  2  id number,
  3  name_code varchar2(10),
  4  GDP number,
  5  constraint t1_pk primary key(id)
  6  )
  7  partition by list(name_code) automatic
  8  (
  9  partition p1 values('USA'),
 10  partition p2 values('China','Japan')
 11  );

Table created.

SQL> insert into t_t1 values(1,'USA',20);

1 row created.

SQL> insert into t_t1 values(2,'China',11);

1 row created.

SQL> insert into t_t1 values(3,'Japan',5);

1 row created.

SQL> insert into t_t1 values(4,'Korea',1);

1 row created.

SQL> commit;

Commit complete

可以看到在分区后边添加automatic即可，也可以直接修改表属性ALTER TABLE T_T1 SET PARTIONING AUTOMATIC

 

 

SQL> alter table t_t1 set partitioning automatic;

Table altered.

SQL> insert into t_t1 values(4,'Korea',1);

1 row created.

 

查看属性

 

SQL> exec dbms_stats.gather_table_stats('SYS','t_t1',cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> set linesize 90
SQL> col table_name for a30
SQL> col partition_name for a30
SQL> col high_value for a16
SQL> select table_name,partition_name,high_value,num_rows
  2  from user_tab_partitions where table_name='T_T1'
  3  order by 1,2;

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE         NUM_ROWS
------------------------------ ------------------------------ ---------------- ----------
T_T1                           P1                             'USA'                     1
T_T1                           P2                             'China', 'Japan'          2
T_T1                           SYS_P886                       'Korea'                   1

SQL> select table_name,autolist from user_par_tables where table_name='T_T1';
select table_name,autolist from user_par_tables where table_name='T_T1'
                                *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select table_name,autolist from user_part_tables where table_name='T_T1';

TABLE_NAME                     AUT
------------------------------ ---
T_T1                           YES

二，时间间隔分区

-- 时间间隔分区 按天
create table T_par1
(
  id        NUMBER not null,
  datatime         DATE not null,
primary key (id)
)
PARTITION BY RANGE ("DATATIME") 
 INTERVAL(NUMTODSINTERVAL(1,'DAY')) 
(
PARTITION P1 VALUES LESS THAN (TO_DATE('2001-01-01','YYYY-MM-DD'))
);

-- 按月
 PARTITION BY RANGE ("DATATIME") 
 INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
 (PARTITION P1 VALUES LESS THAN (TO_DATE('2002-01-01', 'YYYY-MM-DD'))
) ;

三，合并分区，

1，合并分区只能合并相邻两个分区，2，快速交换不同表分区数据，只能是分区表到非分区表

ALTER TABLE T_DATA MERGE PARTITIONS SYS_P1081, SYS_P1082 INTO PARTITION SYS_P1081_M UPDATE INDEXES;
alter table T_DATA exchange partition  SYS_P1081_M with table T_DATA_HIS ;