ORACLE性能优化之表分区

最新推荐文章于 2024-09-13 07:20:36 发布

置顶 tom成

最新推荐文章于 2024-09-13 07:20:36 发布

阅读量1.1k

点赞数 3

分类专栏： oracle 文章标签： oracle 性能优化数据库表分区分区表

本文链接：https://blog.csdn.net/ssc96y9gh86/article/details/78730565

版权

oracle 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

ORACLE性能优化之表分区

一、概念

表分区一般指分区表，将表中的数据在物理上存放到多个“表空间”(物理文件上)的这个过程称为表分区。

二、什么时候用？

在构建千兆字节数据系统或超高可用性系统时，当表数据量大小达到2G时推荐使用表分区。
2G：ORACLE官方文档给出的数字，这也是常见的一道面试题。

三、表分区的几种类型以及操作方法

1.范围分区

1.1概念

范围分区将数据基于某个字段值的范围映射到每一个分区。

1.2 使用规则

前提:创建分区要和创建表的语句一块用
1)每一个分区都必须有一个VALUES LESS THEN子句，它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。
2)在最高的分区中，MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，也可以理解为高于任何分区中指定的VALUE LESS THEN的值，同时包括空值。
3)语法上请注意：) 这之间不能有换行符PARTITION BY RANGE
示例1：假设有一个CUSTOMER表，表中有数据200000行，我们将此表通过CUSTOMER_ID进行分区，每个分区存储100000行，我们将每个分区保存到单独的表空间中。

CREATE TABLE CUSTOMER 
(
CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, 
FIRST_NAME VARCHAR2(30) NOT NULL, 
LAST_NAME VARCHAR2(30) NOT NULL, 
PHONE VARCHAR2(15) NOT NULL, 
EMAIL VARCHAR2(80), 
STATUS CHAR(1) 
) 
PARTITION BY RANGE (CUSTOMER_ID) 
( 
PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE TEST_SPACE1, 
PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE TEST_SPACE2 
);

验证上限值：该值存放在CUS_PART2

insert into CUSTOMER values(100000, ‘sc’, ‘s’, ‘12312121222’, ‘sasd@sds.cn’, ‘y’);

示例2：按时间划分

CREATE TABLE ORDER_ACTIVITIES
(
    ORDER_ID      NUMBER(7) NOT NULL,
    ORDER_DATE    DATE,
    TOTAL_AMOUNT  NUMBER,
    CUSTOTMER_ID  NUMBER(7),
    PAID           CHAR(1)
)
PARTITION BY RANGE (ORDER_DATE)
(
PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE('01-05-2017','DD-mm-YYYY')) TABLESPACE TEST_SPACE1,
PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-09-2017','DD-mm-YYYY')) TABLESPACE TEST_SPACE2,
PARTITION ORD_ACT_PART03 VALUES LESS THAN (TO_DATE('31-12-2017','DD-mm-YYYY')) TABLESPACE TEST_SPACE3
);

示例3：MAXVALUE

CREATE TABLE RangeTable
(
  idd     INT PRIMARY KEY ,
  iNAME   VARCHAR(10),
  grade   INT 
)
PARTITION  BY  RANGE (grade)
(
 PARTITION  part1 VALUES  LESS  THAN (1000) TABLESPACE  TEST_SPACE1,
 PARTITION  part2 VALUES  LESS  THAN (MAXVALUE) TABLESPACE  TEST_SPACE2
);

2.列表分区

该分区的特点是某列的值只有几个，基于这样的特点我们可以采用列表分区。
示例1：

CREATE TABLE PROBLEM_TICKETS
(
    PROBLEM_ID   NUMBER(7) NOT NULL PRIMARY KEY,
    DESCRIPTION  VARCHAR2(2000),
    CUSTOMER_ID  NUMBER(7) NOT NULL,
    DATE_ENTERED DATE NOT NULL,
    STATUS       VARCHAR2(20)
)
PARTITION BY LIST (STATUS)
(
      PARTITION PROB_ACTIVE   VALUES ('ACTIVE') TABLESPACE TEST_SPACE1,
      PARTITION PROB_INACTIVE  VALUES ('INACTIVE') TABLESPACE TEST_SPACE2
);

插入实验值：

