Oracle篇—分区表和分区索引的介绍和分类（第一篇，总共五篇）

发布时间：2024年01月22日

??博主介绍??：

?又是一天没白过，我是奈斯，DBA一名?

???擅长Oracle、MySQL、SQLserver、Linux，也在积极的扩展IT方向的其他知识面???

??????大佬们都喜欢静静的看文章，并且也会默默的点赞收藏加关注??????

? ? 新的一周如期而至，那么全新的学习机遇和挑战也随之而来。众所周知Oracle始终以其卓越的性能和灵活性而著称。今天我将引导大家一同走进Oracle的分区技术世界，深入了解分区表和分区索引的奥秘。因为分区技术需要介绍的太多，那么我将分成五篇来进行介绍，以便大家因为篇幅过长而感到阅读疲惫。五篇的内容分别如下，让大家先做了解：

第一篇：分区表和分区索引的介绍和分类

第二篇：分区表的管理

第三篇：分区索引的重建和管理

第四篇：分区表和分区索引常用的检查语句

第五篇：普通表迁移到分区表

? ? 那么先开始今天第一篇分区表和分区索引的介绍和分类的内容。

? ? 分区表，作为Oracle数据库管理大数据量的利器，能够将庞大的数据表分割成更小、更易于管理的片段，即分区。每个分区可以独立存储、备份和索引，从而显著提高查询性能、减少维护时间，并优化存储资源的使用。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。从应用程序的角度来看，分区后的表与非分区表完全相同，使用 SQL?DML命令访问分区后的表时，无需任何修改。

? ? 而分区索引，则是分区表的得力助手。它能够为分区表提供高效的数据检索能力，加速查询速度，并允许对特定分区进行快速的数据访问。通过合理设计和使用分区索引，我们能够进一步提升数据库系统的整体性能。

? ? 在本文中，我将全面介绍Oracle分区表和分区索引的基本概念、分类方式以及实际应用中的最佳实践。无论是数据库管理员、开发人员还是对数据库技术感兴趣的爱好者，相信都能从中获得宝贵的知识和经验，为工作和学习带来新的突破和提升。

1、分区表的作用与分类

?1.1 范围分区（Range Partitioning）

案例一：将普通表的数据迁移到分区表，单个字段按时间不同表空间分区

1.2列表分区（List Partitioning）

案例：将普通表的数据迁移到分区表，单个字段按值分区

1.3 哈希分区（Hash Partitioning）?

案例：将普通表的数据迁移到分区表，单个字段按数值分区

1.4 复合(组合)分区（Composite Partitioning）

案例一：复合分区range—hash。为每个range分区创建2个hash子分区

案例二：复合分区range—list。为每个range分区创建3个list子分区

1.5间隔分区（interval partitioning）

案例：间隔分区之间隔范围分区。根据定义的分区字段每个月自动创建一个分区

1.6虚拟列分区（virtual Column Partitioning）

案例：根据日期列进行分区，每个月份一个分区，总共 12个分区。不管是哪一年的，只要是那个月，就放在那个月的分区中。

1.7系统分区（system partitioning）

案例：将普通表的数据迁移到分区表，分区表分4个区

2、分区索引的作用与分类?

2.1本地索引(local index自动维护索引分区。进行add/drop/split/truncate表的分区时，本地索引会自动维护其索引分区不失效。如果不加local参数那么就是普通表的单列，是不会分区的，所以特别注意是分区表时一定要加上local)

?案例一：创建本地索引（创建的本地索引引用分区列，那么索引自动分区）

?案例二：创建本地索引（创建的本地索引不引用分区列，索引还是自动分区）

2.2全局索引（global index手工维护索引分区。进行add/drop/split/truncate表的分区时，全局索引不会维护其索引分区会导致失效。如果不加global参数那么就是普通表的单列）

案例一：全局索引之前缀range分区索引（全局索引不会维护其索引分区会导致失效）

? ??

1、分区表的作用与分类

分区表的优点、缺点、特性：

（1）由于将数据分散到各个分区中，减少了数据损坏的可能性；

（2）可以对单独的分区进行备份和恢复；

（3）可以将分区映射到不同的物理磁盘上，来分散 IO；

（4）提高可管理性、可用性和性能。

（5）缺点：已经存在的表没有方法可以直接转化为分区表。不过Oracle提供了在线重定义表的功能。

（6）特殊性：含有LONG、LONGRAW数据类型的表不能进行分区，一般分区类

型为：varchar,varchar2,number,date

（7）每个表的分区或子分区数的总数不能超过1023个。

? ??

什么时候用分区表（设计原则）：?

（1）单表过大，当表的大小超过2GB，或对于OLTP系统，表的记录超过1000万，都应考虑对表进行分区。

（2）历史数据据需要剥离的，表中包含历史数据，新的数据被增加到新的分区中。

（3）查询特征非常明显，比如是按整年、整月或者按某个范围！

（4）基于表的大部分查询应用，只访问表中少量的数据。

（5）按时间段删除成批的数据，例如按月删除历史数据。

（6）按时间周期进行表空间的备份时，将分区与表空间建立对应关系。

（7）如果一个表中大部分数据都是只读数据，通过对表进行分区，可将只读数据存储在只读表空间中，对于大数据库的备份是非常有益的。

（8）对于经常执行并行查询的表应考虑进行分区。

（9）当对表的部分数据可用性要求很高时，应考虑进行表分区。

? ??

分区表的类型：

一、范围分区（Range Partitioning）8i，

? ? 以某一个范围进行分区，支持多个字段分区。eg:时间、数值划分。

二、哈希分区（Hash Partitioning）8i，

? ? 当表中没有合适的列进行某种分区并且表数据量大，可以根据字段的hash值进行hash算法进行的散列分区，可以有效的消除io的竞争，使得每个分区数据量大致一致（数据的均匀存储）。每个分区的数据感觉上是没有规律的，是根据hash算法自动分配

三、列表分区（List Partitioning）9i，

? ? 以某列只有几个值进行分区，只支持单个字段分区。eg：地区分区，省份进行划分。

四、复合(组合)分区（Composite Partitioning），9i

