分区表区间分区散列分区列表分区

最新推荐文章于 2024-03-13 19:13:30 发布

cuishaobo1694

最新推荐文章于 2024-03-13 19:13:30 发布

阅读量189

点赞数

文章标签：数据库

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