insert into PROBLEM_TICKETS values(11, 'sssssdsdsd', 212, to_date('2017-11-20','yyyy-mm-dd'), 'ACTIVE');
insert into PROBLEM_TICKETS values(12, 'sssssdsdsd', 212, to_date('2017-11-20','yyyy-mm-dd'), 'INACTIVE');

列表外的其他值插入会报错：--ORA-14400：插入的分区关键字未映射到任何分区

insert into PROBLEM_TICKETS values(11, 'sssssdsdsd', 212, to_date('2017-11-20','yyyy-mm-dd'), 'ACTIVE2');

3.散列分区

这类分区是在列值上使用散列算法，以确定将行放入哪个分区中。当列的值没有合适的条件时，建议使用散列分区。
散列分区为通过指定分区编号来均匀分布数据的一种分区类型，通过在I/O设备上进行散列分区，使得这些分区大小一致。
例1：

CREATE TABLE HASH_TABLE
(
  COL NUMBER(8),
  INF VARCHAR2(100)
)
PARTITION BY HASH (COL)
(
  PARTITION PART01 TABLESPACE TEST_SPACE1,
  PARTITION PART02 TABLESPACE TEST_SPACE2,
  PARTITION PART03 TABLESPACE TEST_SPACE3,
  PARTITION PART04 TABLESPACE TEST_SPACE4
);

简写法：

PARTITION BY HASH (COL) PARTITIONS 4 STORE IN (TEST_SPACE1, TEST_SPACE2, TEST_SPACE3, TEST_SPACE4);

注意：该简写无需指定分区名称，分区名系统自动生成：SYS_PXXX
hash分区最主要的机制是根据hash算法来计算具体某条纪录应该插入到哪个分区中,
hash算法中最重要的是hash函数，Oracle中如果你要使用hash分区，只需指定分区的数量即可。
建议分区的数量采用2的n次方，这样可以使得各个分区间数据分布更加均匀。
备注：和hash算法的原理有关
官方文档：create multiple partitions and subparttions for each parttion
that is a power of two.For example,2,4,8,16,32,64,128,and so on.

4.组合(composite)范围列表分区

这种分区是基于范围分区和列表分区，表首先按某列进行范围分区，
然后再按某列进行列表分区，分区之中的分区被称为子分区。
示例1：

CREATE TABLE SALES
(
PRODUCT_ID VARCHAR2(5),
SALES_DATE DATE,
SALES_COST NUMBER(10),
STATUS VARCHAR2(20)
)
PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS)
(
   PARTITION P1 VALUES LESS THAN(TO_DATE('2017-06-01','YYYY-MM-DD'))TABLESPACE TEST_SPACE1
          (
              SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE TEST_SPACE3,
              SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE TEST_SPACE4
          ),
   PARTITION P2 VALUES LESS THAN (TO_DATE('2017-12-31','YYYY-MM-DD')) TABLESPACE TEST_SPACE2
          (
              SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE TEST_SPACE5,
              SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE TEST_SPACE6
          )
);

5.复合范围散列分区

这种分区是基于范围分区和散列分区，表首先按某列进行范围分区，然后再按某列进行散列分区。

create table dinya_test
 (
 transaction_id   number primary key,
 item_id        number(8) not null,
 item_description varchar2(300),
 transaction_date date
 )
 partition by range(transaction_date)subpartition by hash(transaction_id)  subpartitions 4 store in 
(TEST_SPACE4,TEST_SPACE5,TEST_SPACE6,TEST_SPACE7)
 (
     partition part_01 values less than(to_date('2017-01-01','yyyy-mm-dd')),
     partition part_02 values less than(to_date('2018-01-01','yyyy-mm-dd')),
     partition part_03 values less than(maxvalue)
 );

四.表分区的维护

注意点：
1.在对表分区进行DDL操作时，会使分区全局索引失效，结尾加上update global indexes重建全局索引。忘记请追加alter index PART_KEY rebuild online;
可以通过:select index_name,status from user_indexes;查看到UNUSABLE状态的索引(表格有数据时才能体现)
2.表分区的维护对象为分区表，不能是普通表

4.1添加分区

以下代码给SALES表添加了一个P3分区(可以指定表空间)

ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2018-06-01','YYYY-MM-DD'));

注意：以上添加的分区界限应该高于最后一个分区界限。
以下代码给SALES表新增P3分区和一个P3SUB1子分区

ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2018-06-01','YYYY-MM-DD'))(
      SUBPARTITION P3SUB1 VALUES('COMPLETE')
);

