oracle 11g 增加了新的分区类型，总结一下目前之前的分区表
区间分区
散列分区
列表分区
区间分区：
create table gh_range_example(
id varchar2(100),
range_date date not null)
partition by range(range_date)
(
partition range_15 values less than (to_date('2016-01-01','yyyy-mm-dd')),
partition range_16 values less than (to_date('2017-01-01','yyyy-mm-dd'))
);
SQL> select * from user_tab_partitions t where t.table_name='GH_RANGE_EXAMPLE';
TABLE_NAME COMPOSITE PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE HIGH_VALUE_LENGTH PARTITION_POSITION TABLESPACE_NAME PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT MAX_SIZE PCT_INCREASE FREELISTS FREELIST_GROUPS LOGGING COMPRESSION COMPRESS_FOR NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED BUFFER_POOL FLASH_CACHE CELL_FLASH_CACHE GLOBAL_STATS USER_STATS IS_NESTED PARENT_TABLE_PARTITION INTERVAL SEGMENT_CREATED
------------------------------ --------- ------------------------------ ------------------ -------------------------------------------------------------------------------- ----------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- -------------- ----------- ---------- ---------- ---------- ------------ ---------- --------------- ------- ----------- ------------ ---------- ---------- ------------ ---------- ---------- ----------- ----------- ------------- ----------- ----------- ---------------- ------------ ---------- --------- ------------------------------ -------- ---------------
GH_RANGE_EXAMPLE NO RANGE_15 0 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 83 1 TS_ZYK_DATA 10 1 255 YES DISABLED DEFAULT DEFAULT DEFAULT NO NO NO NO NO
GH_RANGE_EXAMPLE NO RANGE_16 0 TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 83 2 TS_ZYK_DATA 10 1 255 YES DISABLED DEFAULT DEFAULT DEFAULT NO NO NO NO NO
SQL>
SQL> insert into gh_range_example(id,range_date) values(100,sysdate-365);
1 row inserted
SQL> insert into gh_range_example(id,range_date) values(100,sysdate);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from gh_range_example partition(range_15);
ID RANGE_DATE
-------------------------------------------------------------------------------- -----------
100 2015/6/16 1
SQL> insert into gh_range_example(id,range_date) values(100,sysdate+365);
insert into gh_range_example(id,range_date) values(100,sysdate+365)
ORA-14400: inserted partition key does not map to any partition
将其他日期都归属与第三个分区MAXVALUE
SQL> alter table gh_range_example add partition RANGE_MAX VALUES LESS THAN (MAXVALUE);
Table altered
MAXVALUE对于DATE,NUMBER,VARCHAR都是可以的
散列分区
hash partitioning
oracle建议分区个数为2的N次方
SQL>
SQL> create table gh_hash_example(
id varchar2(100),
hash_date date)
partition by hash(hash_date)
(partition hash_1 tablespace ts_zyk_data,
partition hash_2 tablespace ts_zyk_data);
Table created
SQL> select * from user_tab_partitions t where t.table_name='GH_HASH_EXAMPLE';
TABLE_NAME COMPOSITE PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE HIGH_VALUE_LENGTH PARTITION_POSITION TABLESPACE_NAME PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT MAX_SIZE PCT_INCREASE FREELISTS FREELIST_GROUPS LOGGING COMPRESSION COMPRESS_FOR NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED BUFFER_POOL FLASH_CACHE CELL_FLASH_CACHE GLOBAL_STATS USER_STATS IS_NESTED PARENT_TABLE_PARTITION INTERVAL SEGMENT_CREATED
------------------------------ --------- ------------------------------ ------------------ -------------------------------------------------------------------------------- ----------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- -------------- ----------- ---------- ---------- ---------- ------------ ---------- --------------- ------- ----------- ------------ ---------- ---------- ------------ ---------- ---------- ----------- ----------- ------------- ----------- ----------- ---------------- ------------ ---------- --------- ------------------------------ -------- ---------------
GH_HASH_EXAMPLE NO HASH_1 0 0 1 TS_ZYK_DATA 10 1 255 YES DISABLED DEFAULT DEFAULT DEFAULT NO NO NO NO NO
GH_HASH_EXAMPLE NO HASH_2 0 0 2 TS_ZYK_DATA 10 1 255 YES DISABLED DEFAULT DEFAULT DEFAULT NO NO NO NO NO
DECLARE
BEGIN
FOR L IN 1..365 LOOP
INSERT INTO GH_HASH_EXAMPLE(ID,HASH_DATE)
VALUES(100,SYSDATE-L);
END LOOP;
COMMIT;
END;
SQL> SELECT COUNT(1) FROM gh_hash_example partition(hash_1);
COUNT(1)
----------
184
SQL> SELECT COUNT(1) FROM gh_hash_example partition(hash_2);
COUNT(1)
----------
181
hash_date有足够多的相异值，数据更容易在多个分区上均匀的分布
样本值差异性越多，越有利于散列分区的使用
散列分区不能明确某一行具体存在那个分区中
SQL> truncate table GH_HASH_EXAMPLE;
Table truncated
DECLARE
BEGIN
FOR L IN 1..365 LOOP
INSERT INTO GH_HASH_EXAMPLE(ID,HASH_DATE)
VALUES(100,SYSDATE-L);
END LOOP;
COMMIT;
END;
SQL> SELECT COUNT(1) FROM gh_hash_example partition(hash_1);
COUNT(1)
----------
175
SQL> SELECT COUNT(1) FROM gh_hash_example partition(hash_2);
COUNT(1)
----------
190
增加新的分区导致所有数据重写
SQL> ALTER TABLE gh_hash_example ADD PARTITION HASH_3 tablespace ts_zyk_data;
Table altered
SQL> ALTER TABLE gh_hash_example ADD PARTITION HASH_4 tablespace ts_zyk_data;
Table altered
SQL> SELECT COUNT(1) FROM gh_hash_example partition(hash_1);
COUNT(1)
----------
94
SQL> SELECT COUNT(1) FROM gh_hash_example partition(hash_2);
COUNT(1)
----------
97
SQL> SELECT COUNT(1) FROM gh_hash_example partition(hash_3);
COUNT(1)
----------
81
SQL> SELECT COUNT(1) FROM gh_hash_example partition(hash_4);
COUNT(1)
----------
93
列表分区
create table gh_list_example(
id varchar2(100),
created_d date)
partition by list(id)
(
partition list_01 values('A','B','C','E'),
partition list_02 values('D','F','G','I')
);
insert into gh_list_example(id,created_d) values('A',sysdate);
insert into gh_list_example(id,created_d) values('D',sysdate);
COMMIT;
SQL> INSERT INTO GH_LIST_EXAMPLE(ID,CREATED_D) VALUES('W',SYSDATE);
INSERT INTO GH_LIST_EXAMPLE(ID,CREATED_D) VALUES('W',SYSDATE)
ORA-14400: inserted partition key does not map to any partition
SQL> ALTER TABLE GH_LIST_EXAMPLE ADD PARTITION LIST_03 VALUES( DEFAULT);
Table altered
SQL> INSERT INTO GH_LIST_EXAMPLE(ID,CREATED_D) VALUES('W',SYSDATE);
1 row inserted
SQL> COMMIT;
Commit complete
ALTER TABLE GH_LIST_EXAMPLE ADD PARTITION LIST_04 VALUES('H')
ORA-14323: cannot add partition when DEFAULT partition exists
FQB.sql
