分区前后效率对比

--3282915记录数
------------以下均是未做索引的情况下的时间
--分区之前
SELECT COUNT(*) FROM T_ZXJC_JCSJ_FS_SSSJ_JCJ_BAK
--12.761s (首次查询时间)
--0.67s  第二次时间
SELECT MAX(JCSJ),MIN(JCSJ) FROM T_ZXJC_JCSJ_FS_SSSJ_JCJ_BAK
--2017-8-15 上午 10:13:20   --- 2016-11-28 下午 03:00:00
--10.827S
SELECT XTXH,COUNT(*) FROM T_ZXJC_JCSJ_FS_SSSJ_JCJ_BAK GROUP BY XTXH
--12.23s

--分区后
SELECT COUNT(*) FROM T_ZXJC_JCSJ_FS_SSSJ_JCJ
--13.761s (首次查询时间)
--0.405s  第二次时间
SELECT MAX(JCSJ),MIN(JCSJ) FROM T_ZXJC_JCSJ_FS_SSSJ_JCJ
--2.714s
SELECT XTXH,COUNT(*) FROM T_ZXJC_JCSJ_FS_SSSJ_JCJ GROUP BY XTXH
--3.151S

以下内容转载自:http://www.cnblogs.com/yumiko/p/6095036.html

前言

  • 本文着重阐述分区表的概念、优势,以及常用分区表的使用方法及使用过程中的注意事项
  • 对于分区表的日常维护方法,如:索引的维护、分区的合并等,将在后续文章中再详细介绍。
  • 本文涉及的相应演示,使用的数据库版本为oracle 11.2.0.4。
  • 本文常用分区表的使用方法,主要以较为常规的范围分区、列表分区、hash分区以及组合分区为主。
  • 本文主要涉及的数据字典视图包括XX_TAB_PARTITIONS、XX_TAB_SUBPARTITIONS以及XX_PART_TABLES。 
  • 注意示例中,使用红色标记的说明。

1、分区表的概述 

  • 分区表就是通过使用分区技术,将一张大表,拆分成多个表分区(独立的segment),从而提升数据访问的性能,以及日常的可维护性。
  • 分区表中,每个分区的逻辑结构必须相同。如:列名、数据类型。
  • 分区表中,每个分区的物理存储参数可以不同。如:各个分区所在的表空间。
  • 对于应用而言完全透明,分区前后没有变化,不需要进行修改。

 

需要注意:虽然各个分区可以存放在不同的表空间中,但这些表空间所使用的块大小(block_size)必须一致

需要注意:除了包含LONG以及LONG RAW字段的表无法使用分区外,其他表均可以使用分区,包括含有LOB字段的表


2、分区表的优点

  • 在维护性方面,可以在分区级别,针对单独的分区,进行索引的维护、数据的加载以及备份恢复等操作。大大降低了维护时长。
  • 在可用性方面,由于各个分区相对独立,当一个分区处于维护或者出现故障时,不会影响到其他分区的正常使用
  • 在性能方面,oracle对于用户的请求,只检索需要的分区,从而提升性能。
  • 在其他方面,由于分区表对于用户是透明的,因此,不需要在分区后,对代码进行修改。

3、分区键的简介

  • 分区键就是决定表中的数据行,属于哪一个分区的一组数据列。在执行DML操作时,ORACLE会根据分区键选择分区。

4、常用分区表简介及使用方法(含注意事项)

4.1、范围分区(range partition)

范围分区特点:
范围分区主要依据分区键定义时给出的键值范围,根据实际的取值,进行分区的选择,进而在相应分区中存储数据。
范围分区比较合适存在以数字为导向,方便进行数字范围划分的数据列。如:员工表的雇佣日期列、工资列等。
范围分区的数据分布可能不均匀。

 

范围分区定义规则:
1、在定义范围分区时,每个分区定义必须使用 values less than(value)子句。其中(value)表示该分区的上限值。
2、在定义范围分区时,最后一个分区可以是values less than(maxvalue)。其中(maxvalue)表示该分区存储高于其他分区上限值的数据行。

 

示例:

示例涉及的测试数据源,来自于ORACLE数据库的测试用户scott下的emp表。

emp表的大致数据情况如下:

--测试表emp的数据总记录条数为14条
Yumiko@sunny >select count(*) from emp;

  COUNT(*)
----------
        14



Yumiko@sunny >select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80            800                    20
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20
      7839 KING       PRESIDENT            17-11月-81           5000                    10
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30
      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
      7900 JAMES      CLERK           7698 03-12月-81            950                    30
      7902 FORD       ANALYST         7566 03-12月-81           3000                    20
      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10