建议：子分区和分区一起建立，如果先建立分区再建立子分区，需要先删除默认子分区，再增加子分区

4.2删除分区

以下代码删除了P3表分区：

ALTER TABLE SALES DROP PARTITION P3;

在以下代码删除了P2SUB2子分区：

ALTER TABLE SALES DROP SUBPARTITION P2SUB2;

注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。（drop table）

4.3截断分区

截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。当表中即使只有一个分区时，也可以截断该分区。

通过以下代码截断分区：

ALTER TABLE SALES TRUNCATE PARTITION P2;

通过以下代码截断子分区：

ALTER TABLE SALES TRUNCATE SUBPARTITION P1SUB1;

4.4合并分区

合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不能将分区合并到界限较低的分区。
以下代码实现了P1 P2分区的合并：

ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2;

4.5拆分分区

拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。

ALTER TABLE SALES SPLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);

4.6接合分区(coalesce)

结合分区是将散列分区中的数据接合到其它分区中，当散列分区中的数据比较大时，可以增加散列分区，然后进行接合，值得注意的是，接合分区只能用于散列分区中。
通过以下代码进行接合分区：

ALTER TABLE HASH_TABLE coalesce PARTITION;

4.7重命名表分区

以下代码将P21更改为P2

ALTER TABLE SALES RENAME PARTITION P22 TO P2;

4.8相关查询

4.8.1跨分区查询

select count(*) from
(
select count(*) cn from CUSTOMER PARTITION (CUS_PART1)
union all
select count(*) cn from CUSTOMER PARTITION (CUS_PART2)
);

-- select 1和select 2的结果加起来，并且不处理重复项。

4.8.2查询表上有多少分区

SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='CUSTOMER';

4.8.3查询索引信息

select object_name,object_type,tablespace_name,sum(value)
from v$segment_statistics
where statistic_name IN ('physical reads','physical write','logical reads')and object_type='INDEX'
group by object_name,object_type,tablespace_name
order by object_name desc

4.8.4显示数据库所有分区表的信息 (SYSDBA)

select * from DBA_PART_TABLES;

4.8.5显示当前用户可访问的所有分区表信息

select * from ALL_PART_TABLES;

4.8.6显示当前用户所有分区表的信息

select * from USER_PART_TABLES;

4.8.7显示表分区信息(显示数据库所有分区表的详细分区信息)

select * from DBA_TAB_PARTITIONS;

4.8.8显示当前用户可访问的所有分区表的详细分区信息

select * from ALL_TAB_PARTITIONS;

4.8.9显示当前用户所有分区表的详细分区信息

select * from USER_TAB_PARTITIONS;

4.8.10显示子分区信息(显示数据库所有组合分区表的子分区信息 )

select * from DBA_TAB_SUBPARTITIONS;

4.8.11显示当前用户可访问的所有组合分区表的子分区信息

select * from ALL_TAB_SUBPARTITIONS;

4.8.12显示当前用户所有组合分区表的子分区信息

select * from USER_TAB_SUBPARTITIONS;

4.8.13显示分区列：显示数据库所有分区表的分区列信息

select * from DBA_PART_KEY_COLUMNS;

4.8.14显示当前用户可访问的所有分区表的分区列信息

select * from ALL_PART_KEY_COLUMNS;

4.8.15显示当前用户所有分区表的分区列信息

select * from USER_PART_KEY_COLUMNS;

4.8.16显示子分区列显示数据库所有分区表的子分区列信息

select * from DBA_SUBPART_KEY_COLUMNS;

4.8.17显示当前用户可访问的所有分区表的子分区列信息

select * from ALL_SUBPART_KEY_COLUMNS;

4.8.18显示当前用户所有分区表的子分区列信息

select * from USER_SUBPART_KEY_COLUMNS;

4.8.19查询出oracle数据库中所有的的分区表

select * from user_tables a where a.partitioned='YES';

4.8.20 查询分区数据

select * from 表名 partition (分区名称);

五.在线重定义（普通表转分区表）

1.在线重定义表具有以下功能：

修改表的存储参数；
可以将表转移到其他表空间；
增加并行查询选项；
增加或删除分区；
重建表以减少碎片；
将堆表改为索引组织表或相反的操作；
增加或删除一个列。
调用DBMS_REDEFINITION包需要EXECUTE_CATALOG_ROLE角色，除此之外，还需要CREATE ANY TABLE、ALTER ANY TABLE、DROP ANY TABLE、LOCK ANY TABLE和SELECT ANY TABLE的权限。