? ? 某表在分区之后仍然较大，可以通过分区内再建子分区的方式将分区再分区，即复合分区的方式。

? ? Oracle 8i包括了2种复合分区：RANGE-HASH、RANGE-LIST

? ? Oracle 11g又提供了4种复合分区：RANGE-RANGE、LIST-RANGE、LIST-HASH和LIST-LIST

五、间隔分区（Interval Extension to Range Partitioning）11g，

? ? 它就是以一个区间分区表为“起点”，并在定义中增加了一个规则（即间隔），使数据库知道将来如何增加分区。eg:比如每个月增加一个分区，比如每隔一天/月要生成新的24个分区，用以存储第二天/月的数据。而在11g中这项工作可以交由Oracle自动完成了，基于Range、List、hash的Interval Partitioning分区登场，从而省去了你不断的ADD或者SPLIT新的分区。可以按天，月，季度，年：1day，1month，3month，12month

? ? ? ?年：interval (numtoyminterval(1,'year'))

? ? ? ?月：interval (numtoyminterval(1,'month') )

? ? ? ?天：interval (numtodsinterval(1,'day'))??????#注意天的间隔和年、月的值不一样

六、虚拟字段分区（Virtual Column Partitioning）11g，

? ? 虚拟列功能，虚拟列的值从其他的列推导而来，Oracle只保存源数据，这个列不占存储空间。虚拟列其中一个引申功能就是虚拟列分区功能。在运行时计算，不存储在数据库中，不能更新虚拟列的值。11g增加对虚拟列的支持，这使得分区功能更加灵活。

? ? 定义一个虚拟列的语法：column name [datatype] [GENERATED ALWAYS] AS [expression] [VIRTUAL]。以下是虚拟字段分区的限制：

? ? ? ? ? ? 1>只能在堆组织表（普递表）上创建虚拟列，不能在素引组织表、外部表、临时表上创建虚拟列
? ? ? ? ? ? 2>虚拟列不能是LOB或者RAW类型
? ? ? ? ? ? 3>虚拟列的值并是不真实存在的，只有用到时，才根据表达式计算出虚拟列的值，磁盘上并不存放
? ? ? ? ? ? ?4>可把虚拟列当做分区关键字建立分区表，这是11g的另一新特性--虚拟列分区
? ? ? ? ? ? ?5>可在虚拟列上创建索引
? ? ? ? ? ? ?6>如果在已经创建的表中增加虚拟列时，若没有指定虚拟列的字段类型，ORACLE会根据generated always as后面的表达式计算的结果自动设置该字段的类型
? ? ? ? ? ? 7>也视列的售由ORACLE根据表达式自动计算得出，不可以做UPDATE和INSERT操作，可以对虚拟列做DELETE操作
? ? ? ? ? ? 8>表达式中的所有列必须在同一张表；表达式不能使用其他虚拟列

七、系统分区，11g

? ? 11g以前的分区表，需要指定一个或多个分区字段，并根据这个分区字段的值，按照一定的算法（RANGE、HASH和LIST）来决定一条记录属于那个分区。从11g开始，Oracle允许用户不指定分区列，完全根据程序来控制数据存储在那个分区中。

? ? 在以前确定了分区列和分区方式，那么一条数据属于哪个分区也就被确定下来。而对于系统分区而言，分区是分区，数据是数据，二者没有对应的关系，数据可以被放在任意一个分区中，这不是由数据本身决定的，而是应用程序在插入时确定的。

? ??

相关视图：

SQL> select * from user/dba/all_part_tables：记录分区的表的信息

???? ? ?partition_type：表的分区类型

?????? ?subpartitoning_type：子分区类型

? ? ? ? partition_count：表的分区数量

? ? ? ? def_subpartition_count：每个分区子分区数量

? ? ? ? partition_key_count：表中有分区的字段数量

????? ? status：表分区是否生效

?????? ?def_tablespace_name：表的默认表空间，不是分区默认的表空间

SQL> select * from user/dba/all_tab_partitions：记录表的分区的信息

?????? ?subpartition_count：每个分区子分区数量

????? ? high_value：这分区保存最高的范围

?? ? ? ?tablespace_name：这个分区默认的表空间

SQL> select * from user/dba/all_tab_subpartitions：记录表的子分区信息

?? ? ? ?subpartition_name：子分区名

?? ? ? ?subpartiton_position：子分区编号，为分区的下的子分区数量

SQL> select * from user/dba/all_part_indexes：查询分区的索引信息

? ? ? ??locality：全局索引global；本地索引local

? ? ? ??alignment：前缀索引prefixed；非前缀索引non-prefixed

SQL> select * from user/dba/all_ind_partitions：记录索引分区的信息

? ? ? ? status列：usable有效，unusable无效。而普通索引通过dba_indexes查看。如果分区有子分区，显示N/A，需要通过dba_ind_subpartitions查看分区下面每个子分区的状态

SQL> select * from user/dba/all_ind_subpartitions：记录索引子分区的信息

? ? ? ? status列：usable有效，unusable无效。

? ? ?

?1.1 范围分区（Range Partitioning）

相关语法：

SQL> create table?table_name

( column1 ?data_type1,

???column2 ?data_type2,

) tablespace tablespace_name

partition by?range(column1,column2)

(

partition partition_name values less than (to_date('2014-01-01','yyyy-mm-dd')) tablespace tablespace_name,

partition partition_name values less than (20010000) tablespace tablespace_name,

partition partition_max values less than (maxvalue) tablespace tablespace_name

) enable row movement;

maxvalues：为范围分区表中没有涉及的任何分区，如果插入的数据不属于任何分区那么将数据放到此分区中。如果没有此分区，并且插入的数据不属于其他分区，那么插入报错，ORA-14400: inserted partition key does not map to any partition

enable row movement：oracle的分区表对于分区字段（分区字段的数据不能修改，其他字段可以）是不允许进行update操作的，可以通过打开表的row movement属性来允许对分区字段的update操作。建议update完成后disable row movement

案例一：将普通表的数据迁移到分区表，单个字段按时间不同表空间分区

1）查看普通表的结构，创建的分区表和普通表的结构一致

