| 分区表概念 |
| 什么情况下使用分区表: 表的容量非常大,存大型数据。可以调用 的空间要比普通表多,读取和存的速度都要比普通 的表要快,数据可以归类放 根据条件不同,将不同的数据存放到不同的表空间上
分区表种类: 1、Range 按范围分 手动去分,如数值,日期
2、Hash 按hash算法分,无法控制 如:流水 用等于查询比较好,做好分为2的倍数 ,高并发时处理好 3、List 按列表分 |
| 示例:创建Range分区表 |
| create table test(a number, b number) partition by range(a) (partition p10 values less than(10) tablespace users, partition p20 values less than(20) tablespace users, partition p30 values less than(maxvalue) tablespace users);
create tablespace ts1 datafile '/home/oracle/ts01.dbf' size 10m; create tablespace ts2 datafile '/home/oracle/ts02.dbf' size 10m;
--插入数据 insert into test values(1,20); insert into test values(2,20); insert into test values(6,20); insert into test values(8,20); insert into test values(10,20); insert into test values(15,20); insert into test values(29,20); insert into test values(30,20); insert into test values(50,20); insert into test values(2000,20);
begin for i in 1..100 loop insert into test values(i,null); end loop commit; end; /
查询: select table_name,partitioned from user_tables where table_name='TEST'; TABLE_NAME PAR ---------- --- TEST YES sys@ERDB> select segment_name,partition_name,tablespace_name,segment_type from user_segments where segment_name='TEST';
SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME SEGMENT_TYPE -------------------- ------------------------------ -------------------- ------------------ TEST P10 TS1 TABLE PARTITION TEST P20 TS2 TABLE PARTITION TEST P30 USERS TABLE PARTITION sys@ERDB> select table_name,partitioning_type,subpartitioning_type,partition_count from user_part_tables where table_name='TEST';
TABLE_NAME PARTITION SUBPARTIT PARTITION_COUNT ---------- --------- --------- --------------- TEST RANGE NONE 3
select table_name , tablespace_name , high_value, partition_name from user_tab_partitions; sys@ERDB> select * from test partition(p10);
不好: 修改数据 的时候,不能跨分区去改
|
| 示例:创建Hash 分区 |
| create table test2 partition by hash(empno) (partition p10 tablespace users, partition p20 tablespace users, partition p30 tablespace users) As select * from scott.emp;
--禁止使用此方法,分区名系统自动创建,默认的表空间会存放在system表空间中 Create table test4 partition by hash(empno) partitions 5 as select * from scott.emp;
alter table test_part enable row movement; //移动分区字段 |
| 示例:创建List 分区 |
| create table test3 partition by list(deptno) (partition d10 values('10') tablespace users, partition d20 values('20','30') tablespace users, partition d30 values(default) tablespace users) As select * from scott.emp; |
| 分区索引 |
| 分区索引,也叫本地索引(local),会在分区上生成相应的索引
在分区表上建索引: sys@ERDB> create index i_emp on test3(empno); 不正确的方式 sys@ERDB> create index i_emp on test3(empno) local; --正确的方式
select segment_name,partition_name,tablespace_name,segment_type from user_segments where segment_name='I_EMP';
分区索引:global 自定义表空间创建索引: --方便管理 人员管理。 create index i_test3 on test3(deptno) global partition by range(deptno) (partition p30 values less than(30) tablespace users, partition p_max values less than (maxvalue) tablespace users) /
Range-hash Range-list |
| 示例:创建子分区 |
| drop table test2 purge; create table test_rh(deptno number,empname varchar(32),grade number) partition by range(deptno) subpartition by hash(empname) ---subpartition 3 store in (ts1,ts2,ts3) (partition p10_2 values less than(10) pctfree 10 tablespace users, partition p20_2 values less than(20) tablespace users, partition p30_2 values less than(maxvalue) (subpartition p3_s1 tablespaces users, subpartition p3_s2 tablespaces users);
sys@ERDB> select * from test_rh subpartition(p3_s1);
sys@ERDB> select table_name,partitioning_type,subpartitioning_type,def_subpartition_count,partition_count from user_part_tables where table_name='TEST_RH';
--按日期
drop table test purge; create table test89 partition by range(hiredate) (partition p10 values less than(to_date('1981-01-01','YYYY-MM-DD')) tablespace ts1, partition p20 values less than(to_date('1982-01-01','YYYY-MM-DD')) tablespace ts2, partition p30 values less than(maxvalue) tablespace users) as select * from emp;
drop table test89 purge; create table test89 partition by range(hiredate) subpartition by list(deptno) (partition p10 values less than(to_date('1981-01-01','YYYY-MM-DD')) tablespace ts1, partition p20 values less than(to_date('1982-01-01','YYYY-MM-DD')) tablespace ts2, partition p30 values less than(maxvalue) (subpartition p30_s1 values('10') tablespace ts1, subpartition p3_s2 values('20','30') tablespace ts2 )) as select * from emp;
>select segment_name,partition_name,tablespace_name,segment_type from user_segments where segment_name='TEST89'; |
| --10g只支持两种组合分区 --range-hash --range-list --组合分区没有分区段,只有子分区段 --数据物理存储在子分区段上,分区成为一个逻辑容器 CREATE TABLE composite_example ( range_key_column date, hash_key_column int, data varchar2(20) ) PARTITION BY RANGE (range_key_column) subpartition by hash(hash_key_column) subpartitions 2 ( PARTITION part_1 VALUES LESS THAN(to_date('01/01/2005','dd/mm/yyyy')) (subpartition part_1_sub_1, subpartition part_1_sub_2 ), PARTITION part_2 VALUES LESS THAN(to_date('01/01/2006','dd/mm/yyyy')) (subpartition part_2_sub_1, subpartition part_2_sub_2 ) ) CREATE TABLE composite_range_list_example ( range_key_column date, code_key_column int, data varchar2(20) ) PARTITION BY RANGE (range_key_column) subpartition by list(code_key_column) ( PARTITION part_1 VALUES LESS THAN(to_date('01/01/2005','dd/mm/yyyy')) (subpartition part_1_sub_1 values( 1, 3, 5, 7 ), subpartition part_1_sub_2 values( 2, 4, 6, 8 ) ), PARTITION part_2 VALUES LESS THAN(to_date('01/01/2006','dd/mm/yyyy')) (subpartition part_2_sub_1 values ( 1, 3 ), subpartition part_2_sub_2 values ( 5, 7 ), subpartition part_2_sub_3 values ( 2, 4, 6, 8 ) ) ) select * from user_segments; select * from user_tab_partitions; select * from user_tab_subpartitions; select * from user_tables select * from user_part_tables |
| 11G 分区 |
| --虚拟列 --虚拟列的值从其他的列计算出来的 --oracle只保存源数据 --虚拟列本身不占存储空间 create table t1 (id nubmer, name varchar2(30), create_date date, partition_month as (to_number(to_char(create_date,'MM'))) ) partition by list(partition_month) (partition p1 values(1), partition p1 values(2), partition p1 values(3)); |
| --system分区 --普通分区需要指定一个或多个分区列,并根据该列的值进行算法(range hash list)来决定一条数据具体放到哪个分区 --允许用户不指定分区列,完全根据程序来控制数据存在哪 --分区是分区,数据是数据,只不过是数据放在了我想放的分区,没有对应关系 create table t1 (id number, name vachar2(30)) partition by system (partition p1,partition p2,partition p3); insert into t1 partition (p1) values(1,'sfafd'); --system完全相同的数据可以放到不同的分区里 --split分区 拆分分区不能,拆分分区是在边界值拆分,system没有边界 --system因为没有分区列,没办法建local索引 --普通分区表不支持跨分区的修改,但system无所谓,因为就没分区 --update data01 set a=11 where a=1;修改后因为跨分区报错 --DML语句中只有INSERT语句需要指明分区名 |
| |
| --interval分区 --自动扩展分区 --range分区的增强,通过功能实现自动添加新分区,省去了不断维护分区表的工作 --有局限的,要求所有的表空间都是一致的才行,不能给单独的分区指定单独的表空间 create table t1 (a date, b number) partition by range(a) interval(numtoyminterval(1,'month')) ( partition p1 values less than(to_date('2014-01-01','yyyy-mm-dd'))); insert into t1('2014-1-1',1); insert into t1('2013-12-12',2); select * from user_segments; create table test_interval_num(vl1 date,vl2 number(12)) partiton by range(vl2) interval(100000) partition p1 values less than (10000)); |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30024909/viewspace-1353139/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30024909/viewspace-1353139/