Oracle 11g分区表性能测试（1）

最新推荐文章于 2022-04-18 16:11:07 发布

六月闻君

最新推荐文章于 2022-04-18 16:11:07 发布

阅读量363

点赞数

分类专栏： Oracle 文章标签： oracle 数据仓库数据库开发数据库架构

本文链接：https://blog.csdn.net/qq_39065491/article/details/120391428

版权

Oracle 专栏收录该内容

71 篇文章 1 订阅

订阅专栏

Oracle 11g分区表性能测试（1）

一、11g分区表

分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。从数据库管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活性。但是，从应用程序的角度来看，分区后的表与非分区表完全相同，使用 SQL DML 命令访问分区后的表时，无需任何修改。

使用分区表官方给的建议是：

a. 表大小超过2GB。

b. 表中包含历史数据，新的数据被增加到新的分区中。

（1）优点：

a.改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
b.增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用。
c.维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可。
d.均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。

（2）缺点：

分区表相关，已经存在的表没有方法可以直接转化为分区表。不过oracle提供了在线重定义表的功能。

（3）实验环境：

SQL*Plus: Release 11.2.0.4.0 Production
Copyright © 1982, 2013, Oracle. All rights reserved.

（4）分区表空间准备：

建立4个数据分区表空间，每个2G备用：

TB_PART_A，TB_PART_B,TB_PART_C,TB_PART_D

建立4个索引分区表空间，每个2G备用：

TB_PART_IND_A，TB_PART_IND_B,TB_PART_IND_C,TB_PART_IND_D

二、分区表分类：

（1）RANGE范围分区：

范围分区将数据基于范围映射到每一个分区，这个范围是在创建分区时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期。举个例子：可能会将销售数据按照月份进行分区。

当使用范围分区时，请考虑以下几个规则：

1、每一个分区都必须有一个VALUES LESS THEN子句，它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。

2、所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。

3、在最高的分区中，MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，也可以理解为高于任何分区中指定的VALUE LESS THEN的值，同时包括空值。

(2)LIST列表分区：

分区的特点是某列的值只有固定几个，基于这样的特点可以采用列表分区。

(3)HASH散列分区：

分区是在列值上使用散列算法，以确定将行放入哪个分区中。当列的值没有合适的条件时，建议使用散列分区。

散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，使得这些分区大小一致。

(4)组合分区

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

在10g中组合分区主要有两种：range-hash，range-list。11g中又增加了range-range，list-range，list-list，list-hash，并且 11g里面还支持Interval分区和虚拟列分区。注意顺序，根分区只能是range分区，子分区可以是hash分区或list分区。

基于RANGE组合分区：

range-hash，range-list，range-range

基于LIST组合分区：

list-range，list-list，list-hash

对于11g一共6种组合分区。

三、RANGE分区

（1）测试表定义

表定义说明，后不赘述。

ORDER_ID：订单ID

ORDER_DATE：订单日期

CONTRACT_ID：合同ID

CITY：所属城市

ORDER_NUM：订单数量

准备城市字典表：

CREATE TABLE TB_CITY (CITY_ID NUMBER(2),CITY_NAME VARCHAR2(20));
INSERT INTO   TB_CITY VALUES (0,'LASA');
INSERT INTO   TB_CITY VALUES (1,'BEIJING');
INSERT INTO   TB_CITY VALUES (2,'TIANJIN');
INSERT INTO   TB_CITY VALUES (3,'SHANGHAI');
INSERT INTO   TB_CITY VALUES (4,'SHENZHEN');
INSERT INTO   TB_CITY VALUES (5,'CHANGSHA');
INSERT INTO   TB_CITY VALUES (6,'CHENGDU');
INSERT INTO   TB_CITY VALUES (7,'WUHAN');
INSERT INTO   TB_CITY VALUES (8,'SUZHOU');
INSERT INTO   TB_CITY VALUES (9,'ZHENGZHOU');
INSERT INTO   TB_CITY VALUES (10,'GUANGZHOU');
INSERT INTO   TB_CITY VALUES (11,'HEFEI');
INSERT INTO   TB_CITY VALUES (12,'CHONGQING');
INSERT INTO   TB_CITY VALUES (13,'GUIYANG');
COMMIT;