SQL> select dbms_metadata.get_ddl('TABLE','TABLELF') from dual;    ---显示TABLELF表的结构

2）创建分区表（与普通表的结构一致），并按照范围分区

SQL> create table TABLE_RANGE
(
  id         NUMBER(20) not null,
  name       VARCHAR2(20) not null,
  age        NUMBER(10) not null,
  sex        VARCHAR2(10) not null,
  cardid     NUMBER(30) not null,
  joindate   DATE not null,
  region     VARCHAR2(12) not null,
  tel        VARCHAR2(12) not null,
  email      VARCHAR2(30) not null,
  recommend  VARCHAR2(10),
  identifier VARCHAR2(100)
)
partition by range(joindate)
(
partition p2014 values less than (to_date('2014-01-01','yyyy-mm-dd')),
partition p2015 values less than (to_date('2015-01-01','yyyy-mm-dd')),
partition p2016 values less than (to_date('2016-01-01','yyyy-mm-dd')),
partition p2017 values less than (to_date('2017-01-01','yyyy-mm-dd')),
partition p_max values less than (maxvalue)
);

3）将普通表的数据插入到分区表。这种方式需要原表不能有任何操作也就是非在线迁移，不然会导致插入后的数据不一致，在第五篇我会介绍在线迁移的方式

SQL> insert into TABLE_RANGE select * from tablelf;
SQL> commit;

4）表分区查询验证

SQL> select * from dba_part_tables where table_name='TABLE_RANGE'; ???????---记录分区的表的信息
SQL> select * from DBA_tab_partitions where table_name='TABLE_RANGE'; ????---记录表的分区的信息
SQL> select * from dba_part_indexes where table_name='TABLE_RANGE'; ?????---查询分区的索引信息

5）验证表数量，和每个分区的数据数量

SQL> select count(*) from TABLE_RANGE;

SQL> select count(*) from TABLE_RANGE partition(P2014);
SQL> select count(*) from TABLE_RANGE partition(P2015);
SQL> select count(*) from TABLE_RANGE partition(P2016);
SQL> select count(*) from TABLE_RANGE partition(P2017);
SQL> select count(*) from TABLE_RANGE partition(p_max);

? ??

案例二：将普通表的数据迁移到分区表，单个字段按数值分区

1）查看普通表的结构，创建的分区表和普通表的结构一致

SQL> select dbms_metadata.get_ddl('TABLE','TABLELF') from dual;    ---显示TABLELF表的结构

2）创建分区表（与普通表的结构一致），并按照范围分区

SQL> create table table_range2
(
  id         NUMBER(20) not null,
  name       VARCHAR2(20) not null,
  age        NUMBER(10) not null,
  sex        VARCHAR2(10) not null,
  cardid     NUMBER(30) not null,
  joindate   DATE not null,
  region     VARCHAR2(12) not null,
  tel        VARCHAR2(12) not null,
  email      VARCHAR2(30) not null,
  recommend  VARCHAR2(10),
  identifier VARCHAR2(100)
)
partition by range(id)
(
partition p10000 values less than (20010000),
partition p20000 values less than (20020000),
partition p30000 values less than (20030000),
partition p40000 values less than (20040000),
partition p50000 values less than (20050000),
partition p_max values less than (maxvalue)
);

3）将普通表的数据插入到分区表。这种方式需要原表不能有任何操作也就是非在线迁移，不然会导致插入后的数据不一致，在第五篇我会介绍在线迁移的方式

SQL> insert into TABLE_RANGE2 select * from tablelf;
SQL> commit;

4）表分区查询验证

SQL> select * from dba_part_tables where table_name='TABLE_RANGE2'; ???????---记录分区的表的信息
SQL> select * from DBA_tab_partitions where table_name='TABLE_RANGE2'; ????---记录表的分区的信息
SQL> select * from dba_part_indexes where table_name='TABLE_RANGE2'; ?????---查询分区的索引信息

5）验证表数量，和每个分区的数据数量

SQL> select count(*) from TABLE_RANGE2;

SQL> select count(*) from TABLE_RANGE2 partition(P10000);
SQL> select count(*) from TABLE_RANGE2 partition(P20000);
SQL> select count(*) from TABLE_RANGE2 partition(P30000);
SQL> select count(*) from TABLE_RANGE2 partition(P40000);
SQL> select count(*) from TABLE_RANGE2 partition(P50000);
SQL> select count(*) from TABLE_RANGE2 partition(P_max);

? ? ?

1.2列表分区（List Partitioning）

相关语法：

create table table_name

( column1 ?data_type1,

???column2 ?data_type2,

) tablespace tablespace_name

partition by?list(column1)

(

partition par_01 values('男') tablespace tablespace_name,

partition par_02 values('女') tablespace tablespace_name ,

partition p_max values (default) tablespace tablespace_name

) enable row movement;

default：为列表分区表中没有涉及的任何分区，如果插入的数据不属于任何分区那么将数据放到此分区中。如果没有此分区，并且插入的数据不属于其他分区，那么插入报错，ORA-14400: ?inserted partition key does not map to any partition

enable row movement：oracle的分区表对于分区字段（分区字段的数据不能修改，其他字段可以）是不允许进行update操作的，可以通过打开表的row movement属性来允许对分区字段的update操作。建议update完成后disable row movement

? ??

案例：将普通表的数据迁移到分区表，单个字段按值分区

1）查看普通表的结构，创建的分区表和普通表的结构一致

SQL> select dbms_metadata.get_ddl('TABLE','TABLELF') from dual;    ---显示TABLELF表的结构

2）创建分区表（与普通表的结构一致），并按照列表分区

SQL> create table table_list
( 
id NUMBER(20) not null primary key, 
name VARCHAR2(20) not null, 
age NUMBER(10) not null, 
sex VARCHAR2(10) not null, 
cardid NUMBER(30) not null, 
joindate DATE not null, 
region VARCHAR2(12) not null, 
tel VARCHAR2(12) not null, 
email VARCHAR2(30) not null, 
recommend VARCHAR2(10), 
identifier VARCHAR2(100) 
)  
partition by list(sex) 
( 
partition par_01 values('男'), 
partition par_02 values('女'), 
partition p_max values (default) 
);

