Oracle 分区类型与介绍
范围分区：一般适合于按时间周期进行数据的存储，例如时间列。方便维护数据，但是分区的数据可能不均匀。
散列分区：分区个数尽量设置为2的幂以保证数据分布的均衡化。适合于静态数据，不需要进行历史数据迁移或者清理。这类信息的访问大部分是通过用户ID或者帐号ID进行。
列表分区：例如地区号、代码号，跟范围分区类似，分区的数据可能不均匀。列表分区只支持单个字段。
范围分区和哈希分区的优缺点几乎正好相反，也正好用于不同类型的表。哈希分区适用于资料表、帐户信息等静态数据，而范围分区则适合于需要进行定期数据清理的流水表等。
组合分区：11g 之前只有 范围-哈希 和 范围-列表 两种。
11g后，结合新的间隔分区，现在共有9种组合分区：
range - range, list, hash
list - range, list, hash
interval - range, list, hash
11g 新的分区技术
间隔分区(interval)：可简化DBA的管理工作，免去规则性的创建范围分区
基于虚拟列的分区(virtual column-based)：在创建表时定义一个列，这个列对应一个表达式或者函数，然后以该列进行分区。
比如，我们创建一个INTERVAL-RANGE分区，主分区按月自动分区，子分区通过模版，使用函数计算出日期，按天分区。
引用分区(reference)：直接根据外键关系，对子表进行与主表相同的分区，且管理也可以同步，主表上增删一个分区，子表上也会自动增删一个分区。
比如，主表为订单表 orders（记录了订单号，客户号，订单日期，订单状态等信息），其主键为 order_id，通过订单日期创建了时间分区，子表为 order_items （记录了订单对应的产品价格，数量等信息），其 order_id 字段有参考 orders.order_id 的外键。则，在定义子表时，只需要写上 PARTITION BY REFERENCE(fk_name) 就可以参考主表自动分区了。
系统分区：即建表时指定使用 system 创建好分区，无需指定分区列，插入数据时，也强行指定该记录进入哪个分区。
分区注意事项
为了避免在每个主分区中都写相同的子分区，可以用模版方式来定义子分区。
对于分区表，要注意是否存在分区数据量严重不均衡的情况，比如大部分数据都进入了默认分区。
分区可以指定不同的表空间来分散 IO。
分区可以 truncate, drop, add, split, 还可以与普通表做交换。
对分区表中的数据做 update，可能会引起数据的分区迁移（ROWID 发生改变），此时需要 enable row movement。
分区表与索引的组合情况
表是否分区与索引是否分区可以两两组合，共4种组合都是可以的：
情况1：表和索引都不分区（常见）
情况2：表分区，索引不分区（常见）[如果数据访问都是通过索引，表分区与否关系不大，此时给表分区主要是考虑到维护和全表扫描时的分区裁剪]
情况3：表不分区，索引分区（不常见）[因为表没有分区，只能创建全局分区索引]
情况4：表和索引都分区（常见，最复杂，主要讨论这种情况）
本地前缀分区索引(Local Prefixed Partitioned Index)
所谓本地，是指索引的分区方法与表的分区方法一致。
所谓前缀，是指分区字段是索引字段的前缀。
例如，一个交易流水表，按交易日期字段（TXN_DATE）按年度进行了范围分区，如果欲创建TXN_DATA字段上的索引，则可以：
create index ... on table_name(TXN_DATA) local;
当某个分区进行 drop 或者 merge 操作之后，Oracle 自动对所对应的索引分区进行相应的操作，不需要进行 rebuild。
全局分区索引(Global Partitioned Index)
例如，欲在地区(AREA)字段上建立分区索引，则：
create index ... on table_name(AREA)
global partition by range(area)
(partition p1 ....,
partition p2 ....,
);
对于查询 select * from table_name where area='05711001'; Oracle 则会聪明地去杭州所在分区索引上去检索了，如果分区索引的高度低于非分区索引，则性能更好。
全局分区索引的缺陷主要体现在数据的高可用性方面，比如，该表 03 年的分区被 drop 了，则全局分区索引和普通的非分区索引都会失效，需要重建。
本地非前缀分区索引(Local noon-Prefixed Partitioned Index)
create index ... on table_name(AREA) local;
因为是本地分区索引，所以，索引的分区方法跟表的分区方法是一样的，都是按照交易日期进行分区，即 03 年的 area 字段索引在 03 年分区，04 年的 area 字段索引在 04 年分区，...也就是说，某个年份的索引分区中，包含了所有地区值。
select * from table_name where area='05711001';
因为每个分区都包含了所有地区值，所以， Oracle 会到每个分区索引中去检索，此时，就会出现分区之后，反而性能下降的问题。
但是，该类索引可以提高按索引访问的可用性，避免因为 drop 分区后而引起的普通索引和全局索引失效问题。
另外，我们可以通过附加额外的条件来提高 Oracle 使用索引的效率：
select * from table_name where txn_date = '2019.12.10' and area = 'yyyy';
这样一来，Oracle 又会自动使用上索引裁减功能，只到 12 月对应的分区索引中去检索数据了。
分区交换
凡是在一个分区大表中，要对一个分区进行某种批处理时，最好的办法是将该分区交换到一个普通表，在普通表上完成处理后，再交换为原分区表。例如，业务表中，将最早的分区归档到归档表中。
分区建议
以下就是Oracle相关文档总结的分区设计建议。
（1）表的大小：当表的大小超过1.5~2GB时，或对于OLTP系统，表的记录超过1000万条时，都应考虑对表进行分区。（非硬性，根据实际硬件性能和业务需求决定）
（2）数据访问特性：基于表的大部分查询应用，只访问表中的少量数据。对于这样的表进行分区，可充分利用分区技术排除无关数据查询的特性。
（3）数据维护：按时间段删除成批的数据，例如按月删除历史数据。对于这样的表需要考虑进行分区，以满足维护的需要。
（4）数据备份和恢复：按时间周期进行表空间的备份时，在分区与表空间之间建立起对应关系。
（5）只读数据：如果一个表中的大部分数据都是只读数据，通过对表进行分区，可将只读数据存储在只读表空间中，对于数据库的备份是非常有益的。
（6）并行数据操作：对于经常执行并行操作的表应考虑进行分区。
（7）表的可用性：当对表中部分数据的可用性要求很高时，应考虑进行分区。
分区表索引失效的情况
- 除了 split 分区，本地索引要失效，其它分区操作本地索引都不失效；
- 除了增加分区，全局索引不失效，其它任何分区操作，都要失效(truncate 一个空分区，不会导致索引失效)；但只要在分区操作命令的后面加上 update global indexes;语句，全局就不会失效； （不需要加 online）
- 增加分区，全局和本地都不失效；
- 切记 split 分区的时候，要将新增分区的局部索引 rebuild。
当分区表的分区条件无法加上时，全局索引性能要好于本地索引。例如表以A列分区，共n个分区，在B列上创建了本地索引，当以B列为条件进行查询时，因为没法分区消除，就会去遍历每一个分区的索引，其索引开销就是全局索引或者普通索引的n倍。
分区表的聚合写法有讲究
普通写法：
select max(nbr) max_nbr
from range_part_tab
where deal_date >= TO_DATE('2015-05-01', 'YYYY-MM-DD')
and deal_date < TO_DATE('2015-06-01', 'YYYY-MM-DD');
特殊写法：
select max(nbr) max_nbr from range_part_tab partition(p_201505);
这两个语句是等价的，因为普通写法的时间范围正好就是特殊写法指定的分区。
普通写法也会使用到分区修剪，只在 p_201505 里面检索数据，但是会用到该分区的全表扫描；特殊写法却会用到 INDEX FULL SCAN(MIN/MAX)。
其它分区表的聚合写法也有类似效果，例如将 max(nbr) 换成 count(*)。
将普通表转换为分区表
最保险的方式创建分区表后，将原表的数据 insert 进新的分区表。
也可以使用在线重定义技术，参考：
Database Administrator's Guide --> Managing Tables --> Redefining Tables Online 参考 example 1 来做。
查看分区表分区情况
查看表定义：
set pages 999
set long 99999
select dbms_metadata.get_ddl('TABLE','DEMOT','CFOP') from dual;
是否分区：
SQL> select OWNER,TABLE_NAME,PARTITIONED from dba_tables where TABLE_NAME = 'DEMOT';
查看表的分区：
set lines 200;
set pages 800;
select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,PARTITION_POSITION
from DBA_TAB_PARTITIONS
where TABLE_NAME='DEMOT';
查看分区列：
set lines 200;
col COLUMN_NAME for a40;
select OWNER,NAME,OBJECT_TYPE,COLUMN_NAME,COLUMN_POSITION
from DBA_PART_KEY_COLUMNS
where NAME='DEMOT' and OWNER='CFOP';
定位分区：
1.找到要drop分区中的任意一行的rowid，然后根据rowid计算出该行所在的block号：
select dbms_rowid.rowid_block_number('AAAVodAAEAAAAIPAAA') from dual;
2.根据这个block号通过 dba_extents 就可以找出其所在的分区：
select partition_name from dba_extents where BL# between block_id and block_id + blocks -1;
查询分区中的数据：
select count(*) from srsdm.SRS_M_TRAN_JOURNAL partition(PART20200614);
查看分区索引分区情况
查看索引定义：
set pages 999
set long 99999
select dbms_metadata.get_ddl('INDEX','IDX_DEMOT','CFOP') from dual;
查询表及其上的索引：
set lines 200;
select OWNER,INDEX_NAME,INDEX_TYPE,PARTITIONED,TABLE_OWNER,TABLE_NAME,TABLE_TYPE
from dba_indexes
where TABLE_NAME='DEMOT';
查看索引与索引列:
col COLUMN_NAME for a30;
set lines 200;
select INDEX_NAME,TABLE_NAME,COLUMN_NAME,COLUMN_POSITION
from DBA_IND_COLUMNS
where TABLE_NAME='DEMOT'
and TABLE_OWNER='CFOP';
查看分区索引的分区：
SELECT INDEX_OWNER,INDEX_NAME,COMPOSITE,PARTITION_NAME,STATUS
FROM DBA_IND_PARTITIONS
WHERE INDEX_NAME='IDX_DEMOT';
查看索引分区列：
set lines 200;
col COLUMN_NAME for a40;
select OWNER,NAME,OBJECT_TYPE,COLUMN_NAME,COLUMN_POSITION
from DBA_PART_KEY_COLUMNS
where NAME='IDX_DEMOT' and OWNER='CFOP';
HINT 指定索引
select /+ INDEX (tab pk_tab)/ * from test.tab;
tab是表名， pk_tab是索引名， tab前面不用加用户名
