分区使用了一种 “分而治之" 的方法，适用于管理非常大的表和索引。它可以将一个表或索引物理地分解为多个更小、更易管理的部分，尽管分区表、索引可能由数十个物理分区组成，但就访问数据库的应用而言，它访问的只是逻辑上的一个表或索引。每个分区都是一个独立的对象，我们可以单独处理，也可以将其作为一个更大对象的一部分进行处理。
分区概述
分区引入了一种 分区键（partition key）的概念，数据会根据其分区键值写入到相应的分区上。
应用场景
- 表的数据量超过 2GB
- 用于存储历史数据的表且新数据会加入至最新分区中
分区优点
- 提高可用性
可用性的提高来源于每个分区的独立性，表或索引中一个分区的可用性或不可用性不会影响表或索引本身的可用性。如果你的表或索引是分区的，那么查询优化器就会意识到这点，并且在执行计划中去除不需要的分区。
比如说一个大对象中的一个分区不可用，而你的查询又不需要这个分区，那么 Oracle 还是能成功地处理这个查询。
- 减少管理负担
分区机制之所以能够减少管理负担，是因为与在一个大对象上执行操作相比，在小对象上执行同样的操作更为容易，速度更快。
- 增强语句性能
利用分区修剪特性，对分区对象的查询可以仅搜索自己关心的分区，从而提高检索速度。
- 均衡 I/O
可以把不同的分区映射到磁盘上以平衡 I/O，从而改善整个系统的性能。
表分区机制
| 序号 | 分区方法 | 关键字 |
| 1 | 区间分区 | RANGE |
| 2 | 散列分区 | HASH |
| 3 | 列表分区 | LIST |
| 4 | 间隔分区 | INTERVAL |
| 5 | 组合分区 | SUBPARTITION |
- 区间分区
下面的 SQL 语句创建了一个使用 RANGE_KEY_COLUMN 列作为分区键的区间分区表，RANGE_KEY_COLUMN 的值严格小于 2015-01-01 的数据会放在分区 RANGE_EXAMPLE_2014 中，RANGE_KEY_COLUMN 的值严格小于 2016-01-01 的数据会放在分区 RANGE_EXAMPLE_2015 中。
SQL> create table range_example(
range_key_column date not null,
data varchar2(20)
)
partition by range (range_key_column)
(
partition range_example_2014 values less than (to_date('2015-01-01', 'YYYY-MM-DD')),
partition range_example_2015 values less than (to_date('2016-01-01', 'YYYY-MM-DD'))
);
SQL> insert into range_example(range_key_column, data) values(to_date('2013-02-15', 'YYYY-MM-DD'), 'Application Data...');
SQL> insert into range_example(range_key_column, data) values(to_date('2014-10-15', 'YYYY-MM-DD'), 'Application Data...');
SQL> insert into range_example(range_key_column, data) values(to_date('2015-01-01', 'YYYY-MM-DD'), 'Application Data...');
SQL> COMMIT;
SQL> select * from range_example partition(range_example_2014);
RANGE_KEY_ DATA
---------- --------------------
2013-02-15 Application Data...
2014-10-15 Application Data...
SQL> select * from range_example partition(range_example_2015);
RANGE_KEY_ DATA
---------- --------------------
2015-01-01 Application Data...
如果插入数据到区间分区中，但其分区键值并不属于任何一个区间，那么数据将不能插入，因为它们无法映射到任何分区。
SQL> insert into range_example(range_key_column, data)
values(to_date('2016-07-20', 'YYYY-MM-DD'), 'Application Data...');
第 1 行出现错误:
ORA-14400: 插入的分区关键字未映射到任何分区
对于上述情形有两种解决方案，一种是使用间隔分区，另一种是利用区间分区的 MAXVALUE 子句实现，此时如果新插入数据到区间分区中，但其分区键值不符合前两个条件，则会存入 MAXVALUE 分区中。
SQL> alter table range_example add partition range_example_max values less than (maxvalue);
SQL> insert into range_example(range_key_column, data) values(to_date('2016-07-20', 'YYYY-MM-DD'), 'Application Data...');
SQL> COMMIT;
SQL> select * from range_example partition(range_example_max);
RANGE_KEY_ DATA
---------- --------------------
2016-07-20 Application Data...
需要注意的是，一旦区间分区表有一个 MAXVALUE 分区，就不能再向这个表中增加更多的分区了，除非我们先移除 MAXVALUE 分区，然后再增加新的 RANGE_EXAMPLE_2016 分区，但是这会造成一个问题，删除 RANGE_EXAMPLE_MAX 分区的同时，该分区的数据也会被清除。所以推荐使用间隔分区来自动扩展分区。
SQL> alter table range_example add partition range_example_2016 values less than (to_date('2017-01-01','YYYY-MM-DD'));
第 1 行出现错误:
ORA-14074: 分区界限必须调整为高于最后一个分区界限
SQL> alter table range_example drop partition range_example_max;
SQL> alter table range_example add partition range_example_2016 values less than (to_date('2017-01-01','YYYY-MM-DD'));
表已更改。
另外，一旦删除分区后，全局索引结构也会变更，这就需要我们手动进行维护，这部分知识，将在后面的章节中讲到。
- 散列分区
对于散列分区表，Oracle 会使用一个散列函数对每一条插入数据的分区列值计算出其散列值，以此确定数据应当放在 N 个分区中的哪一个分区。Oracle 建议 N 是 2 的一个幂（2、4、8 等），这样表中的数据才能最好的分布在所有的分区上；
下面的 SQL 语句将创建一个带有两个分区的散列表，并将 HASH_KEY_COLUMN 列作为分区键，Oracle 会取这个列中的值，并计算它的散列值，从而确定一行数据将存储在哪个分区中。
SQL> create table hash_example(
hash_key_column date,
data varchar2(20)
)
partition by hash (hash_key_column)
(
partition hash_example_1,
partition hash_example_2
);
SQL> insert into hash_example(hash_key_column, data) values(to_date('2014-06-25', 'YYYY-MM-DD'), 'Application Data...');
SQL> insert into hash_example(hash_key_column, data) values(to_date('2015-09-27', 'YYYY-MM-DD'), 'Application Data...');
SQL> COMMIT;
SQL> select * from hash_example partition(hash_example_1);
HASH_KEY_C DATA
---------- --------------------
2015-09-27 Application Data...
SQL> select * from hash_example partition(hash_example_2);
HASH_KEY_C DATA
---------- --------------------
2014-06-25 Application Data...
需要注意的是，如果使用散列分区，我们将无法控制数据行最终存放至哪个分区中，Oracle 会应用散列函数，并根据散列的结果来确定数据行最终存放至哪里。
- 列表分区
列表分区为每个分区指定了一个离散值列表，每行数据根据其分区键值所属列表归至相应的分区中。
SQL> create table list_example(
state_cd varchar2(2),
data varchar2(20)
)
partition by list(state_cd)
(
partition list_example_1 values('ME', 'NH', 'VT', 'MA'),
partition list_example_2 values('CT', 'RI', 'NY')
);
SQL> insert into list_example(state_cd, data) values('VT', 'Application data...');
SQL> insert into list_example(state_cd, data) values('NY', 'Application data...');
SQL> COMMIT;
SQL> select * from list_example partition(list_example_1);
ST DATA
-- --------------------
VT Application data...
SQL> select * from list_example partition(list_example_2);
ST DATA
-- --------------------
NY Application data...
如果插入数据到列表分区中，但其分区键值并不属于任何一个列表，那么数据将不能插入，因为它们无法映射到任何分区，当然可以使用 VALUES(DEFAULT) 来解决这个问题。
- 间隔分区
间隔分区就是以一个区间分区作为"起点"，不过它在定义中还有一个规则（即间隔），让数据库知道将来如何增加分区，换句话说，我们无需预先为数据库创建分区，而是在插入数据时让数据库自己创建分区；
间隔分区表的分区键列只能为单列，并且其数据类型必须能够与 NUMBER 或 INTERVAL 类型进行加法计算（例如，VARCHAR2 字段就不能用于间隔分区，因为不能让 VARCHAR2 与某个数字或间隔相加）；
在间隔分区表上，每当数据插入时，数据库就会去查看数据所属分区是否存在，并按需来创建新分区。
间隔分区的分区方式：
| 序号 | 分区方式 | 说明 |
| 1 | INTERVAL(NUMTODSINTERVAL(1, 'DAY')) | 按天分区 |
| 2 | INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) | 按月分区 |
| 3 | INTERVAL(NUMTOYMINTERVAL(1, 'YEAR')) | 按年分区 |
| 4 | INTERVAL(10000) | 按数字分区 |
- 按天分区
SQL> create table interval_day_example(
ts timestamp,
data varchar2(20)
)
partition by range(ts)
interval(numtodsinterval(1, 'DAY'))
store in (users, example)
(
partition interval_day_example_1 values less than (to_date('2011-01-01 00', 'YYYY-MM-DD HH24'))
);
SQL> insert into interval_day_example(ts, data) values(to_timestamp('2010-12-23 23', 'YYYY-MM-DD HH24'), 'Application data...');
SQL> insert into interval_day_example(ts, data) values(to_timestamp('2011-01-01 01', 'YYYY-MM-DD HH24'), 'Application data...');
SQL> COMMIT;
SQL> select
t.table_name,
t.partition_name,
t.partition_position
from user_tab_partitions t where table_name = 'INTERVAL_DAY_EXAMPLE';
TABLE_NAME PARTITION_NAME PARTITION_POSITION
------------------------------ ------------------------------ ------------------
INTERVAL_DAY_EXAMPLE INTERVAL_DAY_EXAMPLE_1 1
INTERVAL_DAY_EXAMPLE SYS_P41 2
store in (users, example) 由此告诉数据库在哪里创建这些新的分区，也就是使用哪些表空间。
- 按月分区
SQL> create table interval_month_example(
ts timestamp,
data varchar2(20)
)
partition by range(ts)
interval(numtoyminterval(1, 'MONTH'))
store in (users, example)
(
partition interval_month_example_1 values less than (to_date('2011-01-01', 'YYYY-MM-DD'))
);
SQL> insert into interval_month_example(ts, data) values(to_timestamp('2010-07-23', 'YYYY-MM-DD'), 'Application data...');
SQL> insert into interval_month_example(ts, data) values(to_timestamp('2010-12-20', 'YYYY-MM-DD'), 'Application data...');
SQL> insert into interval_month_example(ts, data) values(to_timestamp('2011-01-02', 'YYYY-MM-DD'), 'Application data...');
SQL> insert into interval_month_example(ts, data) values(to_timestamp('2011-02-28', 'YYYY-MM-DD'), 'Application data...');
SQL> COMMIT;
SQL> select
t.table_name,
t.partition_name,
t.partition_position
from user_tab_partitions t where table_name = 'INTERVAL_MONTH_EXAMPLE';
TABLE_NAME PARTITION_NAME PARTITION_POSITION
------------------------------ ------------------------------ ------------------
INTERVAL_MONTH_EXAMPLE INTERVAL_MONTH_EXAMPLE_1 1
INTERVAL_MONTH_EXAMPLE SYS_P44 2
INTERVAL_MONTH_EXAMPLE SYS_P45 3
- 按年分区
SQL> create table interval_year_example(
ts timestamp,
data varchar2(20)
)
partition by range(ts)
interval(numtoyminterval(1, 'YEAR'))
store in (users, example)
(
partition interval_month_example_2010 values less than (to_date('2011-01-01', 'YYYY-MM-DD'))
);
- 按数字分区
SQL> create table interval_number_example( ts number, data varchar2(20) ) partition by range(ts) interval(10000) store in (users, example) ( partition interval_number_example_2010 values less than (20110101), partition interval_number_example_2011 values less than (20120101) );
- 组合分区
组合分区是区间分区、散列分区以及列表分区的组合，当我们使用组合分区时，数据库中并不存在分区段，而是只存在子分区段，分区本身并没有分区段。数据实际存储在子分区段上，分区此时就成为了容纳子分区的逻辑容器。
区间分区与散列分区的组合：
SQL> create table composite_range_hash_example(
range_key_column date,
hash_key_column int,
data varchar2(20)
)
partition by range(range_key_column)
subpartition by hash(hash_key_column)
(
partition part_1 values less than(to_date('2014-01-01', 'YYYY-MM-DD'))
(
subpartition part_1_sub_1,
subpartition part_1_sub_2
),
partition part_2 values less than(to_date('2015-01-01', 'YYYY-MM-DD'))
(
subpartition part_2_sub_1,
subpartition part_2_sub_2
)
);
区间分区与列表分区的组合：
SQL> create table composite_range_list_example(
range_key_column date,
list_key_column int,
data varchar2(20)
)
partition by range(range_key_column)
subpartition by list(list_key_column)
(
partition part_1 values less than(to_date('2014-01-01', 'YYYY-MM-DD'))
(
subpartition part_1_sub_1 values(1, 3, 5, 7),
subpartition part_1_sub_2 values(2, 4, 6, 8)
),
partition part_2 values less than(to_date('2015-01-01', 'YYYY-MM-DD'))
(
subpartition part_2_sub_1 values(1, 3),
subpartition part_2_sub_2 values(5, 7),
subpartition part_2_sub_3 values(2, 4, 6, 8)
)
);
SQL> insert into composite_range_list_example(range_key_column, list_key_column, data) values(to_date('2012-10-19', 'YYYY-MM-DD'), 1, 'Application data...');
SQL> insert into composite_range_list_example(range_key_column, list_key_column, data) values(to_date('2014-03-20', 'YYYY-MM-DD'), 6, 'Application data...');
SQL> COMMIT;
统计信息收集：
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'COMPOSITE_RANGE_LIST_EXAMPLE',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
granularity => 'AUTO',
cascade => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE REPEAT');
end;
/
查看分区及统计信息：
SQL> select table_name, partition_name, subpartition_count, num_rows from user_tab_partitions s where table_name = 'COMPOSITE_RANGE_LIST_EXAMPLE'; TABLE_NAME PARTITION_NAME SUBPARTITION_COUNT NUM_ROWS ------------------------------ ------------------------------ ------------------ ---------- COMPOSITE_RANGE_LIST_EXAMPLE PART_1 2 1 COMPOSITE_RANGE_LIST_EXAMPLE PART_2 3 1 SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_subpartitions s where table_name = 'COMPOSITE_RANGE_LIST_EXAMPLE'; TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS ------------------------------ ------------------------------ ------------------------------ ---------- COMPOSITE_RANGE_LIST_EXAMPLE PART_1 PART_1_SUB_2 0 COMPOSITE_RANGE_LIST_EXAMPLE PART_1 PART_1_SUB_1 1 COMPOSITE_RANGE_LIST_EXAMPLE PART_2 PART_2_SUB_3 1 COMPOSITE_RANGE_LIST_EXAMPLE PART_2 PART_2_SUB_2 0 COMPOSITE_RANGE_LIST_EXAMPLE PART_2 PART_2_SUB_1 0
分区表索引
分区表创建好了之后，如果需要最大化分区表的性能就需要结合索引的使用，分区表上可以创建有两种索引： 本地索引和全局索引，而全局索引又可以分为全局分区索引和全局非分区索引。
关于 LOCAL INDEX、GLOBAL INDEX、GLOBAL PARTITION INDEX 的适用场景：
- 一个大原则，能创建
LOCAL INDEX的尽量创建LOCAL INDEX，无论从分区管理角度，还是查询性能角度LOCAL INDEX都具有优势； - 当查询条件和分区键值不符的情况下，例如主键是保单号，分区键是日期，且又要通过保单号查询，这种情况下，将保单号字段创建为
GLOBAL INDEX较为合适； - 如果主键是复合索引并且包含分区键，或者主键本身就是分区键则当然还是使用
LOCAL INDEX； - 如果查询条件中包含分区键，那肯定是创建
LOCAL INDEX，因为可以使用到本地索引分区修剪技术； - 在 Oracle 官方文档中，一般建议 OLTP 系统因为查询条件变化多，单次查询数据量少，所以会较多
GLOBAL INDEX，而在 OLAP 系统，多从维护和并行查询角度出发，建议多LOCAL INDEX； - 在 OLTP 系统中，
GLOBAL INDEX可能会导致并发竞争，为了缓解并发竞争，在分区数量不多的情况下，可能会选择LOCAL INDEX，虽然串行扫描多个分区会降低性能，但缓解了并发竞争的消耗，而取得一个平衡；在分区数量多的情况下，这个时候更好的选择应该是GLOBAL PARTITION INDEX，因为如果分区过多，LOCAL INDEX全分区扫描的性能会大幅降低，这样单次执行的时间会变长，会加剧并发竞争。
本地索引
本地索引特点：
- 本地索引一定是分区索引，索引分区数等同于表的分区数，即本地索引的分区机制和表的分区机制一样，一般建议在表的分区键上创建本地索引；
- 如果本地索引的索引列以表的分区键作为前导列，则称为本地前缀索引；
- 如果本地索引的列不是以表的分区键作为前导列，或者不包含表的分区键列，则称为本地非前缀索引；
- 本地前缀索引和本地非前缀索引都可以用于分区修剪，前提是查询的条件中包含本地索引键；
- 本地索引是对单个分区的，每个本地索引都会指向一个表分区，全局分区索引则不然，一个全局分区索引能指向 n 个表分区，同时，一个表分区，也可能指向 n 个索引分区，对分区表中的某个分区做
TRUNCATE或者SPLIT等 操作，可能会影响到 n 个全局分区索引，正因为这点，本地分区索引具有更高的可用性； - 位图索引只能为本地分区索引；
- 本地索引多应用于数据仓库环境中。
本地索引列为分区键且查询条件使用本地索引键：
SQL> create table local_index_example (
n_id number,
d_date date,
c_desc varchar2(4000)
)
partition by range (d_date) (
partition local_index_example_q1_2014 values less than (to_date('2014-04-01', 'YYYY-MM-DD')),
partition local_index_example_q2_2014 values less than (to_date('2014-07-01', 'YYYY-MM-DD')),
partition local_index_example_q3_2014 values less than (to_date('2014-10-01', 'YYYY-MM-DD')),
partition local_index_example_q4_2014 values less than (to_date('2015-01-01', 'YYYY-MM-DD'))
);
SQL> create index idx_local_index_example on local_index_example(d_date) local;
SQL> insert into local_index_example
select
rownum as n_id,
trunc(to_date('2014-01-01', 'YYYY-MM-DD') + rownum / 27.4) as d_date,
dbms_random.string('p', 255) as c_data
from dual
connect by level <= 10000
order by d_date;
--统计信息收集
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'LOCAL_INDEX_EXAMPLE',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
granularity => 'AUTO',
cascade => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE REPEAT',
degree => 4);
end;
/
--查看执行计划
SQL> alter session set statistics_level=all;
SQL> select * from local_index_example where d_date = to_date('2014-07-19', 'YYYY-MM-DD');
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'iostats last partition'));
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | Buffers |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 7 |
| 1 | PARTITION RANGE SINGLE | | 1 | 3 | 3 | 7 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| LOCAL_INDEX_EXAMPLE | 1 | 3 | 3 | 7 |
|* 3 | INDEX RANGE SCAN | IDX_LOCAL_INDEX_EXAMPLE | 1 | 3 | 3 | 4 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D_DATE"=TO_DATE(' 2014-07-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
本地索引列为非分区键且查询条件使用本地索引键：
SQL> create index idx_local_index_example2 on local_index_example(n_id) local;
--统计信息收集
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'LOCAL_INDEX_EXAMPLE',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
granularity => 'AUTO',
cascade => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE REPEAT',
degree => 4);
end;
/
--查看执行计划
SQL> alter session set statistics_level=all;
SQL> select * from local_index_example where n_id = 1000;
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'iostats last partition'));
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | Buffers |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 10 |
| 1 | PARTITION RANGE ALL | | 1 | 1 | 4 | 10 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| LOCAL_INDEX_EXAMPLE | 4 | 1 | 4 | 10 |
|* 3 | INDEX RANGE SCAN | IDX_LOCAL_INDEX_EXAMPLE2 | 4 | 1 | 4 | 9 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("N_ID"=1000)
从执行计划中可以看出，有一个 PARTITION RANGE ALL 操作，这表示当本地索引列不是表的分区键列时，查询优化器扫描了所有的分区，可以查看下面全局索引的例子，对比执行计划中 Buffer 列的值。
全局索引
在创建索引时如果不显式指定 GLOBAL 或 LOCAL，则索引默认是 GLOBAL INDEX。
- 全局索引的索引条目指向分区表的所有分区
- 对分区表中的数据进行
DDL操作后（如SPLIT、TRUNCATE等），都会造成分区表上的全局索引失效 - 表用 a 列作分区键，索引用 b 列做本地索引，若 where 条件中用 b 列来查询，那么此时 Oracle 会扫描所有的表分区和本地索引分区，成本会比分区更高，此时可以考虑用 b 列做全局索引。
SQL> create table global_index_example (
n_id number,
d_date date,
c_desc varchar2(4000)
)
partition by range (d_date) (
partition global_index_example_q1_2014 values less than (to_date('2014-04-01', 'YYYY-MM-DD')),
partition global_index_example_q2_2014 values less than (to_date('2014-07-01', 'YYYY-MM-DD')),
partition global_index_example_q3_2014 values less than (to_date('2014-10-01', 'YYYY-MM-DD')),
partition global_index_example_q4_2014 values less than (to_date('2015-01-01', 'YYYY-MM-DD'))
);
SQL> create index idx_global_index_example on global_index_example(n_id) global;
SQL> insert into global_index_example
select
rownum as n_id,
trunc(to_date('2014-01-01', 'YYYY-MM-DD') + rownum / 27.4) as d_date,
dbms_random.string('p', 255) as c_data
from dual
connect by level <= 10000
order by d_date;
--统计信息收集
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'GLOBAL_INDEX_EXAMPLE',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
granularity => 'AUTO',
cascade => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE REPEAT',
degree => 4);
end;
/
--查看执行计划
SQL> alter session set statistics_level=all;
SQL> select * from global_index_example where n_id = 1000;
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'iostats last partition'));
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | Buffers |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| GLOBAL_INDEX_EXAMPLE | 1 | ROWID | ROWID | 4 |
|* 2 | INDEX RANGE SCAN | IDX_GLOBAL_INDEX_EXAMPLE | 1 | | | 3 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N_ID"=1000)
从执行计划中可以看出，Buffers 列的值， 要明显低于上例中的值。
全局分区索引
全局分区索引特点：
- 全局分区索引必须是前缀索引，即全局分区索引的索引列必须是以索引的分区键作为其前导列；
SQL> create table partitioned(
timestamp date,
id int
)
partition by range(timestamp)
(
partition part_1 values less than(to_date('2014-01-01', 'YYYY-MM-DD')),
partition part_2 values less than(to_date('2015-01-01', 'YYYY-MM-DD'))
);
下例中，全局分区索引的索引前导列 id 同时也是分区索引中的分区键 id， 所以创建成功。
SQL> create index idx_global_partitioned1 on partitioned(id) GLOBAL partition by range(id) ( partition part_1 values less than(100), partition part_2 values less than(maxvalue) ); 索引已创建。
下例中，全局分区索引的索引前导列 id 不是分区索引中的分区键 timestamp， 所以创建失败。
SQL> create index idx_global_partitioned2 on partitioned(id)
GLOBAL
partition by range(timestamp)
(
partition part_1 values less than(to_date('2014-01-01', 'YYYY-MM-DD')),
partition part_2 values less than(maxvalue)
);
第 3 行出现错误:
ORA-14038: GLOBAL 分区索引必须加上前缀
下例中，全局分区索引的索引前导列 timestamp 同时也是分区索引中的分区键 timestamp， 所以创建成功。
SQL> create index idx_global_partitioned3 on partitioned(timestamp, id)
GLOBAL
partition by range(timestamp)
(
partition part_1 values less than(to_date('2014-01-01', 'YYYY-MM-DD')),
partition part_2 values less than(maxvalue)
);
索引已创建。
下例中，全局分区索引的索引前导列 timestamp 不是分区索引中的分区键 id， 所以创建失败。
SQL> create index idx_global_partitioned4 on partitioned(timestamp, id) GLOBAL partition by range(id) ( partition part_1 values less than(100), partition part_2 values less than(maxvalue) ); 第 3 行出现错误: ORA-14038: GLOBAL 分区索引必须加上前缀
- 全局分区索引要求其最后一个分区的分区上界必须为
MAXVALUE：
SQL> drop index idx_global_partitioned1; SQL> create index idx_global_partitioned1 on partitioned(id, timestamp) GLOBAL partition by range(id) ( partition part_1 values less than(100), partition part_2 values less than(200) ); 第 7 行出现错误: ORA-14021: 必须指定所有列的 MAXVALUE
- 全局分区索引可能无法与位图索引一起使用
SQL> create bitmap index idx_global_partitioned on partitioned(id) GLOBAL partition by range(id) ( partition part_1 values less than(100), partition part_2 values less than(maxvalue) ); 第 2 行出现错误: ORA-25113: GLOBAL 可能无法与位图索引一起使用
-
全局分区索引的索引条目可能指向若干个分区，因此，对于全局分区索引，即使只截断一个分区中的数据，都需要
REBULID若干个分区甚至是整个索引 -
全局分区索引多应用于 OLTP 系统中；
-
全局分区索引只能按
RANGE或者HASH分区； -
对分区表中的数据进行
DDL操作后（如SPLIT、TRUNCATE等），都会造成分区表上的全局分区索引失效， 但可以同时指定用UPDATE GLOBAL INDEXES子句来同步更新全局分区索引，用消耗一定的资源来换取高度的可用性； -
全局分区索引不能够将其作为整体重建，必须对每个分区重建
分区表、索引管理与维护
对于分区表，我们可以执行如下几类操作，但需要注意的是，这些操作有可能会导致索引的失效（状态从 USABLE 变为 UNUSABLE），为了验证索引的有效性，针对这几种操作，我会在对应的分区表上创建三类索引 LOCAL INDEX、GLOBAL INDEX 和 GLOBAL PARTITION INDEX，分别检查在对应的操作下，索引的状态。
分区表管理
| 序号 | 操作类型 | 关键字 |
| 1 | 增加分区 | ADD |
| 2 | 移动分区 | MOVE |
| 3 | 截断分区 | TRUNCATE |
| 4 | 删除分区 | DROP |
| 5 | 拆分分区 | SPLIT |
| 6 | 合并分区 | MERGE |
| 7 | 交换分区 | EXCHANGE |
| 8 | 收缩分区 | COALESCE |
| 9 | 重命名分区 | RENAME |
- 增加分区
增加分区维护操作，主要针对当前分区表执行添加新分区的操作。
创建分区表 RANGE_ADD_EXAMPLE，使用 RANGE 分区：
SQL> create table range_add_example (
n_id number,
d_date date,
c_desc varchar2(4000)
)
partition by range (d_date) (
partition range_add_example_2014 values less than (to_date('2015-01-01', 'YYYY-MM-DD')),
partition range_add_example_2015 values less than (to_date('2016-01-01', 'YYYY-MM-DD'))
);
--创建本地索引、全局索引、全局分区索引
SQL> create index idx_add_local on range_add_example(d_date) local;
SQL> create index idx_add_global on range_add_example(c_desc) global;
SQL> create index idx_add_global_partition on range_add_example(n_id) global
partition by range(n_id)
(
partition idx_add_global_partition01 values less than (5000),
partition idx_add_global_partition02 values less than (maxvalue)
);
写入数据：
SQL> insert into range_add_example
select
rownum as n_id,
to_date('2014-01-01', 'YYYY-MM-DD') + rownum - 1 as d_date,
dbms_random.string('p', 255) as c_desc
from dual
connect by level <= 600
order by d_date;
SQL> COMMIT;
统计信息收集：
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'RANGE_ADD_EXAMPLE',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
granularity => 'AUTO',
cascade => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE REPEAT',
degree => 4);
end;
/
查看索引分区及状态：
SQL> select t2.table_name, t1.index_name, t2.partition_name, t3.column_name, t1.subpartition_count, t2.num_rows, t2.clustering_factor, t1.status from user_ind_partitions t1, user_ind_statistics t2, user_ind_columns t3 where t1.index_name = t2.index_name and t3.index_name = t2.index_name and t1.partition_name = t2.partition_name and t2.table_name = 'RANGE_ADD_EXAMPLE' union all select t4.table_name, t4.index_name, null partition_name, t5.column_name, null subpartition_count, t4.num_rows, t4.clustering_factor, t4.status from user_indexes t4, user_ind_columns t5 where t4.index_name = t5.index_name and t4.table_name = 'RANGE_ADD_EXAMPLE' and t4.status <> 'N/A' order by 2, 3; TABLE_NAME INDEX_NAME PARTITION_NAME COLUMN_NAME SUBPARTITION_COUNT NUM_ROWS CLUSTERING_FACTOR STATUS ------------------------------ ----------------------------------- ------------------------------ --------------- ------------------ ---------- ----------------- -------- RANGE_ADD_EXAMPLE IDX_ADD_GLOBAL C_DESC 600 574 VALID RANGE_ADD_EXAMPLE IDX_ADD_GLOBAL_PARTITION IDX_ADD_GLOBAL_PARTITION01 N_ID 0 600 25 USABLE RANGE_ADD_EXAMPLE IDX_ADD_GLOBAL_PARTITION IDX_ADD_GLOBAL_PARTITION02 N_ID 0 0 0 USABLE RANGE_ADD_EXAMPLE IDX_ADD_LOCAL RANGE_ADD_EXAMPLE_2014 D_DATE 0 365 15 USABLE RANGE_ADD_EXAMPLE IDX_ADD_LOCAL RANGE_ADD_EXAMPLE_2015 D_DATE 0 235 10 USABLE
新增分区：
SQL> alter table range_add_example add partition
range_add_example_2016 values less than (to_date('2017-01-01', 'YYYY-MM-DD'));
表已更改。
需要注意的是，当分区表存在默认的最大分区，如：RANGE 分区表的 MAXVALUE 分区、LIST 分区表的 DEFAULT 分区，此时增加分区操作会报错，必须先删除默认的最大分区，再执行新增操作。
新增分区后，索引状态：
SQL> select t2.table_name, t1.index_name, t2.partition_name, t3.column_name, t1.subpartition_count, t2.num_rows, t2.clustering_factor, t1.status from user_ind_partitions t1, user_ind_statistics t2, user_ind_columns t3 where t1.index_name = t2.index_name and t3.index_name = t2.index_name and t1.partition_name = t2.partition_name and t2.table_name = 'RANGE_ADD_EXAMPLE' union all select t4.table_name, t4.index_name, null partition_name, t5.column_name, null subpartition_count, t4.num_rows, t4.clustering_factor, t4.status from user_indexes t4, user_ind_columns t5 where t4.index_name = t5.index_name and t4.table_name = 'RANGE_ADD_EXAMPLE' and t4.status <> 'N/A' order by 2, 3; TABLE_NAME INDEX_NAME PARTITION_NAME COLUMN_NAME SUBPARTITION_COUNT NUM_ROWS CLUSTERING_FACTOR STATUS ------------------------------ ----------------------------------- ------------------------------ --------------- ------------------ ---------- ----------------- -------- RANGE_ADD_EXAMPLE IDX_ADD_GLOBAL C_DESC 600 574 VALID RANGE_ADD_EXAMPLE IDX_ADD_GLOBAL_PARTITION IDX_ADD_GLOBAL_PARTITION01 N_ID 0 600 25 USABLE RANGE_ADD_EXAMPLE IDX_ADD_GLOBAL_PARTITION IDX_ADD_GLOBAL_PARTITION02 N_ID 0 0 0 USABLE RANGE_ADD_EXAMPLE IDX_ADD_LOCAL RANGE_ADD_EXAMPLE_2014 D_DATE 0 365 15 USABLE RANGE_ADD_EXAMPLE IDX_ADD_LOCAL RANGE_ADD_EXAMPLE_2015 D_DATE 0 235 10 USABLE RANGE_ADD_EXAMPLE IDX_ADD_LOCAL RANGE_ADD_EXAMPLE_2016 D_DATE 0 USABLE
全局索引状态为 VALID，本地索引和全局分区索引状态为 USABLE，说明对 RANGE 分区表执行 ADD 分区操作，不会影响索引的有效性，但对 HASH 分区表执行 ADD 分区操作，会使索引失效。
- 移动分区
移动分区维护操作，主要是将分区从一个表空间迁移至另一个表空间中。
创建分区表 RANGE_MOVE_EXAMPLE，使用 RANGE 分区：
SQL> create table range_move_example (
n_id number,
d_date date,
c_desc varchar2(4000)
)
partition by range (d_date) (
partition range_move_example_2014 values less than (to_date('2015-01-01', 'YYYY-MM-DD')),
partition range_move_example_2015 values less than (to_date('2016-01-01', 'YYYY-MM-DD'))
);
--创建本地索引、全局索引、全局分区索引
SQL> create index idx_move_local on range_move_example(d_date) local;
SQL> create index idx_move_global on range_move_example(c_desc) global;
SQL> create index idx_move_global_partition on range_move_example(n_id) global
partition by range(n_id)
(
partition idx_move_global_partition01 values less than (5000),
partition idx_move_global_partition02 values less than (maxvalue)
);
写入数据：
SQL> insert into range_move_example
select
rownum as n_id,
to_date('2014-01-01', 'YYYY-MM-DD') + rownum - 1 as d_date,
dbms_random.string('p', 255) as c_desc
from dual
connect by level <= 600
order by d_date;
SQL> COMMIT;
统计信息收集：
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'RANGE_MOVE_EXAMPLE',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
granularity => 'AUTO',
cascade => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE REPEAT',
degree => 4);
end;
/
查看索引分区及状态：
SQL> select t2.table_name, t1.index_name, t2.partition_name, t3.column_name, t1.subpartition_count, t2.num_rows, t2.clustering_factor, t1.status from user_ind_partitions t1, user_ind_statistics t2, user_ind_columns t3 where t1.index_name = t2.index_name and t3.index_name = t2.index_name and t1.partition_name = t2.partition_name and t2.table_name = 'RANGE_MOVE_EXAMPLE' union all select t4.table_name, t4.index_name, null partition_name, t5.column_name, null subpartition_count, t4.num_rows, t4.clustering_factor, t4.status from user_indexes t4, user_ind_columns t5 where t4.index_name = t5.index_name and t4.table_name = 'RANGE_MOVE_EXAMPLE' and t4.status <> 'N/A' order by 2, 3; TABLE_NAME INDEX_NAME PARTITION_NAME COLUMN_NAME SUBPARTITION_COUNT NUM_ROWS CLUSTERING_FACTOR STATUS ------------------------------ ----------------------------------- ------------------------------ --------------- ------------------ ---------- ----------------- -------- RANGE_MOVE_EXAMPLE IDX_MOVE_GLOBAL C_DESC 600 577 VALID RANGE_MOVE_EXAMPLE IDX_MOVE_GLOBAL_PARTITION IDX_MOVE_GLOBAL_PARTITION01 N_ID 0 600 25 USABLE RANGE_MOVE_EXAMPLE IDX_MOVE_GLOBAL_PARTITION IDX_MOVE_GLOBAL_PARTITION02 N_ID 0 0 0 USABLE RANGE_MOVE_EXAMPLE IDX_MOVE_LOCAL RANGE_MOVE_EXAMPLE_2014 D_DATE 0 365 15 USABLE RANGE_MOVE_EXAMPLE IDX_MOVE_LOCAL RANGE_MOVE_EXAMPLE_2015 D_DATE 0 235 10 USABLE
移动分区，将分区 RANGE_MOVE_EXAMPLE_2015 表空间移动为 EXAMPLE：
SQL> alter table range_move_example move partition range_move_example_2015 tablespace example; SQL> select table_name, partition_name, tablespace_name from user_tab_partitions where table_name = 'RANGE_MOVE_EXAMPLE' and partition_name = 'RANGE_MOVE_EXAMPLE_2015'; TABLE_NAME PARTITION_NAME TABLESPACE_NAME -------------------- ------------------------------ -------------------- RANGE_MOVE_EXAMPLE RANGE_MOVE_EXAMPLE_2015 EXAMPLE
移动分区后，查看索引分区及状态：
SQL> select t2.table_name, t1.index_name, t2.partition_name, t3.column_name, t1.subpartition_count, t2.num_rows, t2.clustering_factor, t1.status from user_ind_partitions t1, user_ind_statistics t2, user_ind_columns t3 where t1.index_name = t2.index_name and t3.index_name = t2.index_name and t1.partition_name = t2.partition_name and t2.table_name = 'RANGE_MOVE_EXAMPLE' union all select t4.table_name, t4.index_name, null partition_name, t5.column_name, null subpartition_count, t4.num_rows, t4.clustering_factor, t4.status from user_indexes t4, user_ind_columns t5 where t4.index_name = t5.index_name and t4.table_name = 'RANGE_MOVE_EXAMPLE' and t4.status <> 'N/A' order by 2, 3; TABLE_NAME INDEX_NAME PARTITION_NAME COLUMN_NAME SUBPARTITION_COUNT NUM_ROWS CLUSTERING_FACTOR STATUS ------------------------------ ----------------------------------- ------------------------------ --------------- ------------------ ---------- ----------------- -------- RANGE_MOVE_EXAMPLE IDX_MOVE_GLOBAL C_DESC 600 577 UNUSABLE RANGE_MOVE_EXAMPLE IDX_MOVE_GLOBAL_PARTITION IDX_MOVE_GLOBAL_PARTITION01 N_ID 0 600 25 UNUSABLE RANGE_MOVE_EXAMPLE IDX_MOVE_GLOBAL_PARTITION IDX_MOVE_GLOBAL_PARTITION02 N_ID 0 0 0 UNUSABLE RANGE_MOVE_EXAMPLE IDX_MOVE_LOCAL RANGE_MOVE_EXAMPLE_2014 D_DATE 0 365 15 USABLE RANGE_MOVE_EXAMPLE IDX_MOVE_LOCAL RANGE_MOVE_EXAMPLE_2015 D_DATE 0 235 10 UNUSABLE
全局索引、全局分区索引状态变为 UNUSABLE，未移动的分区对应的本地索引状态仍为USABLE，被移动的分区对应的本地索引状态变为 UNUSABLE，说明对分区表执行 MOVE 分区操作，会影响索引的有效性。
需要注意的是，对于组合分区，无法直接移动分区，而是需先移动其子分区，再通过修改分区的属性来完成。
- 截断分区
截断分区维护操作，相对于传统的 DELETE 操作，删除数据的效率会更高。而且会降低高水位线。
创建分区表 RANGE_TRUNCATE_EXAMPLE，使用 RANGE 分区：
SQL> create table range_truncate_example (
n_id number,
d_date date,
c_desc varchar2(4000)
)
partition by range (d_date) (
partition range_truncate_example_2014 values less than (to_date('2015-01-01', 'YYYY-MM-DD')),
partition range_truncate_example_2015 values less than (to_date('2016-01-01', 'YYYY-MM-DD'))
);
--创建本地索引、全局索引、全局分区索引
SQL> create index idx_truncate_local on range_truncate_example(d_date) local;
SQL> create index idx_truncate_global on range_truncate_example(c_desc) global;
SQL> create index idx_truncate_global_partition on range_truncate_example(n_id) global
partition by range(n_id)
(
partition idx_trunc_global_partition01 values less than (5000),
partition idx_trunc_global_partition02 values less than (maxvalue)
);
写入数据：
SQL> insert into range_truncate_example
select
rownum as n_id,
to_date('2014-01-01', 'YYYY-MM-DD') + rownum - 1 as d_date,
dbms_random.string('p', 255) as c_desc
from dual
connect by level <= 600
order by d_date;
SQL> COMMIT;
统计信息收集：
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'RANGE_TRUNCATE_EXAMPLE',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
granularity => 'AUTO',
cascade => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE REPEAT',
degree => 4);
end;
/
查看索引分区及状态：
SQL> select t2.table_name, t1.index_name, t2.partition_name, t3.column_name, t1.subpartition_count, t2.num_rows, t2.clustering_factor, t1.status from user_ind_partitions t1, user_ind_statistics t2, user_ind_columns t3 where t1.index_name = t2.index_name and t3.index_name = t2.index_name and t1.partition_name = t2.partition_name and t2.table_name = 'RANGE_TRUNCATE_EXAMPLE' union all select t4.table_name, t4.index_name, null partition_name, t5.column_name, null subpartition_count, t4.num_rows, t4.clustering_factor, t4.status from user_indexes t4, user_ind_columns t5 where t4.index_name = t5.index_name and t4.table_name = 'RANGE_TRUNCATE_EXAMPLE' and t4.status <> 'N/A' order by 2, 3; TABLE_NAME INDEX_NAME PARTITION_NAME COLUMN_NAME SUBPARTITION_COUNT NUM_ROWS CLUSTERING_FACTOR STATUS ------------------------------ ----------------------------------- ------------------------------ --------------- ------------------ ---------- ----------------- -------- RANGE_TRUNCATE_EXAMPLE IDX_TRUNCATE_GLOBAL C_DESC 600 570 VALID RANGE_TRUNCATE_EXAMPLE IDX_TRUNCATE_GLOBAL_PARTITION IDX_TRUNC_GLOBAL_PARTITION01 N_ID 0 600 25 USABLE RANGE_TRUNCATE_EXAMPLE IDX_TRUNCATE_GLOBAL_PARTITION IDX_TRUNC_GLOBAL_PARTITION02 N_ID 0 0 0 USABLE RANGE_TRUNCATE_EXAMPLE IDX_TRUNCATE_LOCAL RANGE_TRUNCATE_EXAMPLE_2014 D_DATE 0 365 15 USABLE RANGE_TRUNCATE_EXAMPLE IDX_TRUNCATE_LOCAL RANGE_TRUNCATE_EXAMPLE_2015 D_DATE 0 235 10 USABLE
执行截断分区操作，将分区 RANGE_TRUNCATE_EXAMPLE_2015 截断：
SQL> alter table range_truncate_example truncate partition range_truncate_example_2015; 表被截断。
截断分区后，查看索引分区及状态：
SQL> select t2.table_name, t1.index_name, t2.partition_name, t3.column_name, t1.subpartition_count, t2.num_rows, t2.clustering_factor, t1.status from user_ind_partitions t1, user_ind_statistics t2, user_ind_columns t3 where t1.index_name = t2.index_name and t3.index_name = t2.index_name and t1.partition_name = t2.partition_name and t2.table_name = 'RANGE_TRUNCATE_EXAMPLE' union all select t4.table_name, t4.index_name, null partition_name, t5.column_name, null subpartition_count, t4.num_rows, t4.clustering_factor, t4.status from user_indexes t4, user_ind_columns t5 where t4.index_name = t5.index_name and t4.table_name = 'RANGE_TRUNCATE_EXAMPLE' and t4.status <> 'N/A' order by 2, 3; TABLE_NAME INDEX_NAME PARTITION_NAME COLUMN_NAME SUBPARTITION_COUNT NUM_ROWS CLUSTERING_FACTOR STATUS ------------------------------ ----------------------------------- ------------------------------ --------------- ------------------ ---------- ----------------- -------- RANGE_TRUNCATE_EXAMPLE IDX_TRUNCATE_GLOBAL C_DESC 600 570 UNUSABLE RANGE_TRUNCATE_EXAMPLE IDX_TRUNCATE_GLOBAL_PARTITION IDX_TRUNC_GLOBAL_PARTITION01 N_ID 0 600 25 UNUSABLE RANGE_TRUNCATE_EXAMPLE IDX_TRUNCATE_GLOBAL_PARTITION IDX_TRUNC_GLOBAL_PARTITION02 N_ID 0 0 0 UNUSABLE RANGE_TRUNCATE_EXAMPLE IDX_TRUNCATE_LOCAL RANGE_TRUNCATE_EXAMPLE_2014 D_DATE 0 365 15 USABLE RANGE_TRUNCATE_EXAMPLE IDX_TRUNCATE_LOCAL RANGE_TRUNCATE_EXAMPLE_2015 D_DATE 0 235 10 USABLE
全局索引、全局分区索引状态变为 UNUSABLE，本地索引状态仍为 USABLE，说明对分区表执行 TRUNCATE 分区操作，会影响全局（全局分区）索引的有效性，但不会影响本地索引的有效性。
- 删除分区
对于分区的删除操作，需要注意，在删除分区后，分区所记录的数据，不会重分布至其他分区中，而是被一并删除。
SQL> create table range_drop_example (
n_id number,
d_date date,
c_desc varchar2(4000)
)
partition by range (d_date) (
partition range_drop_example_2014 values less than (to_date('2015-01-01', 'YYYY-MM-DD')),
partition range_drop_example_2015 values less than (to_date('2016-01-01', 'YYYY-MM-DD'))
);
--创建本地索引、全局索引、全局分区索引
SQL> create index idx_drop_local on range_drop_example(d_date) local;
SQL> create index idx_drop_global on range_drop_example(c_desc) global;
SQL> create index idx_drop_global_partition on range_drop_example(n_id) global
partition by range(n_id)
(
partition idx_drop_global_partition01 values less than (5000),
partition idx_drop_global_partition02 values less than (maxvalue)
);
写入数据：
SQL> insert into range_drop_example
select
rownum as n_id,
to_date('2014-01-01', 'YYYY-MM-DD') + rownum - 1 as d_date,
dbms_random.string('p', 255) as c_desc
from dual
connect by level <= 600
order by d_date;
SQL> COMMIT;
统计信息收集：
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'RANGE_DROP_EXAMPLE',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
granularity => 'AUTO',
cascade => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE REPEAT',
degree => 4);
end;
/
查看索引分区及状态：
SQL> select t2.table_name, t1.index_name, t2.partition_name, t3.column_name, t1.subpartition_count, t2.num_rows, t2.clustering_factor, t1.status from user_ind_partitions t1, user_ind_statistics t2, user_ind_columns t3 where t1.index_name = t2.index_name and t3.index_name = t2.index_name and t1.partition_name = t2.partition_name and t2.table_name = 'RANGE_DROP_EXAMPLE' union all select t4.table_name, t4.index_name, null partition_name, t5.column_name, null subpartition_count, t4.num_rows, t4.clustering_factor, t4.status from user_indexes t4, user_ind_columns t5 where t4.index_name = t5.index_name and t4.table_name = 'RANGE_DROP_EXAMPLE' and t4.status <> 'N/A' order by 2, 3; TABLE_NAME INDEX_NAME PARTITION_NAME COLUMN_NAME SUBPARTITION_COUNT NUM_ROWS CLUSTERING_FACTOR STATUS ------------------------------ ----------------------------------- ------------------------------ --------------- ------------------ ---------- ----------------- -------- RANGE_DROP_EXAMPLE IDX_DROP_GLOBAL C_DESC 600 573 VALID RANGE_DROP_EXAMPLE IDX_DROP_GLOBAL_PARTITION IDX_DROP_GLOBAL_PARTITION01 N_ID 0 600 25 USABLE RANGE_DROP_EXAMPLE IDX_DROP_GLOBAL_PARTITION IDX_DROP_GLOBAL_PARTITION02 N_ID 0 0 0 USABLE RANGE_DROP_EXAMPLE IDX_DROP_LOCAL RANGE_DROP_EXAMPLE_2014 D_DATE 0 365 15 USABLE RANGE_DROP_EXAMPLE IDX_DROP_LOCAL RANGE_DROP_EXAMPLE_2015 D_DATE 0 235 10 USABLE
将分区 RANGE_DROP_EXAMPLE_2014 删除：
SQL> alter table range_drop_example drop partition range_drop_example_2014; 表已更改。
删除分区后，查看索引分区及状态：
SQL> select t2.table_name, t1.index_name, t2.partition_name, t3.column_name, t1.subpartition_count, t2.num_rows, t2.clustering_factor, t1.status from user_ind_partitions t1, user_ind_statistics t2, user_ind_columns t3 where t1.index_name = t2.index_name and t3.index_name = t2.index_name and t1.partition_name = t2.partition_name and t2.table_name = 'RANGE_DROP_EXAMPLE' union all select t4.table_name, t4.index_name, null partition_name, t5.column_name, null subpartition_count, t4.num_rows, t4.clustering_factor, t4.status from user_indexes t4, user_ind_columns t5 where t4.index_name = t5.index_name and t4.table_name = 'RANGE_DROP_EXAMPLE' and t4.status <> 'N/A' order by 2, 3; TABLE_NAME INDEX_NAME PARTITION_NAME COLUMN_NAME SUBPARTITION_COUNT NUM_ROWS CLUSTERING_FACTOR STATUS ------------------------------ ----------------------------------- ------------------------------ --------------- ------------------ ---------- ----------------- -------- RANGE_DROP_EXAMPLE IDX_DROP_GLOBAL C_DESC 600 573 UNUSABLE RANGE_DROP_EXAMPLE IDX_DROP_GLOBAL_PARTITION IDX_DROP_GLOBAL_PARTITION01 N_ID 0 600 25 UNUSABLE RANGE_DROP_EXAMPLE IDX_DROP_GLOBAL_PARTITION IDX_DROP_GLOBAL_PARTITION02 N_ID 0 0 0 UNUSABLE RANGE_DROP_EXAMPLE IDX_DROP_LOCAL RANGE_DROP_EXAMPLE_2015 D_DATE 0 235 10 USABLE
全局索引、全局分区索引状态变为 UNUSABLE，本地索引状态仍为 USABLE，说明对分区表执行 DROP 分区操作，会影响全局、全局分区索引的有效性，但不会影响本地索引的有效性。
- 拆分分区
当分区表存在默认的最大分区时，必须要先删除默认的最大分区，才能进行分区新增操作，但我们可以通过拆分分区这种方法，来实现新增分区操作。
创建分区表 RANGE_SPLIT_EXAMPLE 且它只有一个分区： RANGE_SPLIT_EXAMPLE_MAX
SQL> create table range_split_example ( n_id number, d_date date, c_desc varchar2(4000) ) partition by range (d_date) ( partition range_split_example_max values less than(maxvalue) ); --创建本地索引、全局索引、全局分区索引 SQL> create index idx_split_local on range_split_example(d_date) local; SQL> create index idx_split_global on range_split_example(c_desc) global; SQL> create index idx_split_global_partition on range_split_example(n_id) global partition by range(n_id) ( partition idx_split_global_partition01 values less than (5000), partition idx_split_global_partition02 values less than (maxvalue) );
写入数据：
SQL> insert into range_split_example
select
rownum as n_id,
trunc(to_date('2014-01-01', 'YYYY-MM-DD') + rownum / 27.4) as d_date,
dbms_random.string('p', 255) as c_data
from dual
connect by level <= 10000
order by d_date;
SQL> COMMIT;
统计信息收集：
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'RANGE_SPLIT_EXAMPLE',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
granularity => 'AUTO',
cascade => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE REPEAT',
degree => 4);
end;
/
查看索引分区及状态：
SQL> select t2.table_name, t1.index_name, t2.partition_name, t3.column_name, t1.subpartition_count, t2.num_rows, t2.clustering_factor, t1.status from user_ind_partitions t1, user_ind_statistics t2, user_ind_columns t3 where t1.index_name = t2.index_name and t3.index_name = t2.index_name and t1.partition_name = t2.partition_name and t2.table_name = 'RANGE_SPLIT_EXAMPLE' union all select t4.table_name, t4.index_name, null partition_name, t5.column_name, null subpartition_count, t4.num_rows, t4.clustering_factor, t4.status from user_indexes t4, user_ind_columns t5 where t4.index_name = t5.index_name and t4.table_name = 'RANGE_SPLIT_EXAMPLE' and t4.status <> 'N/A' order by 2, 3; TABLE_NAME INDEX_NAME PARTITION_NAME COLUMN_NAME SUBPARTITION_COUNT NUM_ROWS CLUSTERING_FACTOR STATUS ------------------------------ ----------------------------------- ------------------------------ --------------- ------------------ ---------- ----------------- ------- RANGE_SPLIT_EXAMPLE IDX_SPLIT_GLOBAL C_DESC 10000 9976 VALID RANGE_SPLIT_EXAMPLE IDX_SPLIT_GLOBAL_PARTITION IDX_SPLIT_GLOBAL_PARTITION01 N_ID 0 4999 193 USABLE RANGE_SPLIT_EXAMPLE IDX_SPLIT_GLOBAL_PARTITION IDX_SPLIT_GLOBAL_PARTITION02 N_ID 0 5001 193 USABLE RANGE_SPLIT_EXAMPLE IDX_SPLIT_LOCAL RANGE_SPLIT_EXAMPLE_MAX D_DATE 0 10000 455 USABLE
查看分区 RANGE_SPLIT_EXAMPLE_MAX 记录数及分区键范围：
SQL> select min(d_date), max(d_date), count(*) from range_split_example partition(range_split_example_max); MIN(D_DATE) MAX(D_DATE) COUNT(*) ------------------- ------------------- ---------- 2014-01-01 00:00:00 2014-12-31 00:00:00 10000
执行分区拆分操作：
SQL> alter table range_split_example split partition range_split_example_max
at (to_date('2014-04-01', 'YYYY-MM-DD'))
into (partition range_split_example_q1_2014, partition range_split_example_max);
SQL> alter table range_split_example split partition range_split_example_max
at (to_date('2014-07-01', 'YYYY-MM-DD'))
into (partition range_split_example_q2_2014, partition range_split_example_max);
SQL> alter table range_split_example split partition range_split_example_max
at (to_date('2014-10-01', 'YYYY-MM-DD'))
into (partition range_split_example_q3_2014, partition range_split_example_max);
SQL> alter table range_split_example split partition range_split_example_max
at (to_date('2015-01-01', 'YYYY-MM-DD'))
into (partition range_split_example_q4_2014, partition range_split_example_max);
统计信息收集：
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'RANGE_SPLIT_EXAMPLE',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
granularity => 'AUTO',
cascade => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE REPEAT',
degree => 4);
end;
/
查看统计信息：
SQL> select table_name, partition_name, num_rows, blocks, avg_row_len, last_analyzed, global_stats from user_tab_statistics where table_name = 'RANGE_SPLIT_EXAMPLE' order by nvl(partition_position, 0); TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED GLO ------------------------------ ------------------------------ ---------- ---------- ----------- ------------------- --- RANGE_SPLIT_EXAMPLE 10000 457 268 2019-01-01 10:17:22 YES RANGE_SPLIT_EXAMPLE RANGE_SPLIT_EXAMPLE_Q1_2014 2465 113 268 2019-01-01 10:17:22 YES RANGE_SPLIT_EXAMPLE RANGE_SPLIT_EXAMPLE_Q2_2014 2494 114 268 2019-01-01 10:17:22 YES RANGE_SPLIT_EXAMPLE RANGE_SPLIT_EXAMPLE_Q3_2014 2521 115 268 2019-01-01 10:17:22 YES RANGE_SPLIT_EXAMPLE RANGE_SPLIT_EXAMPLE_Q4_2014 2520 115 268 2019-01-01 10:17:22 YES RANGE_SPLIT_EXAMPLE RANGE_SPLIT_EXAMPLE_MAX 0 0 0 2019-01-01 10:17:22 YES
从统计信息可以看出， 成功将分区 RANGE_SPLIT_EXAMPLE_MAX 拆分成四个分区。
查看索引分区及状态：
SQL> select t2.table_name, t1.index_name, t2.partition_name, t3.column_name, t1.subpartition_count, t2.num_rows, t2.clustering_factor, t1.status from user_ind_partitions t1, user_ind_statistics t2, user_ind_columns t3 where t1.index_name = t2.index_name and t3.index_name = t2.index_name and t1.partition_name = t2.partition_name and t2.table_name = 'RANGE_SPLIT_EXAMPLE' union all select t4.table_name, t4.index_name, null partition_name, t5.column_name, null subpartition_count, t4.num_rows, t4.clustering_factor, t4.status from user_indexes t4, user_ind_columns t5 where t4.index_name = t5.index_name and t4.table_name = 'RANGE_SPLIT_EXAMPLE' and t4.status <> 'N/A' order by 2, 3; TABLE_NAME INDEX_NAME PARTITION_NAME COLUMN_NAME SUBPARTITION_COUNT NUM_ROWS CLUSTERING_FACTOR STATUS ------------------------------ ----------------------------------- ------------------------------ --------------- ------------------ ---------- ----------------- -------- RANGE_SPLIT_EXAMPLE IDX_SPLIT_GLOBAL C_DESC 10000 9976 UNUSABLE RANGE_SPLIT_EXAMPLE IDX_SPLIT_GLOBAL_PARTITION IDX_SPLIT_GLOBAL_PARTITION01 N_ID 0 4999 193 UNUSABLE RANGE_SPLIT_EXAMPLE IDX_SPLIT_GLOBAL_PARTITION IDX_SPLIT_GLOBAL_PARTITION02 N_ID 0 5001 193 UNUSABLE RANGE_SPLIT_EXAMPLE IDX_SPLIT_LOCAL RANGE_SPLIT_EXAMPLE_MAX D_DATE 0 0 0 USABLE RANGE_SPLIT_EXAMPLE IDX_SPLIT_LOCAL RANGE_SPLIT_EXAMPLE_Q1_2014 D_DATE 0 0 0 UNUSABLE RANGE_SPLIT_EXAMPLE IDX_SPLIT_LOCAL RANGE_SPLIT_EXAMPLE_Q2_2014 D_DATE 0 0 0 UNUSABLE RANGE_SPLIT_EXAMPLE IDX_SPLIT_LOCAL RANGE_SPLIT_EXAMPLE_Q3_2014 D_DATE 0 0 0 UNUSABLE RANGE_SPLIT_EXAMPLE IDX_SPLIT_LOCAL RANGE_SPLIT_EXAMPLE_Q4_2014 D_DATE 0 0 0 UNUSABLE
全局索引、全局分区索引状态变为 UNUSABLE，被拆分分区对应的本地索引状态仍为 USABLE，通过拆分而新增的分区对应的本地索引状态为 UNUSABLE，说明对分区表执行 SPLIT 分区操作，会影响索引的有效性。
- 合并分区
合并分区操作，主要是将不同的分区，通过分区的合并，进行整合。
需要注意的是：
1. 对于 LIST 分区，合并的分区无限制要求；
2. 对于 RANGE 分区，合并的分区必须相邻，否则无法进行合并操作；
3. 对于 HASH 分区，无法进行合并分区操作；
此外，对于 RANGE 分区，下限值由边界值较低的分区决定，上限值由边界值较高的分区决定。
创建分区表 RANGE_MERGE_EXAMPLE，它有四个分区，将前二个分区合并为一个分区
SQL> create table range_merge_example (
n_id number,
d_date date,
c_desc varchar2(4000)
)
partition by range (d_date) (
partition range_merge_example_q1_2014 values less than (to_date('2014-04-01', 'YYYY-MM-DD')),
partition range_merge_example_q2_2014 values less than (to_date('2014-07-01', 'YYYY-MM-DD')),
partition range_merge_example_q3_2014 values less than (to_date('2014-10-01', 'YYYY-MM-DD')),
partition range_merge_example_q4_2014 values less than (to_date('2015-01-01', 'YYYY-MM-DD'))
);
--创建本地索引、全局索引、全局分区索引
SQL> create index idx_merge_local on range_merge_example(d_date) local;
SQL> create index idx_merge_global on range_merge_example(c_desc) global;
SQL> create index idx_merge_global_partition on range_merge_example(n_id) global
partition by range(n_id)
(
partition idx_merge_global_partition01 values less than (5000),
partition idx_merge_global_partition02 values less than (maxvalue)
);
写入数据：
SQL> insert into range_merge_example
select
rownum as n_id,
trunc(to_date('2014-01-01', 'YYYY-MM-DD') + rownum / 27.4) as d_date,
dbms_random.string('p', 255) as c_data
from dual
connect by level <= 10000
order by d_date;
SQL> COMMIT;
统计信息收集：
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'RANGE_MERGE_EXAMPLE',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
granularity => 'AUTO',
cascade => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE REPEAT',
degree => 4);
end;
/
查看索引分区及状态：
SQL> select t2.table_name, t1.index_name, t2.partition_name, t3.column_name, t1.subpartition_count, t2.num_rows, t2.clustering_factor, t1.status from user_ind_partitions t1, user_ind_statistics t2, user_ind_columns t3 where t1.index_name = t2.index_name and t3.index_name = t2.index_name and t1.partition_name = t2.partition_name and t2.table_name = 'RANGE_MERGE_EXAMPLE' union all select t4.table_name, t4.index_name, null partition_name, t5.column_name, null subpartition_count, t4.num_rows, t4.clustering_factor, t4.status from user_indexes t4, user_ind_columns t5 where t4.index_name = t5.index_name and t4.table_name = 'RANGE_MERGE_EXAMPLE' and t4.status <> 'N/A' order by 2, 3; TABLE_NAME INDEX_NAME PARTITION_NAME COLUMN_NAME SUBPARTITION_COUNT NUM_ROWS CLUSTERING_FACTOR STATUS ------------------------------ ----------------------------------- ------------------------------ --------------- ------------------ ---------- ----------------- -------- RANGE_MERGE_EXAMPLE IDX_MERGE_GLOBAL C_DESC 10000 9973 VALID RANGE_MERGE_EXAMPLE IDX_MERGE_GLOBAL_PARTITION IDX_MERGE_GLOBAL_PARTITION01 N_ID 0 4999 193 USABLE RANGE_MERGE_EXAMPLE IDX_MERGE_GLOBAL_PARTITION IDX_MERGE_GLOBAL_PARTITION02 N_ID 0 5001 193 USABLE RANGE_MERGE_EXAMPLE IDX_MERGE_LOCAL RANGE_MERGE_EXAMPLE_Q1_2014 D_DATE 0 2465 113 USABLE RANGE_MERGE_EXAMPLE IDX_MERGE_LOCAL RANGE_MERGE_EXAMPLE_Q2_2014 D_DATE 0 2494 113 USABLE RANGE_MERGE_EXAMPLE IDX_MERGE_LOCAL RANGE_MERGE_EXAMPLE_Q3_2014 D_DATE 0 2521 113 USABLE RANGE_MERGE_EXAMPLE IDX_MERGE_LOCAL RANGE_MERGE_EXAMPLE_Q4_2014 D_DATE 0 2520 113 USABLE
执行分区合并操作：
SQL> alter table range_merge_example merge partitions range_merge_example_q1_2014, range_merge_example_q2_2014 into partition range_merge_example_fh_2014; 表已更改。
重新收集统计信息：
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'RANGE_MERGE_EXAMPLE',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
granularity => 'AUTO',
cascade => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE REPEAT',
degree => 4);
end;
/
查看统计信息：
SQL> select table_name, partition_name, num_rows, blocks, avg_row_len, last_analyzed, global_stats from user_tab_statistics where table_name = 'RANGE_MERGE_EXAMPLE' order by nvl(partition_position, 0); TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED GLO ------------------------------ ------------------------------ ---------- ---------- ----------- ------------------- --- RANGE_MERGE_EXAMPLE 10000 429 268 2019-01-01 10:33:32 YES RANGE_MERGE_EXAMPLE RANGE_MERGE_EXAMPLE_FH_2014 4959 209 268 2019-01-01 10:33:32 YES RANGE_MERGE_EXAMPLE RANGE_MERGE_EXAMPLE_Q3_2014 2521 110 268 2019-01-01 10:33:32 YES RANGE_MERGE_EXAMPLE RANGE_MERGE_EXAMPLE_Q4_2014 2520 110 268 2019-01-01 10:33:32 YES
从统计信息可以看出，分区 RANGE_MERGE_EXAMPLE_Q1_2014 和 RANGE_MERGE_EXAMPLE_Q2_2014 成功被 MERGE 为个一个分区 RANGE_MERGE_EXAMPLE_FH_2014。
查看索引分区及状态：
SQL> select t2.table_name, t1.index_name, t2.partition_name, t3.column_name, t1.subpartition_count, t2.num_rows, t2.clustering_factor, t1.status from user_ind_partitions t1, user_ind_statistics t2, user_ind_columns t3 where t1.index_name = t2.index_name and t3.index_name = t2.index_name and t1.partition_name = t2.partition_name and t2.table_name = 'RANGE_MERGE_EXAMPLE' union all select t4.table_name, t4.index_name, null partition_name, t5.column_name, null subpartition_count, t4.num_rows, t4.clustering_factor, t4.status from user_indexes t4, user_ind_columns t5 where t4.index_name = t5.index_name and t4.table_name = 'RANGE_MERGE_EXAMPLE' and t4.status <> 'N/A' order by 2, 3; TABLE_NAME INDEX_NAME PARTITION_NAME COLUMN_NAME SUBPARTITION_COUNT NUM_ROWS CLUSTERING_FACTOR STATUS ------------------------------ ----------------------------------- ------------------------------ --------------- ------------------ ---------- ----------------- -------- RANGE_MERGE_EXAMPLE IDX_MERGE_GLOBAL C_DESC 0 0 UNUSABLE RANGE_MERGE_EXAMPLE IDX_MERGE_GLOBAL_PARTITION IDX_MERGE_GLOBAL_PARTITION01 N_ID 0 0 0 UNUSABLE RANGE_MERGE_EXAMPLE IDX_MERGE_GLOBAL_PARTITION IDX_MERGE_GLOBAL_PARTITION02 N_ID 0 0 0 UNUSABLE RANGE_MERGE_EXAMPLE IDX_MERGE_LOCAL RANGE_MERGE_EXAMPLE_FH_2014 D_DATE 0 0 0 UNUSABLE RANGE_MERGE_EXAMPLE IDX_MERGE_LOCAL RANGE_MERGE_EXAMPLE_Q3_2014 D_DATE 0 2521 113 USABLE RANGE_MERGE_EXAMPLE IDX_MERGE_LOCAL RANGE_MERGE_EXAMPLE_Q4_2014 D_DATE 0 2520 113 USABLE
全局索引、全局分区索引状态变为 UNUSABLE，未执行 MERGE 操作的分区对应的本地索引状态仍为 USABLE，执行了 MERGE 操作的分区对应的本地索引状态变为 UNUSABLE，说明对分区表执行 MERGE 分区操作，会影响索引的有效性。
- EXCHANGE
交换分区技术，主要是将一个非分区表的数据与一个分区表的一个分区进行数据交换。支持双向交换，既可以从分区表的分区中迁移至非分区表，也可以从非分区表迁移至分区表中的某个分区；
原则上，非分区表的表结构、数据分布等情况，要符合分区表中目标分区的定义规则。
创建分区表 RANGE_EXCHANGE_EXAMPLE， 使用 RANGE 分区：
SQL> create table range_exchange_example (
n_id number,
d_date date,
c_desc varchar2(4000)
)
partition by range (d_date) (
partition range_exchange_example_2014 values less than (to_date('2015-01-01', 'YYYY-MM-DD'))
);
--创建本地索引、全局索引、全局分区索引
SQL> create index idx_exchange_local on range_exchange_example(d_date) local;
SQL> create index idx_exchange_global on range_exchange_example(c_desc) global;
SQL> create index idx_exchange_global_partition on range_exchange_example(n_id) global
partition by range(n_id)
(
partition idx_exchge_global_partition01 values less than (5000),
partition idx_exchge_global_partition02 values less than (maxvalue)
);
创建普通堆表 EXCHANGE_EXAMPLE：
SQL> create table exchange_example (
n_id number,
d_date date,
c_desc varchar2(4000)
);
SQL> insert into exchange_example
select
rownum as n_id,
trunc(to_date('2014-01-01', 'YYYY-MM-DD') + rownum / 27.4) as d_date,
dbms_random.string('p', 255) as c_data
from dual
connect by level <= 10000
order by d_date;
SQL> COMMIT;
执行交换分区操作，将非分区表的数据转移至分区表中：
SQL> alter table range_exchange_example exchange partition range_exchange_example_2014 with table exchange_example; 表已更改。
查看分区 RANGE_EXCHANGE_EXAMPLE_2014 记录数：
SQL> select count(*) from range_exchange_example partition(range_exchange_example_2014);
COUNT(*)
----------
10000
查看普通堆表 EXCHANGE_EXAMPLE 记录数
SQL> select count(*) from exchange_example;
COUNT(*)
----------
0
查看索引分区及状态：
SQL> select t2.table_name, t1.index_name, t2.partition_name, t3.column_name, t1.subpartition_count, t2.num_rows, t2.clustering_factor, t1.status from user_ind_partitions t1, user_ind_statistics t2, user_ind_columns t3 where t1.index_name = t2.index_name and t3.index_name = t2.index_name and t1.partition_name = t2.partition_name and t2.table_name = 'RANGE_EXCHANGE_EXAMPLE' union all select t4.table_name, t4.index_name, null partition_name, t5.column_name, null subpartition_count, t4.num_rows, t4.clustering_factor, t4.status from user_indexes t4, user_ind_columns t5 where t4.index_name = t5.index_name and t4.table_name = 'RANGE_EXCHANGE_EXAMPLE' and t4.status <> 'N/A' order by 2, 3; TABLE_NAME INDEX_NAME PARTITION_NAME COLUMN_NAME SUBPARTITION_COUNT NUM_ROWS CLUSTERING_FACTOR STATUS ------------------------------ ----------------------------------- ------------------------------ --------------- ------------------ ---------- ----------------- -------- RANGE_EXCHANGE_EXAMPLE IDX_EXCHANGE_GLOBAL C_DESC 0 0 UNUSABLE RANGE_EXCHANGE_EXAMPLE IDX_EXCHANGE_GLOBAL_PARTITION IDX_EXCHGE_GLOBAL_PARTITION01 N_ID 0 0 0 UNUSABLE RANGE_EXCHANGE_EXAMPLE IDX_EXCHANGE_GLOBAL_PARTITION IDX_EXCHGE_GLOBAL_PARTITION02 N_ID 0 0 0 UNUSABLE RANGE_EXCHANGE_EXAMPLE IDX_EXCHANGE_LOCAL RANGE_EXCHANGE_EXAMPLE_2014 D_DATE 0 0 0 UNUSABLE
全局索引、全局分区索引状态变为 UNUSABLE，未执行 EXCHANGE 操作的分区对应的本地索引状态仍为 USABLE，执行了 EXCHANGE 操作的分区对应的本地索引状态变为 UNUSABLE，说明对分区表执行 EXCHANGE 分区操作（将非分区表的数据转移至分区表中），会影响索引的有效性。
重建索引：
--重建本地索引，不能将分区索引作为整体重建 SQL> alter index idx_exchange_local rebuild partition range_exchange_example_2014; --重建全局索引 SQL> alter index idx_exchange_global rebuild; --重建全局分区索引 SQL> alter index idx_exchange_global_partition rebuild partition idx_exchge_global_partition01; SQL> alter index idx_exchange_global_partition rebuild partition idx_exchge_global_partition02;
查看重建后的索引分区及状态：
SQL> select t2.table_name, t1.index_name, t2.partition_name, t3.column_name, t1.subpartition_count, t2.num_rows, t2.clustering_factor, t1.status from user_ind_partitions t1, user_ind_statistics t2, user_ind_columns t3 where t1.index_name = t2.index_name and t3.index_name = t2.index_name and t1.partition_name = t2.partition_name and t2.table_name = 'RANGE_EXCHANGE_EXAMPLE' union all select t4.table_name, t4.index_name, null partition_name, t5.column_name, null subpartition_count, t4.num_rows, t4.clustering_factor, t4.status from user_indexes t4, user_ind_columns t5 where t4.index_name = t5.index_name and t4.table_name = 'RANGE_EXCHANGE_EXAMPLE' and t4.status <> 'N/A' order by 2, 3; TABLE_NAME INDEX_NAME PARTITION_NAME COLUMN_NAME SUBPARTITION_COUNT NUM_ROWS CLUSTERING_FACTOR STATUS ------------------------------ ----------------------------------- ------------------------------ --------------- ------------------ ---------- ----------------- -------- RANGE_EXCHANGE_EXAMPLE IDX_EXCHANGE_GLOBAL C_DESC 10000 9976 VALID RANGE_EXCHANGE_EXAMPLE IDX_EXCHANGE_GLOBAL_PARTITION IDX_EXCHGE_GLOBAL_PARTITION01 N_ID 0 4999 193 USABLE RANGE_EXCHANGE_EXAMPLE IDX_EXCHANGE_GLOBAL_PARTITION IDX_EXCHGE_GLOBAL_PARTITION02 N_ID 0 5001 193 USABLE RANGE_EXCHANGE_EXAMPLE IDX_EXCHANGE_LOCAL RANGE_EXCHANGE_EXAMPLE_2014 D_DATE 0 10000 487 USABLE
执行交换分区操作，将分区表的数据转移至非分区表中：
SQL> alter table range_exchange_example exchange partition range_exchange_example_2014 with table exchange_example; 表已更改。
查看普通堆表 EXCHANGE_EXAMPLE 记录数
SQL> select count(*) from exchange_example;
COUNT(*)
----------
10000
查看分区 RANGE_EXCHANGE_EXAMPLE_2014 记录数：
SQL> select count(*) from range_exchange_example partition(range_exchange_example_2014);
COUNT(*)
----------
0
查看索引分区及状态：
SQL> select t2.table_name, t1.index_name, t2.partition_name, t3.column_name, t1.subpartition_count, t2.num_rows, t2.clustering_factor, t1.status from user_ind_partitions t1, user_ind_statistics t2, user_ind_columns t3 where t1.index_name = t2.index_name and t3.index_name = t2.index_name and t1.partition_name = t2.partition_name and t2.table_name = 'RANGE_EXCHANGE_EXAMPLE' union all select t4.table_name, t4.index_name, null partition_name, t5.column_name, null subpartition_count, t4.num_rows, t4.clustering_factor, t4.status from user_indexes t4, user_ind_columns t5 where t4.index_name = t5.index_name and t4.table_name = 'RANGE_EXCHANGE_EXAMPLE' and t4.status <> 'N/A' order by 2, 3; TABLE_NAME INDEX_NAME PARTITION_NAME COLUMN_NAME SUBPARTITION_COUNT NUM_ROWS CLUSTERING_FACTOR STATUS ------------------------------ ----------------------------------- ------------------------------ --------------- ------------------ ---------- ----------------- -------- RANGE_EXCHANGE_EXAMPLE IDX_EXCHANGE_GLOBAL C_DESC 10000 9976 UNUSABLE RANGE_EXCHANGE_EXAMPLE IDX_EXCHANGE_GLOBAL_PARTITION IDX_EXCHGE_GLOBAL_PARTITION01 N_ID 0 4999 193 UNUSABLE RANGE_EXCHANGE_EXAMPLE IDX_EXCHANGE_GLOBAL_PARTITION IDX_EXCHGE_GLOBAL_PARTITION02 N_ID 0 5001 193 UNUSABLE RANGE_EXCHANGE_EXAMPLE IDX_EXCHANGE_LOCAL RANGE_EXCHANGE_EXAMPLE_2014 D_DATE 0 10000 487 UNUSABLE
全局索引、全局分区索引状态变为 UNUSABLE，未执行 EXCHANGE 操作的分区对应的本地索引状态仍为 USABLE，执行了 EXCHANGE 操作的分区对应的本地索引状态变为 UNUSABLE，说明对分区表执行 EXCHANGE 分区操作（将分区表的数据转移至非分区表中），也会影响索引的有效性。
需要注意的是，若非分区表的数据，不符合所交换分区的分区键范围，则会交换失败，除非使用 WITHOUT VALIDATION 子句。
- 收缩分区
收缩分区维护操作，仅仅可以在 HASH 分区以及组合分区的 HASH 子分区上进行使用，通过使用收缩分区技术，可以收缩当前 HASH 分区的分区数量，对于 HASH 分区的数据，在收缩过程中，Oracle 也会自动完成数据在分区间的重分布。
创建分区表，使用 HASH 分区：
SQL> create table hash_coalesce_example (
n_id number,
d_date date,
c_desc varchar2(4000)
)
partition by hash (n_id) (
partition hash_coalesce_example_01,
partition hash_coalesce_example_02
);
--创建本地索引、全局索引、全局分区索引
SQL> create index idx_coalesce_local on hash_coalesce_example(n_id) local;
SQL> create index idx_coalesce_global on hash_coalesce_example(c_desc) global;
SQL> create index idx_coalesce_global_partition on hash_coalesce_example(d_date) global
partition by range(d_date)
(
partition idx_coales_global_partition01 values less than (to_date('2015-01-01', 'YYYY-MM-DD')),
partition idx_coales_global_partition02 values less than (maxvalue)
);
写入数据：
SQL> insert into hash_coalesce_example
select
rownum as n_id,
trunc(to_date('2014-01-01', 'YYYY-MM-DD') + rownum / 27.4) as d_date,
dbms_random.string('p', 255) as c_desc
from dual
connect by level <= 10000
order by dbms_random.value;
SQL> COMMIT;
统计信息收集：
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'HASH_COALESCE_EXAMPLE',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
granularity => 'AUTO',
cascade => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE REPEAT',
degree => 4);
end;
/
查看索引分区及状态：
SQL> select t2.table_name, t1.index_name, t2.partition_name, t3.column_name, t1.subpartition_count, t2.num_rows, t2.clustering_factor, t1.status from user_ind_partitions t1, user_ind_statistics t2, user_ind_columns t3 where t1.index_name = t2.index_name and t3.index_name = t2.index_name and t1.partition_name = t2.partition_name and t2.table_name = 'HASH_COALESCE_EXAMPLE' union all select t4.table_name, t4.index_name, null partition_name, t5.column_name, null subpartition_count, t4.num_rows, t4.clustering_factor, t4.status from user_indexes t4, user_ind_columns t5 where t4.index_name = t5.index_name and t4.table_name = 'HASH_COALESCE_EXAMPLE' and t4.status <> 'N/A' order by 2, 3; TABLE_NAME INDEX_NAME PARTITION_NAME COLUMN_NAME SUBPARTITION_COUNT NUM_ROWS CLUSTERING_FACTOR STATUS ------------------------------ ----------------------------------- ------------------------------ --------------- ------------------ ---------- ----------------- -------- HASH_COALESCE_EXAMPLE IDX_COALESCE_GLOBAL C_DESC 10000 9976 VALID HASH_COALESCE_EXAMPLE IDX_COALESCE_GLOBAL_PARTITION IDX_COALES_GLOBAL_PARTITION01 D_DATE 0 10000 9658 USABLE HASH_COALESCE_EXAMPLE IDX_COALESCE_GLOBAL_PARTITION IDX_COALES_GLOBAL_PARTITION02 D_DATE 0 0 0 USABLE HASH_COALESCE_EXAMPLE IDX_COALESCE_LOCAL HASH_COALESCE_EXAMPLE_01 N_ID 0 4992 4962 USABLE HASH_COALESCE_EXAMPLE IDX_COALESCE_LOCAL HASH_COALESCE_EXAMPLE_02 N_ID 0 5008 4984 USABLE
执行 HASH 分区收缩操作：
SQL> alter table hash_coalesce_example coalesce partition; 表已更改。
重新进行统计信息收集：
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'HASH_COALESCE_EXAMPLE',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
granularity => 'AUTO',
cascade => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE REPEAT',
degree => 4);
end;
/
查看执行 HASH 分区收缩操作后的分区信息：
SQL> select table_name, partition_name, num_rows, blocks, avg_row_len, last_analyzed, global_stats from user_tab_statistics where table_name = 'HASH_COALESCE_EXAMPLE' order by nvl(partition_position, 0); TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED GLO ------------------------------ ------------------------------ ---------- ---------- ----------- ------------------- --- HASH_COALESCE_EXAMPLE 10000 389 260 2018-12-28 22:51:56 YES HASH_COALESCE_EXAMPLE HASH_COALESCE_EXAMPLE_01 10000 389 260 2018-12-28 22:51:56 YES
从统计信息可以看出，通过收缩分区，原本两个 HASH 分区整合成一个，数据同时也被整合， 而且 BLOCKS 列也变小了。
需要注意的是，当 HASH 分区表中只有一个分区时，则无法进行收缩操作。
查看索引分区及状态：
SQL> select t2.table_name, t1.index_name, t2.partition_name, t3.column_name, t1.subpartition_count, t2.num_rows, t2.clustering_factor, t1.status from user_ind_partitions t1, user_ind_statistics t2, user_ind_columns t3 where t1.index_name = t2.index_name and t3.index_name = t2.index_name and t1.partition_name = t2.partition_name and t2.table_name = 'HASH_COALESCE_EXAMPLE' union all select t4.table_name, t4.index_name, null partition_name, t5.column_name, null subpartition_count, t4.num_rows, t4.clustering_factor, t4.status from user_indexes t4, user_ind_columns t5 where t4.index_name = t5.index_name and t4.table_name = 'HASH_COALESCE_EXAMPLE' and t4.status <> 'N/A' order by 2, 3; TABLE_NAME INDEX_NAME PARTITION_NAME COLUMN_NAME SUBPARTITION_COUNT NUM_ROWS CLUSTERING_FACTOR STATUS ------------------------------ ----------------------------------- ------------------------------ --------------- ------------------ ---------- ----------------- -------- HASH_COALESCE_EXAMPLE IDX_COALESCE_GLOBAL C_DESC 10000 9976 UNUSABLE HASH_COALESCE_EXAMPLE IDX_COALESCE_GLOBAL_PARTITION IDX_COALES_GLOBAL_PARTITION01 D_DATE 0 10000 9658 UNUSABLE HASH_COALESCE_EXAMPLE IDX_COALESCE_GLOBAL_PARTITION IDX_COALES_GLOBAL_PARTITION02 D_DATE 0 0 0 UNUSABLE HASH_COALESCE_EXAMPLE IDX_COALESCE_LOCAL HASH_COALESCE_EXAMPLE_01 N_ID 0 0 0 UNUSABLE
本地索引、全局索引、全局分区索引状态全部变为 UNUSABLE 说明对 HASH 分区表执行 COALESCE 分区操作，会影响索引的有效性。
- 重命名分区
将上列中分区 HASH_COALESCE_EXAMPLE_01 修改为 HASH_COALESCE_EXAMPLE_ALL：
SQL> alter table hash_coalesce_example rename partition hash_coalesce_example_01 to hash_coalesce_example_all;
索引维护
对分区表进行上述操作，可能会导致分区表上的索引失效。
| 序号 | 操作类型 | 失效索引 |
| 1 | 增加分区 ADD | HASH 分区索引失效 |
| 2 | 移动分区 MOVE | 全局索引失效、被移动的分区本地索引失效 |
| 3 | 截断分区 TRUNCATE | 全局索引失效，本地索引正常 |
| 4 | 删除分区 DROP | 全局索引失效，没有被删除的分区本地索引正常 |
| 5 | 拆分分区 SPLIT | 全局索引失效，拆分出来的新分区本地索引失效 |
| 6 | 合并分区 MERGE | 全局索引失效，合成生成的新分区本地索引失效 |
| 7 | 交换分区 EXCHANGE | 全局索引失效，执行了交换操作的分区本地索引失效 |
| 8 | 收缩分区 COALESCE | 全局索引、本地索引失效（仅支持 HASH 分区使用收缩操作） |
- 本地索引的维护
重建本地索引时，不能将分区索引作为整体重建，必须对单个分区依次重建。
SQL> alter index index_name rebuild partition partition_name;
- 全局索引的维护
SQL> alter index index_name rebuild;
- 全局分区索引的维护
重建全局分区索引时，不能将分区索引作为整体重建，必须对单个分区依次重建。
SQL> alter index index_name rebuild partition partition_name;
对于全局、全局分区索引，我们也可以通过 UPDATE GLOBAL INDEXES 子句，在进行分区操作的同时维护全局索引，以 HASH 分区 ADD 操作为例：
SQL> create table hash_add_example (
n_id number,
d_date date,
c_desc varchar2(4000)
)
partition by hash (n_id) (
partition hash_add_example_01,
partition hash_add_example_02
);
--创建本地索引、全局索引、全局分区索引
SQL> create index idx_hash_add_local on hash_add_example(n_id) local;
SQL> create index idx_hash_add_global on hash_add_example(c_desc) global;
SQL> create index idx_hash_add_global_partition on hash_add_example(d_date) global
partition by range(d_date)
(
partition idx_hashadd_global_partition01 values less than (to_date('2015-01-01', 'YYYY-MM-DD')),
partition idx_hashadd_global_partition02 values less than (maxvalue)
);
--写入数据
insert into hash_add_example
select
rownum as n_id,
trunc(to_date('2014-01-01', 'YYYY-MM-DD') + rownum / 27.4) as d_date,
dbms_random.string('p', 255) as c_desc
from dual
connect by level <= 10000
order by dbms_random.value;
--使用 UPDATE GLOBAL INDEXES 子句方式，新增 HASH 分区
SQL> alter table hash_add_example add partition hash_add_example_03
update global indexes;
--查看索引分区及状态
SQL> select
t2.table_name,
t1.index_name,
t2.partition_name,
t3.column_name,
t1.subpartition_count,
t2.num_rows,
t2.clustering_factor,
t1.status
from user_ind_partitions t1, user_ind_statistics t2, user_ind_columns t3
where t1.index_name = t2.index_name
and t3.index_name = t2.index_name
and t1.partition_name = t2.partition_name
and t2.table_name = 'HASH_ADD_EXAMPLE'
union all
select
t4.table_name,
t4.index_name,
null partition_name,
t5.column_name,
null subpartition_count,
t4.num_rows,
t4.clustering_factor,
t4.status
from user_indexes t4, user_ind_columns t5
where t4.index_name = t5.index_name
and t4.table_name = 'HASH_ADD_EXAMPLE'
and t4.status <> 'N/A'
order by 2, 3;
TABLE_NAME INDEX_NAME PARTITION_NAME COLUMN_NAME SUBPARTITION_COUNT NUM_ROWS CLUSTERING_FACTOR STATUS
------------------------------ ----------------------------------- ------------------------------ --------------- ------------------ ---------- ----------------- --------
HASH_ADD_EXAMPLE IDX_HASH_ADD_GLOBAL C_DESC 0 0 VALID
HASH_ADD_EXAMPLE IDX_HASH_ADD_GLOBAL_PARTITION IDX_HASHADD_GLOBAL_PARTITION01 D_DATE 0 0 0 USABLE
HASH_ADD_EXAMPLE IDX_HASH_ADD_GLOBAL_PARTITION IDX_HASHADD_GLOBAL_PARTITION02 D_DATE 0 0 0 USABLE
HASH_ADD_EXAMPLE IDX_HASH_ADD_LOCAL HASH_ADD_EXAMPLE_01 N_ID 0 0 0 UNUSABLE
HASH_ADD_EXAMPLE IDX_HASH_ADD_LOCAL HASH_ADD_EXAMPLE_02 N_ID 0 0 0 USABLE
HASH_ADD_EXAMPLE IDX_HASH_ADD_LOCAL HASH_ADD_EXAMPLE_03 N_ID 0 UNUSABLE
可以看到，使用 UPDATE GLOBAL INDEXES 子句后，对分区进行操作的同时也会维护全局索引，但并不会维护本地索引，所以，在对分区进行操作后，一定要及时检查索引的可用性。
分区表、索引数据字典
- 分区表信息
SQL> select table_name, partitioning_type, subpartitioning_type, partition_count, status, def_tablespace_name, interval from user_part_tables; TABLE_NAME PARTITION SUBPARTIT PARTITION_COUNT STATUS DEF_TABLESPACE_NAME INTERVAL ------------------------------ --------- --------- --------------- -------- ------------------------------ ------------------------- INTERVAL_NUMBER_EXAMPLE RANGE NONE 1048575 VALID USERS 10000 INTERVAL_DAY_EXAMPLE RANGE NONE 1048575 VALID USERS NUMTODSINTERVAL(1,'DAY') ......
- 分区表分区键
SQL> select name, object_type, column_name from user_part_key_columns; NAME OBJECT_TYPE COLUMN_NAME ------------------------------ ----------- ------------------- COMPOSITE_RANGE_HASH_EXAMPLE TABLE RANGE_KEY_COLUMN COMPOSITE_RANGE_LIST_EXAMPLE TABLE RANGE_KEY_COLUMN ......
- 分区表各分区尺寸
SQL> select segment_name, partition_name, segment_type, tablespace_name, bytes / 1024 / 1024 bytes_mb, blocks from user_segments where segment_type = 'TABLE PARTITION'; SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES_MB BLOCKS -------------------- ------------------------------ ------------------ -------------------- ---------- ---------- RANGE_SPLIT_EXAMPLE RANGE_SPLIT_EXAMPLE_Q3_2014 TABLE PARTITION USERS 8 1024 RANGE_SPLIT_EXAMPLE RANGE_SPLIT_EXAMPLE_Q4_2014 TABLE PARTITION USERS 8 1024 HASH_COALESCE_EXAMPL HASH_COALESCE_EXAMPLE_03 TABLE PARTITION USERS 8 1024 ......
- 分区表统计信息
SQL> select index_name, table_name, partition_name, object_type, blevel, num_rows, clustering_factor, last_analyzed, global_stats from user_ind_statistics where object_type = 'PARTITION'; INDEX_NAME TABLE_NAME PARTITION_NAME OBJECT_TYPE BLEVEL NUM_ROWS CLUSTERING_FACTOR LAST_ANALYZED GLO ----------------------------------- ------------------------------ ------------------------------ ------------ ---------- ---------- ----------------- ------------------- --- IDX_ADD_GLOBAL_PARTITION RANGE_ADD_EXAMPLE IDX_ADD_GLOBAL_PARTITION01 PARTITION 1 600 25 2018-12-31 23:32:30 YES IDX_ADD_GLOBAL_PARTITION RANGE_ADD_EXAMPLE IDX_ADD_GLOBAL_PARTITION02 PARTITION 0 0 0 2018-12-31 23:32:30 YES ......
- 查询分区表索引情况
user_inedx; user_ind_partitions;
- 查询分区表索引段的分配情况
SQL> select
segment_name,
partition_name,
segment_type,
bytes
from user_segments where segment_name in(
select
index_name
from user_indexes
where table_name = 'PARTITION_TABLE_NAME'
);
- 查询索引及统计信息
SQL> select t2.table_name, t1.index_name, t2.object_type, t2.clustering_factor, t2.num_rows, t2.blevel, t2.last_analyzed, t1.status from user_indexes t1, user_ind_statistics t2 where t1.index_name = t2.index_name and t2.object_type = 'INDEX' and t2.table_name = table_name order by t1.partitioned;
- 查询索引分区及统计信息
SQL> select t2.table_name, t2.partition_name, t1.index_name, t2.object_type, t2.clustering_factor, t2.num_rows, t2.blevel, t2.last_analyzed, t1.status from user_ind_partitions t1, user_ind_statistics t2 where t1.index_name = t2.index_name and t1.partition_name = t2.partition_name and t2.table_name = table_name order by t1.partition_position;
分区表、索引监控
- 监控失效索引
--当前用户下，失效普通索引 SQL> select index_name, table_name, blevel, num_rows, leaf_blocks, distinct_keys from user_indexes where status = 'INVALID'; --当前用户下，失效分区索引 SQL> select t2.table_name, t1.partition_name, t1.index_name, t1.blevel, t1.leaf_blocks, t1.status from user_ind_partitions t1, user_indexes t2 where t1.index_name = t2.index_name and t1.status = 'UNUSABLE';
- 监控未建分区的大表
--当前用户下，表大小超过2GB且未使用分区 SQL> select segment_name, segment_type, sum(bytes) / 2014 / 1024 / 1024 object_size from user_segments where segment_type = 'TABLE' group by segment_name, segment_type having sum(bytes) / 1024 / 1024 / 1024 >= 2 order by object_size desc;
- 监控分区数过多的表
--当前用户下，分区数最多的前10个对象
SQL> select
*
from (
select
table_name,
count(*) cnt
from user_tab_partitions
group by table_name
order by cnt desc
) where rownum <= 10;
- 监控分区表各分区大小严重不均匀的情况
SQL> select
table_name,
max(num_rows) "MAX_ROWS",
trunc(avg(num_rows), 0) "AVG_ROWS",
sum(num_rows) "SUM_ROWS",
case when sum(num_rows) = 0 then 0
else trunc(max(num_rows) / sum(num_rows), 2)
end "RATIO_ROWS",
count(*) "PARTITION_CNT"
from user_tab_partitions
group by table_name
having max(num_rows) / sum(num_rows) > 2 / count(*);
- 监控当前有多少个带子分区的分区表
SQL> select table_name, partitioning_type, subpartitioning_type, partition_count from user_part_tables where subpartitioning_type <> 'NONE';
分区修剪
分区修剪建立在 Oracle 存在多个 SEGMENT 结构的情形中，在默认情况下，数据表和索引都是对应一个 SEGMENT 结构；
而在应用分区（Partition）技术之后，一个数据库存储对象对应的会是多个 SEGMENT 结构，针对分区的分区特性，比如 RANGE 或者 LIST 分区，对于特殊的一些数据库操作（如 DML 和 DDL），Oracle 此时可以找到一些操作"捷径"，进而避免大规模数据操作的情况，这个过程就称为分区修剪。
数据表分区修剪
创建分区表
TA_PRUNING，该表没有全局或者本地索引结构：
SQL> create table ta_pruning (
n_id number,
d_d1 date,
n_n1 number,
n_n2 number,
n_n3 number,
c_pad varchar2(4000),
constraint pk_ta_pruning primary key (n_id)
)
partition by range (n_n1, d_d1) (
partition ta_pruning_1_jan_2014 values less than (1, to_date('2014-02-01', 'YYYY-MM-DD')),
partition ta_pruning_1_feb_2014 values less than (1, to_date('2014-03-01', 'YYYY-MM-DD')),
partition ta_pruning_1_mar_2014 values less than (1, to_date('2014-04-01', 'YYYY-MM-DD')),
partition ta_pruning_1_apr_2014 values less than (1, to_date('2014-05-01', 'YYYY-MM-DD')),
partition ta_pruning_1_may_2014 values less than (1, to_date('2014-06-01', 'YYYY-MM-DD')),
partition ta_pruning_1_jun_2014 values less than (1, to_date('2014-07-01', 'YYYY-MM-DD')),
partition ta_pruning_1_jul_2014 values less than (1, to_date('2014-08-01', 'YYYY-MM-DD')),
partition ta_pruning_1_aug_2014 values less than (1, to_date('2014-09-01', 'YYYY-MM-DD')),
partition ta_pruning_1_sep_2014 values less than (1, to_date('2014-10-01', 'YYYY-MM-DD')),
partition ta_pruning_1_oct_2014 values less than (1, to_date('2014-11-01', 'YYYY-MM-DD')),
partition ta_pruning_1_nov_2014 values less than (1, to_date('2014-12-01', 'YYYY-MM-DD')),
partition ta_pruning_1_dec_2014 values less than (1, to_date('2015-01-01', 'YYYY-MM-DD')),
partition ta_pruning_2_jan_2014 values less than (2, to_date('2014-02-01', 'YYYY-MM-DD')),
partition ta_pruning_2_feb_2014 values less than (2, to_date('2014-03-01', 'YYYY-MM-DD')),
partition ta_pruning_2_mar_2014 values less than (2, to_date('2014-04-01', 'YYYY-MM-DD')),
partition ta_pruning_2_apr_2014 values less than (2, to_date('2014-05-01', 'YYYY-MM-DD')),
partition ta_pruning_2_may_2014 values less than (2, to_date('2014-06-01', 'YYYY-MM-DD')),
partition ta_pruning_2_jun_2014 values less than (2, to_date('2014-07-01', 'YYYY-MM-DD')),
partition ta_pruning_2_jul_2014 values less than (2, to_date('2014-08-01', 'YYYY-MM-DD')),
partition ta_pruning_2_aug_2014 values less than (2, to_date('2014-09-01', 'YYYY-MM-DD')),
partition ta_pruning_2_sep_2014 values less than (2, to_date('2014-10-01', 'YYYY-MM-DD')),
partition ta_pruning_2_oct_2014 values less than (2, to_date('2014-11-01', 'YYYY-MM-DD')),
partition ta_pruning_2_nov_2014 values less than (2, to_date('2014-12-01', 'YYYY-MM-DD')),
partition ta_pruning_2_dec_2014 values less than (2, to_date('2015-01-01', 'YYYY-MM-DD')),
partition ta_pruning_3_jan_2014 values less than (3, to_date('2014-02-01', 'YYYY-MM-DD')),
partition ta_pruning_3_feb_2014 values less than (3, to_date('2014-03-01', 'YYYY-MM-DD')),
partition ta_pruning_3_mar_2014 values less than (3, to_date('2014-04-01', 'YYYY-MM-DD')),
partition ta_pruning_3_apr_2014 values less than (3, to_date('2014-05-01', 'YYYY-MM-DD')),
partition ta_pruning_3_may_2014 values less than (3, to_date('2014-06-01', 'YYYY-MM-DD')),
partition ta_pruning_3_jun_2014 values less than (3, to_date('2014-07-01', 'YYYY-MM-DD')),
partition ta_pruning_3_jul_2014 values less than (3, to_date('2014-08-01', 'YYYY-MM-DD')),
partition ta_pruning_3_aug_2014 values less than (3, to_date('2014-09-01', 'YYYY-MM-DD')),
partition ta_pruning_3_sep_2014 values less than (3, to_date('2014-10-01', 'YYYY-MM-DD')),
partition ta_pruning_3_oct_2014 values less than (3, to_date('2014-11-01', 'YYYY-MM-DD')),
partition ta_pruning_3_nov_2014 values less than (3, to_date('2014-12-01', 'YYYY-MM-DD')),
partition ta_pruning_3_dec_2014 values less than (3, to_date('2015-01-01', 'YYYY-MM-DD')),
partition ta_pruning_4_jan_2014 values less than (4, to_date('2014-02-01', 'YYYY-MM-DD')),
partition ta_pruning_4_feb_2014 values less than (4, to_date('2014-03-01', 'YYYY-MM-DD')),
partition ta_pruning_4_mar_2014 values less than (4, to_date('2014-04-01', 'YYYY-MM-DD')),
partition ta_pruning_4_apr_2014 values less than (4, to_date('2014-05-01', 'YYYY-MM-DD')),
partition ta_pruning_4_may_2014 values less than (4, to_date('2014-06-01', 'YYYY-MM-DD')),
partition ta_pruning_4_jun_2014 values less than (4, to_date('2014-07-01', 'YYYY-MM-DD')),
partition ta_pruning_4_jul_2014 values less than (4, to_date('2014-08-01', 'YYYY-MM-DD')),
partition ta_pruning_4_aug_2014 values less than (4, to_date('2014-09-01', 'YYYY-MM-DD')),
partition ta_pruning_4_sep_2014 values less than (4, to_date('2014-10-01', 'YYYY-MM-DD')),
partition ta_pruning_4_oct_2014 values less than (4, to_date('2014-11-01', 'YYYY-MM-DD')),
partition ta_pruning_4_nov_2014 values less than (4, to_date('2014-12-01', 'YYYY-MM-DD')),
partition ta_pruning_4_dec_2014 values less than (4, to_date('2015-01-01', 'YYYY-MM-DD'))
);
插入数据：
SQL> insert into ta_pruning
select
rownum as id,
trunc(to_date('2014-01-01', 'YYYY-MM-DD') + rownum / 27.4) as d1,
1 + mod(rownum, 4) as n1,
255 + mod(trunc(dbms_random.normal * 1000), 255) as n2,
round(4515 + dbms_random.normal * 1234) as n3,
dbms_random.string('p', 255) as pad
from dual
connect by level <= 10000
order by dbms_random.value;
SQL> COMMIT;
统计信息收集：
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'TA_PRUNING',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
granularity => 'AUTO',
cascade => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE REPEAT',
degree => 4);
end;
/
查看统计信息：
SQL> select table_name, partition_name, num_rows, blocks, avg_row_len, last_analyzed, global_stats from user_tab_statistics where table_name = 'TA_PRUNING' order by nvl(partition_position, 0); TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED GLO ------------------------------ ------------------------------ ---------- ---------- ----------- ------------------- --- TA_PRUNING 10000 2208 279 2018-12-29 12:57:13 YES TA_PRUNING TA_PRUNING_1_JAN_2014 212 46 279 2018-12-29 12:57:12 YES TA_PRUNING TA_PRUNING_1_FEB_2014 192 46 279 2018-12-29 12:57:12 YES TA_PRUNING TA_PRUNING_1_MAR_2014 212 46 279 2018-12-29 12:57:12 YES TA_PRUNING TA_PRUNING_1_APR_2014 205 46 279 2018-12-29 12:57:12 YES TA_PRUNING TA_PRUNING_1_MAY_2014 213 46 279 2018-12-29 12:57:12 YES TA_PRUNING TA_PRUNING_1_JUN_2014 205 46 279 2018-12-29 12:57:12 YES TA_PRUNING TA_PRUNING_1_JUL_2014 213 46 279 2018-12-29 12:57:12 YES TA_PRUNING TA_PRUNING_1_AUG_2014 212 46 279 2018-12-29 12:57:12 YES TA_PRUNING TA_PRUNING_1_SEP_2014 206 46 279 2018-12-29 12:57:12 YES TA_PRUNING TA_PRUNING_1_OCT_2014 212 46 279 2018-12-29 12:57:12 YES TA_PRUNING TA_PRUNING_1_NOV_2014 205 46 279 2018-12-29 12:57:12 YES ......
- PARTITION RANGE SINGLE
下面的 SQL 语句，WHERE 子句包含两个限制条件，分别对应分区键的每列；
在这样的情况下，查询优化器识别出只有单独一个分区包含相关数据，结果在执行计划里会显示 PARTITION RANGE SINGLE 操作；
重点需要知道它的子操作 TABLE ACCESS FULL 并不是对整张表进行全表扫描，而是只访问了单独一个分区，这也被 Starts 列的值所证实；
Pstart 和 Pstop 列指明了访问的分区范围。
SQL> alter session set statistics_level=all;
SQL> select * from ta_pruning where n_n1 = 3 and d_d1 = to_date('2014-07-19', 'YYYY-MM-DD');
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'iostats last partition'));
--------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | Buffers |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 36 |
| 1 | PARTITION RANGE SINGLE| | 1 | 31 | 31 | 36 |
|* 2 | TABLE ACCESS FULL | TA_PRUNING | 1 | 31 | 31 | 36 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("D_D1"=TO_DATE(' 2014-07-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "N_N1"=3))
通过 USER_TAB_PARTITION 视图验证 Pstart 和 Pstop 分区范围：
SQL> select partition_name from user_tab_partitions where table_name = 'TA_PRUNING' and partition_position = 31; PARTITION_NAME ------------------------------ TA_PRUNING_3_JUL_2014
- PARTITION RANGE ITERATOR
下面的 SQL 语句，限制条件使用了小于条件（<）而不是等值条件（=），因此，操作变成了 PARTITION RANGE ITERATOR，并且 Pstart 和 Pstop 列显示被访问的分区范围；
Starts 列显示 Id1 操作只执行了一次，但是 Id2 操作执行了七次，换句话说，执行了多次分区全扫描。
SQL> alter session set statistics_level=all;
SQL> select * from ta_pruning where n_n1 = 3 and d_d1 < to_date('2014-07-19', 'YYYY-MM-DD');
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'iostats last partition'));
----------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | Buffers |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 333 |
| 1 | PARTITION RANGE ITERATOR| | 1 | 25 | 31 | 333 |
|* 2 | TABLE ACCESS FULL | TA_PRUNING | 7 | 25 | 31 | 333 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("N_N1"=3 AND "D_D1"<TO_DATE(' 2014-07-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
需要注意的是，该操作只对连续的分区范围有效，操作非连续分区范围时，执行计划中的操作就会变成 PARTITION RANGE INLIST。
- PARTITION RANGE INLIST
如果限制基于一个或多个由超过一个元素组成的 IN 条件时，那么在执行计划中就会有 PARTITION RANGE INLIST 操作；
使用这个操作，Pstart 和 Pstop 列不会给出访问哪个分区的具体信息，相反，它们会显示 KEY(I) 的值，这代表对 IN 条件中的每个值分别进行分区修剪；
Starts 列显示访问了多少个分区（本例中访问的分区数为2）。
SQL> alter session set statistics_level=all;
SQL> select * from ta_pruning where n_n1 in (1, 3) and d_d1 = to_date('2014-07-19', 'YYYY-MM-DD');
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'iostats last partition'));
--------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | Buffers |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 71 |
| 1 | PARTITION RANGE INLIST| | 1 |KEY(I) |KEY(I) | 71 |
|* 2 | TABLE ACCESS FULL | TA_PRUNING | 2 |KEY(I) |KEY(I) | 71 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("D_D1"=TO_DATE(' 2014-07-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
INTERNAL_FUNCTION("N_N1")))
需要注意的是，如果 IN 条件中的值是完全分散的，那就有可能大部分分区都要访问，这种情况下，优化器认为所有分区都需要访问，此时执行计划中的操作就会变成 PARTITION RANGE ALL。
- PARTITION RANGE ALL
如果分区键上没有限制条件（本例中 n3 为非分区键），那么所有分区都必须访问，这种情况下，执行计划会显示 PARTITION RANGE ALL 操作，并且 Pstart 和 Pstop 列显示所有分区都会被访问。
SQL> alter session set statistics_level=all;
SQL> select * from ta_pruning where n_n3 between 6000 and 7000;
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'iostats last partition'));
-----------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | Buffers |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1737 |
| 1 | PARTITION RANGE ALL| | 1 | 1 | 48 | 1737 |
|* 2 | TABLE ACCESS FULL | TA_PRUNING | 48 | 1 | 48 | 1737 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("N_N3">=6000 AND "N_N3"<=7000))
这说明，要利用分区修剪，不仅要基于分区键的限制条件，而且不能在分区键上使用表达式或函数。
- PARTITION RANGE EMPTY
当查询优化器发现没有分区保存相关处理数据时，执行计划中会出现这个特别的操作 PARTITION RANGE EMPTY；
例如，下面的查询查找的数据没有分区保存（对于 n_n1 列来说，值 5 超出了范围）；
同样需要注意的是，此时不仅会将 Pstart 和 Pstop 列设置为 INVALID，而且只会执行 Id1 且该操作不会消耗任何资源，因为基本上这是一个空操作。
SQL> alter session set statistics_level=all;
SQL> select * from ta_pruning where n_n1 = 5 and d_d1 = to_date('2014-07-19', 'YYYY-MM-DD');
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'iostats last partition'));
---------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | |
| 1 | PARTITION RANGE EMPTY| | 1 |INVALID|INVALID|
|* 2 | TABLE ACCESS FULL | TA_PRUNING | 0 |INVALID|INVALID|
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("D_D1"=TO_DATE(' 2014-07-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"N_N1"=5))
- PARTITION RANGE OR
当分区键上的分隔谓词用在了 WHERE 子句上（由 OR 条件组合的谓词），执行计划中会出现 PARTITION RANGE OR；
此时 Pstart 和 Pstop 列也会被设置为 KEY(OR)。
SQL> alter session set statistics_level=all;
SQL> select * from ta_pruning where n_n1 = 3 or d_d1 = to_date('2014-03-06', 'YYYY-MM-DD');
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'iostats last partition'));
----------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | Buffers |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 795 |
| 1 | PARTITION RANGE OR| | 1 |KEY(OR)|KEY(OR)| 795 |
|* 2 | TABLE ACCESS FULL| TA_PRUNING | 18 |KEY(OR)|KEY(OR)| 795 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("N_N1"=3 OR "D_D1"=TO_DATE(' 2014-03-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
- PARTITION RANGE MULTI-COLUMN
如果分区键由多列组合而成，重点需要观察当限制条件没有固定在所有列上时会发生什么。
当限制条件为分区键的前导列时：
SQL> alter session set statistics_level=all;
SQL> select * from ta_pruning where n_n1 = 3;
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'iostats last partition'));
----------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | Buffers |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 619 |
| 1 | PARTITION RANGE ITERATOR| | 1 | 25 | 37 | 619 |
|* 2 | TABLE ACCESS FULL | TA_PRUNING | 13 | 25 | 37 | 619 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("N_N1"=3)
当限制条件为分区键的非前导列时：
SQL> alter session set statistics_level=all;
SQL> select * from ta_pruning where d_d1 = to_date('2014-07-19', 'YYYY-MM-DD');
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'iostats last partition'));
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | Buffers |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 282 |
| 1 | PARTITION RANGE MULTI-COLUMN| | 1 |KEY(MC)|KEY(MC)| 282 |
|* 2 | TABLE ACCESS FULL | TA_PRUNING | 8 |KEY(MC)|KEY(MC)| 282 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("D_D1"=TO_DATE(' 2014-07-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
需要注意的是，PARTITION RANGE MULTI-COLUMN 操作并没有提供具体访问了哪个分区。
本地索引分区修剪
和 Global Index 不同，每个 Local Index 都会指向一个表分区，在创建 Local Index 时， 应尽量考虑表的分区键作为本地索引键。
创建分区表 TB_PRUNING，使用 RANGE 分区：
SQL> create table tb_pruning (
n_id number,
d_d1 date,
n_n1 number,
n_n2 number,
n_n3 number,
c_pad varchar2(4000),
constraint pk_tb_pruning primary key (n_id)
)
partition by range (d_d1) (
partition tb_pruning_jan_2014 values less than (to_date('2014-02-01', 'YYYY-MM-DD')),
partition tb_pruning_feb_2014 values less than (to_date('2014-03-01', 'YYYY-MM-DD')),
partition tb_pruning_mar_2014 values less than (to_date('2014-04-01', 'YYYY-MM-DD')),
partition tb_pruning_apr_2014 values less than (to_date('2014-05-01', 'YYYY-MM-DD')),
partition tb_pruning_may_2014 values less than (to_date('2014-06-01', 'YYYY-MM-DD')),
partition tb_pruning_jun_2014 values less than (to_date('2014-07-01', 'YYYY-MM-DD')),
partition tb_pruning_jul_2014 values less than (to_date('2014-08-01', 'YYYY-MM-DD')),
partition tb_pruning_aug_2014 values less than (to_date('2014-09-01', 'YYYY-MM-DD')),
partition tb_pruning_sep_2014 values less than (to_date('2014-10-01', 'YYYY-MM-DD')),
partition tb_pruning_oct_2014 values less than (to_date('2014-11-01', 'YYYY-MM-DD')),
partition tb_pruning_nov_2014 values less than (to_date('2014-12-01', 'YYYY-MM-DD')),
partition tb_pruning_dec_2014 values less than (to_date('2015-01-01', 'YYYY-MM-DD'))
);
创建本地索引，索引列为表的分区键：
SQL> create index idx_tb_pruning on tb_pruning(d_d1) local; 索引已创建。
强烈建议，创建表后，根据实际业务按需创建索引列，而不是等到数据量很大时再创建索引，因为普通堆表表中数据是无序存放的，如果此时创建索引，聚簇因子的值也可能很大，这就会导致执行计划的不稳定。
按照索引列排序，顺序写入数据：
SQL> insert into tb_pruning
select
rownum as n_id,
trunc(to_date('2014-01-01', 'YYYY-MM-DD') + rownum / 27.4) as d_d1,
1 + mod(rownum, 4) as n_n1,
255 + mod(trunc(dbms_random.normal * 1000), 255) as n_n2,
round(4515 + dbms_random.normal * 1234) as n_n3,
dbms_random.string('p', 255) as c_pad
from dual
connect by level <= 10000
order by d_d1;
SQL> COMMIT;
统计信息收集：
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'TB_PRUNING',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
granularity => 'AUTO',
cascade => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE REPEAT',
degree => 4);
end;
/
查看本地索引分区及统计信息：
SQL> select t2.table_name, t2.partition_name, t1.index_name, t2.object_type, t2.clustering_factor, t2.num_rows, t2.blevel, t2.last_analyzed, t1.status from user_ind_partitions t1, user_ind_statistics t2 where t1.index_name = t2.index_name and t1.partition_name = t2.partition_name and t2.table_name = 'TB_PRUNING' order by t1.partition_position; TABLE_NAME PARTITION_NAME INDEX_NAME OBJECT_TYPE CLUSTERING_FACTOR NUM_ROWS BLEVEL LAST_ANALYZED STATUS ------------- ------------------------------ -------------------- ------------ ----------------- ---------- ---------- ------------------- ------- TB_PRUNING TB_PRUNING_JAN_2014 IDX_TB_PRUNING PARTITION 38 849 1 2018-12-29 23:12:19 USABLE TB_PRUNING TB_PRUNING_FEB_2014 IDX_TB_PRUNING PARTITION 35 767 1 2018-12-29 23:12:19 USABLE TB_PRUNING TB_PRUNING_MAR_2014 IDX_TB_PRUNING PARTITION 38 849 1 2018-12-29 23:12:19 USABLE TB_PRUNING TB_PRUNING_APR_2014 IDX_TB_PRUNING PARTITION 37 822 1 2018-12-29 23:12:19 USABLE TB_PRUNING TB_PRUNING_MAY_2014 IDX_TB_PRUNING PARTITION 38 850 1 2018-12-29 23:12:19 USABLE TB_PRUNING TB_PRUNING_JUN_2014 IDX_TB_PRUNING PARTITION 37 822 1 2018-12-29 23:12:19 USABLE TB_PRUNING TB_PRUNING_JUL_2014 IDX_TB_PRUNING PARTITION 38 849 1 2018-12-29 23:12:19 USABLE TB_PRUNING TB_PRUNING_AUG_2014 IDX_TB_PRUNING PARTITION 38 850 1 2018-12-29 23:12:19 USABLE TB_PRUNING TB_PRUNING_SEP_2014 IDX_TB_PRUNING PARTITION 37 822 1 2018-12-29 23:12:19 USABLE TB_PRUNING TB_PRUNING_OCT_2014 IDX_TB_PRUNING PARTITION 38 849 1 2018-12-29 23:12:19 USABLE TB_PRUNING TB_PRUNING_NOV_2014 IDX_TB_PRUNING PARTITION 37 822 1 2018-12-29 23:12:19 USABLE TB_PRUNING TB_PRUNING_DEC_2014 IDX_TB_PRUNING PARTITION 38 849 1 2018-12-29 23:12:19 USABLE
执行 SQL 查询， 限制条件使用表分区键 D_D1 同时也是本地索引键，且为等值查询：
SQL> alter session set statistics_level=all;
SQL> select * from tb_pruning where d_d1 = to_date('2014-07-19', 'YYYY-MM-DD');
查看执行计划：
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'iostats last partition'));
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | A-Rows | Buffers |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 27 | 8 |
| 1 | PARTITION RANGE SINGLE | | 1 | 7 | 7 | 27 | 8 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| TB_PRUNING | 1 | 7 | 7 | 27 | 8 |
|* 3 | INDEX RANGE SCAN | IDX_TB_PRUNING | 1 | 7 | 7 | 27 | 4 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D_D1"=TO_DATE(' 2014-07-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
从执行计划可以看出，Oracle 执行了分区修剪操作 PARTITION RANGE SINGLE，而且是以本地索引方式访问数据 LOCAL INDEX ROWID。
但这里有一个问题：数据表和索引都分区了，那这个分区修剪操作是针对本地索引还是表呢？我们可以通过 10046 event 来进行验证。
查看 TRACE 文件路径：
SQL> select value from v$diag_info where name = 'Default Trace File'; VALUE --------------------------------------------------------------------- C:\APP\ALLEN\diag\rdbms\orcl\orcl\trace\orcl_ora_980.trc
清除缓冲区缓存，生产环境勿用：
SQL> alter system flush shared_pool; SQL> alter system flush buffer_cache;
开启 10046 检查：
SQL> alter session set events='10046 trace name context forever, level 12'; 会话已更改。
执行 SQL 语句：
SQL> select * from tb_pruning where d_d1 = to_date('2014-07-19', 'YYYY-MM-DD');
关闭 10046 检查：
SQL> alter session set events='10046 trace name context off'; 会话已更改。
查看对应的 Trace File 信息片段：
=====================
PARSING IN CURSOR #353585368 len=73 dep=0 uid=83 oct=3 lid=83 tim=263515328756 hv=4183086457 ad='7ff582bd9c0' sqlid='b78fs73wp9pbt'
select * from tb_pruning where d_d1 = to_date('2014-07-19', 'YYYY-MM-DD')
END OF STMT
PARSE #353585368:c=62401,e=73324,p=30,cr=686,cu=0,mis=1,r=0,dep=0,og=1,plh=2750291697,tim=263515328756
EXEC #353585368:c=0,e=52,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2750291697,tim=263515328855
WAIT #353585368: nam='SQL*Net message to client' ela= 6 driver id=1111838976 #bytes=1 p3=0 obj#=13891 tim=263515328910
WAIT #353585368: nam='Disk file operations I/O' ela= 184 FileOperation=2 fileno=4 filetype=2 obj#=78455 tim=263515329235
WAIT #353585368: nam='db file sequential read' ela= 483 file#=4 block#=120579 blocks=1 obj#=78455 tim=263515329788
WAIT #353585368: nam='db file scattered read' ela= 488 file#=4 block#=120580 blocks=4 obj#=78455 tim=263515330469
WAIT #353585368: nam='db file sequential read' ela= 551 file#=4 block#=145554 blocks=1 obj#=78441 tim=263515331236
FETCH #353585368:c=0,e=2379,p=6,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=2750291697,tim=263515331312
WAIT #353585368: nam='SQL*Net message from client' ela= 876 driver id=1111838976 #bytes=1 p3=0 obj#=78441 tim=263515332245
WAIT #353585368: nam='SQL*Net message to client' ela= 5 driver id=1111838976 #bytes=1 p3=0 obj#=78441 tim=263515332353
FETCH #353585368:c=0,e=71,p=0,cr=2,cu=0,mis=0,r=15,dep=0,og=1,plh=2750291697,tim=263515332390
*** 2018-12-30 00:54:30.806
WAIT #353585368: nam='SQL*Net message from client' ela= 124638 driver id=1111838976 #bytes=1 p3=0 obj#=78441 tim=263515457062
WAIT #353585368: nam='SQL*Net message to client' ela= 8 driver id=1111838976 #bytes=1 p3=0 obj#=78441 tim=263515457279
WAIT #353585368: nam='db file sequential read' ela= 411 file#=4 block#=145598 blocks=1 obj#=78441 tim=263515457782
FETCH #353585368:c=0,e=771,p=1,cr=3,cu=0,mis=0,r=11,dep=0,og=1,plh=2750291697,tim=263515457973
STAT #353585368 id=1 cnt=27 pid=0 pos=1 obj=0 op='PARTITION RANGE SINGLE PARTITION: 7 7 (cr=8 pr=7 pw=0 time=2401 us cost=3 size=7533 card=27)'
STAT #353585368 id=2 cnt=27 pid=1 pos=1 obj=78434 op='TABLE ACCESS BY LOCAL INDEX ROWID TB_PRUNING PARTITION: 7 7 (cr=8 pr=7 pw=0 time=2350 us cost=3 size=7533 card=27)'
STAT #353585368 id=3 cnt=27 pid=2 pos=1 obj=78448 op='INDEX RANGE SCAN IDX_TB_PRUNING PARTITION: 7 7 (cr=4 pr=5 pw=0 time=2054 us cost=1 size=0 card=27)'
*** 2018-12-30 00:54:36.999
WAIT #353585368: nam='SQL*Net message from client' ela= 6203195 driver id=1111838976 #bytes=1 p3=0 obj#=78441 tim=263521661616
CLOSE #353585368:c=0,e=10,dep=0,type=0,tim=263521661740
=====================
通过 obj# 定位对象：
SQL> select object_name, subobject_name, object_type from dba_objects where object_id = 78455; OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE ------------------------------ ------------------------------ ------------------ IDX_TB_PRUNING TB_PRUNING_JUL_2014 INDEX PARTITION
从上述查询可以看出，进行的是本地索引段的分区修剪操作。
全局索引分区修剪
Global Index 是分区表中使用的一种默认索引方案，对于分区的数据表，Global Index 形成的是一个覆盖所有分区的索引树结构，对应的是一个段对象；
在性能优化方面，Global Index 和分区表是“相斥”的，SQL 语句走分区修剪，就不会选择 Global Index，反之选择了 Global Index，直接定位结果集 ROWID，也就没有必要进行数据表分区修剪；
作为两个都需要消耗优化资源的方案，无论哪个路径，都需要"闲置"一种优化策略。
创建分区表 TC_PRUNING，按 RANGE 分区：
SQL> create table tc_pruning (
n_id number,
d_d1 date,
n_n1 number,
n_n2 number,
n_n3 number,
c_pad varchar2(4000),
constraint pk_tc_pruning primary key (n_id)
)
partition by range (d_d1) (
partition tc_pruning_jan_2014 values less than (to_date('2014-02-01', 'YYYY-MM-DD')),
partition tc_pruning_feb_2014 values less than (to_date('2014-03-01', 'YYYY-MM-DD')),
partition tc_pruning_mar_2014 values less than (to_date('2014-04-01', 'YYYY-MM-DD')),
partition tc_pruning_apr_2014 values less than (to_date('2014-05-01', 'YYYY-MM-DD')),
partition tc_pruning_may_2014 values less than (to_date('2014-06-01', 'YYYY-MM-DD')),
partition tc_pruning_jun_2014 values less than (to_date('2014-07-01', 'YYYY-MM-DD')),
partition tc_pruning_jul_2014 values less than (to_date('2014-08-01', 'YYYY-MM-DD')),
partition tc_pruning_aug_2014 values less than (to_date('2014-09-01', 'YYYY-MM-DD')),
partition tc_pruning_sep_2014 values less than (to_date('2014-10-01', 'YYYY-MM-DD')),
partition tc_pruning_oct_2014 values less than (to_date('2014-11-01', 'YYYY-MM-DD')),
partition tc_pruning_nov_2014 values less than (to_date('2014-12-01', 'YYYY-MM-DD')),
partition tc_pruning_dec_2014 values less than (to_date('2015-01-01', 'YYYY-MM-DD'))
);
SQL> insert into tc_pruning
select
rownum as n_id,
trunc(to_date('2014-01-01', 'YYYY-MM-DD') + rownum / 27.4) as d_d1,
1 + mod(rownum, 4) as n_n1,
255 + mod(trunc(dbms_random.normal * 1000), 255) as n_n2,
round(4515 + dbms_random.normal * 1234) as n_n3,
dbms_random.string('p', 255) as c_pad
from dual
connect by level <= 10000
order by dbms_random.value;
SQL> COMMIT;
创建全局索引：
SQL> create index idx_tc_pruning on tc_pruning(n_n2) global; 索引已创建。
统计信息收集：
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'TC_PRUNING',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
granularity => 'AUTO',
cascade => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE REPEAT',
degree => 4);
end;
/
执行 SQL 查询， 限制条件使用分区键 D_D1 与全局索引键 N_N2，且均为等值查询：
SQL> alter session set statistics_level=all;
SQL> select * from tc_pruning where d_d1 = to_date('2014-07-19', 'YYYY-MM-DD') and n_n2 = 200;
查看执行计划：
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'iostats last partition'));
--------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | Buffers |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 55 |
| 1 | PARTITION RANGE SINGLE| | 1 | 7 | 7 | 55 |
|* 2 | TABLE ACCESS FULL | TC_PRUNING | 1 | 7 | 7 | 55 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("N_N2"=200 AND "D_D1"=TO_DATE(' 2014-07-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
从执行计划可以看出，此时发生了分区修剪 PARTITION RANGE SINGLE，且并未走全局索引。
执行 SQL 查询， 限制条件仅使用全局索引键且为等值查询：
SQL> alter session set statistics_level=all; SQL> select * from tc_pruning where n_n1 = 3;
查看执行计划：
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'iostats last partition'));
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | Buffers |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 15 |
|* 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| TC_PRUNING | 1 | ROWID | ROWID | 15 |
|* 2 | INDEX RANGE SCAN | IDX_TC_PRUNING | 1 | | | 3 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("D_D1">TO_DATE(' 2014-07-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
2 - access("N_N2"=200)
从执行计划可以看出，此时并没有发生分区修剪，而是直接走全局索引 GLOBAL INDEX ROWID。
全局分区索引分区修剪
全局分区索引的 RANGE 分区最后一个分区必须是 MAXVALUE。
创建分区表 TD_PRUNING，按 RANGE 分区：
create table td_pruning (
n_id number,
d_d1 date,
n_n1 number,
n_n2 number,
n_n3 number,
c_pad varchar2(4000),
constraint pk_td_pruning primary key (n_id)
)
partition by range (d_d1) (
partition td_pruning_jan_2014 values less than (to_date('2014-02-01', 'YYYY-MM-DD')),
partition td_pruning_feb_2014 values less than (to_date('2014-03-01', 'YYYY-MM-DD')),
partition td_pruning_mar_2014 values less than (to_date('2014-04-01', 'YYYY-MM-DD')),
partition td_pruning_apr_2014 values less than (to_date('2014-05-01', 'YYYY-MM-DD')),
partition td_pruning_may_2014 values less than (to_date('2014-06-01', 'YYYY-MM-DD')),
partition td_pruning_jun_2014 values less than (to_date('2014-07-01', 'YYYY-MM-DD')),
partition td_pruning_jul_2014 values less than (to_date('2014-08-01', 'YYYY-MM-DD')),
partition td_pruning_aug_2014 values less than (to_date('2014-09-01', 'YYYY-MM-DD')),
partition td_pruning_sep_2014 values less than (to_date('2014-10-01', 'YYYY-MM-DD')),
partition td_pruning_oct_2014 values less than (to_date('2014-11-01', 'YYYY-MM-DD')),
partition td_pruning_nov_2014 values less than (to_date('2014-12-01', 'YYYY-MM-DD')),
partition td_pruning_dec_2014 values less than (to_date('2015-01-01', 'YYYY-MM-DD'))
);
创建全局分区索引，索引列为表的分区列：
SQL> create index idx_td_pruning on td_pruning(d_d1) global
partition by range(d_d1)
(
partition idx_td_pruning_q1_2014 values less than(to_date('2014-04-01', 'YYYY-MM-DD')),
partition idx_td_pruning_q2_2014 values less than(to_date('2014-07-01', 'YYYY-MM-DD')),
partition idx_td_pruning_q3_2014 values less than(to_date('2014-10-01', 'YYYY-MM-DD')),
partition idx_td_pruning_q4_2014 values less than(to_date('2015-01-01', 'YYYY-MM-DD')),
partition idx_td_pruning_max values less than(maxvalue)
)
按照索引列排序，顺序写入数据：
SQL> insert into /*+ append */ td_pruning
select
rownum as n_id,
trunc(to_date('2014-01-01', 'YYYY-MM-DD') + rownum / 27.4) as d_d1,
1 + mod(rownum, 4) as n_n1,
255 + mod(trunc(dbms_random.normal * 1000), 255) as n_n2,
round(4515 + dbms_random.normal * 1234) as n_n3,
dbms_random.string('p', 255) as c_pad
from dual
connect by level <= 10000
order by d_d1;
SQL> COMMIT;
统计信息收集：
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'TD_PRUNING',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
granularity => 'AUTO',
cascade => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE REPEAT',
degree => 4);
end;
查看全局分区索引分区及统计信息：
SQL> select t2.table_name, t2.partition_name, t1.index_name, t2.object_type, t2.clustering_factor, t2.num_rows, t2.blevel, t2.last_analyzed, t1.status from user_ind_partitions t1, user_ind_statistics t2 where t1.index_name = t2.index_name and t1.partition_name = t2.partition_name and t2.table_name = 'TD_PRUNING' order by t1.partition_position; TABLE_NAME PARTITION_NAME INDEX_NAME OBJECT_TYPE CLUSTERING_FACTOR NUM_ROWS BLEVEL LAST_ANALYZED STATUS -------------------- ------------------------------ -------------------- ------------ ----------------- ---------- ---------- ------------------- -------- TD_PRUNING IDX_TD_PRUNING_Q1_2014 IDX_TD_PRUNING PARTITION 121 2465 1 2018-12-30 10:54:29 USABLE TD_PRUNING IDX_TD_PRUNING_Q2_2014 IDX_TD_PRUNING PARTITION 122 2494 1 2018-12-30 10:54:29 USABLE TD_PRUNING IDX_TD_PRUNING_Q3_2014 IDX_TD_PRUNING PARTITION 124 2521 1 2018-12-30 10:54:29 USABLE TD_PRUNING IDX_TD_PRUNING_Q4_2014 IDX_TD_PRUNING PARTITION 124 2520 1 2018-12-30 10:54:29 USABLE TD_PRUNING IDX_TD_PRUNING_MAX IDX_TD_PRUNING PARTITION 0 0 0 2018-12-30 10:54:29 USABLE
执行 SQL 查询， 限制条件使用表分区键 D_D1 同时也是全局分区索引键，且为等值查询：
SQL> alter session set statistics_level=all;
SQL> select * from td_pruning where d_d1 = to_date('2014-07-19', 'YYYY-MM-DD');
查看执行计划：
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'iostats last partition'));
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | Buffers |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 8 |
| 1 | PARTITION RANGE SINGLE | | 1 | 3 | 3 | 8 |
| 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| TD_PRUNING | 1 | 7 | 7 | 8 |
|* 3 | INDEX RANGE SCAN | IDX_TD_PRUNING | 1 | 3 | 3 | 4 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D_D1"=TO_DATE(' 2014-07-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
从执行计划可以看出，Oracle 执行了分区修剪操作 PARTITION RANGE SINGLE，而且是以全局索引方式访问数据 GLOBAL INDEX ROWID。
但这里有一个问题：数据表和索引都分区了，那这个分区修剪操作是针对全局分区索引还是表呢？我们可以通过 10046 event 来进行验证。
查看 TRACE 文件路径：
SQL> select value from v$diag_info where name = 'Default Trace File'; VALUE ---------------------------------------------------------- C:\APP\ALLEN\diag\rdbms\orcl\orcl\trace\orcl_ora_3640.trc
清除缓冲区缓存，生产环境勿用：
SQL> alter system flush shared_pool; SQL> alter system flush buffer_cache;
开启 10046 检查：
SQL> alter session set events='10046 trace name context forever, level 12'; 会话已更改。
执行 SQL 语句：
SQL> select * from td_pruning where d_d1 = to_date('2014-07-19', 'YYYY-MM-DD');
关闭 10046 检查：
SQL> alter session set events='10046 trace name context off'; 会话已更改。
查看对应的 Trace File 信息片段：
=====================
PARSING IN CURSOR #353331752 len=73 dep=0 uid=83 oct=3 lid=83 tim=267332685677 hv=2408896744 ad='7ff5883a310' sqlid='f8k795q7t9q78'
select * from td_pruning where d_d1 = to_date('2014-07-19', 'YYYY-MM-DD')
END OF STMT
PARSE #353331752:c=78000,e=93946,p=39,cr=707,cu=0,mis=1,r=0,dep=0,og=1,plh=1769254484,tim=267332685676
EXEC #353331752:c=0,e=31,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1769254484,tim=267332685760
WAIT #353331752: nam='SQL*Net message to client' ela= 5 driver id=1111838976 #bytes=1 p3=0 obj#=0 tim=267332685808
WAIT #353331752: nam='db file scattered read' ela= 722 file#=4 block#=120920 blocks=8 obj#=78628 tim=267332686632
WAIT #353331752: nam='db file scattered read' ela= 808 file#=4 block#=182584 blocks=8 obj#=78618 tim=267332688147
FETCH #353331752:c=0,e=2414,p=16,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=1769254484,tim=267332688245
WAIT #353331752: nam='SQL*Net message from client' ela= 726 driver id=1111838976 #bytes=1 p3=0 obj#=78618 tim=267332689027
WAIT #353331752: nam='SQL*Net message to client' ela= 5 driver id=1111838976 #bytes=1 p3=0 obj#=78618 tim=267332689148
FETCH #353331752:c=0,e=145,p=0,cr=3,cu=0,mis=0,r=15,dep=0,og=1,plh=1769254484,tim=267332689251
WAIT #353331752: nam='SQL*Net message from client' ela= 186358 driver id=1111838976 #bytes=1 p3=0 obj#=78618 tim=267332875648
WAIT #353331752: nam='SQL*Net message to client' ela= 8 driver id=1111838976 #bytes=1 p3=0 obj#=78618 tim=267332875886
FETCH #353331752:c=0,e=123,p=0,cr=2,cu=0,mis=0,r=11,dep=0,og=1,plh=1769254484,tim=267332875936
STAT #353331752 id=1 cnt=27 pid=0 pos=1 obj=0 op='PARTITION RANGE SINGLE PARTITION: 3 3 (cr=8 pr=16 pw=0 time=2550 us cost=3 size=7533 card=27)'
STAT #353331752 id=2 cnt=27 pid=1 pos=1 obj=78611 op='TABLE ACCESS BY GLOBAL INDEX ROWID TD_PRUNING PARTITION: 7 7 (cr=8 pr=16 pw=0 time=2490 us cost=3 size=7533 card=27)'
STAT #353331752 id=3 cnt=27 pid=2 pos=1 obj=78625 op='INDEX RANGE SCAN IDX_TD_PRUNING PARTITION: 3 3 (cr=4 pr=8 pw=0 time=1504 us cost=1 size=0 card=2)'
*** 2018-12-30 11:17:23.843
WAIT #353331752: nam='SQL*Net message from client' ela= 22269687 driver id=1111838976 #bytes=1 p3=0 obj#=78618 tim=267355145750
CLOSE #353331752:c=0,e=12,dep=0,type=0,tim=267355145886
=====================
通过 obj# 定位对象：
SQL> select object_name, subobject_name, object_type from dba_objects where object_id = 78628; OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE ------------------------------ ------------------------------ ---------------- IDX_TD_PRUNING IDX_TD_PRUNING_Q3_2014 INDEX PARTITION
从上述查询可以看出，进行的是全局分区索引段的分区修剪操作。
分区统计信息
对于分区表和分区索引，DBMS_STATS 包既可以单独地收集某个分区的统计信息（partition 级别），也可以收集整个表或索引的统计信息（global 级别）；
参数 GRANULARITY 控制分区统计信息的收集，因为分区统计信息和全局统计信息对于大多数系统来说都是非常重要的，所以 Oracle 推荐将其设置为 AUTO 来同时收集分区，以及全局的统计信息。
| 序号 | 统计信息 |
| 1 | 收集分区及全局统计信息 |
| 2 | 仅收集全局统计信息 |
| 3 | 仅收集所有分区统计信息 |
| 4 | 仅收集单个分区统计信息 |
| 5 | 清除统计信息 |
| 6 | 获取需要收集统计信息的分区表 |
- 收集分区及全局统计信息
SQL> create table ta (
n_id number,
d_date date,
c_desc varchar2(4000)
)
partition by range (d_date) (
partition ta_jan_2014 values less than (to_date('2014-02-01', 'YYYY-MM-DD')),
partition ta_feb_2014 values less than (to_date('2014-03-01', 'YYYY-MM-DD')),
partition ta_mar_2014 values less than (to_date('2014-04-01', 'YYYY-MM-DD')),
partition ta_apr_2014 values less than (to_date('2014-05-01', 'YYYY-MM-DD')),
partition ta_may_2014 values less than (to_date('2014-06-01', 'YYYY-MM-DD')),
partition ta_jun_2014 values less than (to_date('2014-07-01', 'YYYY-MM-DD')),
partition ta_jul_2014 values less than (to_date('2014-08-01', 'YYYY-MM-DD')),
partition ta_aug_2014 values less than (to_date('2014-09-01', 'YYYY-MM-DD')),
partition ta_sep_2014 values less than (to_date('2014-10-01', 'YYYY-MM-DD')),
partition ta_oct_2014 values less than (to_date('2014-11-01', 'YYYY-MM-DD')),
partition ta_nov_2014 values less than (to_date('2014-12-01', 'YYYY-MM-DD')),
partition ta_dec_2014 values less than (to_date('2015-01-01', 'YYYY-MM-DD'))
);
SQL> insert into ta
select
rownum as n_id,
trunc(to_date('2014-01-01', 'YYYY-MM-DD') + rownum / 27.4) as d_date,
dbms_random.string('p', 255) as c_data
from dual
connect by level <= 10000
order by dbms_random.value;
查看统计信息：
SQL> select table_name, partition_name, num_rows, blocks, avg_row_len, last_analyzed, global_stats from user_tab_statistics where table_name = 'TA' order by nvl(partition_position, 0); TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALY GLO ------------------------------ ------------------------------ ---------- ---------- ----------- ---------- --- TA NO TA TA_JAN_2014 NO TA TA_FEB_2014 NO TA TA_MAR_2014 NO TA TA_APR_2014 NO TA TA_MAY_2014 NO TA TA_JUN_2014 NO TA TA_JUL_2014 NO TA TA_AUG_2014 NO TA TA_SEP_2014 NO TA TA_OCT_2014 NO TA TA_NOV_2014 NO TA TA_DEC_2014 NO
以 GRANULARITY => 'AUTO' 方式收集分区和全局统计信息：
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'TA',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
granularity => 'AUTO',
cascade => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE REPEAT',
degree => 4);
end;
/
查看统计信息：
SQL> select table_name, partition_name, num_rows, blocks, avg_row_len, last_analyzed, global_stats from user_tab_statistics where table_name = 'TA' order by nvl(partition_position, 0); TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALY GLO ------------------------------ ------------------------------ ---------- ---------- ----------- ---------- --- TA 10000 552 268 2018-12-27 YES TA TA_JAN_2014 849 46 268 2018-12-27 YES TA TA_FEB_2014 767 46 268 2018-12-27 YES TA TA_MAR_2014 849 46 268 2018-12-27 YES TA TA_APR_2014 822 46 268 2018-12-27 YES TA TA_MAY_2014 850 46 268 2018-12-27 YES TA TA_JUN_2014 822 46 268 2018-12-27 YES TA TA_JUL_2014 849 46 268 2018-12-27 YES TA TA_AUG_2014 850 46 268 2018-12-27 YES TA TA_SEP_2014 822 46 268 2018-12-27 YES TA TA_OCT_2014 849 46 268 2018-12-27 YES TA TA_NOV_2014 822 46 268 2018-12-27 YES TA TA_DEC_2014 849 46 268 2018-12-27 YES
- 仅收集全局统计信息
SQL> create table tb (
n_id number,
d_date date,
c_desc varchar2(4000)
)
partition by range (d_date) (
partition tb_jan_2014 values less than (to_date('2014-02-01', 'YYYY-MM-DD')),
partition tb_feb_2014 values less than (to_date('2014-03-01', 'YYYY-MM-DD')),
partition tb_mar_2014 values less than (to_date('2014-04-01', 'YYYY-MM-DD')),
partition tb_apr_2014 values less than (to_date('2014-05-01', 'YYYY-MM-DD')),
partition tb_may_2014 values less than (to_date('2014-06-01', 'YYYY-MM-DD')),
partition tb_jun_2014 values less than (to_date('2014-07-01', 'YYYY-MM-DD')),
partition tb_jul_2014 values less than (to_date('2014-08-01', 'YYYY-MM-DD')),
partition tb_aug_2014 values less than (to_date('2014-09-01', 'YYYY-MM-DD')),
partition tb_sep_2014 values less than (to_date('2014-10-01', 'YYYY-MM-DD')),
partition tb_oct_2014 values less than (to_date('2014-11-01', 'YYYY-MM-DD')),
partition tb_nov_2014 values less than (to_date('2014-12-01', 'YYYY-MM-DD')),
partition tb_dec_2014 values less than (to_date('2015-01-01', 'YYYY-MM-DD'))
);
SQL> insert into tb
select
rownum as n_id,
trunc(to_date('2014-01-01', 'YYYY-MM-DD') + rownum / 27.4) as d_date,
dbms_random.string('p', 255) as c_datb
from dual
connect by level <= 10000
order by dbms_random.value;
查看统计信息：
SQL> select table_name, partition_name, num_rows, blocks, avg_row_len, last_analyzed, global_stats from user_tab_statistics where table_name = 'TB' order by nvl(partition_position, 0); TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALY GLO ------------------------------ ------------------------------ ---------- ---------- ----------- ---------- --- TB NO TB TB_JAN_2014 NO TB TB_FEB_2014 NO TB TB_MAR_2014 NO TB TB_APR_2014 NO TB TB_MAY_2014 NO TB TB_JUN_2014 NO TB TB_JUL_2014 NO TB TB_AUG_2014 NO TB TB_SEP_2014 NO TB TB_OCT_2014 NO TB TB_NOV_2014 NO TB TB_DEC_2014 NO
以 GRANULARITY => 'GLOBAL' 方式仅收集全局统计信息：
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'TB',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
granularity => 'GLOBAL',
cascade => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE REPEAT',
degree => 4);
end;
/
查看统计信息：
SQL> select table_name, partition_name, num_rows, blocks, avg_row_len, last_analyzed, global_stats from user_tab_statistics where table_name = 'TB' order by nvl(partition_position, 0); TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALY GLO ------------------------------ ------------------------------ ---------- ---------- ----------- ---------- --- TB 10000 552 268 2018-12-27 YES TB TB_JAN_2014 NO TB TB_FEB_2014 NO TB TB_MAR_2014 NO TB TB_APR_2014 NO TB TB_MAY_2014 NO TB TB_JUN_2014 NO TB TB_JUL_2014 NO TB TB_AUG_2014 NO TB TB_SEP_2014 NO TB TB_OCT_2014 NO TB TB_NOV_2014 NO TB TB_DEC_2014 NO
- 仅收集所有分区统计信息
SQL> create table tc (
n_id number,
d_date date,
c_desc varchar2(4000)
)
partition by range (d_date) (
partition tc_jan_2014 values less than (to_date('2014-02-01', 'YYYY-MM-DD')),
partition tc_feb_2014 values less than (to_date('2014-03-01', 'YYYY-MM-DD')),
partition tc_mar_2014 values less than (to_date('2014-04-01', 'YYYY-MM-DD')),
partition tc_apr_2014 values less than (to_date('2014-05-01', 'YYYY-MM-DD')),
partition tc_may_2014 values less than (to_date('2014-06-01', 'YYYY-MM-DD')),
partition tc_jun_2014 values less than (to_date('2014-07-01', 'YYYY-MM-DD')),
partition tc_jul_2014 values less than (to_date('2014-08-01', 'YYYY-MM-DD')),
partition tc_aug_2014 values less than (to_date('2014-09-01', 'YYYY-MM-DD')),
partition tc_sep_2014 values less than (to_date('2014-10-01', 'YYYY-MM-DD')),
partition tc_oct_2014 values less than (to_date('2014-11-01', 'YYYY-MM-DD')),
partition tc_nov_2014 values less than (to_date('2014-12-01', 'YYYY-MM-DD')),
partition tc_dec_2014 values less than (to_date('2015-01-01', 'YYYY-MM-DD'))
);
SQL> insert into tc
select
rownum as n_id,
trunc(to_date('2014-01-01', 'YYYY-MM-DD') + rownum / 27.4) as d_date,
dbms_random.string('p', 255) as c_datc
from dual
connect by level <= 10000
order by dbms_random.value;
SQL> COMMIT;
查看统计信息：
SQL> select table_name, partition_name, num_rows, blocks, avg_row_len, last_analyzed, global_stats from user_tab_statistics where table_name = 'TC' order by nvl(partition_position, 0); TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALY GLO ------------------------------ ------------------------------ ---------- ---------- ----------- ---------- --- TC NO TC TC_JAN_2014 NO TC TC_FEB_2014 NO TC TC_MAR_2014 NO TC TC_APR_2014 NO TC TC_MAY_2014 NO TC TC_JUN_2014 NO TC TC_JUL_2014 NO TC TC_AUG_2014 NO TC TC_SEP_2014 NO TC TC_OCT_2014 NO TC TC_NOV_2014 NO TC TC_DEC_2014 NO
以 GRANULARITY => 'PARTITION' 方式仅收集全局统计信息：
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'TC',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
granularity => 'PARTITION',
cascade => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE REPEAT',
degree => 4);
end;
/
查看统计信息：
此时， 第一行记录中，GLOBAL_STATS 列的值为NO
SQL> select table_name, partition_name, num_rows, blocks, avg_row_len, last_analyzed, global_stats from user_tab_statistics where table_name = 'TC' order by nvl(partition_position, 0); TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALY GLO ------------------------------ ------------------------------ ---------- ---------- ----------- ---------- --- TC 10000 552 268 2018-12-27 NO TC TC_JAN_2014 849 46 268 2018-12-27 YES TC TC_FEB_2014 767 46 268 2018-12-27 YES TC TC_MAR_2014 849 46 268 2018-12-27 YES TC TC_APR_2014 822 46 268 2018-12-27 YES TC TC_MAY_2014 850 46 268 2018-12-27 YES TC TC_JUN_2014 822 46 268 2018-12-27 YES TC TC_JUL_2014 849 46 268 2018-12-27 YES TC TC_AUG_2014 850 46 268 2018-12-27 YES TC TC_SEP_2014 822 46 268 2018-12-27 YES TC TC_OCT_2014 849 46 268 2018-12-27 YES TC TC_NOV_2014 822 46 268 2018-12-27 YES TC TC_DEC_2014 849 46 268 2018-12-27 YES
- 仅收集单个分区统计信息
SQL> create table td (
n_id number,
d_date date,
c_desc varchar2(4000)
)
partition by range (d_date) (
partition td_jan_2014 values less than (to_date('2014-02-01', 'YYYY-MM-DD')),
partition td_feb_2014 values less than (to_date('2014-03-01', 'YYYY-MM-DD')),
partition td_mar_2014 values less than (to_date('2014-04-01', 'YYYY-MM-DD')),
partition td_apr_2014 values less than (to_date('2014-05-01', 'YYYY-MM-DD')),
partition td_may_2014 values less than (to_date('2014-06-01', 'YYYY-MM-DD')),
partition td_jun_2014 values less than (to_date('2014-07-01', 'YYYY-MM-DD')),
partition td_jul_2014 values less than (to_date('2014-08-01', 'YYYY-MM-DD')),
partition td_aug_2014 values less than (to_date('2014-09-01', 'YYYY-MM-DD')),
partition td_sep_2014 values less than (to_date('2014-10-01', 'YYYY-MM-DD')),
partition td_oct_2014 values less than (to_date('2014-11-01', 'YYYY-MM-DD')),
partition td_nov_2014 values less than (to_date('2014-12-01', 'YYYY-MM-DD')),
partition td_dec_2014 values less than (to_date('2015-01-01', 'YYYY-MM-DD'))
);
SQL> insert into td
select
rownum as n_id,
trunc(to_date('2014-01-01', 'YYYY-MM-DD') + rownum / 27.4) as d_date,
dbms_random.string('p', 255) as c_datd
from dual
connect by level <= 10000
order by dbms_random.value;
SQL> COMMIT;
查看统计信息：
SQL> select table_name, partition_name, num_rows, blocks, avg_row_len, last_analyzed, global_stats from user_tab_statistics where table_name = 'TD' order by nvl(partition_position, 0); TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALY GLO ------------------------------ ------------------------------ ---------- ---------- ----------- ---------- --- TD NO TD TD_JAN_2014 NO TD TD_FEB_2014 NO TD TD_MAR_2014 NO TD TD_APR_2014 NO TD TD_MAY_2014 NO TD TD_JUN_2014 NO TD TD_JUL_2014 NO TD TD_AUG_2014 NO TD TD_SEP_2014 NO TD TD_OCT_2014 NO TD TD_NOV_2014 NO TD TD_DEC_2014 NO
以 GRANULARITY => 'PARTITION' + PARTNAME => 分区名 方式仅单个分区统计信息：
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'TD',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
granularity => 'PARTITION',
partname => 'TD_MAY_2014',
cascade => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE REPEAT',
degree => 4);
end;
/
查看统计信息：
SQL> select table_name, partition_name, num_rows, blocks, avg_row_len, last_analyzed, global_stats from user_tab_statistics where table_name = 'TD' order by nvl(partition_position, 0); TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALY GLO ------------------------------ ------------------------------ ---------- ---------- ----------- ---------- --- TD NO TD TD_JAN_2014 NO TD TD_FEB_2014 NO TD TD_MAR_2014 NO TD TD_APR_2014 NO TD TD_MAY_2014 850 46 268 2018-12-27 YES TD TD_JUN_2014 NO TD TD_JUL_2014 NO TD TD_AUG_2014 NO TD TD_SEP_2014 NO TD TD_OCT_2014 NO TD TD_NOV_2014 NO TD TD_DEC_2014 NO
- 清除统计信息
查看表 TA 现有统计信息：
SQL> select table_name, partition_name, num_rows, blocks, avg_row_len, last_analyzed, global_stats from user_tab_statistics where table_name = 'TA' order by nvl(partition_position, 0); TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALY GLO ------------------------------ ------------------------------ ------------ ---------- ----------- ---------- --- TA TABLE 552 268 2018-12-27 YES TA TA_JAN_2014 PARTITION 46 268 2018-12-27 YES TA TA_FEB_2014 PARTITION 46 268 2018-12-27 YES TA TA_MAR_2014 PARTITION 46 268 2018-12-27 YES TA TA_APR_2014 PARTITION 46 268 2018-12-27 YES TA TA_MAY_2014 PARTITION 46 268 2018-12-27 YES TA TA_JUN_2014 PARTITION 46 268 2018-12-27 YES TA TA_JUL_2014 PARTITION 46 268 2018-12-27 YES TA TA_AUG_2014 PARTITION 46 268 2018-12-27 YES TA TA_SEP_2014 PARTITION 46 268 2018-12-27 YES TA TA_OCT_2014 PARTITION 46 268 2018-12-27 YES TA TA_NOV_2014 PARTITION 46 268 2018-12-27 YES TA TA_DEC_2014 PARTITION 46 268 2018-12-27 YES
清除表 TA 统计信息：
begin
DBMS_STATS.DELETE_TABLE_STATS(ownname => 'SCOTT',
tabname => 'TA');
end;
/
查看表 TA 统计信息：
SQL> select table_name, partition_name, num_rows, blocks, avg_row_len, last_analyzed, global_stats from user_tab_statistics where table_name = 'TA' order by nvl(partition_position, 0); TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALY GLO ------------------------------ ------------------------------ ---------- ---------- ----------- ---------- --- TA NO TA TA_JAN_2014 NO TA TA_FEB_2014 NO TA TA_MAR_2014 NO TA TA_APR_2014 NO TA TA_MAY_2014 NO TA TA_JUN_2014 NO TA TA_JUL_2014 NO TA TA_AUG_2014 NO TA TA_SEP_2014 NO TA TA_OCT_2014 NO TA TA_NOV_2014 NO TA TA_DEC_2014 NO
- 获取需要收集统计信息的分区表
SQL> select m.table_name, m.partition_name, sum(m.inserts + m.updates + m.deletes) effect_rows, p.num_rows from user_tab_modifications m, user_tab_partitions p where m.partition_name = p.partition_name group by m.table_name, m.partition_name, p.num_rows having sum(m.inserts + m.updates + m.deletes) > p.num_rows * 0.1;
增量统计信息收集
-
分区表中
GLOBAL_STATS=YES的全局统计信息是否准确关系到查询优化器能否选择较优的执行计划，对分区表执行全局统计信息收集会不可避免的产生FTS（FAST TABLE SCAN）加重系统负担，尤其对于 DW 环境里规模较大的分区表而言更是如此； -
INCREMENTAL STATISTICS COLLECT正是在这一背景下应运而生，简单的说INCREMENTAL STATISTICS COLLECT会实时记录分区表里每个PARTITION每列值的情况，这一信息保存在SYSAUX表空间中，后续根据这一信息在收集全局统计时仅会针对有变化的PARTITION进行STATISTICS COLLECT，并将收集的结果与没有变化过的PARTITION原有的统计信息进行整合，计算出准确的GLOBAL STATS，省去了必须去扫描每一个PARTITION的步骤； -
增量统计信息收集技术非常适用于分区表新增分区的情况， 因为在非
INCREMENTAL方式下新增分区后会扫描分区表中所有的分区并收集所有分区的统计信息，即使那些没有改变过的分区也会被重新扫描一遍，才能得出GLOBAL的统计信息； -
从 Oracle 官方的例子中，给出的使用场景是：如
SALES表，使用RANGE分区，需要每天新增一个分区记录当天的销售状况。过去的分区几乎没有变动，主要的DML操作和SELECT查询都会在新增的这个分区中。因此，当把SALES表的INCREMENTAL开启后（默认是不开启），使用DBMS_STATS.GATHER_TABLE_STATS()收集统计信息时，由于其他分区几乎无变动，因此概要信息基本不会变动，只对一个分区（即新增的这个分区）的概要信息再次分析，并且通过概要信息，再汇总得出GLOBAL的信息。
开启增量统计信息收集的条件，下列查询用于查看系统中的默认值：
- 分区表的
PUBLISH设为TRUE；
SQL> select
DBMS_STATS.GET_PREFS(pname => 'PUBLISH',
ownname => 'SCOTT',
tabname => 'TA') "PUBLISH_STATUS"
from dual;
PUBLISH_STATUS
-----------------------------
TRUE
- 分区表的
INCREMENTAL设为TRUE；
SQL> select
DBMS_STATS.GET_PREFS(pname => 'INCREMENTAL',
ownname => 'SCOTT',
tabname => 'TA') "INCREMENTAL_STATUS"
from dual;
INCREMENTAL_STATUS
-----------------------
FALSE
ESTIMATE_PERCENT设为AUTO_SAMPLE_SIZE；
SQL> select
DBMS_STATS.GET_PREFS(pname => 'ESTIMATE_PERCENT',
ownname => 'SCOTT',
tabname => 'TA') "ESTIMATE_PERCENT_STATUS"
from dual;
ESTIMATE_PERCENT_STATUS
-------------------------------------
DBMS_STATS.AUTO_SAMPLE_SIZE
GRANULARITY设为AUTO。
SQL> select
DBMS_STATS.GET_PREFS(pname => 'GRANULARITY',
ownname => 'SCOTT',
tabname => 'TA') "GRANULARITY_STATUS"
from dual;
GRANULARITY_STATUS
--------------------------
AUTO
将分区表 TA 设置为 INCREMENTAL，分区表 TB 使用默认值为非 INCREMENTAL：
begin
DBMS_STATS.SET_TABLE_PREFS(ownname => 'SCOTT',
tabname => 'TA',
pname => 'INCREMENTAL',
pvalue => 'TRUE');
end;
/
| 序号 | 统计信息收集方式 |
| 1 | NONINCREMENTAL |
| 2 | INCREMENTAL |
非 INCREMENTAL 下， 分区表 TB 统计信息的收集方式：
- 清除分区表
TB的统计信息，确保GLOBAL、PARTITION级别没有统计信息
SQL> alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'; begin DBMS_STATS.DELETE_TABLE_STATS(ownname => 'SCOTT', tabname => 'TB'); end; / SQL> select table_name, partition_name, num_rows, blocks, avg_row_len, last_analyzed, global_stats from user_tab_statistics where table_name = 'TB' order by nvl(partition_position, 0); TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED GLO ------------------------------ ------------------------------ ---------- ---------- ----------- ------------------- --- TB NO TB TB_JAN_2014 NO TB TB_FEB_2014 NO TB TB_MAR_2014 NO TB TB_APR_2014 NO TB TB_MAY_2014 NO TB TB_JUN_2014 NO TB TB_JUL_2014 NO TB TB_AUG_2014 NO TB TB_SEP_2014 NO TB TB_OCT_2014 NO TB TB_NOV_2014 NO TB TB_DEC_2014 NO
- 以
AUTO方式收集GLOBAL、PARTITION级别统计信息
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'TB',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
granularity => 'AUTO',
cascade => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE REPEAT',
degree => 4);
end;
/
- 查看此时
GLOBAL、PARTITION级别统计信息
SQL> select table_name, partition_name, num_rows, blocks, avg_row_len, last_analyzed, global_stats from user_tab_statistics where table_name = 'TB' order by nvl(partition_position, 0); TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED GLO ------------------------------ ------------------------------ ---------- ---------- ----------- ------------------- --- TB 10000 552 268 2018-12-27 23:27:48 YES TB TB_JAN_2014 849 46 268 2018-12-27 23:27:48 YES TB TB_FEB_2014 767 46 268 2018-12-27 23:27:48 YES TB TB_MAR_2014 849 46 268 2018-12-27 23:27:48 YES TB TB_APR_2014 822 46 268 2018-12-27 23:27:48 YES TB TB_MAY_2014 850 46 268 2018-12-27 23:27:48 YES TB TB_JUN_2014 822 46 268 2018-12-27 23:27:48 YES TB TB_JUL_2014 849 46 268 2018-12-27 23:27:48 YES TB TB_AUG_2014 850 46 268 2018-12-27 23:27:48 YES TB TB_SEP_2014 822 46 268 2018-12-27 23:27:48 YES TB TB_OCT_2014 849 46 268 2018-12-27 23:27:48 YES TB TB_NOV_2014 822 46 268 2018-12-27 23:27:48 YES TB TB_DEC_2014 849 46 268 2018-12-27 23:27:48 YES
- 为分区表
TB新增一个分区
SQL> alter table tb add partition tb_jan_2015 values less than (to_date('2015-02-01', 'YYYY-MM-DD'));
表已更改。
- 为新分区添加记录数
SQL> insert into tb
with a as(
select max(n_id) n_id from tb
)
select
n_id + rownum n_id,
trunc(to_date('2015-01-01', 'YYYY-MM-DD') + rownum / 27.4 / 3) as d_date,
dbms_random.string('p', 255) as c_datd
from a
connect by level <= 2000
order by dbms_random.value;
- 对新建的分区
TB_JAN_2015进行PARTIITON级别 统计信息收集，确认这个分区已经有最新的统计信息了
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'TB',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
granularity => 'PARTITION',
partname => 'TB_JAN_2015',
cascade => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE REPEAT',
degree => 4);
end;
/
SQL> select
table_name, partition_name,
num_rows, blocks, avg_row_len,
last_analyzed, global_stats
from user_tab_statistics where table_name = 'TB'
order by nvl(partition_position, 0);
TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED GLO
------------------------------ ------------------------------ ---------- ---------- ----------- ------------------- ---
TB 10000 552 268 2018-12-27 23:27:48 YES
TB TB_JAN_2014 849 46 268 2018-12-27 23:27:48 YES
TB TB_FEB_2014 767 46 268 2018-12-27 23:27:48 YES
TB TB_MAR_2014 849 46 268 2018-12-27 23:27:48 YES
TB TB_APR_2014 822 46 268 2018-12-27 23:27:48 YES
TB TB_MAY_2014 850 46 268 2018-12-27 23:27:48 YES
TB TB_JUN_2014 822 46 268 2018-12-27 23:27:48 YES
TB TB_JUL_2014 849 46 268 2018-12-27 23:27:48 YES
TB TB_AUG_2014 850 46 268 2018-12-27 23:27:48 YES
TB TB_SEP_2014 822 46 268 2018-12-27 23:27:48 YES
TB TB_OCT_2014 849 46 268 2018-12-27 23:27:48 YES
TB TB_NOV_2014 822 46 268 2018-12-27 23:27:48 YES
TB TB_DEC_2014 849 46 268 2018-12-27 23:27:48 YES
TB TB_JAN_2015 2000 110 269 2018-12-27 23:47:53 YES
从统计信息可以看出， 最后一行记录（也就是新加分区）的统计信息收集时间明显区别于其它分区。
- 以
AUTO方式重新同时收集GLOBAL、PARTITION级别统计信息
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'TB',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
granularity => 'AUTO',
cascade => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE REPEAT',
degree => 4);
end;
/
- 查看统计信息
SQL> select table_name, partition_name, num_rows, blocks, avg_row_len, last_analyzed, global_stats from user_tab_statistics where table_name = 'TB' order by nvl(partition_position, 0); TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED GLO ------------------------------ ------------------------------ ---------- ---------- ----------- ------------------- --- TB 12000 662 268 2018-12-27 23:53:39 YES TB TB_JAN_2014 849 46 268 2018-12-27 23:53:39 YES TB TB_FEB_2014 767 46 268 2018-12-27 23:53:38 YES TB TB_MAR_2014 849 46 268 2018-12-27 23:53:39 YES TB TB_APR_2014 822 46 268 2018-12-27 23:53:38 YES TB TB_MAY_2014 850 46 268 2018-12-27 23:53:39 YES TB TB_JUN_2014 822 46 268 2018-12-27 23:53:39 YES TB TB_JUL_2014 849 46 268 2018-12-27 23:53:39 YES TB TB_AUG_2014 850 46 268 2018-12-27 23:53:38 YES TB TB_SEP_2014 822 46 268 2018-12-27 23:53:39 YES TB TB_OCT_2014 849 46 268 2018-12-27 23:53:39 YES TB TB_NOV_2014 822 46 268 2018-12-27 23:53:39 YES TB TB_DEC_2014 849 46 268 2018-12-27 23:53:38 YES TB TB_JAN_2015 2000 110 269 2018-12-27 23:53:39 YES
可以看出，GLOBAL 和 PARTITION 级别的统计信息都已经刷新为最新时间了，这说明在非 INCREMENTAL 方式下。即使没有更改过的分区，其统计信息也会被重刷一遍。
INCREMENTAL 下， 分区表 TA 统计信息的收集方式：
- 清除分区表
TA的统计信息，确保GLOBAL、PARTITION级别没有统计信息
SQL> alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'; begin DBMS_STATS.DELETE_TABLE_STATS(ownname => 'SCOTT', tabname => 'TA'); end; / SQL> select table_name, partition_name, num_rows, blocks, avg_row_len, last_analyzed, global_stats from user_tab_statistics where table_name = 'TA' order by nvl(partition_position, 0); TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED GLO ------------------------------ ------------------------------ ---------- ---------- ----------- ------------------- --- TA NO TA TA_JAN_2014 NO TA TA_FEB_2014 NO TA TA_MAR_2014 NO TA TA_APR_2014 NO TA TA_MAY_2014 NO TA TA_JUN_2014 NO TA TA_JUL_2014 NO TA TA_AUG_2014 NO TA TA_SEP_2014 NO TA TA_OCT_2014 NO TA TA_NOV_2014 NO TA TA_DEC_2014 NO
- 以
AUTO方式同时收集GLOBAL、PARTITION级别统计信息
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'TA',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
granularity => 'AUTO',
cascade => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE REPEAT',
degree => 4);
end;
/
- 查看此时
GLOBAL、PARTITION级别统计信息
SQL> select table_name, partition_name, num_rows, blocks, avg_row_len, last_analyzed, global_stats from user_tab_statistics where table_name = 'TA' order by nvl(partition_position, 0); TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED GLO ------------------------------ ------------------------------ ---------- ---------- ----------- ------------------- --- TA 10000 552 268 2018-12-28 10:18:24 YES TA TA_JAN_2014 849 46 268 2018-12-28 10:18:24 YES TA TA_FEB_2014 767 46 268 2018-12-28 10:18:24 YES TA TA_MAR_2014 849 46 268 2018-12-28 10:18:24 YES TA TA_APR_2014 822 46 268 2018-12-28 10:18:24 YES TA TA_MAY_2014 850 46 268 2018-12-28 10:18:24 YES TA TA_JUN_2014 822 46 268 2018-12-28 10:18:23 YES TA TA_JUL_2014 849 46 268 2018-12-28 10:18:24 YES TA TA_AUG_2014 850 46 268 2018-12-28 10:18:24 YES TA TA_SEP_2014 822 46 268 2018-12-28 10:18:24 YES TA TA_OCT_2014 849 46 268 2018-12-28 10:18:24 YES TA TA_NOV_2014 822 46 268 2018-12-28 10:18:24 YES TA TA_DEC_2014 849 46 268 2018-12-28 10:18:24 YES
- 为分区表
TA新增一个分区
SQL> alter table ta add partition ta_jan_2015
values less than (to_date('2015-02-01', 'YYYY-MM-DD'));
表已更改。
- 为新分区添加记录数
SQL> insert into ta
with a as(
select max(n_id) n_id from ta
)
select
n_id + rownum n_id,
trunc(to_date('2015-01-01', 'YYYY-MM-DD') + rownum / 27.4 / 3) as d_date,
dbms_random.string('p', 255) as c_datd
from a
connect by level <= 2000
order by dbms_random.value;
SQL> COMMIT;
- 对新建的分区
TA_JAN_2015进行PARTIITON级别 统计信息收集，确认这个分区已经有最新的统计信息了
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'TA',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
granularity => 'PARTITION',
partname => 'TA_JAN_2015',
cascade => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE REPEAT',
degree => 4);
end;
/
SQL> select
table_name, partition_name,
num_rows, blocks, avg_row_len,
last_analyzed, global_stats
from user_tab_statistics where table_name = 'TA'
order by nvl(partition_position, 0);
TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED GLO
------------------------------ ------------------------------ ---------- ---------- ----------- ------------------- ---
TA 10000 552 268 2018-12-28 10:18:24 YES
TA TA_JAN_2014 849 46 268 2018-12-28 10:18:24 YES
TA TA_FEB_2014 767 46 268 2018-12-28 10:18:24 YES
TA TA_MAR_2014 849 46 268 2018-12-28 10:18:24 YES
TA TA_APR_2014 822 46 268 2018-12-28 10:18:24 YES
TA TA_MAY_2014 850 46 268 2018-12-28 10:18:24 YES
TA TA_JUN_2014 822 46 268 2018-12-28 10:18:23 YES
TA TA_JUL_2014 849 46 268 2018-12-28 10:18:24 YES
TA TA_AUG_2014 850 46 268 2018-12-28 10:18:24 YES
TA TA_SEP_2014 822 46 268 2018-12-28 10:18:24 YES
TA TA_OCT_2014 849 46 268 2018-12-28 10:18:24 YES
TA TA_NOV_2014 822 46 268 2018-12-28 10:18:24 YES
TA TA_DEC_2014 849 46 268 2018-12-28 10:18:24 YES
TA TA_JAN_2015 2000 110 269 2018-12-28 10:24:17 YES
从统计信息可以看出， 最后一行记录（也就是新加分区）的统计信息收集时间明显区别于其它分区。
- 以
AUTO方式重新同时收集GLOBAL、PARTITION级别统计信息
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'TA',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
granularity => 'AUTO',
cascade => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE REPEAT',
degree => 4);
end;
/
- 查看统计信息
SQL> select table_name, partition_name, num_rows, blocks, avg_row_len, last_analyzed, global_stats from user_tab_statistics where table_name = 'TA' order by nvl(partition_position, 0); TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED GLO ------------------------------ ------------------------------ ---------- ---------- ----------- ------------------- --- TA 12000 662 268 2018-12-28 10:27:48 YES TA TA_JAN_2014 849 46 268 2018-12-28 10:18:24 YES TA TA_FEB_2014 767 46 268 2018-12-28 10:18:24 YES TA TA_MAR_2014 849 46 268 2018-12-28 10:18:24 YES TA TA_APR_2014 822 46 268 2018-12-28 10:18:24 YES TA TA_MAY_2014 850 46 268 2018-12-28 10:18:24 YES TA TA_JUN_2014 822 46 268 2018-12-28 10:18:23 YES TA TA_JUL_2014 849 46 268 2018-12-28 10:18:24 YES TA TA_AUG_2014 850 46 268 2018-12-28 10:18:24 YES TA TA_SEP_2014 822 46 268 2018-12-28 10:18:24 YES TA TA_OCT_2014 849 46 268 2018-12-28 10:18:24 YES TA TA_NOV_2014 822 46 268 2018-12-28 10:18:24 YES TA TA_DEC_2014 849 46 268 2018-12-28 10:18:24 YES TA TA_JAN_2015 2000 110 269 2018-12-28 10:24:17 YES
可以看出，GLOBAL 级别的统计信息已经刷新为最新时间了，但 PARTITION 级别统计信息并没有更新，这是因为分区没有发生变更。
- 对分区
TA_JAN_2015执行DML操作：
SQL> delete from ta partition(ta_jan_2015) where rownum <= 1500;
- 以
AUTO方式重新收集GLOBAL、PARTITION级别统计信息
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'TA',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
granularity => 'AUTO',
cascade => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE REPEAT',
degree => 4);
end;
/
- 查看统计信息
SQL> select table_name, partition_name, num_rows, blocks, avg_row_len, last_analyzed, global_stats from user_tab_statistics where table_name = 'TA' order by nvl(partition_position, 0); TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED GLO ------------------------------ ------------------------------ ---------- ---------- ----------- ------------------- --- TA 10500 662 268 2018-12-28 11:01:34 YES TA TA_JAN_2014 849 46 268 2018-12-28 10:18:24 YES TA TA_FEB_2014 767 46 268 2018-12-28 10:18:24 YES TA TA_MAR_2014 849 46 268 2018-12-28 10:18:24 YES TA TA_APR_2014 822 46 268 2018-12-28 10:18:24 YES TA TA_MAY_2014 850 46 268 2018-12-28 10:18:24 YES TA TA_JUN_2014 822 46 268 2018-12-28 10:18:23 YES TA TA_JUL_2014 849 46 268 2018-12-28 10:18:24 YES TA TA_AUG_2014 850 46 268 2018-12-28 10:18:24 YES TA TA_SEP_2014 822 46 268 2018-12-28 10:18:24 YES TA TA_OCT_2014 849 46 268 2018-12-28 10:18:24 YES TA TA_NOV_2014 822 46 268 2018-12-28 10:18:24 YES TA TA_DEC_2014 849 46 268 2018-12-28 10:18:24 YES TA TA_JAN_2015 500 110 269 2018-12-28 11:01:34 YES
可以看出，GLOBAL 级别的统计信息已经刷新为最新时间了，但 PARTITION 级别统计信息只有分区 TA_JAN_2015 有更新，这是因为 INCREMENTAL 方式下仅会读取更改过的分区的统计信息，并汇总至 GLOBAL 统计
。
参考资料
http://blog.itpub.net/17203031/viewspace-1191341/
https://blog.csdn.net/caoyhao/article/details/50269613
http://blog.itpub.net/53956/viewspace-1372944
https://www.cnblogs.com/yumiko/p/6163523.html
原创文章，转载请注明出处：http://www.opcoder.cn/article/18/