2.在线重定义的大致操作流程如下：

（1）创建基础表A，如果存在，就不需要操作
（2）创建临时的分区表B
（3）开始重定义，将基表A的数据导入临时分区表B
（4）结束重定义，此时在DB的 Name Directory里，已经将2个表进行了交换。即此时基表A成了分区表，我们创建的临时分区表B 成了普通表。此时我们可以删除我们创建的临时表B。
示例：
a. 创建基本表和索引（重定义的表必须有主键）

create table unpartTAB(
  aid number,siteid number,vdate date,seid number,sename varchar2(4000),swordnum number,pv number,vt number,uv number,ip number,
  stime number,atime number,ltime number,jump number,jump_atime number,time_month number
);

alter table unpartTAB add constraint unpart_KEY primary key (aid);

b. 收集表的统计信息（普通用户权限不足使用的SYSDBA用户）

exec dbms_stats.gather_table_stats('test','unpartTAB', cascade => true);

c. 创建临时分区表

create table partTAB(
  aid number,siteid number,vdate date,seid number,sename varchar2(4000),swordnum number,pv number,vt number,uv number,ip number,
  stime number,atime number,ltime number,jump number,jump_atime number,time_month number
)
partition by list(time_month)(
    partition p1 values(1) tablespace TESTSPACE1,
    partition p2 values(2) tablespace TESTSPACE1,
    partition p3 values(3) tablespace TESTSPACE1,
    partition p4 values(4) tablespace TESTSPACE1,
    partition p5 values(5) tablespace TESTSPACE1,
    partition p6 values(6) tablespace TESTSPACE1,
    partition p7 values(7) tablespace TESTSPACE1,
    partition p8 values(8) tablespace TESTSPACE1,
    partition p9 values(9) tablespace TESTSPACE1,
    partition p10 values(10) tablespace TESTSPACE1,
    partition p11 values(11) tablespace TESTSPACE1,
    partition p12 values(12) tablespace TESTSPACE1
);

普通表和临时分区表都必须有主键，不然会报错

alter table partTAB add constraint part_KEY primary key (aid);

d. 进行重定义操作
d1. 检查重定义的合理性

exec dbms_redefinition.can_redef_table('test', 'unpartTAB');

d2. 如果d1没有问题，开始重定义，这个过程可能要等一会
这里要注意：如果分区表和原表列名相同，可以用如下方式进行：

EXEC DBMS_REDEFINITION.start_redef_table(uname =>'test',orig_table =>'unpartTAB',int_table =>'partTAB');

如果分区表的列名和原表不一致，那么在开始重定义的时候，需要重新指定
映射关系：(注意写上主键aid aid 对应的映射关系为siteid site)

EXEC DBMS_REDEFINITION.START_REDEF_TABLE('test','unpartTAB','partTAB','aid aid,siteid site',dbms_REDEFINITION.CONS_USE_PK);

这一步操作结束后，数据就已经同步到这个临时的分区表partTAB里来了。
d3. 同步新表：sync_interim_table的目的是为了缩短finish时锁定表的时间。

exec DBMS_REDEFINITION.sync_interim_table(uname =>'test',orig_table =>'unpartTAB',int_table =>'partTAB');

d4. 收集新表的统计信息

exec dbms_stats.gather_table_stats('test', 'partTAB', cascade => true);

d5. 结束重定义

exec dbms_redefinition.finish_redef_table(uname => 'test',orig_table =>'unpartTAB',int_table =>'partTAB');

结束重定义的意义：
基表unpartTAB和临时分区表partTAB进行了交换。此时临时分区表
partTAB成了普通表，我们的基表unpartTAB成了分区表。
我们在重定义的时候，基表unpartTAB是可以进行DML操作的。只有在2个表进行切换的时候会有短暂的锁表。
d6. 删除临时表

drop table test.partTAB cascade constraints purge;

d7. 重命名索引

alter index PART_KEY rename to UNPART_KEY;

六.Oracle 11g 新特性（表分区方面）

6.1、引用分区（Reference Partition）

概念：处理父子表对等分区的问题，以这种方式处理分区，父表与子表的分区是一对一关系。

6.2、间隔分区（Interval Partition）