按时间分区，从15年到26年的时间分区，按年度进行分区：

 CREATE TABLE tb_part_range
    (
    ORDER_ID      CHAR(30) NOT NULL,
    ORDER_DATE    DATE,
	CONTRACT_ID   CHAR(16) NOT NULL,		
	CITY          VARCHAR2(20),
	ORDER_NUM     NUMBER
    )
    PARTITION BY RANGE (ORDER_DATE)
   (
     PARTITION PART1 VALUES LESS THAN (TO_DATE('2015-01-01', 'yyyy-mm-dd')) TABLESPACE TB_PART_A,
     PARTITION PART2 VALUES LESS THAN (TO_DATE('2016-01-01', 'yyyy-mm-dd')) TABLESPACE TB_PART_B,
     PARTITION PART3 VALUES LESS THAN (TO_DATE('2017-01-01', 'yyyy-mm-dd')) TABLESPACE TB_PART_C,
     PARTITION PART4 VALUES LESS THAN (TO_DATE('2018-01-01', 'yyyy-mm-dd')) TABLESPACE TB_PART_D,
     PARTITION PART5 VALUES LESS THAN (TO_DATE('2019-01-01', 'yyyy-mm-dd')) TABLESPACE TB_PART_A,
	 PARTITION PART6 VALUES LESS THAN (TO_DATE('2020-01-01', 'yyyy-mm-dd')) TABLESPACE TB_PART_B,
	 PARTITION PART7 VALUES LESS THAN (TO_DATE('2021-01-01', 'yyyy-mm-dd')) TABLESPACE TB_PART_C,
	 PARTITION PART8 VALUES LESS THAN (TO_DATE('2022-01-01', 'yyyy-mm-dd')) TABLESPACE TB_PART_D,
	 PARTITION PART9 VALUES LESS THAN (TO_DATE('2023-01-01', 'yyyy-mm-dd')) TABLESPACE TB_PART_A,
	 PARTITION PART10 VALUES LESS THAN (TO_DATE('2024-01-01', 'yyyy-mm-dd')) TABLESPACE TB_PART_B,
	 PARTITION PART11 VALUES LESS THAN (TO_DATE('2025-01-01', 'yyyy-mm-dd')) TABLESPACE TB_PART_C,
	 PARTITION PART12 VALUES LESS THAN (TO_DATE('2026-01-01', 'yyyy-mm-dd')) TABLESPACE TB_PART_D,
	 partition PART13 values less than (maxvalue) TABLESPACE TB_PART_A
   );

（2）测试数据准备

DECLARE
  V_IMPORT_DATE DATE;
  V_BUSI_DATE   DATE;
  V_MONTH_BEGIN DATE;
  V_MONTH_END   DATE;
  V_RECORD      NUMBER;
  V_CITY        VARCHAR2(30);
  i             NUMBER;
  ID            number;

BEGIN

  /*         循环每天       */

  V_MONTH_END   := DATE '2020-10-03';
  V_MONTH_BEGIN := DATE '2015-01-01';
  V_BUSI_DATE   := V_MONTH_BEGIN;
  i             := 0;

  while i < 1000 Loop
    V_BUSI_DATE := V_MONTH_BEGIN;
  
    WHILE (V_BUSI_DATE <= V_MONTH_END) LOOP
      --取城市id
      ID := TO_NUMBER(ROUND(dbms_random.value(1, 13), 0));
      --      DBMS_OUTPUT.PUT_LINE(I);
    
      SELECT CITY_NAME INTO V_CITY FROM DH.TB_CITY WHERE CITY_ID = ID;
    
      --      DBMS_OUTPUT.PUT_LINE(V_CITY);
      INSERT INTO DH.tb_part_range
        (ORDER_ID, ORDER_DATE, CONTRACT_ID, CITY, ORDER_NUM)
      VALUES
        (DBMS_RANDOM.STRING('A', 16),
         V_BUSI_DATE,
         DBMS_RANDOM.STRING('U', 16),
         V_CITY,
         round(dbms_random.value(1, 10000)));
    
      V_BUSI_DATE := V_BUSI_DATE + 1;
    
    END LOOP; --wihle 结束
  
    COMMIT; -- 循环提交
    i := i + 1;
    --DBMS_OUTPUT.PUT_LINE('i  is :' || i);
  
  END LOOP;

END;
/

数据量210万，用于测试：

SQL> select count(*) from tb_part_range;

  COUNT(*)
----------
   2103000

Executed in 0.129 seconds

四、LIST分区

（1）测试表定义

 CREATE TABLE tb_part_list
   (
    ORDER_ID      CHAR(16) NOT NULL,
    ORDER_DATE    DATE,
	CONTRACT_ID   CHAR(16) NOT NULL,		
	CITY          VARCHAR2(20),
	ORDER_NUM     NUMBER
    )
	PARTITION BY LIST(CITY)
	(
	PARTITION part01 VALUES('BEIJING','TIANJIN','HEFEI','LASA') TABLESPACE TB_PART_A,
	PARTITION part02 VALUES('SHANGHAI','SHENZHEN','CHONGQING') TABLESPACE TB_PART_B,
	PARTITION part03 VALUES('CHANGSHA','CHENGDU','GUANGZHOU') TABLESPACE TB_PART_C,
	PARTITION part04 VALUES('WUHAN','SUZHOU','ZHENGZHOU','GUIYANG') TABLESPACE TB_PART_D
	);
	/

