再上一篇:13.1分区概述
上一篇:13.2表分区机制
主页
下一篇:13.4再论分区和性能
再下一篇:13.5审计和段空间压缩
文章列表

13.3索引分区

Oracle 9i 10g编程艺术:深入数据库体系结构

索引与表类似,也可以分区。对索引进行分区有两种可能的方法: 随表对索引完成相应的分区:这也称为局部分区索引(locally pertitioned index)。每
个表分区都有一个索引分区,而且只索引该表分区。一个给定索引分区中的所有条目都指向一 个表分区,表分区中的所有行都表示在一个索引分区中。
按区间对索引分区:这也称为全局分区索引(globally partitioned index)。在此,索引
按区间分区(或者在Oracle 10g中该可以按散列分区),一个索引分区可能指向任何(和所有) 表分区。
图13-5展示了局部索引和全局索引的区别。

图13-5 局部和全局索引分区 对于全局分区索引,要注意实际上索引分区数可能不同于表分区数。
由于全局索引只按区间或散列分区,如果希望有一个列表或组合分区索引,就必须使用局部索引。局 部索引会使用底层表相同的机制分区。
注意 全局索引的散列分区是Oracle 10g Release 1及以后的版本中才有的新特性。在Oracle9i及 以前的版本中,只能按区间进行全局分区。

13.3.1局部索引

Oracle划分了以下两类局部索引:
局部前缀索引(local prefixed index):在这些索引中,分区键在索引定义的前几列上。 例如,一个表在名为 LOAD_DATE的列上进行区间分区,该表上的局部前缀索引就是 LOAD_DATE 作为其索引列列表中的第一列。
局部非前缀索引(local nonprefixed index):这些索引不以分区键作为其列列表的前几 列。索引可能包含分区键列,也可能不包含。
这 两类索引都可以利用分区消除,它们都支持惟一性(只有非前缀索引包含分区键)等。事实上, 使用局部前缀索引的查询总允许索引分区消除,而使用局部非前缀索 引的查询可能不允许。正是由于这个 原因,所以在某些人看来局部非前缀索引“更慢”,它们不能保证分区消除(但确实可以支持分区消除)。
如果查询中将索引用作访问表的初始路径,那么从本质来讲,局部前缀索引并不比局部非前缀索引更 好。我的意思是说,如何查询把“扫描一个索引”作为第一步,那么前缀索引和非前缀索引之间并没有太 大的差别。

1. 分区消除行为

如果查询首先访问索引,它是否能消除分区完全取决于查询中的谓词。要说明这一点,举一个小例子
于2的值都在分区PART_1中,小于3的值则都在分区PART_2中:
ops$tkyte@ORA10G> CREATE TABLE partitioned_table
2 ( a int,
3 b int,
4 data char(20)
5 )
6 PARTITION BY RANGE (a)
7 (
8 PARTITION part_1 VALUES LESS THAN(2) tablespace p1,
9 PARTITION part_2 VALUES LESS THAN(3) tablespace p2
10 )
11 /
Table created.

然后我们创建一个局部前缀索引LOCAL_PREFIXED和一个局部非前缀索引LOCAL_NONPREFIXED。注意,
非前缀索引在其定义中没有以A作为其最前列,这是这一点使之成为一个非前缀索引:
ops$tkyte@ORA10G> create index local_prefixed on partitioned_table (a,b) local;
Index created.
ops$tkyte@ORA10G> create index local_nonprefixed on partitioned_table (b) local; Index created.


接下来,我们向一个分区中插入一些数据,并收集统计信息:
ops$tkyte@ORA10G> insert into partitioned_table
2 select mod(rownum-1,2)+1, rownum, 'x'
3 from all_objects;
48967 rows created.

ops$tkyte@ORA10G> begin
2 dbms_stats.gather_table_stats
3 ( user,
4 'PARTITIONED_TABLE',
5 cascade=>TRUE );
6 end;
7 /
PL/SQL procedure successfully completed.
将表空间P2离线,其中包含用于表和索引的PART_2分区:
ops$tkyte@ORA10G> alter tablespace p2 offline;
Tablespace altered.

表空间 P2 离线后,Oracle 就无法访问这些特定的索引分区。这就好像是我们遭遇了“介质故障”,
导致分区不可用。现在我们查询这个表,来看看不同的查询需要哪些索引分区。第一个查询编写为允许使 用局部前缀索引:
ops$tkyte@ORA10G> select * from partitioned_table where a = 1 and b = 1;
A B DATA
---------- ---------- --------------------
1 1 x

这个查询成功了,通过查看解释计划,可以看到这个查询为什么能成功。我们将使用内置包
DBMS_XPLAN来查看这个查询访问了哪些分区。输出中的PSTART (分区开始)和PSTOP(分区结束)这 两列准确地显示出,这个查询要想成功需要哪些分区必须在线而且可用:
ops$tkyte@ORA10G> delete from plan_table;
4 rows deleted.
ops$tkyte@ORA10G> explain plan for

2 select * from partitioned_table where a = 1 and b = 1; Explained.

ops$tkyte@ORA10G> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------

| Operation | Name

| Rows | Pstart | Pstop |

----------------------------------------------------------------------------------

| SELECT STATEMENT | | 1

| | |

| PARTITION RANGE SINGLE | | 1 | 1

| 1 |

| TABLE ACCESS BY LOCAL INDEX ROWID | PARTITIONED_TABLE | 1 | 1 | 1 |

| INDEX RANGE SCAN | LOCAL_PREFIXED | 1 | 1 | 1

|