概念：以一个区间分区表为“起点”，并在定义中增加了一个规则（即间隔），使数据库知道将来如何增加分区。

6.3、虚拟列分区（Partitioning Virtual Columns）

概念：在表中引入了虚拟列，根据虚拟列的值进行分区操作。
虚拟列：是一个表达式，在运行时计算，不存储在数据库中，不能更新虚拟列的值。

6.4、系统分区（System Partition）

概念：不需要指定分区列，完全根据程序来控制数据存储在那个分区中。

6.5.拓展组合分区（Extended Composite Partitioning）

概念：同复合分区。
新增4种复合分区：RANGE-RANGE、LIST-RANGE、LIST-HASH和LIST-LIST。

七.表分区查询性能优化演示

在数据量达到1000M以上的时候，普通表的查询时间是27秒左右，分区表的查询时间为4秒左右。

--普通表ws_se
create table ws_se(
  aid number,siteid number,vdate date,seid number,sename varchar2(4000),swordnum number,pv number,vt number,uv number,ip number,
  stime number,atime number,ltime number,jump number,jump_atime number,time_month number
)
--分区表ws_se2
create table ws_se2(
  aid number,siteid number,vdate date,seid number,sename varchar2(4000),swordnum number,pv number,vt number,uv number,ip number,
  stime number,atime number,ltime number,jump number,jump_atime number,time_month number
)
partition by list(time_month)(
    partition p1 values(1) tablespace TEST_SPACE1,
    partition p2 values(2) tablespace TEST_SPACE2,
    partition p3 values(3) tablespace TEST_SPACE3,
    partition p4 values(4) tablespace TEST_SPACE4,
    partition p5 values(5) tablespace TEST_SPACE5,
    partition p6 values(6) tablespace TEST_SPACE6,
    partition p7 values(7) tablespace TEST_SPACE7,
    partition p8 values(8) tablespace TEST_SPACE8,
    partition p9 values(9) tablespace TEST_SPACE9,
    partition p10 values(10) tablespace TESTSPACE,
    partition p11 values(11) tablespace TESTSPACE,
    partition p12 values(12) tablespace TESTSPACE
);
--制造数据
alter table ws_se nologging PARALLEL 4;--设置：不打印日志，并行度4
insert  /*+APPEND*/into ws_se3
  (aid,
   siteid,
   vdate,
   seid,
   sename,
   swordnum,
   pv,
   uv,
   vt,
   ip,
   stime,
   atime,
   ltime,
   jump,
   jump_atime,
   time_month)
select rownum,
       TRUNC(DBMS_RANDOM.VALUE(0, 300)),
       TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2455198, 2455198 + 364)), 'J'),
       TRUNC(DBMS_RANDOM.VALUE(0, 10)),
       DBMS_RANDOM.STRING('A', 10),
       TRUNC(DBMS_RANDOM.VALUE(0, 100)),
       TRUNC(DBMS_RANDOM.VALUE(0, 50)),
       TRUNC(DBMS_RANDOM.VALUE(0, 50)),
       TRUNC(DBMS_RANDOM.VALUE(0, 50)),
       TRUNC(DBMS_RANDOM.VALUE(0, 50)),
       TRUNC(DBMS_RANDOM.VALUE(0, 30)),
       TRUNC(DBMS_RANDOM.VALUE(0, 30)),
       TRUNC(DBMS_RANDOM.VALUE(0, 30)),
       TRUNC(DBMS_RANDOM.VALUE(0, 30)),
       TRUNC(DBMS_RANDOM.VALUE(0, 30)),
       TRUNC(DBMS_RANDOM.VALUE(1, 12))
from xmltable('1 to 16000000');--更大的数据量采用此方式（Oracle 10g支持）
--from dual  connect by level<200000; 此循环connect by level有上限,如果超出上限,系统会报,"connect by level"内存不足


--查询表格数据实际占用物理空间大小
--先手动分析
analyze table WS_SE2 compute statistics;
--查询
select num_rows * avg_row_len/1024/1024/1024 "GB" 
from user_tables 
where table_name = 'WS_SE';


--查询效率比较 注意sename的值和time_month的值是随机的，请自行查询表数据输入
select * from ws_se t where t.sename = 'EryZlPisHD' and t.time_month=8;
select * from ws_se2 t where t.sename = 'FocufArOEF' and t.time_month=7;