已选择14行。

下面采用范围分区的方法创建分区,并将emp表的数据导入该分区表。

这里,使用HIREDATE列作为分区键进行分区操作。

建议,使用dbms_metadata.get_ddl的方法进行emp表结构创建语法的提取工作,并进而修改。

Yumiko@sunny >CREATE TABLE "EMPLOYEE_RANGE_PART"
  2  ( "EMPNO" NUMBER(4,0),
  3    "ENAME" VARCHAR2(10),
  4    "JOB" VARCHAR2(9),
  5    "MGR" NUMBER(4,0),
  6    "HIREDATE" DATE,
  7    "SAL" NUMBER(7,2),
  8    "COMM" NUMBER(7,2),
  9    "DEPTNO" NUMBER(2,0)
 10  )
 11  PARTITION BY RANGE (HIREDATE)
 12  (
 13  PARTITION EMPLOYEE_PART01 VALUES LESS THAN (TO_DATE('1981-01-01','yyyy-mm-dd'))
 14  TABLESPACE test_tbs_01,
 15  PARTITION EMPLOYEE_PART02 VALUES LESS THAN (TO_DATE('1982-01-01','yyyy-mm-dd'))
 16  TABLESPACE test_tbs_02,
 17  PARTITION EMPLOYEE_PART03 VALUES LESS THAN (MAXVALUE)
 18  TABLESPACE test_tbs_03
 19  );  

表已创建。



Yumiko@sunny >insert into EMPLOYEE_RANGE_PART select * from emp;
已创建 14 行。


Yumiko@sunny >commit;
提交完成。

查看此时分区表的状态,以及emp表的数据在分区表中的分布情况。

注意,新建的数据表,统计信息未必收集,可通过analyze table tablename compute statistics进行收集。

Yumiko@sunny >select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT,STATUS
  2  from dba_part_tables
  3  where TABLE_NAME='EMPLOYEE_RANGE_PART';

TABLE_NAME                     PARTITION PARTITION_COUNT STATUS
------------------------------ --------- --------------- --------
EMPLOYEE_RANGE_PART            RANGE                   3 VALID




Yumiko@sunny >select PARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATED
  2  from dba_tab_partitions
  3  where TABLE_NAME='EMPLOYEE_RANGE_PART';

PARTITION_NAME                   NUM_ROWS TABLESPACE_NAME                SEGM
------------------------------ ---------- ------------------------------ ----
EMPLOYEE_PART03                         3 TEST_TBS_03                    YES
EMPLOYEE_PART02                        10 TEST_TBS_02                    YES
EMPLOYEE_PART01                         1 TEST_TBS_01                    YES
查询单独分区的数据信息。

Yumiko@sunny >select * from EMPLOYEE_RANGE_PART partition(EMPLOYEE_PART01);

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80            800                    20

4.2、列表分区(list partition)

列表分区特点:
列表分区主要依据分区键定义时给出的取值列表,根据实际的取值,进行分区的选择,进而在相应分区中存储数据。
列表分区比较合适列唯一取值有限,且较为固定的数据列。如:员工表的部门列。
列表分区的数据分布可能不均匀。

 

列表分区定义规则:
1、在定义范围分区时,每个分区定义必须使用 values('value01','value02'....)子句。表示该分区存储包含相关value值的数据行。
2、在定义范围分区时,最后一个分区可以是values(DEFAULT)。表示该分区存储未在其他分区定义的数据行。

 

示例:

本示例数据来源,与上一节相同,均为emp表。

本示例中,将使用JOB列作为分区键进行分区操作。

 

首先,看一下JOB列中,目前涉及的工作分类有哪些。


Yumiko@sunny >select job,count(*) job from emp group by job ;

JOB              JOB
--------- ----------
CLERK              4
SALESMAN           4
PRESIDENT          1
MANAGER            3
ANALYST            2

从上面的信息可以看出,目前涉及五种职位。

下面采用列表分区的方法进行分区表的创建,并倒入emp中的数据。其中,

涉及PRESIDENT,MANAGER以及ANALYST三种职位的数据,存放在分区一;

涉及CLERK职位的数据,存放在分区二;

涉及SALESMAN职位的数据,以及未来可能出现的新职位的数据,存放在分区三;

