不想深究，只想记录使用过程涉及的一些点，方便以后查阅，所以描述可能不准确也不全面，有些方法并没有用过所以，样例直接参考别人。
国庆将至，就将一些日常的使用笔记来硬混……后续应该会整理一下标签
1 Oracle分区表
Oracle分区表的描述比较复杂，包含一些手动分配表空间之类的配置过程，不同的资料给出的针对不同情况的解决方案也是五花八门。每次涉及的时候查找资料，就会感觉自己手里的这个表似乎没有必要做那么复杂的配置了。所以的话，只会往简单里说。
1.1 什么时候需要分区表？
什么时候需要分区表？当你搜索分区表的时候应该就是意识到需要用到分区表了。一般来说，一个是数据量太大，一个是因为有历史数据，需要更新的数据及历史数据放在不同分区会比较方便。
1.2 创建分区表
表分区有四种类型：
-
范围分区(Range)：指定上限进行分区
-
散列分区(Hash)：数据随机放入不同分区
-
列表分区(List)：指定某列的值来决定分区
-
组合分区(Range-Hash 或者 Range -list)
1.2.1 范围分区
/******范围分区***** 范围分区通过指定分区的上限来进行分区 可设置一个上限为maxvalue的分区 *******/ --创建示例表 create table range_example ( id number(2), done_date date, data varchar2(50) ) --创建分区，分区键为示例表（range_example）中的 done_date 字段 partition by range (done_date) ( partition part_1 values less than ( to_date('20160901', 'yyyymmdd') ), partition part_2 values less than ( to_date('20161001', 'yyyymmdd') ), partition part_3 values less than ( maxvalue ) ); --查看range_example表的分区信息 select * from user_tab_partitions where table_name = 'RANGE_EXAMPLE';
1.2.2 散列分区
/********散列分区***** 散列分区通过hash函数对指定列进行加工后决定将数据放在哪个分区 无法控制数据具体放在哪个分区 ****/ --创建示例表 create table hash_example ( id number(2), done_date date, data varchar2(50) ) --创建散列分区，分区键为示例表（hash_example）中的 done_date 字段 partition by hash (done_date) ( partition part_1, partition part_2 ); select * from user_tab_partitions where table_name = 'HASH_EXAMPLE';
1.2.3 列表分区
/**列表分区***** 通过指定列的值来创建分区 可创建default分区，不属于其他分区的数据都会放进default 但是创建了default分区之后不可以再添加更多的分区了 *********************/ --创建示例表 create table list_example ( id number(2), name varchar(30), data varchar2(50) ) --创建列表分区，分区键为示例表（list_example）中的 id 字段 partition by list (id) ( partition part_1 values ( '1', '3', '5', '7' ), partition part_2 values ( '2', '4', '6', '8' ), partition part_default values ( default ) ); select * from user_tab_partitions where table_name = 'LIST_EXAMPLE';
1.2.4 组合分区：范围-散列分区
/****组合分区：范围-散列分区****** 组合分区就是将上面三种分区套在一起，在分区中创建子分区 其中主分区是指向子分区的逻辑地址，子分区是物理地址 可以有范围-散列分区和范围-列表分区 ****/ create table range_hash_example ( id number(2), done_date date, data varchar2(50) ) --顶层范围分区的分区键为 range_hash_example 表中的 done_date 字段； --第二层散列分区的分区键为 range_hash_example 表中的 id 字段； partition by range (done_date) subpartition by hash (id) ( partition part_1 values less than ( to_date('20160901', 'yyyymmdd') ) ( subpartition part_1_sub_1, subpartition part_1_sub_2 ), partition part_2 values less than ( to_date('20161001', 'yyyymmdd') ) ( subpartition part_2_sub_1, subpartition part_2_sub_2 ), partition part_3 values less than ( maxvalue ) ( subpartition part_3_sub_1, subpartition part_3_sub_2 ) ); select * from user_tab_partitions where table_name = 'RANGE_HASH_EXAMPLE';
1.2.5 组合分区：范围-列表分区
/****组合分区：范围-列表分区**************************************/ create table range_list_example ( id number(2), done_date date, data varchar2(50) ) --顶层范围分区的分区键为 range_list_example 表中的 done_date 字段； --第二层列表分区的分区键为 range_list_example 表中的 id 字段； partition by range (done_date) subpartition by list (id) ( partition part_1 values less than ( to_date('20160901', 'yyyymmdd') ) ( subpartition part_1_sub_1 values ( '1', '3', '5' ), subpartition part_1_sub_2 values ( '2', '4', '6' ) ), partition part_2 values less than ( to_date('20161001', 'yyyymmdd') ) ( subpartition part_2_sub_1 values ( '11', '13', '15', '17' ), subpartition part_2_sub_2 values ( '12', '14' ), subpartition part_2_sub_3 values ( '16', '18' ) ), partition part_3 values less than ( maxvalue ) ( subpartition part_3_sub_1 values ( '21', '23', '25' ), subpartition part_3_sub_2 values ( '22', '24', '26' ) ) ); select * from user_tab_partitions where table_name = 'RANGE_LIST_EXAMPLE';
1.3 针对分区表的一些操作
我这里的应用主要是针对列表分区。
表名my_table，以字段month_part(字符类型)作为分区字段，分区命名month_part_yyyymm
1.3.1 查询表分区情况
--查询分区表存在情况 SELECT TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME FROM USER_TAB_PARTITIONS where upper(TABLE_NAME)=upper('my_table');
1.3.2 从分区表查询数据
对于数据量巨大的表，每次查询都需要进行全表查询，创建分区表之后，使用合适的方法，仅仅需要在对应分区查询就可以了，极大提高了查询的效率。
可以使用指定partition的方法，也可以使用where，但是使用where的时候需要注意，分区字段不能够进行转换，譬如说字符类型的分区字段，使用=数字类型数字，隐含了转换过程，还是会进行全表扫描的。
--从分区表查询数据 select * from my_table partition(MONTH_PART_202107);--分区必须存在 select * from my_table where month_part='202107'; --仅在分区查询 select * from my_table where month_part=202107; --由于分区字段是字符类型，中间进行了转换，所以是全表查询
1.3.3 向已存在的分区插入数据
向分区插入数据的前提分区已存在，散列分区不存在分区不存在的问题，范围分区和列表分区可以设置默认分区来避免分区不存在的报错。
数据插入分区表，可以指定分区，也可以不指定分区，都是能正常插入对应分区的。只试过列表分区，散列分区不确定能不能指定分区插入。
---插入数据 insert into my_table select * from my_table_t where month_part='202109'; insert into my_table partition(MONTH_PART_202109) select * from my_table_t where month_part='202109';
1.3.4 添加分区及删除分区
需要注意的是，列表分区如果已经创建了default分区，不可再添加新的分区，需要删除。
添加及删除分区的前提是该表已经是分区表。
--删除分区 alter table my_table DROP PARTITION(MONTH_PART_202109); alter table my_table DROP PARTITION(MONTH_PART_other);--之前创建的默认分区 --新建分区 alter table my_table add partition MONTH_PART_202109 values ('202109');
1.3.5 清空分区数据
如果一个表需要频繁删除数据插入数据，使用delete+insert的话，表内会产生大量的内存碎片，表空间会膨胀，查询速度也会变慢。所以建议使用truncate+insert。
如果每次删除数据时还需要保留一部分，在允许的情况下可以使用分区表的truncate+insert。
--清空分区数据 alter table my_table TRUNCATE PARTITION(MONTH_PART_202109);
1.3.6 合并分区
ALTER TABLE TABLENAME MERGE PARTITIONS P1,P2 INTO PARTITION P2;
1.3.7 复制分区表的分区结构和索引
现在找到的唯一方式是：想办法生成原表建表包含的全部结构的sql语句。
上面连接给的是英文版plsql导出sql语句的方法，即在sql运行界面右键对应表，选择【view】，在弹出页面右下角【View SQL】即可。
中文版，在sql运行界面右键对应表，选【查看】，在弹出页面右下角【查看SQL】即可。
1.3.8 分区表和索引
参考：
说的是因为建主键的同时会建一个唯一性的全局索引，在drop分区表时如果不指定update global indexes则会使索引失效，导致数据无法入库。
文章说可以将主键建成本地索引，但是我并没有深入了解。而且建立分区表的原因之一是数据量过大，而数据量过大的时候维护索引也是很大的开销。
2、普通表转分区表
普通表转换为分区表，Oracle给我们提供了以下的方式：
-
数据泵导入
-
子查询方式插入
-
分区交换
-
在线重定义
-
ALTER TABLE…MODIFY…方式（12.2之后支持）
其中除了ALTER TABLE…MODIFY…方式可以直接操作源表更改为分区表并保留索引，数据泵导入、子查询方式插入、分区交换、在线重定义都需要额外创建一个同结构的分区表，然后将原表数据及索引等结构以各种方式转到新的分区表中。
我使用的是子查询插入，即
-
使用语句创建同结构分区表
-
添加分区
-
将数据插入分区表
我这里是因为数据还不多，分区还不多，如果分区过多，可以考虑使用存储过程来进行这个过程。
3、执行计划
3.1 有关sql优化的看法
无论是创建索引还是创建分区表，都是为了优化SQL语句，提高查询效率，只是表结构更改之后进行查询，除了自己感受到的查询速度的变化，如何能更清楚直观的看待SQL语句优化后的效果呢？就像是分区表中字段值分区后，因为使用select * from my_table where month_part=202107并没有使用到分区查询不是白费力气？
本来是为了找如何分析sql语句执行过程的，找了半天，我找到了文章
3.2 Oracle执行计划
3.2.1 SQL语句
执行计划是一条查询语句在Oracle中执行过程或者访问路径的描述。
执行计划分析的过程如下所示：
EXPLAIN PLAN FOR select * from dual; select * from table(dbms_xplan.display);
结果：
3.2.2 执行计划解释
借用下图，其中横向列出来的基数是指返回结果集行数，字节是执行该步骤后返回的字节数，耗费是Oracle估计的该步骤的执行成本，用于说明SQL执行的代价，理论上越小越好。可以与上图对应。
而对象(Name)指向操作的对象，Description(Operation)则是执行的操作，包含访问表及访问表的方式。
这些操作的执行顺序：缩进越多的最先执行，缩进相同时，最上面的最先执行。
3.2.3 访问表的方式
-
TABLE ACCESS FULL：全表扫描，Oracle会读取表中的所有行，并检查是否满足where语句中的条件。当数据量太大时，建议避免全表扫描。
-
TABLE ACCESS BY ROWID：通过rowid的表存取。
-
select rowid from dual
-
rowid：oracle会自动加在表的每一行的最后一列伪列，表中并不会物理存储rowid的值，一旦一行数据插入后，则其对应的rowid在该行的生命周期内是唯一的，即使发生行迁移，该行的rowid值也不变。
-
-
行迁移：前面说“即使发生行迁移，该行的rowid值也不变”是我在文章中看到的一个说法，但是在其他文章有说法，如果需要改变rowid值，那么需要启用row movement特性，rowid发生变化的前提是启用row movement特性。
-
TABLE ACCESS BY INDEX SCAN：索引扫描。在索引块中即存储每个索引的键值，也存储具有改键值所对应的rowid。索引的扫描分为两步：首先是找到索引所对应的rowid，其次通过rowid读取该行数据。索引扫描又分五种：
-
INDEX UNIQUE SCAN：索引唯一扫描。针对唯一索引的扫描，每次至多只返回一条记录，主要针对该字段为主键或者唯一。
-
INDEX RANGE SCAN：索引范围扫描。使用一个索引存取多行数据。发生范围索引扫描有三种情况：1)在唯一索引上使用了范围操作符(如:>,<,<>,>=,<=,between) 2) 在组合索引上，只是用部分进行查询(查询时必须包含前导列，否则会走全表扫描) 3)对非唯一索引列进行的任何查询。
-
INDEX FULL SCAN：索引全扫描，进行全索引扫描时，查询出的数据都必须从索引中可以直接得到。
-
INDEX FAST FULL SCAN：索引快速扫描。扫描索引中的所有的数据块，与INDEX FULL SCAN类似，但是一个显著的区别是它不对查询出的数据进行排序。
-
INDEX SKIP SCAN：索引跳跃扫描。Oracle 9i后提供，有时候复合索引的前导列（索引包含的第一列）没有在查询语句中出现，oralce也会使用该复合索引，这时候就使用的INDEX SKIP SCAN;
当Oracle发现前导列的唯一值个数很少时，会将每个唯一值都作为常规扫描的入口，在此基础上做一次查找，最后合并这些查询；
其实认真看来，有些描述并不准确，而且还有许多如分区、并行等等相关的并未详细整理，但是应该能看懂其执行计划的结果了。
-
3.3 分区表查询语句执行计划分析
前面并没有提到的有关分区的访问方式：
-
partition list single：扫描单个分区
-
partition list iterator ：连续扫描N个分区
-
partition list inlist：不连续扫描N个分区
-
partition list full：扫描全部分区
表名:MY_TABLE，分区字段MONTH_PART，与分区字段同值的非分区字段MONTH_ID。分区情况如下。
| TABLE_NAME | PARTITION_NAME | TABLESPACE_NAME |
|---|---|---|
| MY_TABLE | MONTH_PART_202106 | ST_BIG_TAB_03 |
| MY_TABLE | MONTH_PART_202107 | ST_BIG_TAB_03 |
| MY_TABLE | MONTH_PART_202108 | ST_BIG_TAB_03 |
| MY_TABLE | MONTH_PART_202109 | ST_BIG_TAB_03 |
| MY_TABLE | MONTH_PART_202110 | ST_BIG_TAB_03 |
3.3.1 单分区查询对比
包含如下语句，可以从结果看出来，只有前两种方式是成功实现了分区表查询（PARTITION LIST SINGLE）,其他语句都是全表扫描（ PARTITION LIST ALL）。后三种全表扫描花费的预估时间差不多，使用partition访问比使用where更快。
而全表扫描并且有添加where语句，似乎都会表现为filter。而filter中也会包含一些隐含的转换，如限制字符类型等于某个数字输入，是会将表中字符类型的数据进行to_number。那么假设这个字段如month_part包含非数字字符，是否会报错呢?如果将where month_part=202107更改为month_part=to_char(202107)是否就能避免这个错误？
--1 PARTITION LIST SINGLE EXPLAIN PLAN FOR select * from MY_TABLE partition(month_part_202107); --2 PARTITION LIST SINGLE EXPLAIN PLAN FOR select * from MY_TABLE where month_part='202107'; --3 PARTITION LIST ALL EXPLAIN PLAN FOR select * from MY_TABLE where month_part=202107; --4 PARTITION LIST ALL EXPLAIN PLAN FOR select * from MY_TABLE where month_id ='202107'; --5 PARTITION LIST ALL EXPLAIN PLAN FOR select * from MY_TABLE where month_id =202107; select * from table(dbms_xplan.display);
3.3.2 多分区查询对比
如果单单从下面5个语句的结果来看，感觉并没有问题，执行过程与预期相符。where .. in ..与where ..or ..是大体等价的。使用union all的话在这种访问多分区的情况下可能会是一个很好的选择（尤其在开并行的情况下）。
只是这个时间TIME让人有些疑惑，似乎在使用month_id，而不是month_part，其预估的时间time反而会少一点。我并没有找到相关说法，或许只是我对这个字段理解有误。
但是在表中month_part与month_id是相等的，从前面单分区查询开始，其返回的行数Rows就不相等……甚至month_part='202107' 和month_part=202107也不相等，让人相当疑惑。
---1、partition list inlist 不连续分区扫描 EXPLAIN PLAN FOR select * from MY_TABLE where month_part in ('202107','202108'); ---2、partition list inlist 不连续分区扫描 EXPLAIN PLAN FOR select * from MY_TABLE where month_part='202107' or month_part='202108'; --3、partition list iterator 连续分区扫描 EXPLAIN PLAN FOR select * from MY_TABLE where month_part<='202107' ; --4、partition list single 单个分区扫描 EXPLAIN PLAN FOR select * from MY_TABLE where month_part='202107' union all select * from MY_TABLE where month_part='202108' ; --5、partition list full 全部分区扫描 EXPLAIN PLAN FOR select * from MY_TABLE where month_id in ('202107','202108'); select * from table(dbms_xplan.display);
3.3.3 更多的分区访问类型
参考:
range分区可用的操作
分区表，按 n1 ,n2 分区
-
partition range single:访问单个分区
-
partition range iterator:访问多个分区
-
partition range inlist: 分区键中用了in 例如： where n1 in(X1,X2) and n2=X3
-
partition range all: 所有的分区
-
partition range empty: 条件在分区中不存在 （或者说是找不到数据）
-
partition range or: 分区键中用了or 例如 where n1=X1 or n2=X2
-
partition range subquery:
-
partition range join-filter:
-
partition range multi-column:
hash分区可用的操作
-
partition hash single:
-
partition hash iterator:
-
partition hash inlist
-
partition hash all
-
partition hash subquery
-
partition hash join-filter
比range少了partition range or和partition range multi-column
list分区可用的操作
-
partition list single
-
partition list iterator
-
partition list inlist
-
partition list all
-
partition list empty
-
partition list or
-
partition list subquery
-
partition list join-filter
3.4 开平行
这是一个额外话题，只记录一下，并不想展开。
下面也是分析分区表的结果之一，我跑完3.3全部语句，并且截了图，对截图结果一脸疑惑不知道怎么开始说，因为结果与我预期的并不一样，结果大部分都如下图所示没什么差别，也就TIME上以及使用union all的时候有点差异。
后面发现，是因为我运行执行计划分析的SQL界面，之前我用的时候开了并行的缘故。 所以并不深入研究了。只一个点记录一下，在对表进行多分区查询的时候，使用union all的方式在并行的情况下比用in、or的方式快了很多。
EXPLAIN PLAN FOR select * from my_table where month_part='202107';
参考