---------------------------------------------------------------------------------- Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"=1 AND "B"=1)
注意 这里对DBMS_XPLAN输出进行了编辑,删除了与这个列在无关的信息,从而减少了例子的篇幅,
以便在一页内能放下。
因此,使用LOCAL_PREFIXED的查询成功了。优化器能消除LOCAL_PREFIXED的PART_2不予考虑,因 为我们在查询中指定了A=1,而且在计划中可以清楚地看到PSTART和PSTOP都等于1.分区消除帮助了我们 。 不过,第二个查询却失败了:
ops$tkyte@ORA10G> select * from partitioned_table where b = 1;
ERROR:
ORA-00376: file 13 cannot be read at this time

ORA-01110: data file 13: '/home/ora10g/.../o1_mf_p2_1dzn8jwp_.dbf' no rows selected

通过使用同样的技术,可以看到这是为什么:
ops$tkyte@ORA10G> delete from plan_table;
4 rows deleted.
ops$tkyte@ORA10G> explain plan for
2 select * from partitioned_table where b = 1; Explained.
ops$tkyte@ORA10G> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------

| Operation | Name

| Rows | Pstart | Pstop |

----------------------------------------------------------------------------------

| SELECT STATEMENT | | 1

| | |

| 1

| TABLE ACCESS BY LOCAL INDEX ROWID | PARTITIONED_TABLE | 1 | 1 | 2 |

|

INDEX RANGE SCAN

| LOCAL_NONPREFIXED

| 1

| 1

|

2

|

----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"=1)

在此,优化器不能不考虑LOCAL_NONPREFIXED的PART_2,为了查看是否有 B=1,索引的PART_1和PART_2 都必须检查。在此,局部非前缀索引存在一个性能问题:它不能像前缀索引那样,在谓词中使用分区键。 并不是说前缀索引更好,我们的意思是:要使用非前缀索引,必须使用一个允许分区消除的查询。
ops$tkyte@ORA10G> drop index local_prefixed;
Index dropped.
ops$tkyte@ORA10G> select * from partitioned_table where a = 1 and b = 1; A B DATA
---------- ---------- --------------------
1 1 x
它会成功,但是正如我们所见,这里使用了先前失败的索引。该计划显示出,在此Oracle能利用分区消除 ,

有了谓词A=1,就有了足够的信息可以让数据库消除索引分区PART_2而不予考虑:
ops$tkyte@ORA10G> delete from plan_table;
4 rows deleted.
ops$tkyte@ORA10G> explain plan for
2 select * from partitioned_table where a = 1 and b = 1; Explained.
ops$tkyte@ORA10G> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------

| Operation | Name

| Rows | Pstart | Pstop |

----------------------------------------------------------------------------------

| SELECT STATEMENT | | 1

| | |

| PARTITION RANGE SINGLE | | 1

| 1 | 1 |

| TABLE ACCESS BY LOCAL INDEX ROWID | PARTITIONED_TABLE | 1 | 1 | 1 |

|

INDEX RANGE SCAN

| LOCAL_NONPREFIXED

| 1

| 1

|

1

|

----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"=1)
3 - access("B"=1)

注意PSTART和PSTOP列值为1和1.这就证明,优化器甚至对非前缀局部索引也能执行分区消除。
如果你频繁地用以下查询来查询先前的表:
select ... from partitioned_table where a = :a and b = :b;
select ... from partitioned_table where b = :b;

可以考虑在(b,a)上使用一个局部非前缀索引。这个索引对于前面的两个查询都是有用的。(a,b)上的局
部前缀索引只对第一个查询有用。
这 里的关键是,不必对非前缀索引退避三舍,也不要认为非前缀索引是主要的性能障碍。如果你有 多个如前所列的查询(可以得益于非前缀索引),就应该考虑使用一 个非前缀索引。重点是,要尽可能保 证查询包含的谓词允许索引分区消除。使用前缀局部索引可以保证这一点,使用非前缀索引则不能保证。 还要考虑如何使用索 引。如果将索引用作查询计划中的第一步,那么这两种类型的索引没有多少差别。

2. 局部索引和惟一约束

为了保证惟一性(这包括UNIQUE约束或PRIMARY KEY约束),如果你想使用一个局部索引来保证这个 约束,那么分区键必须包括在约束本身中。在我看来,这是局部索引的最大限制。Oracle只保证索引分区 内部的惟一性,而不能跨分区。这说明什么呢?例如,这意味着不能一方面在一个TIMESTAMP字段上执行 区间分区,而另一方面在ID上有一个主键(使用一个局部分区索引来保证)。Oracle会利用全局索引来保 证惟一性。

在下面的例子中,我们将创建一个区间分区表,它按一个名为LOAD_TYPE的列分区,却在ID列上有 一个主键。为此,可以在一个没有任何其他对象的模式中执行以下CREATE TABLE语句,所以通过查看这个 用户所拥有的每一个段,就能很容易地看出到底创建了哪些对象:
ops$tkyte@ORA10G> CREATE TABLE partitioned

2 ( load_date date,
3 id int,
4 constraint partitioned_pk primary key(id)
5 )
6 PARTITION BY RANGE (load_date)
7 (
8 PARTITION part_1 VALUES LESS THAN
9 ( to_date('01/01/2000','dd/mm/yyyy') ) ,
10 PARTITION part_2 VALUES LESS THAN
11 ( to_date('01/01/2001','dd/mm/yyyy') )
12 )
13 /
Table created.
ops$tkyte@ORA10G> select segment_name, partition_name, segment_type
2 from user_segments;
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE
-------------- --------------- ------------------ PARTITIONED PART_1 TABLE PARTITION
PARTITIONED PART_2 TABLE PARTITION
PARTITIONED_PK INDEX
PARTITIONED_PK索引甚至没有分区,更不用说脚本分区了。而且我们将会看到,它根本无法进行局
部分区。由于认识到非惟一索引也能像惟一索引一样保证主键,我们想以此骗过Oracle,但是可以看到这 种方法也不能奏效:

ops$tkyte@ORA10G> CREATE TABLE partitioned
2 ( timestamp date,
3 id int
4 )
5 PARTITION BY RANGE (timestamp)
6 (
7 PARTITION part_1 VALUES LESS THAN
8 ( to_date('01-jan-2000','dd-mon-yyyy') ) ,
9 PARTITION part_2 VALUES LESS THAN
10 ( to_date('01-jan-2001','dd-mon-yyyy') )
11 )
12 /
Table created.
ops$tkyte@ORA10G> create index partitioned_idx
2 on partitioned(id) local
3 /
Index created.
ops$tkyte@ORA10G> select segment_name, partition_name, segment_type
2 from user_segments;
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE
--------------- --------------- ------------------

PARTITIONED PART_1 TABLE PARTITION PARTITIONED_IDX PART_2 INDEX PARTITION PARTITIONED PART_ 2 TABLE PARTITION PARTITIONED_IDX PART_1 INDEX PARTITION
ops$tkyte@ORA10G> alter table partitioned
2 add constraint
3 partitioned_pk
4 primary key(id)
5 /
alter table partitioned
*
ERROR at line 1:
ORA-01408: such column list already indexed
在此,Oracle试图在 ID 上创建一个全局索引,却发现办不到,这是因为ID上已经存在一个索引。
如果已创建的索引没有分区,前面的语句就能工作,Oracle会使用这个索引来保证约束。
为什么局部分区索引不能保证惟一性(除非分区键是约束的一部分),原因有两方面。首先,如果 Oracle允 许如此,就会丧失分区的大多数好处。可用性和可扩缩性都会丧失殆尽,因为对于任何插入和 更新,总是要求所有分区都一定可用,而且要扫描每一个分区。你的分 区越多,数据就会变得越不可用。 另外,分区越多,要扫描的索引分区就越多,分区也会变得越发不可扩缩。这样做不仅不能提供可用性和 可扩缩性,相反,实际上 反倒会削弱可用性和可扩缩性。
另外,倘若局部分区索引能保证惟一性,Oracle就必须在事务级对这个表的插入和更新有效地串行 化。这是因为,如果向PART_1增加ID=1,Oracle 就必须以某种方式防止其他人向PART_2增加ID=1。对 此惟一的做法是防止别人修改索引分区PART_2,因为无法通过对这个分区中的内容“锁定”来做到(找不 出什么可以锁定)。
在一个OLTP系统中,惟一性约束必须由系统保证(也就是说,由Oracle保证),以确保数据的完整 性。这意味着,应用的逻辑模型会对物理设计产生影响。惟一性约束能决定底层的表分区机制,影响分区 键的选择,或者指示你应该使用全局索引。下面将更深入地介绍全局索引。

13.3.2全局索引

全局索引使用一种有别于底层表的机制进行分区。表可以按一个TIMESTAMP列划分为10个分区,而 这个表上的一个全局索引可以按REGION列划分为5个分区。与局部索引不同,全局索引只有一类,这就是 前缀全局索引(prefixed global index)。如果全局索引的索引键未从该索引的分区键开始,这是不允许 的。这说明,不论用什么属性对索引分区,这些属性都必须是索引键的前几列。

下面继续看前面的例子,这里给出一个使用全局索引的小例子。它显示全局分区索引可以用于保证主 键的惟一性,这样一来,即使不包括表的分区键,也可以有能保证惟一性的分区索引。下面的例子创建了 一个按TIMESTAMP分区的表,它有一个按ID分区的索引:
ops$tkyte@ORA10G> CREATE TABLE partitioned
2 ( timestamp date,
3 id int
4 )
5 PARTITION BY RANGE (timestamp)
6 (
7 PARTITION part_1 VALUES LESS THAN
8 ( to_date('01-jan-2000','dd-mon-yyyy') ) ,
9 PARTITION part_2 VALUES LESS THAN
10 ( to_date('01-jan-2001','dd-mon-yyyy') )
11 )
12 /
Table created.
ops$tkyte@ORA10G> create index partitioned_index
2 on partitioned(id)
3 GLOBAL
4 partition by range(id)

5 (
6 partition part_1 values less than(1000),
7 partition part_2 values less than (MAXVALUE)
8 )
9 /
Index created.
注意,这个索引中使用了MAXVALUE。MAXVALUE可以不仅可以用于索引中,还可以用于任何区间分区
表中。它表示区间的“无限上界”。在此前的所有例子中,我们都使用了区间的硬性上界(小于<某个值> 的值)。不过,全局索引有一个需求,即最高分区(最后一个分区)必须有一个值为 MAXVALUE的分区上界。 这可以确保底层表中的所有行都能放在这个索引中。
下面,在这个例子的最后,我们将向表增加主键:
ops$tkyte@ORA10G> alter table partitioned add constraint
2 partitioned_pk
3 primary key(id)
4 /
Table altered.

从这个代码还不能明显看出Oracle在使用我们创建的索引来保证主键(只有我是“明眼人”,因为我
很清楚Oracle确实在使用这个索引),所以可以试着删除这个索引来证明这一点:
ops$tkyte@ORA10G> drop index partitioned_index;
drop index partitioned_index
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key


为了显示Oracle不允许创建一个非前缀全局索引,只需执行下面的语句:
ops$tkyte@ORA10G> create index partitioned_index2
2 on partitioned(timestamp,id)
3 GLOBAL