CREATE TABLE "EMPLOYEE_LIST_PART"
  2  ( "EMPNO" NUMBER(4,0),
  3    "ENAME" VARCHAR2(10),
  4    "JOB" VARCHAR2(9),
  5    "MGR" NUMBER(4,0),
  6    "HIREDATE" DATE,
  7    "SAL" NUMBER(7,2),
  8    "COMM" NUMBER(7,2),
  9    "DEPTNO" NUMBER(2,0)
 10  )
 11  PARTITION BY LIST (JOB)
 12  (
 13  PARTITION EMPLOYEE_PART01 VALUES ('PRESIDENT','MANAGER','ANALYST')
 14  TABLESPACE test_tbs_01,
 15  PARTITION EMPLOYEE_PART02 VALUES ('CLERK')
 16  TABLESPACE test_tbs_02,
 17  PARTITION EMPLOYEE_PART03 VALUES (DEFAULT)
 18  TABLESPACE test_tbs_03
 19  );   

表已创建。


Yumiko@sunny >insert into EMPLOYEE_LIST_PART select * from emp;
已创建 14 行。


Yumiko@sunny >commit;
提交完成。
首先观察一下EMPLOYEE_PART02分区。
Yumiko@sunny >select SEGMENT_NAME,PARTITION_NAME,HEADER_FILE,HEADER_BLOCK,BLOCKS
  2  from dba_segments where PARTITION_NAME='EMPLOYEE_PART02';

SEGMENT_NAME         PARTITION_NAME       HEADER_FILE HEADER_BLOCK     BLOCKS
-------------------- -------------------- ----------- ------------ ----------
EMPLOYEE_LIST_PART   EMPLOYEE_PART02               10         1169       1024
EMPLOYEE_RANGE_PART  EMPLOYEE_PART02               10          145       1024

从上面的查询结果可以看到,前面创建的两张分区表,都采用相同的EMPLOYEE_PART02名字,进行了分区的命名。

虽然分区均处于相同的表空间下,但相互之前并未受到影响。

由此可以得出结论:

1、对于普通分区表,只要不是同一张的分区表,分区的命名可以相同;

2、对于组合分区表,同一张表的子分区命名,不能够相同。(创建时会有提示,此处只给出结论,不再进行演示)

查看此时分区表的状态以及数据在分区间的分布情况
Yumiko@sunny >select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT,STATUS
  2  from dba_part_tables
  3  where TABLE_NAME='EMPLOYEE_LIST_PART';

TABLE_NAME                     PARTITION PARTITION_COUNT STATUS
------------------------------ --------- --------------- --------
EMPLOYEE_LIST_PART             LIST                    3 VALID




Yumiko@sunny >select PARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATED
  2  from dba_tab_partitions
  3  where TABLE_NAME='EMPLOYEE_LIST_PART';

PARTITION_NAME         NUM_ROWS TABLESPACE_NAME                SEGM
-------------------- ---------- ------------------------------ ----
EMPLOYEE_PART03               4 TEST_TBS_03                    YES
EMPLOYEE_PART02               4 TEST_TBS_02                    YES
EMPLOYEE_PART01               6 TEST_TBS_01                    YES

查询单独分区的数据信息。
Yumiko@sunny >select * from EMPLOYEE_LIST_PART partition(EMPLOYEE_PART01);

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20
      7839 KING       PRESIDENT            17-11月-81           5000                    10
      7902 FORD       ANALYST         7566 03-12月-81           3000                    20

已选择6行。

4.3、HASH分区(hash partition)

HASH分区特点:
HASH分区主要通过hash算法确定相应数据行应该被存放到哪个分区中。
HASH分区比较适合列差异值很多的数据列。

HASH分区的注意事项:
对于HASH分区,无法控制一条数据在分区间的具体分布。具体分布由hash算法决定。
对于HASH分区,如果更改分区的数量,将导致所有数据在分区间的重新分布
HASH分区定义规则:
在定义HASH分区时,其分区数量应为2的N次方,如:2,4,8,16等

示例说明:

此示例的数据源依然来源于emp表。

关于HASH分区的创建,有标准写法以及简易写法。

标准写法示例:
Yumiko@sunny >CREATE TABLE "EMPLOYEE_HASH_PART"
  2  ( "EMPNO" NUMBER(4,0),
  3    "ENAME" VARCHAR2(10),
  4    "JOB" VARCHAR2(9),
  5    "MGR" NUMBER(4,0),
  6    "HIREDATE" DATE,
  7    "SAL" NUMBER(7,2),
  8    "COMM" NUMBER(7,2),
  9    "DEPTNO" NUMBER(2,0)
 10  )
 11  PARTITION BY HASH (ENAME)
 12  (
 13  PARTITION EMPLOYEE_PART01 TABLESPACE test_tbs_01,
 14  PARTITION EMPLOYEE_PART02 TABLESPACE test_tbs_02
 15  );   