SQL> insert into table_list select * from tablelf;
SQL> commit;

4）表分区查询验证

SQL> select * from dba_part_tables where table_name='TABLE_LIST';        ---记录分区的表的信息 
SQL> select * from DBA_tab_partitions where table_name='TABLE_LIST';     ---记录表的分区的信息 
SQL> select * from dba_part_indexes where table_name='TABLE_LIST';      ---查询分区的索引信息

5）验证表数量，和每个分区的数据数量

SQL> select count(*) from TABLE_LIST;

SQL> select count(*) from TABLE_LIST partition(par_01);
SQL> select count(*) from TABLE_LIST partition(par_02);
SQL> select count(*) from TABLE_LIST partition(p_max);

? ??

1.3 哈希分区（Hash Partitioning）?

相关语句：

create table?table_name

( column1 ?data_type1,

???column2 ?data_type2,

) tablespace tablespace_name

partition by hash(column1)

partitions number ?tablespace tablespace_name

enable row movement;

partitions number：根据hash算法将表中的数据均匀的分成几个区

enable row movement：oracle的分区表对于分区字段（分区字段的数据不能修改，其他字段可以）是不允许进行update操作的，可以通过打开表的row movement属性来允许对分区字段的update操作。建议update完成后disable row movement

? ?

案例：将普通表的数据迁移到分区表，单个字段按数值分区

1）查看普通表的结构，创建的分区表和普通表的结构一致

SQL> select dbms_metadata.get_ddl('TABLE','TABLELF') from dual;    ---显示TABLELF表的结构

2）创建分区表（与普通表的结构一致），并按照哈希分区

SQL> create table table_hash
(
id NUMBER(20) not null primary key,
name VARCHAR2(20) not null,
age NUMBER(10) not null,
sex VARCHAR2(10) not null,
cardid NUMBER(30) not null,
joindate DATE not null,
region VARCHAR2(12) not null,
tel VARCHAR2(12) not null,
email VARCHAR2(30) not null,
recommend VARCHAR2(10),
identifier VARCHAR2(100)
)
partition by hash(cardid)
partitions 4;

通过上面的语句那么hash分区名自动分配，也自定义hash分区的分区名

SQL> create table table_hash
(
id NUMBER(20) not null primary key,
name VARCHAR2(20) not null,
age NUMBER(10) not null,
sex VARCHAR2(10) not null,
cardid NUMBER(30) not null,
joindate DATE not null,
region VARCHAR2(12) not null,
tel VARCHAR2(12) not null,
email VARCHAR2(30) not null,
recommend VARCHAR2(10),
identifier VARCHAR2(100)
)
partition by hash(cardid)
(
partition P1,
partition P2,
partition P3,
partition P4
);

SQL> insert into table_hash select * from tablelf;
SQL> commit;

4）表分区查询验证

SQL> select * from dba_part_tables where table_name='TABLE_HASH';        ---记录分区的表的信息 
SQL> select * from DBA_tab_partitions where table_name='TABLE_HASH';     ---记录表的分区的信息 
SQL> select * from dba_part_indexes where table_name='TABLE_HASH';      ---查询分区的索引信息

5）验证表数量，和每个分区的数据数量

SQL> select count(*) from TABLE_HASH;

SQL> select count(*) from TABLE_HASH partition(sys_P61);    ---hash分区名自动分配，通过DBA_tab_partitions
SQL> select count(*) from TABLE_HASH partition(sys_p62);    ---hash分区名自动分配，通过DBA_tab_partitions
SQL> select count(*) from TABLE_HASH partition(sys_p63);    ---hash分区名自动分配，通过DBA_tab_partitions
SQL> select count(*) from TABLE_HASH partition(sys_p64);    ---hash分区名自动分配，通过DBA_tab_partitions

? ??

1.4 复合(组合)分区（Composite Partitioning）

案例一：复合分区range—hash。为每个range分区创建2个hash子分区

1）查看普通表的结构，创建的分区表和普通表的结构一致

SQL> select dbms_metadata.get_ddl('TABLE','TABLELF') from dual;    ---显示TABLELF表的结构

2）创建分区表（与普通表的结构一致）

SQL> create table table_rh1
(
id NUMBER(20) not null primary key,
name VARCHAR2(20) not null,
age NUMBER(10) not null,
sex VARCHAR2(10) not null,
cardid NUMBER(30) not null,
joindate DATE not null,
region VARCHAR2(12) not null,
tel VARCHAR2(12) not null,
email VARCHAR2(30) not null,
recommend VARCHAR2(10),
identifier VARCHAR2(100)
)
partition by range(joindate)
subpartition by hash(cardid) subpartitions 2
(
partition p2014 values less than (to_date('2015-01-01','YYYY-MM-DD')),
partition p2015 values less than (to_date('2016-01-01','YYYY-MM-DD')),
partition p2016 values less than (to_date('2017-01-01','YYYY-MM-DD')),
partition p_max values less than (maxvalue)
);

SQL> insert into table_rh1 select * from tablelf;
SQL> commit;

4）表分区查询验证

SQL> select * from dba_part_tables where table_name='TABLE_RH1';        ---记录分区的表的信息 
SQL> select * from DBA_tab_partitions where table_name='TABLE_RH1';     ---记录表的分区的信息 
SQL> select * from dba_tab_subpartitions where table_name='TABLE_RH1';  ---记录表的子分区信息
SQL> select * from dba_part_indexes where table_name='TABLE_RH1';       ---查询分区的索引信息

5）验证表数量，和每个分区的数据数量?

SQL> select count(*) from TABLE_RH1;

SQL> select count(*) from TABLE_RH1 partition(p2014);
SQL> select count(*) from TABLE_RH1 subpartition(SYS_SUBP121);    ---hash子分区名自动分配，通过dba_tab_subpartitions
SQL> select count(*) from TABLE_RH1 subpartition(SYS_SUBP122);    ---hash子分区名自动分配，通过dba_tab_subpartitions