4 partition by range(id)
5 (
6 partition part_1 values less than(1000),
7 partition part_2 values less than (MAXVALUE)
8 )
9 /
partition by range(id)
* ERROR at line 4:
ORA-14038: GLOBAL partitioned index must be prefixed
错误信息相当明确。全局索引必须是前缀索引。那么,要在什么时候使用全局索引呢?我们将分析两
种不同类型的系统(数据仓库和OLTP)。来看看何时可以应用全局索引。

1. 数据仓库和全局索引

原先数据仓库和全局索引是相当互斥的。数据仓库就意味着系统有某些性质,如有大量的数据出入。 许多数据仓库都实现了一种滑动窗口(sliding window)方法来管理数据,也就是说,删除表中最旧的分 区,并为新加载的数据增加一个新分区。在过去(Oracle8i及以前的版本),数据仓库系统都避免使用全 局索引,对此有一个很好的原因:全局索引缺乏可用性。大多数分区操作(如删除一个旧分区)都会使全 局索引无效,除非重建全局索引,否则无法使用,这会严重地影响可用性,以前往往都是如此。
滑动窗口和索引 下面的例子实现了一个经典的数据滑动窗口。在许多实现中,会随着时间的推移向仓库中增加数据,
而最旧的数据会老化。在很多时候,这个数据会按一个日期属性进行区间分区,所以最旧的数据多存储在
一个分区中,新加载的数据很可能都存储在一个新分区中。每月的加载过程涉及:
去除老数据:最旧的分区要么被删除,要么与一个空表交换(将最旧的分区变为一个表), 从而允许对旧数据进行归档。
加载新数据并建立索引:将新数据加载到一个“工作”表中,建立索引并进行验证。 关联新数据:一旦加载并处理了新数据,数据所在的表会与分区表中的一个空分区交换,
将表中的这些新加载的数据变成分区表中的一个分区(分区表会变得更大)。
这个过程会没有重复,或者执行加载过程的任何周期重复;可以是每天或每周。我们将在这一节实现 这个非常典型的过程,显示全局分区索引的影响,并展示分区操作期间可以用哪些选项来提高可用性,从 而能实现一个数据滑动窗口,并维持数据的连续可用性。
在这个例子中,我们将处理每年的数据,并加载 2004和2005财政年度的数据。这个表按 TIMESTAMP

列分区,并创建了两个索引,一个是ID列上的局部分区索引,另一个是TIMESTAMP列上的全局索引(这里 为分区):
ops$tkyte@ORA10G> CREATE TABLE partitioned
2 ( timestamp date,
3 id int
4 )
5 PARTITION BY RANGE (timestamp)
6 (
7 PARTITION fy_2004 VALUES LESS THAN
8 ( to_date('01-jan-2005','dd-mon-yyyy') ) ,
9 PARTITION fy_2005 VALUES LESS THAN
10 ( to_date('01-jan-2006','dd-mon-yyyy') )
11 )
12 /
Table created.
ops$tkyte@ORA10G> insert into partitioned partition(fy_2004)
2 select to_date('31-dec-2004',’dd-mon-yyyy’)-mod(rownum,360), object_id
3 from all_objects
4 /
48514 rows created.
ops$tkyte@ORA10G> insert into partitioned partition(fy_2005)
2 select to_date('31-dec-2005',’dd-mon-yyyy’)-mod(rownum,360), object_id

3 from all_objects
4 /
48514 rows created.
ops$tkyte@ORA10G> create index partitioned_idx_local
2 on partitioned(id)
3 LOCAL
4 /
Index created.
ops$tkyte@ORA10G> create index partitioned_idx_global
2 on partitioned(timestamp)
3 GLOBAL
4 /
Index created.
这就建立了我们的“仓库”表。数据按财政年度分区,而且最后两年的数据在线。这个表有两个索引 :
一个是LOCAL索引,另一个是GLOBAL索引。现在正处于年末,我们想做下面的工作:
(1) 删除最旧的财政年度数据。我们不想永远地丢掉这个数据,而只是希望它老化,并将其 归档。
(2) 增加最新的财政年度数据。加载、转换、建索引等工作需要一定的时间。我们想做这个 工作,但是希望尽可能不影响当前数据的可用性。

第一步是为 2004 财政年度建立一个看上去就像分区表的空表。我们将使用这个表与分区表中的 FY_2004分区交换,将这个分区转变成一个表,相应地是分区表中的分区为空。这样做的效果就是分区表 中最旧的数据(实际上)会在交换之后被删除:
ops$tkyte@ORA10G> create table fy_2004 ( timestamp date, id int );
Table created.

ops$tkyte@ORA10G> create index fy_2004_idx on fy_2004(id)
2 /
Index created.
对要加载的新数据做同样的工作。我们将创建并加载一个表,其结构就像是现在的分区表(但是它本
身并不是分区表):
ops$tkyte@ORA10G> create table fy_2006 ( timestamp date, id int );
Table created.
ops$tkyte@ORA10G> insert into fy_2006
2 select to_date('31-dec-2006',’dd-mon-yyyy’)-mod(rownum,360), object_id
3 from all_objects
4 /
48521 rows created.
ops$tkyte@ORA10G> create index fy_2006_idx on fy_2006(id) nologging
2 /
Index created.

我们将当前的满分区变成一个空分区,并创建了一个包含FY_2004数据的“慢”表。而且,我们完成
了使用FY_2006数据的所有必要工作,这包括验证数据、进行转换以及准备这些数据所需完成的所有复杂 任务。

