Oracle优化-分区表的详细解释和操作（四）「终于解决」

一、什么是表分区

表分区其实就是将一张大数据量表中的数据按照不同的分区策略分配到不同的系统分区、硬盘或是不同的服务器设备上，实现数据的均衡分配，这样做的好处是均衡大数据量数据到不同的存储介子中，这样每个分区均摊了一部分数据，然后可以定位到指定的分区中，对数据表进行需求操作，另外，也方便管理水表，比如要删除某个时间段的数据，就可以按照日期分区，然后直接删除该日期分区即可，并且效率相对于传统的DELETE数据效率高很多。

二、分区的条件 官方建议：

1、单表过大，当表的大小超过 2GB。

2、包含历史数据的表，其中新数据被添加到最新的分区中。一个典型的例子是历史表，其中只有当月的数据是可更新的，其他11个月的数据是只读的。

实际应用中每月或年超过5000w可以考虑分区。oracle 18c中，3000w的表加了并行也不慢。

三、分区表的优势 官方文档

1. 分区支持数据管理操作，例如数据加载，索引创建和重建，以及分区级别的备份/恢复，而不是整个表。这导致这些操作的时间显着减少。
2. 分区可提高查询性能。在许多情况下，查询的结果可以通过访问分区的子集而不是整个表来实现。对于某些查询，此技术（称为分区 修剪）可以提供性能的数量级增益。
3. 分区可以显着减少计划停机对维护操作的影响。
4. 分区维护操作的分区独立性允许您在同一个表或索引的不同分区上执行并发维护操作。您还可以SELECT对不受维护操作影响的分区运行并发和DML操作。
5. 如果将关键表和索引划分为多个分区以减少维护窗口，恢复时间和故障影响，则分区可提高任务关键型数据库的可用性。
6. 无需对应用程序进行任何修改即可实现分区。例如，您可以将非分区表转换为分区表，而无需修改SELECT访问该表的任何语句或DML语句。您无需重写应用程序代码即可利用分区。

四、分区类型及SQL语句

1. 范围分区 range ，在数据类项目中主要放结果表，用来供给应用系统查询。一般按照日期等分区。

范围分区即根据指定的值范围进行分区，分区键常用日期。分区的列是一个或者多个。

例句
create table table_name(列1，列2，……)
partition by range(分区列1，列2)--支持多列
(
partition 分区1 values less than (值1,值2)  tablespace t1, --tablespace可以省略
partition 分区2 values less than (值3,值4) ，-
...
partition 分区n values less than (maxvalue,值5)
)
说明：
values less than 指定分区上限，每个分区除第一个分区外会有下限，即前一个分区的上限。
如上语句所示，分区2的范围为>分区1，<分区2
maxvalue 表示无穷值，一旦出现第一个maxvalue，那么后面字段无论是什么都代表maxvalue。
举例1：多字段
CREATE TABLE sales_demo (
   year          NUMBER, 
   month         NUMBER,
   day           NUMBER) 
PARTITION BY RANGE (year,month) 
  (PARTITION before2001 VALUES LESS THAN (2001,1)  tablespace t1, --tablespace可以省略
   PARTITION q1_2001    VALUES LESS THAN (2001,4),
   PARTITION q2_2001    VALUES LESS THAN (2001,7),
   PARTITION q3_2001    VALUES LESS THAN (2001,10),
   PARTITION q4_2001    VALUES LESS THAN (2002,1),
   PARTITION future     VALUES LESS THAN (MAXVALUE,0));--这行代表无限
举例2：一个字段
create table range_part_tab (seq number,deal_date date)
partition by range (deal_date)
(
partition p1 values less than (TO_DATE('2018-11-01','YYYY-MM-DD')),
partition p2 values less than (TO_DATE('2018-12-02','YYYY-MM-DD')),
partition p3 values less than (TO_DATE('2019-01-01','YYYY-MM-DD')),
partition p4 values less than (TO_DATE('2019-02-01','YYYY-MM-DD')),
partition p5 values less than (TO_DATE('2019-03-01','YYYY-MM-DD')),
partition p6 values less than (TO_DATE('2019-04-01','YYYY-MM-DD')),
);

