分区技术分而治之,对于hash分区能够有效缓解热点块问题(提高index range成本)
ORACLE 分区技术(对于unique local需要包含分区键)
分区技术可以将大表、大索引分解为更小更易管理的块,这些块被称为分区,通过分区技术可以有效的解决大表、大索引带来的问题,在VLDB环境中,分区表有助于提高数据库的可用性和可维护性。将一个分区表划分成几个易于管理的称为’分区’的部分,分区可以进一步化成分子分区。分区表上的索引可能是未分区的。可以使用分区表相同的方式或不同的方式对它们进行分区。
分区表还可以改善数据库性能:可以使用并行操作来访问分区表的每个分区,一个表的每个分区可以并且应该存储在自己的表空间中,分区的其他属性如存储属性可以不同,但是每个分区的列数据类型和约束必须相同,即表级别的属性每个分区相同,分区级属性可以不同。
每个分区存储在不同的表空间及其他优点汇总如下:
- 如果一个表空间已损坏,减少在多个分区数据损坏的可能性。
- 每个分区可以独立的备份与恢复。
单个分区出现问题,不会影响其他表分区的应用,只是涉及该分区数据时会报错,例如表A 分区为1、2、3,其中分区1损坏。
对于应用程序SELECT * from A不影响数据查询
Select * FROM A partition(1)影响查询。
分区对程序是透明的,并且利用分区不需要修改sql语句。在某些情况下制定分区是有利的。
语法为SELECT * FROM table_name partit(partitION_name)
SELECT * FROM table_name subpartit(partitION_name)
- 分区类型及创建范例
1.1范围分区(range)
CREATE TABLE clg_range_table2
(req_num NUMBER NOT NULL,
req_date DATE NOT NULL,
status VARCHAR2(2) not NULL
)
PARTITION BY RANGE(req_date)
(PARTITION p1 VALUES LESS THAN(to_date('20140101','yyyymmdd')) TABLESPACE p1,
PARTITION p2 VALUES LESS THAN (to_date('20140201','yyyymmdd')) TABLESPACE p2,
PARTITION p3 VALUES LESS THAN (to_date('20140301','yyyymmdd')) TABLESPACE p3,
PARTITION p4 VALUES LESS THAN (MAXVALUE) TABLESPACE p4
)
1.2 列表分区(list)
CREATE TABLE clg_list_table2
(req_num NUMBER NOT NULL,
req_date DATE NOT NULL,
status VARCHAR2(2) not NULL
)
PARTITION BY list (req_num)
(PARTITION p1 VALUES (1,2,3,4)TABLESPACE p1,
PARTITION p2 VALUES (5,6,7,8)TABLESPACE p2,
PARTITION p3 VALUES (11,23,122,2222)TABLESPACE p3,
PARTITION p4 VALUES (default)TABLESPACE p4
)
1.3 哈希分区(hash) (最好创建2的幂数个)
CREATE TABLE clg_hash_table2
(req_num NUMBER NOT NULL,
req_date DATE NOT NULL,
status VARCHAR2(2) not NULL
)
PARTITION BY hash (status)
(PARTITION p1 TABLESPACE p1,
PARTITION p2 TABLESPACE p2,
PARTITION p3 TABLESPACE p3,
PARTITION p4 TABLESPACE p4
)
或者
partitions 7
Store in(users01,users02,users03,users04);
create table hashpp partition by hash(object_id) partitions 16 store in (users,system,sysaux) as select * from dba_objects;
SQL> select table_name,partition_name from user_tab_partitions where table_NAme='HASHP';
TABLE_NAME PARTITION_NAME
---------------------------------------- --------------------------------------------------
HASHP SYS_P81
HASHP SYS_P82
HASHP SYS_P83
HASHP SYS_P84
HASHP SYS_P85
HASHP SYS_P86
HASHP SYS_P87
HASHP SYS_P88
HASHP SYS_P89
HASHP SYS_P90
HASHP SYS_P91
TABLE_NAME PARTITION_NAME
---------------------------------------- --------------------------------------------------
HASHP SYS_P92
HASHP SYS_P93
HASHP SYS_P94
HASHP SYS_P95
HASHP SYS_P96
已选择16行。
SQL> select table_name,partition_name from user_tab_partitions where table_NAme='HASHPP';
TABLE_NAME PARTITION_NAME
---------------------------------------- --------------------------------------------------
HASHPP SYS_P97
HASHPP SYS_P98
HASHPP SYS_P99
HASHPP SYS_P100
HASHPP SYS_P101
HASHPP SYS_P102
HASHPP SYS_P103
HASHPP SYS_P104
HASHPP SYS_P105
HASHPP SYS_P106
HASHPP SYS_P107
TABLE_NAME PARTITION_NAME
---------------------------------------- --------------------------------------------------
HASHPP SYS_P108
HASHPP SYS_P109
HASHPP SYS_P110
HASHPP SYS_P111
HASHPP SYS_P112
已选择16行。
ALTER TABLE HASHPP ADD PARTITION;
当不知道如何划分一个分区,但知道需要散列保存时,请使用散列分区
1.4组合分区
11g之前
Range-list
创建范例:
Create table clg_range_list_tb
(num number,
Location varchar2(10),
Status varchar2(2)
)
Partition by range(date)
Subpartition by list(location)
(partition P1 values less than(100)
(subpartition s_p1 values(‘天津’,’北京’) tablespace P1,
Subpartition s_p2 values(default) tablespace P2
) ,
Partition P2 values(maxvalue) tablespace P1
)
Range-range(同上可类似书写)
Range-hash(同上可类似书写)
11g新增形式
list-hash
list-range
list-list
11g新增其他分区形式
1.5间隔分区
间隔分区自动创建新的范围分区。
使用引用分区
引用分区利用表之间的父—子关系来优化分区特征,即父表分区特征被子表继承。
子表使用partition by reference(外键名称)关键段创建分区子表
1.5.1 date型间隔分区测试
- 1 DAY
oracle自动分区 (时间 数字)
按照时间,每天分区
create table p_inter_day (id number,createtime date)
partition by range(createtime) interval(numtodsinterval(1,'day')) store in (users)
(
partition test_p_p1 values less than(to_date('20150630','yyyymmdd'))
);
CREATE TABLE purchase_interval PARTITION BY RANGE (time_id) INTERVAL
(NUMTOYMINTERVAL(1,'month')) STORE IN (tbs1,tbs2,tbs3) ( PARTITION p1 VALUES LESS
THAN(TO_DATE('1-1-2005', 'dd-mm-yyyy')), PARTITION p2 VALUES LESS THAN(TO_DATE('1-1- 2007',
'dd-mm-yyyy'))) AS SELECT * FROM purchases WHERE time_id < TO_DATE('1-1-2007','dd-mm-yyyy');create index index_test_p_id1 on p_inter_day (id) local;
create index index_test_p_createtime1 on p_inter_day (createtime) local;
- 2 YEAR
create table p_inter_year (id number,createtime date)
partition by range(createtime) interval(numtoyminterval(1,'year')) store in (users, BTF, BIGTBS01, OGG, ORACLE)
(
partition test_p_p1 values less than(to_date('20150630','yyyymmdd'))
);
create index index_test_p_id2 on p_inter_year (id) local;
create index index_test_p_createtime2 on p_inter_year (createtime) local;
- 3 MONTH
create table p_inter_month(id number,createtime date)
partition by range(createtime) interval(numtoyminterval(1,'month')) store in (users, BTF, BIGTBS01, OGG, ORACLE)
(
partition test_p_p1 values less than(to_date('20150630','yyyymmdd'))
);
create index index_test_p_id3 on p_inter_month (id) local;
create index index_test_p_createtime3 on p_inter_month (createtime) local;
1.5.2 number型间隔分区测试
按照数字,2000一个分区
create table p_inter_num1(id number,createtime date)
partition by range(id) interval(2000) store in (users,system )
(
partition test_p_p1 values less than(2000) tablespace users
);
create index index_test_p_id_num1 on p_inter_num1 (id) local;
create index index_test_p_createtime_num1 on p_inter_num1 (createtime) local;
--插入测试数据
declare
i number :=0;
begin
select 0 into i from dual;
loop
i:=i+1;
exit when i>=2000;
insert into p_inter_num1 select dbms_random.value(1,8000),sysdate+dbms_random.value(1,10) from dual;
commit;
end loop;
end;
/
---验证
col table_owner for a10
col table_name for a10
col partition_name for a10
col high_value for a10
col tablesapce_name for a20
set lines 100
select table_owner,table_name,partition_name,high_value,tablespace_name from dba_tab_partitions where table_name=upper('p_inter_num');
1.6使用应用控制分区(系统分区不需要分区键)
范例:
Create table sytem_partition_tbs
(id number,
Item_data varchar2(20)
)
PARTITION BY SYSTEM
(PARTITION P1 TABLESPACE P1,
PARTITION P2 TABLESPACE P2)
;
###在插入数据时要制定分区名称进行插入数据,理解为不需要分区键,根据应用逻辑插入数据到分区内,进行存储。####
1.7 使用虚拟列分区
创建范例
Create table a
(A NUMBER,
B NUMBER,
C AS (A*B)
)
PARTITION BY RANGE (C)
(分区部分) 注意虚拟列存在时候insert操作。
Insert into a(a,b) select 1,1 from dual c列自动计算.
压缩的分区表
分区使用压缩属性。
create table pval (id1 number,id2 number,id3 number generated always as (trunc(id1+id2))) partition by range(id3) (partition p1 values less than (2000), partition p2 values less than (maxvalue));
insert into pval(id1,id2) select object_id,object_id from dba_objects; |
1.8范围哈希分区
Create table orders(ordid number,acct_no number(5),cust_name char(30),orderdate date)
partition by range(order date)
subpartition by hash subpartitions 9 store in(users01,users02,users03,users04)
(partition p1 values less than (to_date(‘’20150101),’yyyymmdd’),
1.9 REF分区
CREATE TABLE purchase_orders ( po_id NUMBER(4), po_date TIMESTAMP, supplier_id NUMBER(6), po_total NUMBER(8,2), CONSTRAINT order_pk PRIMARY KEY(po_id)) PARTITION BY RANGE(po_date) ( PARTITION Q1 VALUES LESS THAN (TO_DATE('1-‐apr-‐2007','d-‐mon-‐yyyy'), PARTITION Q2 VALUES LESS THAN (TO_DATE('1-‐jul-‐2007','d-‐mon-‐yyyy'), PARTITION Q3 VALUES LESS THAN (TO_DATE('1-‐oct-‐2007','d-‐mon-‐yyyy'), PARTITION Q4 VALUES LESS THAN (TO_DATE('1-‐jan-‐2008','d-‐mon-‐yyyy')); CREATE TABLE purchase_order_items ( po_id NUMBER(4) NOT NULL, product_id NUMBER(6) NOT NULL, unit_price NUMBER(8,2), quantity NUMBER(8), CONSTRAINT po_items_fk FOREIGN KEY (po_id) REFERENCES purchase_orders(po_id)) PARTITION BY REFERENCE(po_items_fk); |
1.9 系统分区
CREATE TABLE PART_SYSTEM
(ID NUMBER,
name varchar2(19)
)
partition by system
(partition p1 tablespace users,
partition p2 tablespace users,
partition p3 tablespace users,
partition p4 tablespace users,
partition p5 tablespace users,
partition p6 tablespace users,
partition p7 tablespace users
)
ALTER TABLE PART_SYSTEM MODIFY NAME VARCHAR2(199);
SELECT * FROM PART_SYSTEM;
INSERT INTO PART_SYSTEM PARTITION(P1) SELECT OBJECT_ID,OBJECT_NAME FROM DBA_OBJECTS WHERE ROWNUM<100;
DELETE FROM PART_SYSTEM;
UPDATE PART_SYSTEM SET ID=1;
CREATE UNIQUE INDEX IDX1 ON PART_SYSTEM(ID) LOCAL; --报错
CREATE INDEX IDX1 ON PART_SYSTEM(ID) LOCAL;
- 分区表维护
2.1 添加分区
Alter table aaa add partition p1 values less than /values/直接指定表空间。
<注意在列表分区中存在default分区则需要先删除default分区在添加新的分区>
2.2 删除分区
Alter table aaa drop partition p2
2.3 移动分区
Alter table aaa move partition P1 TABLESPACE NEW_TBS
2.4 截断分区
Alter table aaa truncate partition P1
2.5 拆分分区
Alter table aaaa split partition p1 at(中间值) into (partition P1_01 tablespace O1, partition p1_02 tablespace users);
2.6 合并分区
Alter table aaa merge partitions P1,P2 into partition p3
2.7 交换分区
Alter table aaa exchange partition p1 with table bbbbb;
- 分区索引
分区索引类型主要包括本地分区索引和全局分区索引,本地分区索引分区前缀和非前缀本地分区索引,主要区别为索引键值是否包含分区键,包含则为前缀,不包含则为非前缀,
默认情况下本地索引分区方式和分区表一致,全局索引和表分区没有关系。
范例创建语句为
Create index ind_list on table(COLUMN1) local;
Create index inx_range on table(AAA) GLOBAL partition by 按分区类型书写;
标注:全局索引在对分区表进行维护时会造成无效,需要进行rebuild重建。
本地分区索引自动维护。
重建分区分区索引语句范例:
Alter index index_name rebuild ;
Alter index index_name partition/subpartition p_name ;
3.1创建local指定新表空间
---创建本地分区索引,并给指定分区指定相应的表空间
CREATE INDEX "NMOSDB"."IX2_ALARM_CLR" ON "NMOSDB"."TFA_ALARM_CLR" ("EVENT_TIME", "C_FP0", "C_FP1", "C_FP2", "C_FP3")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT) LOCAL
(PARTITION "TFA_ALARM_CLR11"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
TABLESPACE "CLRDBS07" ,
PARTITION "TFA_ALARM_CLR12"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
TABLESPACE "CLRDBS08" ,
PARTITION "TFA_ALARM_CLR13"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
TABLESPACE "CLRDBS09" ,
PARTITION "TFA_ALARM_CLR0"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
TABLESPACE "CLRDBS10" ,
PARTITION "TFA_ALARM_CLR1"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
TABLESPACE "CLRDBS11" ,
PARTITION "TFA_ALARM_CLR2"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
TABLESPACE "CLRDBS12" ,
PARTITION "TFA_ALARM_CLR3"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
TABLESPACE "CLRDBS13" ,
PARTITION "TFA_ALARM_CLR4"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
TABLESPACE "CLRDBS14" ,
PARTITION "TFA_ALARM_CLR5"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
TABLESPACE "CLRDBS01" ,
PARTITION "TFA_ALARM_CLR6"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
TABLESPACE "CLRDBS02" ,
PARTITION "TFA_ALARM_CLR7"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
TABLESPACE "CLRDBS03" ,
PARTITION "TFA_ALARM_CLR8"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
TABLESPACE "CLRDBS04" ,
PARTITION "TFA_ALARM_CLR9"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
TABLESPACE "CLRDBS05" ,
PARTITION "TFA_ALARM_CLR10"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
TABLESPACE "CLRDBS06" ) ;
- 主键分区更新为local
Using index local
- 普通分区表转化间隔分区表
--创建普通范围分区表 CREATE TABLE clg_range_table2 (req_num NUMBER NOT NULL, req_date DATE NOT NULL, status VARCHAR2(2) not NULL ) PARTITION BY RANGE(req_date) (PARTITION p1 VALUES LESS THAN(to_date('20140101','yyyymmdd')) TABLESPACE p1, PARTITION p2 VALUES LESS THAN (to_date('20140201','yyyymmdd')) TABLESPACE p2, PARTITION p3 VALUES LESS THAN (to_date('20140301','yyyymmdd')) TABLESPACE p3, PARTITION p4 VALUES LESS THAN (MAXVALUE) TABLESPACE p4 )
--添加表空间 create tablespace p1 datafile 'D:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\PORD\p1.dbf' size 10M; create tablespace p2 datafile 'D:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\PORD\p2.dbf' size 10M; create tablespace p3 datafile 'D:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\PORD\p3.dbf' size 10M; create tablespace p4 datafile 'D:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\PORD\p4.dbf' size 10M;
--将分区表转化为间隔分区 alter table CLG_RANGE_TABLE2 drop partition p4; alter table CLG_RANGE_TABLE2 set interval(numtodsinterval(1,'DAY')) alter table CLG_RANGE_TABLE2 set STORE IN (P1,P2,P3,P4)
--测试
INSERT INTO clg_range_table2 VALUES(20170101,SYSDATE-1,'Y'); INSERT INTO clg_range_table2 VALUES(20170101,SYSDATE-2,'Y'); INSERT INTO clg_range_table2 VALUES(20170101,SYSDATE,'Y'); COMMIT; --查询 SELECT TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME=UPPER('clg_range_table2'); --禁用interval alter table CLG_RANGE_TABLE2 set interval(); alter table CLG_RANGE_TABLE2 add partition pmax values LESS THAN (MAXVALUE) TABLESPACE p4; |
- 分区创建案例
create table niosdb.EUTRANRELATIONTDD ( int_id INTEGER, name VARCHAR2(128), object_rdn VARCHAR2(255), zh_name VARCHAR2(128), omc_id INTEGER, vendor_id INTEGER, time_stamp DATE, update_stamp DATE, confirmed INTEGER, m_int_id INTEGER, n_int_id INTEGER, m_name VARCHAR2(128), n_name VARCHAR2(128), id VARCHAR2(255), tci VARCHAR2(255), pci INTEGER, isremoveallowed INTEGER, ishoallowed INTEGER, isicicinformationsendallowed INTEGER, islballowed INTEGER, plmnidlist VARCHAR2(255), earfcn INTEGER, cellindividualoffset VARCHAR2(64), qoffsetcell VARCHAR2(64), distance NUMBER(38,8), managedelement VARCHAR2(128), enbfunction INTEGER, eutrancelltdd VARCHAR2(128), eutranrelation VARCHAR2(128) ) PARTITION BY LIST (vendor_id) Subpartition by hash(id) subpartitions 8 store in (USERS) (PARTITION P1 VALUES (7), PARTITION P2 VALUES (8), PARTITION P3 VALUES (DEFAULT) ); SQL> select table_name ,partition_name from dba_tab_partitions where TABLE_NAME='EUTRANRELATIONTDD';
TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ EUTRANRELATIONTDD P1 EUTRANRELATIONTDD P2 EUTRANRELATIONTDD P3
SQL> select partition_name,subpartition_name from dba_tab_subpartitions where table_name='EUTRANRELATIONTDD' ;
PARTITION_NAME SUBPARTITION_NAME ------------------------------ ------------------------------ P1 SYS_SUBP41 P1 SYS_SUBP42 P1 SYS_SUBP43 P1 SYS_SUBP44 P1 SYS_SUBP45 P1 SYS_SUBP46 P1 SYS_SUBP47 P1 SYS_SUBP48 P2 SYS_SUBP49 P2 SYS_SUBP50 P2 SYS_SUBP51
PARTITION_NAME SUBPARTITION_NAME ------------------------------ ------------------------------ P2 SYS_SUBP52 P2 SYS_SUBP53 P2 SYS_SUBP54 P2 SYS_SUBP55 P2 SYS_SUBP56 P3 SYS_SUBP57 P3 SYS_SUBP58 P3 SYS_SUBP59 P3 SYS_SUBP60 P3 SYS_SUBP61 P3 SYS_SUBP62
PARTITION_NAME SUBPARTITION_NAME ------------------------------ ------------------------------ P3 SYS_SUBP63 P3 SYS_SUBP64
24 rows selected
|