表已创建。

Yumiko@sunny >insert into EMPLOYEE_HASH_PART select * from emp;
已创建 14 行。

Yumiko@sunny >commit;
提交完成。

查看标准写法下,HASH分区表的状态及数据分布情况
Yumiko@sunny >select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT,STATUS
  2  from dba_part_tables
  3  where TABLE_NAME='EMPLOYEE_HASH_PART';

TABLE_NAME                     PARTITION PARTITION_COUNT STATUS
------------------------------ --------- --------------- --------
EMPLOYEE_HASH_PART             HASH                    2 VALID



Yumiko@sunny >select PARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATED
  2  from dba_tab_partitions
  3  where TABLE_NAME='EMPLOYEE_HASH_PART';

PARTITION_NAME         NUM_ROWS TABLESPACE_NAME                SEGM
-------------------- ---------- ------------------------------ ----
EMPLOYEE_PART02               6 TEST_TBS_02                    YES
EMPLOYEE_PART01               8 TEST_TBS_01                    YES
简单写法示例:
Yumiko@sunny >CREATE TABLE "EMPLOYEE_HASH_PART_SAMPLE"
  2  ( "EMPNO" NUMBER(4,0),
  3    "ENAME" VARCHAR2(10),
  4    "JOB" VARCHAR2(9),
  5    "MGR" NUMBER(4,0),
  6    "HIREDATE" DATE,
  7    "SAL" NUMBER(7,2),
  8    "COMM" NUMBER(7,2),
  9    "DEPTNO" NUMBER(2,0)
 10  )
 11  PARTITION BY HASH (ENAME) PARTITIONS 2
 12  STORE IN (test_tbs_01,test_tbs_02);

表已创建。


Yumiko@sunny >select PARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATED
  2  from dba_tab_partitions
  3  where TABLE_NAME='EMPLOYEE_HASH_PART_SAMPLE';

PARTITION_NAME         NUM_ROWS TABLESPACE_NAME                SEGM
-------------------- ---------- ------------------------------ ----
SYS_P11074                    6 TEST_TBS_02                    YES
SYS_P11073                    8 TEST_TBS_01                    YES
从上图可以看到,相较于标准写法,简单写法创建的分区名字,由oracle自动指定。

4.4、组合分区(composite partition)

组合分区的特点:
组合分区中,主要通过在不同列上,使用“范围分区”、“列表分区”以及“HASH分区”不同组合方式,进而实现组合分区。
组合分区中,分区本身没有相应的segment,可以认为是一个逻辑容器,只有子分区拥有实际的segment,用于存放数据。


组合分区的注意事项:
在11g以前,组合分区主要有两种组合方式:“RANGE-HASH”以及“RANGE-LIST”。
在11g以后,组合分区新增了四种组合方式:“RANGE-RANGE”、“LIST-RANGE”、“LIST-HASH”以及“LIST-LIST”。

 

示例说明:

关于本示例的数据源依然选择emp表为参考。

关于本示例的演示,将采用LIST-LIST的组合方式,创建组合分区。

关于其他组合方式效仿即可。

 

示例:

首先按照部门(deptno)进行分区,针对相同部门,再按照职位(JOB)是否为manager再次进行子分区。具体如下:


Yumiko@sunny >CREATE TABLE "EMPLOYEE_LIST_LIST_PART"
  2  ( "EMPNO" NUMBER(4,0),
  3    "ENAME" VARCHAR2(10),
  4    "JOB" VARCHAR2(9),
  5    "MGR" NUMBER(4,0),
  6    "HIREDATE" DATE,
  7    "SAL" NUMBER(7,2),
  8    "COMM" NUMBER(7,2),
  9    "DEPTNO" NUMBER(2,0)
 10  )
 11  PARTITION BY LIST (DEPTNO)
 12  SUBPARTITION BY LIST (JOB)
 13  (
 14  PARTITION EMPLOYEE_DEPTNO_10 VALUES (10) TABLESPACE test_tbs_01
 15    ( SUBPARTITION EMPLOYEE_10_JOB_MAGAGER VALUES ('MANAGER'),
 16      SUBPARTITION EMPLOYEE_10_JOB_DEFAULT VALUES (DEFAULT)
 17    ),
 18  PARTITION EMPLOYEE_DEPTNO_20 VALUES (20) TABLESPACE test_tbs_02
 19    ( SUBPARTITION EMPLOYEE_20_JOB_MAGAGER VALUES ('MANAGER'),
 20      SUBPARTITION EMPLOYEE_20_JOB_DEFAULT VALUES (DEFAULT)
 21    ),
 22  PARTITION EMPLOYEE_DEPTNO_OTHERS VALUES (DEFAULT) TABLESPACE test_tbs_03
 23    ( SUBPARTITION EMPLOYEE_30_JOB_MAGAGER VALUES ('MANAGER'),
 24      SUBPARTITION EMPLOYEE_30_JOB_DEFAULT VALUES (DEFAULT)
 25    )
 26  );