现在可以使用一个交换分区来更新“活动”数据:
ops$tkyte@ORA10G> alter table partitioned
2 exchange partition fy_2004
3 with table fy_2004
4 including indexes
5 without validation

6 /
Table altered.
ops$tkyte@ORA10G> alter table partitioned
2 drop partition fy_2004
3 /
Table altered.
要把旧数据“老化”,所要做的仅此而已。我们将分区变成一个满表,而将空表变成一个分区。这是
一个简单的数据字典更新,瞬时就会完成,而不会发生大量的I/O。现在可以将FY_2004表从数据库中导 出(可能要使用一个可移植的表空间)来实现归档。如果需要,还可以很快地重新关联这些数据。
接下来,我们想“滑入”(即增加)新数据:
ops$tkyte@ORA10G> alter table partitioned
2 add partition fy_2006
3 values less than ( to_date('01-jan-2007','dd-mon-yyyy') )
4 /
Table altered.
ops$tkyte@ORA10G> alter table partitioned
2 exchange partition fy_2006
3 with table fy_2006
4 including indexes
5 without validation
6 /
Table altered.

同样,这个工作也会立即完成;这是通过简单的数据字典更新实现的。增加空分区几乎不需要多少时
间来处理。然后,将新创建的空分区与满表交换(满表与空分区交换),这个操作也会很快完成。新数据是
在线的。
不过,通过查看索引,可以看到下面的结果:
ops$tkyte@ORA10G> select index_name, status from user_indexes;
INDEX_NAME STATUS
------------------------------ -------- FY_2006_IDX VALID FY_2004_IDX VALID
PARTITIONED_IDX_GLOBAL UNUSABLE

当 然,在这个操作之后,全局索引是不可用的。由于每个索引分区可能指向任何表分区,而我们刚

才取走了一个分区,并增加了一个分区,所以这个索引已经无效了。 其中有些条目指向我们已经生成的分 区,却没有任何条目指向刚增加的分区。使用了这个索引的任何查询可能会失败而无法执行,或者如果我 们跳过不可用的索引, 尽管查询能执行,但查询的性能会受到负面影响(因为无法使用这个索引):
ops$tkyte@ORA10G> set autotrace on explain
ops$tkyte@ORA10G> select /*+ index( partitioned PARTITIONED_IDX_GLOBAL ) */ count(*)
2 from partitioned
3 where timestamp between sysdate-50 and sysdate;
select /*+ index( partitioned PARTITIONED_IDX_GLOBAL ) */ count(*)
*
ERROR at line 1:
ORA-01502: index 'OPS$TKYTE.PARTITIONED_IDX_GLOBAL' or partition of such index is in unusable state
ops$tkyte@ORA10G> select count(*)
2 from partitioned
3 where timestamp between sysdate-50 and sysdate;

