一,自动列表分区,就是自动扩展列表分区
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 ;