表已创建。


Yumiko@sunny >insert into EMPLOYEE_LIST_LIST_PART select * from emp;
已创建 14 行。


Yumiko@sunny >commit;
提交完成。

需要注意:关于表空间(tablespace)的指定,可以放在分区一级指定,也可以放在子分区一级指定。

本示例中,表空间的指定,放在了分区一级指定。对于子分区直接套用分区指定。

查看分区创建情况


Yumiko@sunny >select PARTITION_NAME,SUBPARTITION_COUNT,COMPOSITE,NUM_ROWS,
  2  TABLESPACE_NAME,SEGMENT_CREATED
  3  from dba_tab_partitions
  4  where TABLE_NAME='EMPLOYEE_LIST_LIST_PART';

PARTITION_NAME          SUBPARTITION_COUNT COM   NUM_ROWS TABLESPACE_NAME SEGM
----------------------- ------------------ --- ---------- --------------- ----
EMPLOYEE_DEPTNO_OTHERS                   2 YES          6 TEST_TBS_03     NONE
EMPLOYEE_DEPTNO_20                       2 YES          5 TEST_TBS_02     NONE
EMPLOYEE_DEPTNO_10                       2 YES          3 TEST_TBS_01     NONE




Yumiko@sunny >select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT,
  2  SUBPARTITIONING_TYPE,STATUS
  3  from dba_part_tables
  4  where TABLE_NAME='EMPLOYEE_LIST_LIST_PART';

TABLE_NAME                     PARTITION PARTITION_COUNT SUBPARTIT STATUS
------------------------------ --------- --------------- --------- --------
EMPLOYEE_LIST_LIST_PART        LIST                    3 LIST      VALID
从上图可以看到,对于组合分区,虽然成功创建了分区,但oracle并未对分区一级,分配实际的segment。
下面再观察下子分区的创建情况:
Yumiko@sunny >select PARTITION_NAME,SUBPARTITION_NAME,
  2  TABLESPACE_NAME,NUM_ROWS,SEGMENT_CREATED 
  3  from USER_TAB_SUBPARTITIONS 
  4  where TABLE_NAME='EMPLOYEE_LIST_LIST_PART';

PARTITION_NAME          SUBPARTITION_NAME              TABLESPACE_NAME   NUM_ROWS SEG
----------------------- ------------------------------ --------------- ---------- ---
EMPLOYEE_DEPTNO_10      EMPLOYEE_10_JOB_MAGAGER        TEST_TBS_01              1 YES
EMPLOYEE_DEPTNO_10      EMPLOYEE_10_JOB_DEFAULT        TEST_TBS_01              2 YES
EMPLOYEE_DEPTNO_20      EMPLOYEE_20_JOB_MAGAGER        TEST_TBS_02              1 YES
EMPLOYEE_DEPTNO_20      EMPLOYEE_20_JOB_DEFAULT        TEST_TBS_02              4 YES
EMPLOYEE_DEPTNO_OTHERS  EMPLOYEE_30_JOB_MAGAGER        TEST_TBS_03              1 YES
EMPLOYEE_DEPTNO_OTHERS  EMPLOYEE_30_JOB_DEFAULT        TEST_TBS_03              5 YES

从上图可以明显看到,针对子分区一级,oracle确实分配了实际的segment用于数据的存储。

此外,需要再次强调一点,对于同一张组合分区表而言,子分区的名字不能相同,即使不在相同的分区里

















作者:星辰 时间:2017-09-19 浏览 468评论 0 赞 0砸 0 标签: 优化 oracle
评论
还可以再输入500个字

请您注意

·自觉遵守:爱国、守法、自律、真实、文明的原则
·尊重网上道德,遵守《全国人大常委会关于维护互联网安全的决定》及中华人民共和国其他各项有关法律法规
·严禁发表危害国家安全,破坏民族团结、国家宗教政策和社会稳定,含侮辱、诽谤、教唆、淫秽等内容的作品
·承担一切因您的行为而直接或间接导致的民事或刑事法律责任
·您在NoteShare上发表的作品,NoteShare有权在网站内保留、转载、引用或者删除
·参与本评论即表明您已经阅读并接受上述条款