COUNT(*)
----------
6750
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=59 Card=1 Bytes=9)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 PARTITION RANGE (ITERATOR) (Cost=59 Card=7234 Bytes=65106)
4 3 TABLE ACCESS (FULL) OF 'PARTITIONED' (TABLE) (Cost=59
Card=7234
ops$tkyte@ORA10G> set autotrace off
因此,执行这个分区操作后,对于全局索引,我们有以下选择:
跳过索引,可以像这个例子中一样(Oracle 10g会透明地这样做),在9i中则可以通过设 置会话参数 SKIP_UNUSABLE_INDEXES=TRUE来跳过索引(Oracle 10g将这个设置默认为 TRUE)。 但是这样一来,就丢失了索引所提供的性能提升。
让查询接收到一个错误,就像9i中一样(SKIP_UNUSABLE_INDEX设置为FALSE),在 10g中 , 显式地请求使用提示的任何查询都会接收到错误。要想让数据再次真正可用,必须重建这个索 引。
到 此为止滑动窗口过程几乎不会带来任何停机时间,但是在我们重建全局索引时,需要相当长的时 间才能完成。如果查询依赖于这些索引,在此期间它们的运行时查询 性能就会受到负面影响,可能根本不 会运行,也可能运行时得不到索引提供的好处。所有数据都必须扫描,而且要根据数据重建整个索引。如 果表的大小为数百DB,这会占用相当多的资源。
“活动”全局索引维护
从 Oracle9i 开始,对于分区维护又增加了另一个选项:可以在分区操作期间使用 UPDATE GLOBAL INEXES子句来维护全局索引。这意味着,在你删除一个分区、分解一个分区以及在分区上执行任何必要的 操作时,Oracle会对全局索引执行必要的修改,保证它是最新的。由于大多数分区操作都会导致全局索引 无效,这个特征对于需要提供数据连续访问的系统来说是一个大福音。你会发现,通过牺牲分区操作的速 度(但是原先重建索引后会有一个可观的不可用窗口,即不可用的停机时间相当长),可以换取100%的数 据可用性(尽管分区操作的总体响应时间会更慢)。简单地说,如果数据仓库不允许有停机时间,而且必须

支持数据的滑入滑出等数据仓库技术,这个特性就再合适不过了,但是你必须了解它带来的影响。
再来看前面的例子,如果分区操作在必要时使用了UPDATE GLOBAL INDEXES子句(在这个例子中,在 ADD PARTITION语句上就没有必要使用这个子句,因为新增加的分区中没有任何行):
ops$tkyte@ORA10G> alter table partitioned
2 exchange partition fy_2004
3 with table fy_2004
4 including indexes
5 without validation
6 UPDATE GLOBAL INDEXES
7 /
Table altered.
ops$tkyte@ORA10G> alter table partitioned
2 drop partition fy_2004
3 UPDATE GLOBAL INDEXES
4 /
Table altered.
ops$tkyte@ORA10G> alter table partitioned
2 add partition fy_2006
3 values less than ( to_date('01-jan-2007','dd-mon-yyyy') )
4 /
Table altered.
ops$tkyte@ORA10G> alter table partitioned

2 exchange partition fy_2006
3 with table fy_2006
4 including indexes
5 without validation
6 UPDATE GLOBAL INDEXES
7 /
Table altered.

就会发现索引完全有效,不论在操作期间还是操作之后这个索引都是可用的:
ops$tkyte@ORA10G> select index_name, status from user_indexes;
INDEX_NAME STATUS
------------------------------ -------- FY_2006_IDX VALID FY_2004_IDX VALID PARTITIONED_IDX_GLOBAL VALID
PARTITIONED_IDX_LOCAL N/A
6 rows selected.
ops$tkyte@ORA10G> set autotrace on explain ops$tkyte@ORA10G> select count(*)
2 from partitioned
3 where timestamp between sysdate-50 and sysdate;
COUNT(*)

----------
6750
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9 Card=1 Bytes=9)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 INDEX (RANGE SCAN) OF 'PARTITIONED_IDX_GLOBAL' (INDEX) (Cost=9...
但是这里要做一个权衡:我们要在全局索引结构上执行 INSERT和DELETE操作的相应逻辑操作。删除
一个分区时,必须删除可能指向该分区的所有全局索引条目。执行表与分区的交换时,必须删除指向原数 据的所有全局索引条目,再插入指向刚滑入的数据的新条目。所以ALTER命令执行的工作量会大幅增加。
实际上,通过使用 runstats,并对前面的例子稍加修改,就能测量出分区操作期间维护全局索引所 执行的“额外”工作量。同前面一样,我们将滑出 FY_2004,并滑入 FY_2006,这就必须加入索引重建。由 于需要重建全局索引,因此滑动窗口实现将导致数据变得不可用。然后我们再滑出 FY_2005,并滑入 FY_2007,不过这一次将使用UPDATE GLOBAL INDEXES子 句,来模拟提供完全数据可用性的滑动窗口实现。 这样一来,即使在分区操作期间,数据也是可用的。采用这种方式,我们就能测量出使用不同技术实现相 同操作的 性能,并对它们进行比较。我们期望的结果是,第一种方法占用的数据库资源更少,因此会完成 得“更快”,但是会带来显著的“停机时间”。第二种方法尽管会占 用更多的资源,而且总的来说可能需要 花费更长的时间才能完成,但是不会带来任何停机时间。对最终用户而言,他们的工作脚步永远不会停止。 尽管可能处理起来 会稍微慢一些(因为存在资源竞争),但是他们能一直处理,而且从不停止。

因此,如果用前面的例子,不过另外创建一个类似FY_2004的空FY_2005表,并创建一个类似FY_2006 的满FY_2007表,这样就可以测量索引重建方法之间有什么差别,先来看“不太可用的方法”:
exec runStats_pkg.rs_start;
alter table partitioned exchange partition fy_2004
with table fy_2004 including indexes without validation;
alter table partitioned drop partition fy_2004;
alter table partitioned add partition fy_2006
values less than ( to_date('01-jan-2007','dd-mon-yyyy') );
alter table partitioned exchange partition fy_2006
with table fy_2006 including indexes without validation;
alter index partitioned_idx_global rebuild;
exec runStats_pkg.rs_middle;


下面是可以提供高度可用性的UPDATE GLOBAL INDEXES方法:
alter table partitioned exchange partition fy_2005
with table fy_2005 including indexes without validation update global indexes;
alter table partitioned drop partition fy_2005 update global indexes;
alter table partitioned add partition fy_2007
values less than ( to_date('01-jan-2008','dd-mon-yyyy') );
alter table partitioned exchange partition fy_2007
with table fy_2007 including indexes without validation update global indexes;
exec runStats_pkg.rs_stop;
可以观察到以下结果:
ops$tkyte@ORA10G> exec runStats_pkg.rs_stop;
Run1 ran in 81 hsecs
Run2 ran in 94 hsecs
run 1 ran in 86.17% of the time
Name Run1 Ru n2 Diff
...
STAT...CPU used when call star 39 59
20
...
STAT...redo entries 938 3,340
2,402
STAT...db block gets 1,348 5,441
4,093
STAT...session logical reads 2,178 6,455
4,277
...

LATCH.cache buffers chains

020

5,675

27,695

22,

...

STAT...table scan rows gotten

6

97,711

131,427

33,71

STAT...undo change vector size

35,100

3,404,056

3,368,956

STAT...redo size 2,694,172 6,197,988 3,
503,816

索引重建方法确实运行得更快一些,从观察到的耗用时间和CPU时间可见一斑。正是由于这一点,许
多DBA都会停下来说:“嘿,我不想用UPDATE GLOBAL INDEXES,它会更慢。”不过,这就显得目光太短浅 了。要记住,尽管操作总的来说会花更长的时间,但是系统上的处理不必再中断。确实,作为DBA可能会 更长时间地盯着屏幕,但系统上要完成的真正重要的工作确实一直在进行当中。你要看看这个权衡对你来 说是否有意义。如果你晚上有8小时的维护窗口来加载新数据,就应该尽可能地使用索引重建方法。不过, 如果必须保证连续可用,维护全局索引的能力则至关重要。
查看这种方法生成的redo时,可以看到UPDATE GLOBAL INDEXES生成的redo会多出许多,它是索引 创建方法的230%,而且可以想见,随着为表增加越来越多的全局索引,UPDATE GLOBAL INDEXES生成的redo 数量还会进一步增加。UPDATE GLOBAL INDEXES生成的redo是不可避免的,不能通过NOLOGGING去掉,因 为全局索引的维护不是其结构的完全重建,而应该算是一种增量式“维护”。另外,由于我们维护着活动索 引结构,必须为之生成undo,万一分区操作失败,必须准备好将索引置回到它原来的样子。而且要记住, undo受redo本身的保护,因此你看到的所生成的redo中,有些来自索引更新,有些来自回滚。如果增加 另一个(或两个)全局索引,可以很自然地想见这些数据量会增加。
所以,UPDATE GLOBAL INDEXES是一种允许用资源耗费的增加来换取可用性的选项。如果需要提供连 续的可用性,这就是一个必要的选择。但是,你必须理解相关的问题,并且适当地确定系统中其他组件的 大小。具体地将,许多数据仓库过一段时间都会改为使用大批量的直接路径操作,而绕过undo生成,如果 允许的话,还会绕过redo生成。但是倘若使用UPDATE GLOBAL INDEXES,就不能绕过undo或redo生成。 在使用这个特性之前,需要检查确定组件大小所用的规则,从而确保这种方法在你的系统上确实能正常工 作。

2. OLTP 和全局索引

OLTP系统的特点是会频繁出现许多小的读写事务,一般来讲,在OLTP系统中,首要的是需要快速访 问所需的行,而且数据完整性很关键,另外可用性也非常重要。
在OLTP系统中,许多情况下全局索引很有意义。表数据可以按一个键(一个列键)分区。不过,你 可能需要以多种不同的方式访问数据。例如,可能会按表中的LOCATION来划分EMPLOYEE数据,但是还需 要按以下列快速访问EMPLOYEE数据:
DEPARTMENT:部门的地理位置很分散。部门和位置之间没有任何关系。 EMPLOYEE_ID:尽管员工ID能确定位置,但是你不希望必须按EMPLOYEE_ID和LOCATION搜
索,因为这样一来索引分区上将不能发生分区消除。而且EMPLOYEE_ID本身必然是惟一的。
JOB_TITLE:JOB_TITLE和LOCATION之间没有任何关系。任何LOCATION上都可以出现所有 JOB_TITLE值。
这里需要按多种不同的键来访问应用中不同位置的EMPLOYEE数据,而且速度至上。在一个数据仓库 中,可以只使用这些键上的局部分区索引,并使用并行索引区间扫描来快速收集大量数据。在这些情况下 不必使用索引分区消除。不过,在OLTP系统中则不同,确实需要使用分区消除,并发查询对这些系统不合 适;我们要适当地提供索引。因此,需要利用某些字段上的全局索引。
我们要满足以下目标: 快速访问 数据完整性 可用性
在一个OLTP系统中,可以通过全局索引实现这些目标。我们可能不实现滑动窗口,而且暂时不考虑 审计。我们并不分解分区(除非有一个预定的停机时间),也不会移动数据,等等。对于数据仓库中执行的 操作,一般来说不会在活动的OLTP系统中执行它们。

以下是一个小例子,显示了如何用全局索引来达到以上所列的3个目标。这里使用简单的“单分区” 全局索引,但是这与多个分区情况下的全局索引也没有不同(只有一点除外,增加索引分区时,可用性和 可管理性会提高)。先创建一个表,它按位置LOC执行区间分区,根据我们的规则,这会把所有小于‘C’ 的LOC值放在分区P1中,小于’D‘的LOC值则放在分区P2中,依此类推:
ops$tkyte@ORA10G> create table emp
2 (EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7,2),
8 COMM NUMBER(7,2),
9 DEPTNO NUMBER(2) NOT NULL,
10 LOC VARCHAR2(13) NOT NULL
11 )
12 partition by range(loc)
13 (
14 partition p1 values less than('C') tablespace p1,
15 partition p2 values less than('D') tablespace p2,
16 partition p3 values less than('N') tablespace p3,
17 partition p4 values less than('Z') tablespace p4
18 )
19 /