SQL> select count(*) from TABLE_RH1 partition(p2015);
SQL> select count(*) from TABLE_RH1 subpartition(SYS_SUBP123);     ---hash子分区名自动分配，通过dba_tab_subpartitions
SQL> select count(*) from TABLE_RH1 subpartition(SYS_SUBP124);     ---hash子分区名自动分配，通过dba_tab_subpartitions
   
SQL> select count(*) from TABLE_RH1 partition(p2016);   
SQL> select count(*) from TABLE_RH1 subpartition(SYS_SUBP125);     ---hash子分区名自动分配，通过dba_tab_subpartitions
SQL> select count(*) from TABLE_RH1 subpartition(SYS_SUBP126);     ---hash子分区名自动分配，通过dba_tab_subpartitions

SQL> select count(*) from TABLE_RH1 partition(p_max);        
SQL> select count(*) from TABLE_RH1 subpartition(SYS_SUBP127);     ---hash子分区名自动分配，通过dba_tab_subpartitions
SQL> select count(*) from TABLE_RH1 subpartition(SYS_SUBP128);     ---hash子分区名自动分配，通过dba_tab_subpartitions

案例二：复合分区range—list。为每个range分区创建3个list子分区

1）查看普通表的结构，创建的分区表和普通表的结构一致

SQL> select dbms_metadata.get_ddl('TABLE','TABLELF') from dual;    ---显示TABLELF表的结构

2）创建分区表（与普通表的结构一致）

SQL> create table table_rl1
(
id NUMBER(20) not null primary key,
name VARCHAR2(20) not null,
age NUMBER(10) not null,
sex VARCHAR2(10) not null,
cardid NUMBER(30) not null,
joindate DATE not null,
region VARCHAR2(12) not null,
tel VARCHAR2(12) not null,
email VARCHAR2(30) not null,
recommend VARCHAR2(10),
identifier VARCHAR2(100)
)
partition by range(joindate)
subpartition by list(sex)
(
partition p2014 values less than (to_date('2015-01-01','YYYY-MM-DD'))
(
subpartition sub_p2014_m values('男'),
subpartition sub_p2014_w values('女'),
subpartition sub_p2014_max values(default) 
),
partition p2015 values less than (to_date('2016-01-01','YYYY-MM-DD'))
(
subpartition sub_p2015_m values('男'),
subpartition sub_p2015_w values('女'),
subpartition sub_p2015_max values(default) 
),
partition p2016 values less than (to_date('2017-01-01','YYYY-MM-DD'))
(
subpartition sub_p2016_m values('男'),
subpartition sub_p2016_w values('女'),
subpartition sub_p2016_max values(default) 
),
partition p_max values less than (maxvalue)
(
subpartition sub_p_max_m values('男'),
subpartition sub_p_max_w values('女'),
subpartition sub_p_max values(default) 
)
);

采用模板(template)为range分区各创建3个list子分区——oracle官方推荐

create table table_rl2
(
id NUMBER(20) not null primary key,
name VARCHAR2(20) not null,
age NUMBER(10) not null,
sex VARCHAR2(10) not null,
cardid NUMBER(30) not null,
joindate DATE not null,
region VARCHAR2(12) not null,
tel VARCHAR2(12) not null,
email VARCHAR2(30) not null,
recommend VARCHAR2(10),
identifier VARCHAR2(100)
)
partition by range(joindate)
subpartition by list(sex)
subpartition template
(
subpartition sub_p_m values('男'),
subpartition sub_p_w values('女'),
subpartition sub_p_other values(default)
)
 (
partition p2014 values less than (to_date('2015-01-01','YYYY-MM-DD')),
partition p2015 values less than (to_date('2016-01-01','YYYY-MM-DD')),
partition p2016 values less than (to_date('2017-01-01','YYYY-MM-DD')),
partition p_max values less than (maxvalue)
);

SQL> insert into table_rl1 select * from tablelf;
SQL> commit;

4）表分区查询验证

SQL> select * from dba_part_tables where table_name='TABLE_RL1';        ---记录分区的表的信息 
SQL> select * from DBA_tab_partitions where table_name='TABLE_RL1';     ---记录表的分区的信息 
SQL> select * from dba_tab_subpartitions where table_name='TABLE_RL1';  ---记录表的子分区信息
SQL> select * from dba_part_indexes where table_name='TABLE_RL1';       ---查询分区的索引信息

5）验证表数量，和每个分区的数据数量
?

SQL> select count(*) from TABLE_RL1;

SQL> select count(*) from TABLE_RL1 partition(p2014);
SQL> select count(*) from TABLE_RL1 subpartition(sub_p2014_m);   
SQL> select count(*) from TABLE_RL1 subpartition(sub_p2014_w); 
SQL> select count(*) from TABLE_RL1 subpartition(sub_p2014_max);  
 
SQL> select count(*) from TABLE_RL1 partition(p2015);
SQL> select count(*) from TABLE_RL1 subpartition(sub_p2015_m);    
SQL> select count(*) from TABLE_RL1 subpartition(sub_p2015_w);    
SQL> select count(*) from TABLE_RL1 subpartition(sub_p2015_max);  
   
SQL> select count(*) from TABLE_RL1 partition(p2016);   
SQL> select count(*) from TABLE_RL1 subpartition(sub_p2016_m);     
SQL> select count(*) from TABLE_RL1 subpartition(sub_p2016_w);    
SQL> select count(*) from TABLE_RL1 subpartition(sub_p2016_max);  

SQL> select count(*) from TABLE_RL1 partition(p_max);        
SQL> select count(*) from TABLE_RL1 subpartition(sub_p_max_m);     
SQL> select count(*) from TABLE_RL1 subpartition(sub_p_max_w); 
SQL> Select count(*) from TABLE_RL1 subpartition(sub_p_max);

ps：关于复合分区其实用的不多，除了上述案例还有复合分区range—range、复合分区list—range、复合分区list—hash、复合分区list—list的案例，如果有需要的小伙伴可以私聊我哦！

1.5间隔分区（interval partitioning）

相关语法：

create table?table_name

( column1 ?data_type1,

???column2 ?data_type2,

) tablespace tablespace_name