2. 列表分区 list

列表分区适用于分区列的值为非数字或日期数据类型，并且分区列的取值范围较少时使用。例如，成绩表中的科目列取值较少，就可以应用列表分区，实际中的机构分区等。

create table achievement
(
	id number primary key,
    name varchar2(8),
    subject varchar2(10),
    score number
)
partition by list(subject)--不支持多列
(
  partition Li_ke values('数学','物理','化学') tablespace xxx,--tablespace可以省略
  partition Wen_ke values('语文','英语','历史') tablespace yyy
)

3. 散列分区 hash 官方文档

Hash分区时，数据库根据hash算法映射行到用户指定的分区键中。行的存放目的地由数据库的内部hash函数来决定。hash算法的目的是在设备上均匀分布行，以便每个分区包含相同数量的行。哈希分区可以轻松地对数据进行分区，而不需要进行范围或列表分区。它用简单的语法做到了这一点，并且易于实现。在以下情况下，这是比范围分区更好的选择

• 您事先不知道有多少数据映射到给定的范围内
• 范围分区的大小差别很大，或者难以手动平衡
• 范围划分会导致数据不合需要地聚集
• 并行DML、分区修剪和分区方式等性能特性
• 拆分、删除或合并分区的概念不适用于哈希分区。相反，可以添加和合并哈希分区。

create table table_name()
partition by hash(列名，列名2)--支持多个列
(
partition 分区名1,
partition 分区名2,
...
)

4. 组合分区

基于两种分区的组合，分区中的分区被称为子分区。oracle11之后支持(范围范围分区)RANGE-RANGE、 (列表范围分区)LIST-RANGE、(列表散列分区)LIST-HASH、(列表列表分区)LIST-LIST这几种组合，为了避免每个主分区中都写相同的从分区，可以用模板方式(subpartition template)

create table tablename (id varchar2(10),name  varchar2(10),hdate date )
partition by range (hdate)  subpartition by list (id)
subpartition template
(subpartition s1 values (211),
subpartition s2 values (212),
subpartition s0 values (DEFAULT) )
(
partition p1 values less than (TO_DATE('2018-11-01','YYYY-MM-DD')),
partition p2 values less than (TO_DATE('2018-12-02','YYYY-MM-DD')),
);--表示p1和p2分区每个里面都有 s1,s2,s0子分区。--未测过

举两个例子： 范围-列表分区

–范围列表分区

create table 表名()
partition by range(列名1) subpartition by list(列名2)
(
partition 分区名1 values less than(值)
(
subpartition 子分区名 values (列值),
...
),
partition 分区名2 values less than(值)
(
....
)
)

–范围-散列分区

create table table_name()
partition by range(列1) subpartition by hash(列2，列3...)
(
partition 分区名1 values less than (值)
(
subpartition 子分区1,
subpartition 子分区2,
...
),
partition 分区名2 values less than （值）
(
......
)
)

五、间隔分区

• 间隔分区是范围分区的一种扩展
• 当插入的数据超过了所有范围分区时，将创建指定间隔的分区
• 必须至少创建一个范围分区
• 间隔分区可以自动创建范围分区
• 间隔分区需要考虑下列限制：
• Ø 只能指定一个分区键列，并且该键列必须是Number或Date类型
• Ø 索引表不支持间隔分区
• Ø 不能为间隔分区表创建域索引

具体查看https://blog.csdn.net/weixin_30895723/article/details/104257825

 --语法