Table created.
接下来修改这个表,在主键列上增加一个约束:
ops$tkyte@ORA10G> alter table emp add constraint emp_pk
2 primary key(empno)
3 /
Table altered.

这有一个副作用,EMPNO列上将有一个惟一索引。由此显示出,完全可以支持和保证数据完整性,这
这是我们的目标之一。最后,在DEPTNO和JOB上创建另外两个全局索引,以便通过这些属性快速地访问记 录:
ops$tkyte@ORA10G> create index emp_job_idx on emp(job)
2 GLOBAL
3 /
Index created.
ops$tkyte@ORA10G> create index emp_dept_idx on emp(deptno)
2 GLOBAL
3 /
Index created.
ops$tkyte@ORA10G> insert into emp
2 select e.*, d.loc
3 from scott.emp e, scott.dept d
4 where e.deptno = d.deptno
5 /
14 rows created.

现在来看每个分区中有什么:

ops$tkyte@ORA10G> break on pname skip 1
ops$tkyte@ORA10G> select 'p1' pname, empno, job, loc from emp partition(p1)
2 union all
3 select 'p2' pname, empno, job, loc from emp partition(p2)
4 union all
5 select 'p3' pname, empno, job, loc from emp partition(p3)
6 union all
7 select 'p4' pname, empno, job, loc from emp partition(p4)
8 /
PN EMPNO JOB LOC
-- ---------- --------- ------------- p2 7499 SALESMAN CHICAGO
7698 MANAGER CHICAGO
7654 SALESMAN CHICAGO
7900 CLERK CHICAGO
7844 SALESMAN CHICAGO
7521 SALESMAN CHICAGO
p3 7369 CLERK DALLAS
7876 CLERK DALLAS
7902 ANALYST DALLAS
7788 ANALYST DALLAS
7566 MANAGER DALLAS
p4 7782 MANAGER NEW YORK

