oracle数据库优化-ORACLE分区技术

执笔画情ora

于 2019-10-16 15:23:24 发布

阅读量525

点赞数

分类专栏： ORACLE数据库性能优化 ORACLE数据库等待事件分析

本文链接：https://blog.csdn.net/oradbm/article/details/102586277

版权

ORACLE数据库性能优化同时被 2 个专栏收录

66 篇文章 5 订阅

订阅专栏

ORACLE数据库等待事件分析

42 篇文章 1 订阅

订阅专栏

分区技术分而治之，对于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