CREATE TABLE table_name
(
  ...
)
PARTITION BY RANGE(column1)
INTERVAL expr [STORE IN (tablespace1,[tablespace2,...])]
(
  PARTITION partition_name1 VALUES LESS THAN(literal | MAXVALUE) [TABLESPACE tablespace1],
  PARTITION partition_name2 VALUES LESS THAN(literal | MAXVALUE) [TABLESPACE tablespace2]
);
--PARTITION BY RANGE(column1):指定一个分区范围列
--INTERVAL:指定分区间隔
--STORE IN：指定分区存储的表空间
--举例
create table achievement
(
	id number primary key,
    name varchar2(8),
    subject varchar2(10),
    score number
)
partition by range(score) interval(10)
(
	partition part1 values less then(60) tablespace xxx,
    partition part2 values less then(70) tablespace yyy
);
--插入一条80的数据，会发现新建立了一个分区。

六、分区索引

需要区分创建的是全局索引，或本地索引，较复杂，可以查看相关文章

六、分区并行

可以使用并行语句

ALTER INDEX … MOVE PARTITION parallel 10;

ALTER INDEX …SPLIT PARTITION parallel 10;

七、表分区的查询和维护

1. 查询: 
   select * from user_tab_partitions t;
2. 添加: 
   alter table <table_name> add partition <partition_name> values less than(to_date('2020-02-02', 'YYYY-MM-DD'));
   alter table <table_name> add partition <partition_name> values less than(1000);
3. 删除: (请注意：无法删除分区表唯一的分区，除非删除表)
   alter table <table_name> drop partition <partition_name>;
   alter table <table_name> drop subpartition <subpartition_name>;
4. 截断分区（'清空某个分区的数据'）
   alter table <table_name> truncate partition <partition_name>;
   alter table <table_name> truncate subpartition <subpartition_name>;
5. 拆分分区（'拆分后，原来分区不再存在'）
   alter table <table_name> sblit partition <p12> at(to_date('2020-01-01', 'YYYY-MM-DD')) into (partition p1, partition p2);
6. 合并分区
   alter table <table_name> merge partitions <p1>, <p2> into partition <p12>;
7. 重命名分
   alter table <table_name> rename partition <pold> to <pnew>

八、分区相关查询

查看文章：https://blog.csdn.net/u014427391/article/details/90315716

（1）查询数据库所有分区表的信息
select * from DBA_PART_TABLES

（2）查询分区表类型、是否有子分区，分区总数
select pt.partitioning_type, pt.subpartitioning_type, pt.partition_count
from user_part_tables pt

（3）查询分区详细详细：
SELECT tab.* FROM USER_TAB_PARTITIONS tab WHERE TABLE_NAME='LIST_PART_TAB'

（4）查询分区表哪列建分区
select column_name, object_type, column_position
from user_part_key_columns
where name = 'LIST_PART_TAB';

（5）查询分区表大小
select sum(bytes / 1024 / 1024)
from user_segments
where segment_name = 'LIST_PART_TAB';

（6）查询分区表各分区的大小和分区名
select partition_name, segment_type, bytes
from user_segments
where segment_name = 'LIST_PART_TAB';

（7）查询分区表各索引大小
select segment_name, segment_type, sum(bytes) / 1024 / 1024
from user_segments
where segment_name in
(select index_name
from user_indexes
where table_name = 'LIST_PART_TAB')
group by segment_name, segment_type;

（8）查询分区表的统计信息
select table_name,
partition_name,
last_analyzed,
partition_position,
num_rows
from user_tab_statistics
where table_name = 'LIST_PART_TAB';

（9）查询分区表索引情况
select table_name,
index_name,
last_analyzed,
blevel,
num_rows,
leaf_blocks,
distinct_keys,
status
from user_indexes
where table_name = 'LIST_PART_TAB';

（10）查询索引在哪些列上
select index_name, column_name, column_position
from user_ind_columns
where table_name = 'LIST_PART_TAB';

（11）查询普通表失效的索引
select ind.index_name,
ind.table_name,
ind.blevel,
ind.num_rows,
ind.leaf_blocks,
ind.distinct_keys
from user_indexes ind
where status = 'INVALID';

（12）查询分区表失效的索引
select a.blevel,
a.leaf_blocks,
a.index_name,
b.table_name,
a.partition_name,
a.status
from user_ind_partitions a, user_indexes b
where a.index_name = b.index_name
and a.status = 'UNUSABLE';