7839 PRESIDENT NEW YORK
7934 CLERK NEW YORK
14 rows selected.

这显示了数据按位置在各个分区中的分布。现在可以检查一些查询计划,来查看会有怎样的性能:
ops$tkyte@ORA10G> variable x varchar2(30);
ops$tkyte@ORA10G> begin
2 dbms_stats.set_table_stats
3 ( user, 'EMP', numrows=>100000, numblks => 10000 );
4 end;
5 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA10G> delete from plan_table;
3 rows deleted.
ops$tkyte@ORA10G> explain plan for
2 select empno, job, loc from emp where empno = :x; Explained.
ops$tkyte@ORA10G> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------

| Operation | Name |Rows |Byt es |Pstart |Pstop |

------------------------------------------------------------------------

| SELECT STATEMENT | | 1 | 27

| | |

| TABLE ACCESS BY GLOBAL INDEX ROWID | EMP | 1 | 27 |ROWID |ROWID |

| INDEX UNIQUE SCAN | EMP_PK | 1 | |

| |

------------------------------------------------------------------------ Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=TO_NUMBER(:X))

注意 这里对解释计划格式进行了编辑,使之适合一页的篇幅。报告中与讨论无关的列都被忽略了。

这里的计划显示出对未分区索引EMP_PK(为支持主键所创建)有一个INDEX UNIQUE SCAN。然后还有 一个 TABLE ACCESS GLOBAL INDEX ROWID,其 PSTART和 PSTOP为 ROWID/ROWID,这说明从索引得到ROWID 时,它会准确地告诉我们读哪个索引分区来得到这一行。这个索引访问与未分区表上的访问同样有效,而 且为此会执行同样数量的I/O。这只是一个简单的单索引惟一扫描,其后是“根据ROWID来得到这一行”。 现在,我们来看一个全局索引,即JOB上的全局索引:
ops$tkyte@ORA10G> delete from plan_table;
3 rows deleted.
ops$tkyte@ORA10G> explain plan for
2 select empno, job, loc from emp where job = :x; Explained.
ops$tkyte@ORA10G> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------

| Operation |Name |Rows |Byt es |Pstart |Pstop |

---------------------------------------------------------------------------

| SELECT STATEMENT | | 1000 |27000 |

| |

| TABLE ACCESS BY GLOBAL INDEX ROWID |EMP | 1000 |27000 |ROWID |ROWID |

| INDEX RANGE SCAN |EMP_JOB_IDX | 400 |

| | |

--------------------------------------------------------------------------- Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JOB"=:X)

当然,对于INDEX RANGE SCAN,可以看到类似的结果。在此使用了我们的索引,而且可以对底层数
据提供高速的OLTP访问。如果索引进行了分区,则必须是前缀索引,并保证索引分区消除;因此,这些索 引也是可扩缩的,这说明我们可以对其分区,而且能观察到类似的行为。稍后我们将看到只使用LOCAL 索 引时会发生什么。

最后,下面来看可用性方面。Oracle文档指出,与局部分区索引相比,全局分区索引更有利于“不 那么可用”的数据。我不完全同意这种以一概全的说法。我认为,在OLTP系统中,全局分区索引与局部分 区索引有着同样的高度可用性。考虑以下例子:
ops$tkyte@ORA10G> alter tablespace p1 offline;
Tablespace altered.
ops$tkyte@ORA10G> alter tablespace p2 offline; Tablespace altered.
ops$tkyte@ORA10G> alter tablespace p3 offline; Tablespace altered.
ops$tkyte@ORA10G> select empno, job, loc from emp where empno = 7782; EMPNO JOB LOC
---------- --------- -------------

7782 MANAGER NEW YORK
在此,即使表中大多数底层数据都不可用,还是可以通过索引访问任何可用的数据。只要我们想要的
EMPNO在可用的表空间中,而且GLOBAL索引可用,就可以利用GLOBAL索引来访问数据。另一方面,如果 一直使用在前面“高度可用”的局部索引,倒有可能不允许访问数据!这是因为我们在LOC上分区而需要 按EMPNO查询,所以会导致这样一个副作用。我们必须探查每一个局部索引分区,而而遭遇到不可用的索 引分区时就会失败。
不过,在这种情况下,其他类型的查询不会(而且不能)工作:
ops$tkyte@ORA10G> select empno, job, loc from emp where job = 'CLERK';
select empno, job, loc from emp where job = 'CLERK'
* ERROR at line 1:
ORA-00376: file 13 cannot be read at this time
ORA-01110: data file 13: '/home/ora10g/oradata/.../o1_mf_p2_1dzn8jwp_.dbf'

所有分区中都有CLERK数据,由于3个表空间离线,这一点确实会对我们带来影响。这是不可避免的 ,
除非我们在JOB上分区,但是这样一来,就会像按LOC分区查询数据一样出现同样的问题。需要由多个不 同的“键”来访问数据时就会有这个问题。Oracle会“尽其所能”地为你提供数据。
不过,要注意,如果可以由索引来回答查询,就要避免 TABLE ACCESS BY ROWID,数据不可用的事实 并不重要:
ops$tkyte@ORA10G> select count(*) from emp where job = 'CLERK';
COUNT(*)
----------
4

在这种情况下,由于 Oracle并不需要表,大多数分区离线的事实也不会影响这个查询。由于OLTP
系统中这种优化(即只使用索引来回答查询)很常见,所以很多应用都不会因为数据离线而受到影响。现 在所要做的只是尽快地让离线数据可用(将其恢复)。