提示：公众号代码会自动换行，建议横屏阅读或左右滑动代码观看
这一节我们将继续介绍分区表的索引和11g中分区表的新特性。分区索引分为本地(local index)索引和全局索引(global index)。局部索引比全局索引容易管理, 而全局索引比较快。
与索引有关的数据字典：
（1）dba_part_indexes 分区索引的概要统计信息，可以得知每个表上有哪些分区索引，分区索引的类型(local/global)
（2）dba_ind_partitions每个分区索引的分区级统计信息
（3）dba_indexes/dba_part_indexes可以得到每个表上有哪些非分区索引
Local索引肯定是分区索引，Global索引可以选择是否分区，如果分区，只能是有前缀的分区索引。
分区索引分2类：有前缀(prefix)的分区索引和无前缀(nonprefix)的分区索引:
（1）有前缀的分区索引指包含了分区键，并且将其作为引导列的索引。如：
CREATE INDEX I_ID_GLOBAL ON WANG_PART(ID) GLOBAL --引导列
PARTITION BY RANGE(ID) --分区键
(
PARTITION P1 VALUES LESS THAN (200),
PARTITION P2 VALUES LESS THAN (MAXVALUE)
);
这里的 ID 就是分区键，并且分区键id 也是索引的引导列。
（2）无前缀的分区索引的列不是以分区键开头，或者不包含分区键列。如：
CREATE INDEX IX_CUSTADDR_LOCAL_ID_P ON CUSTADDR(ID)LOCAL
(
PARTITION T_LIST556 TABLESPACE ICD_SERVICE,
PARTITION P_OTHER TABLESPACE ICD_SERVICE
);
这个分区是按照areacode来的。但是索引的引导列是ID。所以它就是非前缀分区索引。全局分区索引不支持非前缀的分区索引，如果创建则会报错。
Local本地索引
对于local索引，当表的分区发生变化时，索引的维护由Oracle自动进行。
注意事项：
（1） 局部索引一定是分区索引，分区键等同于表的分区键。
（2） 前缀和非前缀索引都可以支持索引分区消除，前提是查询的条件中包含索
引分区键。
（3） 局部索引只支持分区内的唯一性，无法支持表上的唯一性，因此如果要用
局部索引去给表做唯一性约束，则约束中必须要包括分区键列。
（4） 局部分区索引是对单个分区的，每个分区索引只指向一个表分区；全局索引则不然，一个分区索引能指向n个表分区，同时，一个表分区，也可能指向 n个索引分区，对分区表中的某个分区做 truncate或者move，shrink等，可能会影响到n个全局索引分区，正因为这点，局部分区索引具有更高的可用性。
（5） 位图索引必须是局部分区索引。
（6） 局部索引多应用于数据仓库环境中。
（7） B树索引和位图索引都可以分区，但是 HASH索引不可以被分区。
Global索引
对于global索引，可以选择是否分区，而且索引的分区可以不与表分区相对应。全局分区索引只能是 B 树索引，到目前为止(10gR2)，Oracle只支持有前缀的全局索引。 另外Oracle不会自动的维护全局分区索引，当我们在对表的分区做修改之后，如果对分区进行维护操作时不加上update global indexes的话，通常会导致全局索引的INVALDED，必须在执行完操作后 REBUILD。
注意事项：
（1）全局索引可以分区，也可以是不分区索引，全局索引必须是前缀索引，即全局索引的索引列必须是以索引分区键作为其前几列。
（2）全局索引可以依附于分区表；也可以依附于非分区表。
（3）全局分区索引的索引条目可能指向若干个分区，因此，对于全局分区索引，即使只截断一个分区中的数据，都需要 rebulid 若干个分区甚至是整个索引。
（4）全局索引多应用于OLTP系统中。
（5）全局分区索引只按范围或者散列分区，hash分区是10g以后才支持。
（6） Oracle9i 以后对分区表做move或者truncate的时可以用update global indexes语句来同步更新全局分区索引，用消耗一定资源来换取高度的可用性。
（7） 表用a列作分区，索引用 b做局部分区索引，若 where条件中用 b来查询，那么Oracle会扫描所有的表和索引的分区，成本会比分区更高，此时可以考虑用b做全局分区索引。
注意：Oracle只支持2种类型的全局分区索引：rangepartitioned 和Hash Partitioned.
示例1 全局索引，全局索引对所有分区类型都支持：
CREATE INDEX IX_CUSTADDR_ GLOBAL_ID ON CUSTADDR(ID) GLOBAL;
示例2：全局分区索引，只支持Range分区和Hash分区：
（1）创建测试分区表：
CREATE TABLE WANG_PART(
ID NUMBER,
TIME DATE
)
PARTITION BY RANGE (TIME)
(
PARTITION P1 VALUES LESS THAN (TO_DATE('2010-10-1', 'yyyy-mm-dd')),
PARTITION P2 VALUES LESS THAN (TO_DATE('2010-11-1', 'yyyy-mm-dd')),
PARTITION P3 VALUES LESS THAN (TO_DATE('2010-12-1', 'yyyy-mm-dd')),
PARTITION P4 VALUES LESS THAN (MAXVALUE)
);
创建有前缀的分区索引。
CREATE INDEX I_ID_GLOBAL ON WANG_PART(ID)GLOBAL
PARTITION BY RANGE(ID)
(
PARTITION P1 VALUES LESS THAN (200),
PARTITION P2 VALUES LESS THAN (MAXVALUE)
);
只要索引的引导列包含分区键，就是有前缀的分区索引。
索引重建问题
（1）分区索引
对于分区索引，不能整体进行重建，只能对单个分区进行重建。语法如下：
ALTER INDEX IDX_NAME REBUILD PARTITION INDEX_PARTITION_NAME [ONLINE NOLOGGING]
说明：
online:表示重建的时候不会锁表。
Nologging:表示建立索引的时候不生成日志，加快速度。
如果要重建分区索引，只能drop表原索引，在重新创建：
CREATE INDEX LOC_XXXX_COL ON XXXX(COL) LOCAL TABLESPACE PART_TAB;
这个操作要求较大的临时表空间和排序区。
（2）全局索引
Oracle会自动维护分区索引，对于全局索引，如果在对分区表操作时，没有指定update index，则会导致全局索引失效，需要重建。
drop分区时使用update indexes
ALTER TABLE WANG_PART DROP PARTITION P4 UPDATE INDEXES;
重建全局索引命令如下：
ALTER INDEX IDX_NAME REBUILD [ONLINE NOLOGGING]
补充一点，分区表存储空间的问题：
通过user_tab_partitions表可以查看到每个分区对应的tablesapce_name， 但是，如果通过all_tables表，却查不到分区表对应表空间的信息。
11g 中的分区表新特性
Partition（分区）一直是Oracle数据库引以为傲的一项技术，正是分区的存储在让Oracle高效的处理海量数据成为可能，在Oracle 11g中，分区技术在易用性和可扩展性上再次得到了增强。
1 IntervalPartitioning
在曾经的一个项目中，由于数据量的巨大，所以表设计为每一个小时一个分区，数据库管理员日常要做的一件重复而无聊的工作就是每隔一天要生成新的24个分区，用以存储第二天的数据。而在11g中这项工作可以交由Oracle自动完成，基于Range和List的Interval Partitioning分区类型登场。
CREATE TABLE WANG_INTERVAL(
ID NUMBER,
TIME_COL DATE
)
PARTITION BY RANGE (TIME_COL)
INTERVAL(NUMTOYMINTERVAL(1, 'month'))
(
PARTITION P0 VALUES LESS THAN (TO_DATE('1-1-2019', 'dd-mm-yyyy'))
);
指定需要Oracle自动创建分区的间隔时间，上面这个例子是1个月，然后至少创建一个基本分区，这个例子是在2019-1-1之前的所有数据都在P0分区中，以后每个月的数据都会存放在Oracle自动创建的一个新分区中。
2 SystemPartitioning
系统分区，在这个新的类型中，我们不需要指定任何分区键，数据会进入哪个分区完全由应用程序决定，实际上也就是由SQL来决定，终于，我们在Insert语句中可以指定插入哪个分区了。
假设我们创建了下面这张分区表，注意，没有指定任何分区键：
CREATE TABLE WANG_TEST(
C1 INT,
C2 INT
)
PARTITION BY SYSTEM
(
PARTITION P1 TABLESPACE TBS_1,
PARTITION P2 TABLESPACE TBS_2,
PARTITION P3 TABLESPACE TBS_3,
PARTITION P4 TABLESPACE TBS_4
);
现在由SQL语句来指定插入哪个分区：
① 数据插入p1分区
INSERT INTO WANG_TEST PARTITION (P1) VALUES (4, 5);
② 数据插入第2个分区，也就是p2分区
INSERT INTO WANG_TEST PARTITION (P2) VALUES (7,8);
③ 为了实现绑定变量，用pno变量来代替实际分区号，以避免过度解析
INSERT INTO WANG_TEST PARTITION (:PNO) VALUES (9,10);
由于System Partitioning的特殊性，所以很明显，这种类型的分区将不支持Partition Split 操作，也不支持create table as select 操作。
3 More CompositePartitioning
在10g中，我们知道复合分区只支持Range-List和Range-Hash，而在在11g中复合分区的类型大大增加，现在Range，List，Interval 都可以作为Top level分区，而Second level 则可以是Range，List，Hash也就是在11g中可以有3*3=9种复合分区，满足更多的业务需求。
4 VirtualColumn-Based Partitioning
Virtual Column是 11g中的一个新功能，这种列中的数据并不实际存储于磁盘上（我们可以看成是一个类似Function的列），只有当读取的时候才实时计算。暂时不讨论性能问题，这个功能还是比较有意思的。
可以通过这样的语句来创建虚拟列。
CREATE TABLE wang_virtual(
COL_1 NUMBER(6) NOT NULL,
COL_2 NUMBER NOT NULL,
COL_V AS (COL_1 *( 1+COL_2)
);
虚拟列虽然没有实际的存储空间，但是却可以跟其他普通列一样，创建索引，作为分区键，甚至可以收集统计信息。
11g虚拟列实现按星期分区表
在11g之前分区表的partitionkey必须是物理存在的。11g开始提供了虚拟列，并且可以作为partition key ，创建分区表：
CREATE TABLE WANT_VIRTUAL
(
GETDATE DATE NOT NULL,
WD NUMBER GENERATED ALWAYS AS (TO_NUMBER(TO_CHAR(GETDATE, 'D'))) VIRTUAL --虚拟列
)
PARTITION BY LIST (WD)(
PARTITION SUN VALUES (1),
PARTITION MON VALUES (2),
PARTITION TUE VALUES (3),
PARTITION WED VALUES (4),
PARTITION THU VALUES (5),
PARTITION FRI VALUES (6),
PARTITION SAT VALUES (7)
);
注：如果是10g，就要添加一物理列。在OracleDatabase 11g中还可以创建新类型的Interval分区表，Interval类型分区表，可以根据加载数据，自动创建指定间隔的分区。注意：如果在建Interval分区表是没有把所有的分区写完成，在插入相关数据后会自动生成分区。
扫描下方二维吗关注公众号，了解相关更新
推荐阅读