partition by range(joindate)

interval (numtoyminterval(1,'month') )

(

partition partition_name values less than (to_date('2014-01-01','yyyy-mm-dd')) tablespace tablespace_name,

partition partition_name values less than (20010000) tablespace tablespace_name,

partition partition_max values less than (maxvalue) tablespace tablespace_name

) enable row movement;

maxvalues：为范围分区表中没有涉及的任何分区，如果插入的数据不属于任何分区那么将数据放到此分区中。如果没有此分区，并且插入的数据不属于其他分区，那么插入报错。

enable row movement：oracle的分区表对于分区字段（分区字段的数据不能修改，其他字段可以）是不允许进行update操作的，可以通过打开表的row movement属性来允许对分区字段的update操作。建议update完成后disable row movement

interval：可以按天，月，季度，年：1day，1month，3month，12month

??????????????年：interval (numtoyminterval(1,'year'))

? ? ? ? ? ? ? 月：interval (numtoyminterval(1,'month') )

? ? ? ? ? ? ? 天：interval (numtodsinterval(1,'day'))????#注意天的间隔和年、月的值不一样

案例：间隔分区之间隔范围分区。根据定义的分区字段每个月自动创建一个分区

1）查看普通表的结构，创建的分区表和普通表的结构一致

SQL> select dbms_metadata.get_ddl('TABLE','TABLELF') from dual;    ---显示TABLELF表的结构

2）创建分区表（与普通表的结构一致）

create table table_i
(
id NUMBER(20) not null primary key,
name VARCHAR2(20) not null,
age NUMBER(10) not null,
sex VARCHAR2(10) not null,
cardid NUMBER(30) not null,
joindate DATE not null,
region VARCHAR2(12) not null,
tel VARCHAR2(12) not null,
email VARCHAR2(30) not null,
recommend VARCHAR2(10),
identifier VARCHAR2(100)
)
partition by range(joindate)
interval (numtoyminterval(1,'month') )
(
partition p_001 values less than (to_date('2015-01-01','YYYY-MM-DD')),
partition p_002 values less than (to_date('2015-02-01','YYYY-MM-DD')),
partition p_003 values less than (to_date('2015-03-01','YYYY-MM-DD'))
);

SQL> insert into table_i select * from tablelf;
SQL> commit;

4）表分区查询验证

SQL> select * from dba_part_tables where table_name='TABLE_I'; ???????---记录分区的表的信息。间隔分区比较特殊虽然是间隔分区，但是查询的类型却是范围分区，只有同时查询interval字段才能确定是不是间隔范围分区

PARTITION_COUNT：不同于其他类型的分区，对于间隔分区表，此列的值总是1048575.

表的sql语句结构也体现不出间隔范围分区，只能通过interval字段确定

SQL> select * from DBA_tab_partitions where table_name='TABLE_I'; ??????---根据分区字段joindate的时间，自动创建按1个月创建分区

SQL> select * from dba_tab_subpartitions where table_name='TABLE_I'; ?---记录表的子分区信息

SQL> select * from dba_part_indexes where table_name='TABLE_I'; ?---查询分区的索引信息（interval和PARTITION_COUNT字段同dba_part_tables视图）

5）验证表数量，和每个分区的数据数量

SQL> select count(*) from table_i;

SQL> select count(*) from table_i partition(SYS_P163)    ---随机查看一个分区的数值

? ??

1.6虚拟列分区（virtual Column Partitioning）

相关语法：

column name [datatype] GENERATED ALWAYS AS [expression] [VIRTUAL]

ps：当插入数据时，虚拟列的值不需要手动添加，会自动根据?generated always as的值自动生成虚拟列的值

? ?

案例：根据日期列进行分区，每个月份一个分区，总共 12个分区。不管是哪一年的，只要是那个月，就放在那个月的分区中。

1）查看普通表的结构，创建的分区表和普通表的结构一致

SQL> select dbms_metadata.get_ddl('TABLE','TABLELF') from dual;    ---显示TABLELF表的结构

2）创建分区表（与普通表的结构一致）

SQL> create table table_v
(
id NUMBER(20) not null primary key,
name VARCHAR2(20) not null,
age NUMBER(10) not null,
sex VARCHAR2(10) not null,
cardid NUMBER(30) not null,
joindate DATE not null,
region VARCHAR2(12) not null,
tel VARCHAR2(12) not null,
email VARCHAR2(30) not null,
recommend VARCHAR2(10),
identifier VARCHAR2(100),
partition_month number(30) generated always as (to_number(to_char(joindate,'MM')))    --此列为虚拟列。joindate为注册的时间列，通过to_number(to_char(joindate,'MM'))取得月份的并将给出的字符转换为数字。参考语句：select to_number(to_char(sysdate,'mm')) from dual;
)
partition by list(partition_month)
(
partition P_M1 values(1),
partition P_M2 values(2),
partition P_M3 values(3),
partition P_M4 values(4),
partition P_M5 values(5),
partition P_M6 values(6),
partition P_M7 values(7),
partition P_M8 values(8),
partition P_M9 values(9),
partition P_M10 values(10),
partition P_M11 values(11),
partition P_M12 values(12)
);

SQL> insert into table_v(id,name,age,sex,cardid,joindate,region,tel,email,recommend,identifier) select id,name,age,sex,cardid,joindate,region,tel,email,recommend,identifier from tablelf;    
SQL> commit;           #因为虚拟列分区表比原表多partition_month列，所以直接运行insert into itpux_v1 select * from itpux_m5会报ORA-00947: not enough values或者ORA-54013: INSERT operation disallowed on virtual columns。
解决方式一：在insert时将对应的字段都写上，不写partition_month列
解决方式二：通过expdp/impdp，亲测可以插入

4）表分区查询验证

SQL> select * from dba_part_tables where table_name='TABLE_V';        ---记录分区的表的信息 
SQL> select * from DBA_tab_partitions where table_name='TABLE_V';     ---记录表的分区的信息 
SQL> select * from dba_tab_subpartitions where table_name='TABLE_V';  ---记录表的子分区信息
SQL> select * from dba_part_indexes where table_name='TABLE_V';       ---查询分区的索引信息