（2）测试数据准备

DECLARE
  V_IMPORT_DATE DATE;
  V_BUSI_DATE   DATE;
  V_MONTH_BEGIN DATE;
  V_MONTH_END   DATE;
  V_RECORD      NUMBER;
  V_CITY        VARCHAR2(30);
  i             NUMBER;
  ID            number;

BEGIN

  /*         循环每天       */

  V_MONTH_END   := DATE '2020-10-03';
  V_MONTH_BEGIN := DATE '2015-01-01';
  V_BUSI_DATE   := V_MONTH_BEGIN;
  i             := 0;

  while i < 1000 Loop
    V_BUSI_DATE := V_MONTH_BEGIN;
  
    WHILE (V_BUSI_DATE <= V_MONTH_END) LOOP
      --取城市id
      ID := TO_NUMBER(ROUND(dbms_random.value(1, 13), 0));
      --      DBMS_OUTPUT.PUT_LINE(I);
    
      SELECT CITY_NAME INTO V_CITY FROM DH.TB_CITY WHERE CITY_ID = ID;
    
      --      DBMS_OUTPUT.PUT_LINE(V_CITY);
      INSERT INTO DH.tb_part_list
        (ORDER_ID, ORDER_DATE, CONTRACT_ID, CITY, ORDER_NUM)
      VALUES
        (DBMS_RANDOM.STRING('A', 16),
         V_BUSI_DATE,
         DBMS_RANDOM.STRING('U', 16),
         V_CITY,
         round(dbms_random.value(1, 10000)));
    
      V_BUSI_DATE := V_BUSI_DATE + 1;
    
    END LOOP; --wihle 结束
  
    COMMIT; -- 循环提交
    i := i + 1;
    --DBMS_OUTPUT.PUT_LINE('i  is :' || i);
  
  END LOOP;

END;
/

五、HASH分区

（1）测试表定义

 CREATE TABLE tb_part_HASH
   (
    ORDER_ID      CHAR(16) NOT NULL,
    ORDER_DATE    DATE,
	CONTRACT_ID   CHAR(16) NOT NULL,		
	CITY          VARCHAR2(20),
	ORDER_NUM     NUMBER
    )
	partition by hash(ORDER_ID,CONTRACT_ID)
(
partition HASHpart01 tablespace TB_PART_A,
partition HASHpart02 tablespace TB_PART_B,
partition HASHpart03 tablespace TB_PART_C,
partition HASHpart04 tablespace TB_PART_D
);

（2）测试数据准备

DECLARE
  V_IMPORT_DATE DATE;
  V_BUSI_DATE   DATE;

  V_MONTH_BEGIN DATE;
  V_MONTH_END   DATE;
  V_RECORD      NUMBER;
  V_CITY        VARCHAR2(30);
  i             NUMBER;
  ID            number;

BEGIN

  /*         循环每天       */

  V_MONTH_END   := DATE '2020-10-03';
  V_MONTH_BEGIN := DATE '2015-01-01';
  V_BUSI_DATE   := V_MONTH_BEGIN;
  i             := 0;

  while i < 1000 Loop
    V_BUSI_DATE := V_MONTH_BEGIN;
  
    WHILE (V_BUSI_DATE <= V_MONTH_END) LOOP
      --取城市id
      ID := TO_NUMBER(ROUND(dbms_random.value(1, 13), 0));
      --      DBMS_OUTPUT.PUT_LINE(I);
    
      SELECT CITY_NAME INTO V_CITY FROM DH.TB_CITY WHERE CITY_ID = ID;

      --      DBMS_OUTPUT.PUT_LINE(V_CITY);
      INSERT INTO DH.tb_part_HASH
        (ORDER_ID, ORDER_DATE, CONTRACT_ID, CITY, ORDER_NUM)
      VALUES
        (DBMS_RANDOM.STRING('A', 16),
         V_BUSI_DATE,
         DBMS_RANDOM.STRING('U', 16),
         V_CITY,
         round(dbms_random.value(1, 10000)));
    
      V_BUSI_DATE := V_BUSI_DATE + 1;
    
    END LOOP; --wihle 结束
  
    COMMIT; -- 循环提交
    i := i + 1;
    --DBMS_OUTPUT.PUT_LINE('i  is :' || i);
  
  END LOOP;

END;
/

六、组合分区（待续）

详见：

https://blog.csdn.net/qq_39065491/article/details/120609021

六月闻君

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Oracle 11g分区表性能测试（1）

Oracle 11g分区表性能测试（1）一、11g分区表分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。从数据库管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活性。但是，从应用程序的角度来看，分区后的表与非分区表完全相同，使用 SQL DML 命令访问分区后的表时，无需任何修改。使用分区表官方给的建议是：a. 表大小超过2GB。b
复制链接

扫一扫