PLSQL_Oracle分区表和相应的分区索引管理和使用（案例）（创建交易表等大表时进行分区提高效率）

来源：互联网发布：罗马帝国扫矿软件编辑：程序博客网时间：2024/06/06 07:20

1、分区表：

随着表的不断增大，对于新纪录的增加、查找、删除等(DML)的维护也更加困难。对于数据库中的超大型表，可通过把它的数据分成若干个小表，从而简化数据库的管理活动。对于每一个简化后的小表，我们称为一个单个的分区

对于分区的访问，我们不需要使用特殊的SQL查询语句或特定的DML语句，而且可以单独的操作单个分区，而不是整个表。同时可以将不同分区的数据放置到不同的表空间，比如将不同年份的销售数据，存放在不同的表空间，即年的销售数据存放到TBS_2001,2002年的销售数据存放到TBS_2002,依次类推，从而实现了分散存储，这将大大的简化大容量表的管理，提高查询性能及I/O并发等。

对于外部应用程序来说，虽然存在不同的分区，且数据位于不同的表空间，但逻辑上仍然是一张表

可以使用SQL*Loader，IMPDP，EXPDP，Import，Export等工具来装载或卸载分区表中的数据

关于分区表的功能实际上同SQL server 中的分区表是同样的概念，只不过SQL server中的数据存放到了文件组，相当于Oracle概念中的表空间，

2、何时分区

当表达到GB大小且继续增长

需要将历史数据和当前的数据分开单独处理，比如历史数据仅仅需要只读，而当前数据则实现DML

3、分区的条件及特性

共性：不同的分区之间必须有相同的逻辑属性，比如表名，列名，数据类型，约束等，
个性：各个分区可以有不同的物理属性，比如pctfree, pctused, and tablespaces.
分区独立性：即使某些分区不可用，其他分区仍然可用。
特殊性：含有LONG、LONGRAW数据类型的表不能进行分区

4、分区的优点

提高查询性能：只需要搜索特定分区，而非整张表，提高查询速度
节约维护时间：单个分区的数据装载，索引重建，备份，维护等将远小于整张表的维护时间。
节约维护成本：可以单独备份和恢复每个分区
均衡I/O：将不同的分区映射到不同的磁盘以平衡I/O，提高并发

5、分区表类型

范围分区表
列表分区表
哈希分区表
组合分区表

6、分区索引类型

全局索引
全局分区索引
本地分区索引

7、分区表建立四种方式语法

(1)、范围分区表

CREATE TABLE range_example(   range_key_column   DATE,   DATA               VARCHAR2 (20),   ID                 INTEGER)PARTITION BY RANGE (range_key_column)   (PARTITION part01       VALUES LESS THAN          (TO_DATE ('2008-07-1 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))       TABLESPACE tbs01,    PARTITION part02       VALUES LESS THAN          (TO_DATE ('2008-08-1 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))       TABLESPACE tbs02,    PARTITION part03       VALUES LESS THAN          (TO_DATE ('2008-09-1 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))       TABLESPACE tbs03);

(2)、列表分区表

CREATE TABLE list_example (dname VARCHAR2 (10), DATA VARCHAR2 (20))PARTITION BY LIST (dname)   (PARTITION part01       VALUES ('ME', 'PE', 'QC', 'RD'),    PARTITION part02       VALUES ('SMT', 'SALE'));

(3)、哈希分区表

CREATE TABLE hash_example(   hash_key_column   DATE,   DATA              VARCHAR2 (20))PARTITION BY HASH (hash_key_cloumn)   (PARTITION part01, PARTITION part02);

(4)、组合分区表

CREATE TABLE range_hash_example(   range_column_key   DATE,   hash_column_key    INT,   DATA               VARCHAR2 (20))PARTITION BY RANGE (range_column_key)   SUBPARTITION BY HASH (hash_column_key)      SUBPARTITIONS 2   (PARTITION part_1       VALUES LESS THAN (TO_DATE ('2008-08-01', 'yyyy-mm-dd')) (       SUBPARTITION part_1_sub_1 ,       SUBPARTITION part_1_sub_2 ,       SUBPARTITION part_1_sub_3    ),    PARTITION part_2       VALUES LESS THAN (TO_DATE ('2008-09-01', 'yyyy-mm-dd'))    (SUBPARTITION part_2_sub_1 , SUBPARTITION part_2_sub_2 ));

8、分区索引的结构图

注:hash partitioned table 新增partition时，现有表的中所有data都有重新计算hash值，然后重新分配到分区中。所以被重新分配的分区的 indexes需要rebuild 。

二、案例 - 创建分区表

案例：创建分区表bxj_emp，以性别区分数据分区方式，将资料分别存放两个男女表空间中

1. 创建两个表空空间

CREATE TABLESPACE bxj_emp_ts1 LOGGING DATAFILE '/opt/oracle/oradata/gavinsit/bxj_emp_data01.dbf'  SIZE 32M  AUTOEXTEND ON  NEXT 32M MAXSIZE 2048M EXTENT MANAGEMENT LOCAL CREATE TABLESPACE bxj_emp_ts2 LOGGING DATAFILE '/opt/oracle/oradata/gavinsit/bxj_emp_data02.dbf'  SIZE 32M  AUTOEXTEND ON  NEXT 32M MAXSIZE 2048M EXTENT MANAGEMENT LOCAL

2. 创建分区表，以sex栏位区分

CREATE TABLE bxj_emp_tb(   emp_id           NUMBER,   employeee_name   VARCHAR (50),   sex              VARCHAR (10),   salary           NUMBER)PARTITION BY LIST (sex)   (PARTITION bxj_emp_ts1       VALUES ('male'),    PARTITION bxj_emp_ts2       VALUES ('female'));

3. 建立测试资料，男女各一笔

insert into apps.bxj_emp_tb values (1, 'gavin.bao', 'male', 100000);       insert into apps.bxj_emp_tb values (2, 'gavin.bao', 'female', 200000);

4. 以条件sex = male进行查询时，系统只遍历tablespace 1 male

5. 以条件sex = female进行查询时，系统只遍历tablespace 1 female

6. 无分区条件查询，系统需全部遍历tablespace 1 and 2 / male and female

三、案例 - 本地分区索引

1. 创建本地分区索引

CREATE INDEX bxj_emp_tb_localindex ON apps.bxj_emp_tb(sex)LOCAL (     PARTITION idx_1 TABLESPACE bxj_emp_ts1,     PARTITION idx_2 TABLESPACE bxj_emp_ts2  );

2. 解析计划中索引遍历方式

四、案例 - 全局分区索引

1. 创建全局分区索引

CREATE INDEX bxj_emp_tb_globalindexON bxj_emp_tb (salary)GLOBAL PARTITION BY RANGE ( salary ) (   PARTITION idx_1 VALUES LESS THAN (10000)    TABLESPACE bxj_emp_ts1,   PARTITION idx_2 VALUES LESS THAN (MAXVALUE) TABLESPACE bxj_emp_ts2 );

2. 条件为salary <= 100, 索引只遍历tablespace1

3. 条件为salary >=100000, 索引只遍历tablespace2

4. 无分区条件时，索引遍历全部

五、案例 - Oracle Erp交易表mtl_material_transactions的结构

0 0