5）验证表数量，和每个分区的数据数量

SQL> select count(*) from TABLE_V;

SQL> select count(*) from TABLE_V partition(P_M1);
SQL> select count(*) from TABLE_V partition(P_M2);
SQL> select count(*) from TABLE_V partition(P_M3);
SQL> select count(*) from TABLE_V partition(P_M4);
SQL> select count(*) from TABLE_V partition(P_M5);
SQL> select count(*) from TABLE_V partition(P_M6);
SQL> select count(*) from TABLE_V partition(P_M7);
SQL> select count(*) from TABLE_V partition(P_M8);
SQL> select count(*) from TABLE_V partition(P_M9);
SQL> select count(*) from TABLE_V partition(P_M10);
SQL> select count(*) from TABLE_V partition(P_M11);
SQL> select count(*) from TABLE_V partition(P_M12);

? ??

1.7系统分区（system partitioning）

相关语句：

create table?table_name

( column1 ?data_type1,

???column2 ?data_type2,

) tablespace tablespace_name

partition by?system

partitions number ?tablespace tablespace_name

enable row movement;

partitions number：根据hash算法将表中的数据均匀的分成几个区

enable row movement：oracle的分区表对于分区字段（分区字段的数据不能修改，其他字段可以）是不允许进行update操作的，可以通过打开表的row movement属性来允许对分区字段的update操作。建议update完成后disable row movement

? ??

案例：将普通表的数据迁移到分区表，分区表分4个区

1）查看普通表的结构，创建的分区表和普通表的结构一致

SQL> select dbms_metadata.get_ddl('TABLE','TABLELF') from dual;    ---显示TABLELF表的结构

2）创建分区表（与普通表的结构一致）

SQL> create table table_s
(
id NUMBER(20) not null primary key,
name VARCHAR2(20) not null,
age NUMBER(10) not null,
sex VARCHAR2(10) not null,
cardid NUMBER(30) not null,
joindate DATE not null,
region VARCHAR2(12) not null,
tel VARCHAR2(12) not null,
email VARCHAR2(30) not null,
recommend VARCHAR2(10),
identifier VARCHAR2(100)
)
partition by system
partitions 4;

3）将普通表的数据插入到分区表，对于系统分区在插入分区时必须指定分区名才可以插入

SQL>?insert into table_s select * from tablelf;

4）表分区查询验证

SQL> select * from dba_part_tables where table_name='TABLE_S'; ???????---记录分区的表的信息

SQL> select * from DBA_tab_partitions where table_name='TABLE_S'; ????---记录表的分区的信息

SQL> select * from dba_part_indexes where table_name='TABLE_S'; ?????---查询分区的索引信息

5）插入数据，验证表数量，和每个分区的数据数量

SQL> insert into  TABLE_S partition(sys_p81) select * from tablelf where id<20020000;
SQL> insert into  TABLE_S partition(sys_p82) select * from tablelf where id between 20020000 and 20040000;
SQL> commit;          ---系统分区表在插入数据时，必须指定分区名才可以插入。这个就不是由数据本身决定的，而是应用程序在插入时确定的。

SQL> select count(*) from TABLE_S;

SQL> select count(*) from TABLE_S partition(sys_p81);    ---hash分区名自动分配，通过DBA_tab_partitions
SQL> select count(*) from TABLE_S partition(sys_p82);    ---hash分区名自动分配，通过DBA_tab_partitions
SQL> select count(*) from TABLE_S partition(sys_p83);    ---hash分区名自动分配，通过DBA_tab_partitions
SQL> select count(*) from TABLE_S partition(sys_p84);    ---hash分区名自动分配，通过DBA_tab_partitions

? ??

? ?

2、分区索引的作用与分类?

? ? 与分区表类似（partitioned table），分区索引（partitioned index）也能够提高系统的可管理性，可用性，可伸缩性，及系统性能。对于索引，需要区分创建的是全局索引，或本地索引：

? ? 分区索引既可以与分区表相对独立（全局索引（global index）），

? ? 也可以采用与分区表相同的分区方式（本地索引（local index））。

? ? 普通表可以建分区索引，分区表可以建非分区的 global index，也可以建range或hash分区的global index，也可以建基于分区的local index。

? ??

使用如下：?

? ?表? ? ? ? ? 索引

--------? ?------------

分区? ? ? ? 不分区

分区? ? ? ? 分区

不分区? ? 分区

不分区? ? 不分区

? ?

什么情况下建什么分区索引：

（1）当有表的分区或子分区操作维护的时候，本地索引提供更好的可用性；（如合并分区时，只有合并的表分区的分区索引失效,而非分区索引以及全局分区索引在合并.删除表分区后全部失效；可以单独重建本地索引；若只有一个分区需要维护，则只有一个本地索引受影响。

（2）本地索引可以提高性能，因为数据大，很多分区必然会被并行的查询。

（3）对于历史表，本地索引是必须，这样在有规律的 drop 分区的时候，比较方便。

（4）本地索引多应用于数据仓库环境中。

（5）在非分区字段上建立 unique 索引只能建全局索引。

（6）全局索引不支持位图索引，全局分区索引全局分区索引只能是 B 树索引。

（7）全局索引多应用于 oltp 系统中。

（8）全局分区索引只按范围或者散列 hash 分区，hash 分区是 10g 以后才支持。

（9）表用a列作分区，索引用b做局部分区索引，若where条件中用b来查询，那么oracle会扫描所有的表和索引的分区，成本会比分区更高，此时可以考虑用 b做全局分区索引。

? ?

分区索引的类型：

1、本地索引(local index)：自动维护索引分区。其分区形式与表的分区完全相同，依赖列相同，存储属性也相同。对于本地索引，其索引分区的维护自动进行，就是add/drop/split/truncate表的分区时，本地索引会自动维护其索引分区。

本地索引又可以分为有前缀(prefix)的索引和无前缀(nonprefix)的索引，而全局索引目前只支持有前缀的索引。

??前缀索引：本地索引的索引列以分区键开头（分区用什么列分的，索引就用什么列创建）

非前缀索引：本地索引的列不是以分区键开头，或者不包含分区键列（分区用什么列分的，索引不用什么列创建）

全局索引的索引列必须是以索引分区键作为其前几列，全局索引只有前缀索引。

2、全局索引(global index)：更多的手工维护索引分区。即可以分区，也可以不分区。即可以建range分区，也可以建hash分区，即可建于分区表，又可创建于非分区表上，就是说，全局索引是完全独立的，因此它也需要我们更多的维护操作。

? ?

相关视图：

SQL> select * from user/dba/all_part_tables：记录分区的表的信息

???? ? ?partition_type：表的分区类型

?????? ?subpartitoning_type：子分区类型

? ? ? ? partition_count：表的分区数量

? ? ? ? def_subpartition_count：每个分区子分区数量

? ? ? ? partition_key_count：表中有分区的字段数量

????? ? status：表分区是否生效

?????? ?def_tablespace_name：表的默认表空间，不是分区默认的表空间

SQL> select * from user/dba/all_tab_partitions：记录表的分区的信息

?????? ?subpartition_count：每个分区子分区数量

????? ? high_value：这分区保存最高的范围

?? ? ? ?tablespace_name：这个分区默认的表空间

SQL> select * from user/dba/all_tab_subpartitions：记录表的子分区信息

?? ? ? ?subpartition_name：子分区名

?? ? ? ?subpartiton_position：子分区编号，为分区的下的子分区数量

SQL> select * from user/dba/all_part_indexes：查询分区的索引信息

? ? ? ??locality：全局索引global；本地索引local

? ? ? ??alignment：前缀索引prefixed；非前缀索引non-prefixed

SQL> select * from user/dba/all_ind_partitions：记录索引分区的信息

? ? ? ? status列：usable有效，unusable无效。而普通索引通过dba_indexes查看。如果分区有子分区，显示N/A，需要通过dba_ind_subpartitions查看分区下面每个子分区的状态

SQL> select * from user/dba/all_ind_subpartitions：记录索引子分区的信息

? ? ? ? status列：usable有效，unusable无效。

? ? ?

2.1本地索引(local index自动维护索引分区。进行add/drop/split/truncate表的分区时，本地索引会自动维护其索引分区不失效。如果不加local参数那么就是普通表的单列，是不会分区的，所以特别注意是分区表时一定要加上local)

相关语法：

create index index_name on table_name (column_name1，column_name2) local

[online] tablespace tablespace _name

nologging

parallel x;

online：在线与非在线的区别：非在线锁表，优先创建索引，此时DML都被阻塞，所以快；相反，在线锁的是行而非表，通过临时表进行索引的创建，所以不会影响DML操作，但副作用就是慢。如果在生产环境操作，不停服务的话，势必导致创建索引期间仍有DML操作进来。另外如果是大表，那么采用非在线而导致锁表所带来的影响可能会很大。一句话，生产环境不停服的脚本操作，建议使用online。

?案例一：创建本地索引（创建的本地索引引用分区列，那么索引自动分区）

1）给分区表table_r的分区字段joindate创建本地索引

SQL> create index idx_table_r_joindate on table_r(joindate) local;

2)查询索引分区信息

SQL> select * from dba_part_indexes where table_name='TABLE_R'; ?????????---查询分区的索引信息

SQL> select * from dba_ind_partitions where index_name='IDX_TABLE_R_JOINDATE';??---记录索引分区的信息

3）验证统计信息是否使用分区索引

SQL>?select * from TABLE_R where joindate=to_date('2010/5/24 13:44:26','yyyy-mm-dd hh24:mi:ss'); ???

范围分区，本地索引范围扫描

?案例二：创建本地索引（创建的本地索引不引用分区列，索引还是自动分区）

1）给分区表table_r的非分区字段cardid创建本地索引

SQL> create index idx_table_r_cardid on table_r(cardid) local;

2)查询索引分区信息

?SQL> select * from dba_part_indexes where table_name='TABLE_R'; ?????????---查询分区的索引信息

SQL> select * from dba_ind_partitions where index_name='IDX_TABLE_R_CARDID';??---记录索引分区的信息

3）验证统计信息是否使用分区索引

SQL> select * from itpux_r1 where cardid=10060248242;

范围分区，本地索引范围扫描

? ?

2.2全局索引（global index手工维护索引分区。进行add/drop/split/truncate表的分区时，全局索引不会维护其索引分区会导致失效。如果不加global参数那么就是普通表的单列）

相关语法：

create index index_name on table_name (column_name1，column_name2) global

[online] tablespace tablespace _name

nologging

parallel x;

online：在线与非在线的区别：非在线锁表，优先创建索引，此时DML都被阻塞，所以快；相反，在线锁的是行而非表，通过临时表进行索引的创建，所以不会影响DML操作，但副作用就是慢。如果在生产环境操作，不停服务的话，势必导致创建索引期间仍有DML操作进来。另外如果是大表，那么采用非在线而导致锁表所带来的影响可能会很大。一句话，生产环境不停服的脚本操作，建议使用online。

? ?

案例一：全局索引之前缀range分区索引（全局索引不会维护其索引分区会导致失效）

1）分区表创建全局索引之前需要查看表的分区，并一一引用

2）创建全局索引

SQL> create index idx_table_r_id on table_r(id) global
partition by range(id)
(
  partition P10000 values less than (20010000),
  partition P20000 values less than (20020000),
  partition P30000 values less than (20030000),
  partition P40000 values less than (20040000),
  partition P50000 values less than (20050000),
  partition P_MAX values less than (MAXVALUE)
);

3）查询索引分区信息

SQL> select * from dba_part_indexes where table_name='TABLE_R'; ?????????---查询分区的索引信息

SQL> select * from dba_ind_partitions where index_name='IDX_TABLE_R_ID';????---记录索引分区的信息

4）验证统计信息是否使用分区索引

SQL> select * from itpux_r2 where id=20000039;

范围分区，全局索引范围扫描? ? ? ? ? ? ? ?

?

文章来源:https://blog.csdn.net/naisiing/article/details/135740928
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：chenni525@qq.com进行投诉反馈，一经查实，立即删除！