oracel分区之索引分区

最新推荐文章于 2022-03-11 10:31:52 发布

cnje5551

最新推荐文章于 2022-03-11 10:31:52 发布

阅读量138

点赞数

文章标签：数据库

索引分区

索引与表类似，也可以分区。对索引进行分区有两种可能的方法：

随表对索引完成相应的分区：这也称为局部(本地)分区索引（locally pertitioned index）。每个表分区都有一个索引分区，而且只索引该表分区。一个给定索引分区中的所有条目都指向一个表分区，表分区中的所有行都表示在一个索引分区中。

按区间对索引分区：这也称为全局分区索引（globally partitioned index）。在此，索引按区间分区（或者在Oracle 10g中该可以按散列分区），一个索引分区可能指向任何（和所有）表分区。

对于全局分区索引，要注意实际上索引分区数可能不同于表分区数。

由于全局索引只按区间或散列分区，如果希望有一个列表或组合分区索引，就必须使用局部索引。局部索引会使用底层表相同的机制分区。

注意全局索引的散列分区是Oracle 10g Release 1及以后的版本中才有的新特性。在Oracle9i及以前的版本中，只能按区间进行全局分区。

局部索引

Oracle划分了以下两类局部索引：

局部前缀索引（local prefixed index）：在这些索引中，分区键在索引定义的前几列上。例如，一个表在名为LOAD_DATE的列上进行区间分区，该表上的局部前缀索引就是LOAD_DATE作为其索引列列表中的第一列。

局部非前缀索引（local nonprefixed index）：这些索引不以分区键作为其列列表的前几列。索引可能包含分区键列，也可能不包含。

这两类索引都可以利用分区消除，它们都支持惟一性（只有前缀索引包含分区键）等。事实上，使用局部前缀索引的查询总允许索引分区消除，而使用局部非前缀索引的查询可能不允许。正是由于这个原因，所以在某些人看来局部非前缀索引“更慢”，它们不能保证分区消除（但确实可以支持分区消除）。

如果查询中将索引用作访问表的初始路径，那么从本质来讲，局部前缀索引并不比局部非前缀索引更好。也就是说，如何查询把 “扫描一个索引”作为第一步，那么前缀索引和非前缀索引之间并没有太大的差别。

分区消除行为

如果查询首先访问索引，它是否能消除分区完全取决于查询中的谓词。下面的代码创建了一个表PARTITIONED_TABLE，它在一个数字列A上进行区间分区，使得小于2的值都在分区PART_1中，小于3的值则都在分区PART_2中：

sys@JINGYONG> create table partitioned_table

2 (a int,

3 b int,

4 data varchar2(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 ;

表已创建。

然后我们创建一个局部前缀索引LOCAL_PREFIXED和一个局部非前缀索引LOCAL_NONPREFIXED。注意，非前缀索引在其定义中没有以A作为其最前列，这是这一点使之成为一个非前缀索引：

sys@JINGYONG> create index local_prefixed on partitioned_table(a,b) local;

索引已创建。

sys@JINGYONG> create index local_nonprefixed on partitioned_table(b) local;

索引已创建。

接下来，我们向一个分区中插入一些数据，并收集统计信息：

sys@JINGYONG> insert into partitioned_table

2 select mod(rownum-1,2)+1,rownum,'x' from all_objects;

已创建50324行。

sys@JINGYONG> commit;

提交完成。

sys@JINGYONG> exec dbms_stats.gather_table_stats(user,'PARTITIONED_TABLE',estima

te_percent=>100,method_opt=>'for all columns size repeat',cascade=>true);

PL/SQL 过程已成功完成。

将表空间P2脱机，其中包含用于表和索引的PART_2分区：

sys@JINGYONG> alter tablespace p2 offline;

表空间已更改。

表空间P2脱机后，Oracle就无法访问这些特定的索引分区。这就好像是我们遭遇了“介质故障”，导致分区不可用。现在我们查询这个表，来看看不同的查询需要哪些索引分区。第一个查询编写为允许使用局部前缀索引：

sys@JINGYONG> select * from partitioned_table where a=1 and b=1;

A B DATA

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

1 1 x

这个查询成功了，通过查看解释计划，可以看到这个查询为什么能成功。我们将使用内置包DBMS_XPLAN来查看这个查询访问了哪些分区。输出中的PSTART (分区开始)和PSTOP（分区结束）这两列准确地显示出，这个查询要想成功需要哪些分区必须在线而且可用：

sys@JINGYONG> delete from plan_table;

已删除0行。

sys@JINGYONG> explain plan for

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

已解释。

sys@JINGYONG> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 1622054381

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

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

| 0 | SELECT STATEMENT | | 1 | 9 | 2 (0)| 00:00:01 | | |

| 1 | PARTITION RANGE SINGLE | | 1 | 9 | 2 (0)| 00:00:01 | 1 | 1 |

| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE | 1 | 9 | 2 (0)| 00:00:01 | 1 | 1 |

|* 3 | INDEX RANGE SCAN | LOCAL_PREFIXED | 1 | | 1 (0)| 00:00:01 | 1 | 1 |

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

Predicate Information (identified by operation id):

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

3 - access("A"=1 AND "B"=1)

已选择15行。

因此，使用LOCAL_PREFIXED的查询成功了。优化器能消除LOCAL_PREFIXED的PART_2不予考虑，因为我们在查询中指定了A=1，而且在计划中可以清楚地看到PSTART和PSTOP都等于1.分区消除帮助了我们。不过，第二个查询却失败了

sys@JINGYONG> select * from partitioned_table where b=1;

ERROR:

ORA-00376: file 7 cannot be read at this time

ORA-01110: data file 7: '/u01/app/oracle/product/10.2.0/oradata/jingyong/jingyon

g/p201.dbf'

我们可以通过查看这个查询的执行计划看到为什么会失败

sys@JINGYONG> delete from plan_table;

已删除4行。

sys@JINGYONG> explain plan for

2 select * from partitioned_table where b=1;

已解释。

sys@JINGYONG> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 440752652

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

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

| 0 | SELECT STATEMENT | | 1 | 9 | 4 (0)| 00:00:01 | | |

| 1 | PARTITION RANGE ALL | | 1 | 9 | 4 (0)| 00:00:01 | 1 | 2 |

| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE | 1 | 9 | 4 (0)| 00:00:01 | 1 | 2 |

|* 3 | INDEX RANGE SCAN | LOCAL_NONPREFIXED | 1 | | 3 (0)| 00:00:01 | 1 | 2 |

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

Predicate Information (identified by operation id):

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

3 - access("B"=1)

已选择15行。

在此，优化器不能不考虑LOCAL_NONPREFIXED的PART_2，为了查看是否有B=1，索引的PART_1和PART_2都必须检查。在此，局部非前缀索引存在一个性能问题：它不能像前缀索引那样，在谓词中使用分区键。并不是说前缀索引更好，我们的意思是：要使用非前缀索引，必须使用一个允许分区消除的查询

sys@JINGYONG> drop index local_prefixed;

索引已删除。

sys@JINGYONG> select * from partitioned_table where a=1 and b=1;

A B DATA

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

1 1 x

它会成功，但是正如我们所见，这里使用了先前失败的索引。该计划显示出，在此Oracle能利用分区消除，有了谓词A=1，就有了足够的信息可以让数据库消除索引分区PART_2而不予考虑：

sys@JINGYONG> delete from plan_table;

已删除4行。

sys@JINGYONG> explain plan for

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

已解释。

sys@JINGYONG> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 904532382

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

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

| 0 | SELECT STATEMENT | | 1 | 9 | 2 (0)| 00:00:01 | | |

| 1 | PARTITION RANGE SINGLE | | 1 | 9 | 2 (0)| 00:00:01 | 1 | 1 |

|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE | 1 | 9 | 2 (0)| 00:00:01 | 1 | 1 |

|* 3 | INDEX RANGE SCAN | LOCAL_NONPREFIXED | 1 | | 1 (0)| 00:00:01 | 1 | 1 |

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

Predicate Information (identified by operation id):

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

2 - filter("A"=1)

3 - access("B"=1)

已选择16行。

注意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）上的局部前缀索引只对第一个查询有用。

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

局部索引和惟一约束

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

在下面的例子中，我们将创建一个区间分区表，它按一个名为LOAD_DATE的列分区，却在ID列上有一个主键。通过查看这个分表的每一个段，就能很容易地看出到底创建了哪些对象：

sys@JINGYONG> 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(to_date('2000-01-01','yyyy-mm-dd')),

9 partition part_2 values less than(to_date('2001-01-01','yyyy-mm-dd'))

10 );

表已创建。

sys@JINGYONG> select segment_name,partition_name,segment_type

2 from user_segments where segment_name like'PARTITIONED%';

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE

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

PARTITIONED PART_1 TABLE PARTITION

PARTITIONED PART_2 TABLE PARTITION

PARTITIONED_PK INDEX

PARTITIONED_PK索引没有分区.而且我们将会看到，它根本无法进行局部分区。由于认识到非惟一索引也能像惟一索引一样保证主键，我们想以此骗过Oracle，但是可以看到这种方法也不能奏效：

sys@JINGYONG> 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('2000-01-01','yyyy-mm-dd')),

9 partition part_2 values less than

10 (to_date('2001-01-01','yyyy-mm-dd'))

11 );

表已创建。

sys@JINGYONG> create index partitioned_idx on partitioned(id) local;

索引已创建。

sys@JINGYONG> select segment_name,partition_name,segment_type

2 from user_segments where segment_name like'PARTITIONED%';

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE

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

PARTITIONED PART_1 TABLE PARTITION

PARTITIONED PART_2 TABLE PARTITION

PARTITIONED_IDX PART_2 INDEX PARTITION

PARTITIONED_IDX PART_1 INDEX PARTITION

sys@JINGYONG> alter table partitioned

2 add constraint partitioned_pk primary key(id);

alter table partitioned

第 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保证），以确保数据的完整性。这意味着，应用的逻辑模型会对物理设计产生影响。惟一性约束能决定底层的表分区机制，影响分区键的选择，或者指示你应该使用全局索引。下面将更深入地了解全局索引。

全局索引

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

下面继续看前面的例子，这里给出一个使用全局索引的小例子。它显示全局分区索引可以用于保证主键的惟一性，这样一来，即使不包括表的分区键，也能保证惟一性的分区索引。下面的例子创建了一个按TIMESTAMP分区的表，它有一个按ID分区的索引：

sys@JINGYONG> create table partitioned

2 (timestamp date,

3 id int

4 )

5 partition by range(timestamp)

6 (

7 partition part_1 values less than(to_date('2000-01-01','yyyy-mm-dd')),

8 partition part_2 values less than(to_date('2001-01-01','yyyy-mm-dd'))

9 );

表已创建。

sys@JINGYONG> create index partitioned_index on partitioned(id) global

2 partition by range(id)

3 (

4 partition part_1 values less than(1000),

5 partition part_2 values less than(maxvalue)

6 );

索引已创建。

注意，这个索引中使用了MAXVALUE。MAXVALUE不仅可以用于索引中，还可以用于任何区间分区表中。它表示区间的“无限上界” 。在此前的所有例子中，我们都使用了区间的硬性上界（小于的值）。不过，全局索引有一个需求，即最高分区（最后一个分区）必须有一个值为MAXVALUE的分区上界。这可以确保底层表中的所有行都能放在这个索引中。

下面，在这个例子的最后，我们将向表增加主键：

sys@JINGYONG> alter table partitioned add constraint

2 partitioned_pk primary key(id);

表已更改。

从这个代码还不能明显看出Oracle在使用我们创建的索引来保证主键,所以可以试着删除这个索引来证明这一点：

sys@JINGYONG> drop index partitioned_index;

drop index partitioned_index

第 1 行出现错误:

ORA-02429: cannot drop index used for enforcement of unique/primary key

为了显示Oracle不允许创建一个非前缀全局索引，只需执行下面的语句：

sys@JINGYONG> create index partitioned_index2 on partitioned(timestamp,id)

2 global

3 partition by range(id)

4 (

5 partition part_1 values less than(1000),

6 partition part_2 values less than(maxvalue)

7 );

partition by range(id)

第 3 行出现错误:

ORA-14038: GLOBAL partitioned index must be prefixed

为了创建一个全局索引那么索引的分区键必须是全局索引中的前几列才行如下:

sys@JINGYONG> create index partitioned_index2 on partitioned(id,timestamp)

2 global

3 partition by range(id)

4 (

5 partition part_1 values less than(1000),

6 partition part_2 values less than(maxvalue)

7 );

索引已创建。

错误信息相当明确。全局索引必须是前缀索引。那么，要在什么时候使用全局索引呢？我们将分析两种不同类型的系统（数据仓库和OLTP）。来看看何时可以应用全局索引

数据仓库和全局索引

原先数据仓库和全局索引是相当互斥的。数据仓库就意味着系统有某些性质，如有大量的数据出入。许多数据仓库都实现了一种滑动窗口（sliding window）方法来管理数据，也就是说，删除表中最旧的分区，并为新加载的数据增加一个新分区。在过去（ Oracle8i及以前的版本），数据仓库系统都避免使用全局索引，对此有一个很好的原因：全局索引缺乏可用性。大多数分区操作（如删除一个旧分区）都会使全局索引无效，除非重建全局索引，否则无法使用，这会严重地影响可用性，以前往往都是如此。

滑动窗口和索引

下面的例子实现了一个经典的数据滑动窗口。在许多实现中，会随着时间的推移向仓库中增加数据，而最旧的数据会老化。在很多时候，这个数据会按一个日期属性进行区间分区，所以最旧的数据多存储在一个分区中，新加载的数据很可能都存储在一个新分区中。每月的加载过程涉及：

去除老数据：最旧的分区要么被删除，要么与一个空表交换（将最旧的分区变为一个表），从而允许对旧数据进行归档。

加载新数据并建立索引：将新数据加载到一个“工作”表中，建立索引并进行验证。

关联新数据：一旦加载并处理了新数据，数据所在的表会与分区表中的一个空分区交换，将表中的这些新加载的数据变成分区表中的一个分区（分区表会变得更大）。

这个过程会没有重复，或者执行加载过程的任何周期重复；可以是每天或每周。我们将在这一节实现这个非常典型的过程，显示全局分区索引的影响，并展示分区操作期间可以用哪些选项来提高可用性，从而能实现一个数据滑动窗口，并维持数据的连续可用性。

在这个例子中，我们将处理每年的数据，并加载2004和2005财政年度的数据。这个表按TIMESTAMP列分区，并创建了两个索引，一个是ID列上的局部分区索引，另一个是TIMESTAMP列上的全局索引（这里为分区）：

sys@JINGYONG> create table partitioned

2 (timestamp date,

3 id int

4 )

5 partition by range(timestamp)

6 (

7 partition fy_2004 values less than(to_date('2005-01-01','yyyy-mm-dd')),

8 partition fy_2005 values less than(to_date('2006-01-01','yyyy-mm-dd'))

9 );

表已创建。

sys@JINGYONG> insert into partitioned partition(fy_2004)

2 select to_date('2004-12-31','yyyy-mm-dd')-mod(rownum,360),object_id

3 from all_objects;

已创建50318行。

sys@JINGYONG> insert into partitioned partition(fy_2005)

2 select to_date('2005-12-31','yyyy-mm-dd')-mod(rownum,360),object_id

3 from all_objects;

已创建50318行。

sys@JINGYONG> create index partitioned_idx_local on partitioned(id) local;

索引已创建。

sys@JINGYONG> create index partitioned_idx_global

2 on partitioned(timestamp) global;

索引已创建。

这就建立了我们的“仓库”表。数据按财政年度分区，而且最后两年的数据在线。这个表有两个索引：一个是LOCAL索引，另一个是GLOBAL索引。现在正处于年末，我们想做下面的工作：

（1）删除最旧的财政年度数据。我们不想永远地丢掉这个数据，而只是希望它老化，并将其归档。

（2）增加最新的财政年度数据。加载、转换、建索引等工作需要一定的时间。我们想做这个工作，但是希望尽可能不影响当前数据的可用性。

第一步是为2004财政年度建立一个看上去就像分区表的空表。我们将使用这个表与分区表中的FY_2004分区交换，将这个分区转变成一个表，相应地使分区表中的分区为空。这样做的效果就是分区表中最旧的数据（实际上）会在交换之后被删除：

sys@JINGYONG> create table fy_2004(timestamp date,id int);

表已创建。

sys@JINGYONG> create index fy_2004_idx on fy_2004(id);

索引已创建。

对要加载的新数据做同样的工作。我们将创建并加载一个表，其结构就像是现在的分区表（但是它本身并不是分区表）：

sys@JINGYONG> create table fy_2006(timestamp date,id int);

表已创建。

sys@JINGYONG> insert into fy_2006

2 select to_date('2006-12-31','yyyy-mm-dd')-mod(rownum,360),object_id

3 from all_objects;

已创建50325行。

sys@JINGYONG> create index fy_2006_idx on fy_2006(id) nologging;

索引已创建。

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

现在可以使用一个交换分区来更新“活动”数据：

sys@JINGYONG> alter table partitioned

2 exchange partition fy_2004

3 with table fy_2004

4 including indexes

5 without validation;

表已更改。

sys@JINGYONG> alter table partitioned drop partition fy_2004;

表已更改。

要把旧数据“老化”，所要做的仅此而已。我们将分区变成一个满表，而将空表变成一个分区。这是一个简单的数据字典更新，瞬时就会完成，而不会发生大量的I/O。现在可以将FY_2004表从数据库中导出（可能要使用一个可移植的表空间）来实现归档。如果需要，还可以很快地重新关联这些数据。

接下来，我们想“滑入”（即增加）新数据：

sys@JINGYONG> alter table partitioned

2 add partition fy_2006

3 values less than(to_date('2007-01-01','yyyy-mm-dd'));

表已更改。

sys@JINGYONG> alter table partitioned

2 exchange partition fy_2006

3 with table fy_2006

4 including indexes

5 without validation;

表已更改。

同样，这个工作也会立即完成；这是通过简单的数据字典更新实现的。增加空分区几乎不需要多少时间来处理。然后，将新创建的空分区与满表交换（满表与空分区交换），这个操作也会很快完成。新数据是在线的。

不过，通过查看索引，可以看到下面的结果：

sys@JINGYONG> select index_name,status from user_indexes where table_name='PARTI

TIONED';

INDEX_NAME STATUS

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

PARTITIONED_IDX_LOCAL N/A

PARTITIONED_IDX_GLOBAL UNUSABLE

sys@JINGYONG> select index_name,status from user_indexes where index_name like'F

Y%';

INDEX_NAME STATUS

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

FY_2004_IDX VALID

FY_2006_IDX VALID

当然，在这个操作之后，全局索引是不可用的。由于每个索引分区可能指向任何表分区，而我们刚才取走了一个分区，并增加了一个分区，所以这个索引已经无效了。其中有些条目指向我们已经生成的分区，却没有任何条目指向刚增加的分区。使用了这个索引的任何查询可能会失败而无法执行，或者如果我们跳过不可用的索引，尽管查询能执行，但查询的性能会受到负面影响（因为无法使用这个索引）：

sys@JINGYONG> set autotrace on explain

sys@JINGYONG> 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(*)

第 1 行出现错误:

ORA-01502: index 'SYS.PARTITIONED_IDX_GLOBAL' or partition of such index is in u

nusable state

sys@JINGYONG> select count(*)

2 from partitioned

3 where timestamp between sysdate-50 and sysdate;

COUNT(*)

----------

执行计划

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

Plan hash value: 2869581836

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

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

| 0 | SELECT STATEMENT | | 1 | 9 | 63 (12)|00:00:01 | | |

| 1 | SORT AGGREGATE | | 1 | 9 | | | | |

|* 2 | FILTER | | | | | | | |

| 3 | PARTITION RANGE ITERATOR| | 3 | 27 | 63 (12)|00:00:01 | KEY | KEY |

|* 4 | TABLE ACCESS FULL | PARTITIONED | 3 | 27 | 63 (12)|00:00:01 | KEY | KEY |

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

Predicate Information (identified by operation id):

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

2 - filter(SYSDATE@!-50<=SYSDATE@!)

4 - filter("TIMESTAMP"<=SYSDATE@! AND "TIMESTAMP">=SYSDATE@!-50)

Note

-----

- dynamic sampling used for this statement

因此，执行这个分区操作后，对于全局索引，我们有以下选择：

跳过索引，可以像这个例子中一样（Oracle 10g会透明地这样做），在9i中则可以通过设置会话参数 SKIP_UNUSABLE_INDEXES=TRUE来跳过索引（Oracle 10g将这个设置默认为TRUE）。但是这样一来，就丢失了索引所提供的性能提升。让查询接收到一个错误，就像9i中一样（SKIP_UNUSABLE_INDEX设置为FALSE），在10g中，显式地请求使用提示的任何查询都会接收到错误。要想让数据再次真正可用，必须重建这个索引。

到此为止滑动窗口过程几乎不会带来任何停机时间，但是在我们重建全局索引时，需要相当长的时间才能完成。如果查询依赖于这些索引，在此期间它们的运行时查询性能就会受到负面影响，可能根本不会运行，也可能运行时得不到索引提供的好处。所有数据都必须扫描，而且要根据数据重建整个索引。如果表的大小为数百GB，这会占用相当多的资源。

“活动”全局索引维护

从Oracle9i开始，对于分区维护又增加了另一个选项：可以在分区操作期间使用UPDATE GLOBAL INEXES子句来维护全局索引。这意味着，在你删除一个分区、分解一个分区以及在分区上执行任何必要的操作时，Oracle会对全局索引执行必要的修改，保证它是最新的。由于大多数分区操作都会导致全局索引无效，这个特征对于需要提供数据连续访问的系统来说是一个大福音。你会发现，通过牺牲分区操作的速度（但是原先重建索引后会有一个可观的不可用窗口，即不可用的停机时间相当长），可以换取100% 的数据可用性（尽管分区操作的总体响应时间会更慢）。简单地说，如果数据仓库不允许有停机时间，而且必须支持数据的滑入滑出等数据仓库技术，这个特性就再合适不过了，但是你必须了解它带来的影响。

再来看前面的例子，如果分区操作在必要时使用了UPDATE GLOBAL INDEXES子句（在这个例子中，在ADD PARTITION语句上就没有必要使用这个子句，因为新增加的分区中没有任何行）：

sys@JINGYONG> 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.

sys@JINGYONG> alter table partitioned

2 drop partition fy_2004

3 UPDATE GLOBAL INDEXES

4 /

Table altered.

sys@JINGYONG> alter table partitioned

2 add partition fy_2006

3 values less than ( to_date('01-jan-2007','dd-mon-yyyy') )

4 /

Table altered.

sys@JINGYONG> 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.

就会发现索引完全有效，不论在操作期间还是操作之后这个索引都是可用的：

sys@JINGYONG> 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

4 rows selected.

sys@JINGYONG> set autotrace on explain

sys@JINGYONG> select count(*)

2 from partitioned

3 where timestamp between sysdate-50 and sysdate;

COUNT(*)

----------

6750

Execution Plan

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

0 SELECT STATEMENT ptimizer=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命令执行的工作量会大幅增加。

索引重建方法比使用UPDATE GLOBAL INDEXES子句来更新全局分区索引确实运行得更快一些,所以，UPDATE GLOBAL INDEXES是一种允许用资源耗费的增加来换取可用性的选项。如果需要提供连续的可用性，这就是一个必要的选择。但是，你必须理解相关的问题，并且适当地确定系统中其他组件的大小。具体地将，许多数据仓库过一段时间都会改为使用大批量的直接路径操作，而绕过undo生成，如果允许的话，还会绕过redo生成。但是倘若使用UPDATE GLOBAL INDEXES,就不能绕过undo或redo生成。在使用这个特性之前，需要检查确定组件大小所用的规则，从而确保这种方法在你的系统上确实能正常工作。

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中，依此类推：

sys@JINGYONG> 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 deptno number(2) not null,

9 loc varchar2(13) not null

10 )

11 partition by range(loc)

12 (

13 partition p1 values less than('C') tablespace p1,

14 partition p2 values less than('D') tablespace p1,

15 partition p3 values less than('N') tablespace p2,

16 partition p4 values less than('Z') tablespace p2

17 )

18 ;

表已创建。

接下来修改这个表，在主键列上增加一个约束：

sys@JINGYONG> alter table emp add constraint emp_pk primary key(empno);

表已更改。

这有一个副作用，EMPNO列上将有一个惟一索引。由此显示出，完全可以支持和保证数据完整性，这是我们的目标之一。最后，在DEPTNO和JOB上创建另外两个全局索引，以便通过这些属性快速地访问记录：

sys@JINGYONG> create index emp_job_idx on emp(job) global;

索引已创建。

sys@JINGYONG> create index emp_dept_idx on emp(deptno) global;

索引已创建。

sys@JINGYONG> insert into emp

2 select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.deptno,d.loc

3 from scott.emp e,scott.dept d

4 where e.deptno=d.deptno;

已创建14行。

sys@JINGYONG> commit;

提交完成。

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

sys@JINGYONG> break on pname skip 1

sys@JINGYONG> 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

7521 SALESMAN CHICAGO

7654 SALESMAN CHICAGO

7698 MANAGER CHICAGO

7844 SALESMAN CHICAGO

7900 CLERK CHICAGO

p3 7369 CLERK DALLAS

7566 MANAGER DALLAS

7788 ANALYST DALLAS

7876 CLERK DALLAS

7902 ANALYST DALLAS

p4 7782 MANAGER NEW YORK

7839 PRESIDENT NEW YORK

7934 CLERK NEW YORK

已选择14行。

这显示了数据按位置在各个分区中的分布。现在可以检查一些查询计划，来查看会有怎样的性能：

sys@JINGYONG> variable x varchar2(30);

sys@JINGYONG> begin

2 dbms_stats.set_table_stats

3 (user,'EMP',numrows=>100000,numblks=>10000);

4 end;

5 /

PL/SQL 过程已成功完成。

sys@JINGYONG> delete from plan_table;

已删除0行。

sys@JINGYONG> explain plan for

2 select empno,job,loc from emp where empno=:x;

已解释。

sys@JINGYONG> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 3656192650

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

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

| 0 | SELECT STATEMENT | | 1 | 27 | 2 (0)| 00:00:01 | | |

| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| EMP | 1 | 27 | 2 (0)| 00:00:01 | ROWID | ROWID |

|* 2 | INDEX UNIQUE SCAN | EMP_PK | 1 | | 1 (0)| 00:00:01 | | |

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

Predicate Information (identified by operation id):

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

2 - access("EMPNO"=TO_NUMBER(:X))

已选择14行。

这里的计划显示出对未分区索引EMP_PK（为支持主键所创建）有一个INDEX UNIQUE SCAN。然后还有一个TABLE ACCESS GLOBAL INDEX ROWID，其PSTART和PSTOP为ROWID/ROWID，这说明从索引得到ROWID时，它会准确地告诉我们读哪个索引分区来得到这一行。这个索引访问与未分区表上的访问同样有效，而且为此会执行同样数量的I/O。这只是一个简单的单索引惟一扫描，其后是“ 根据ROWID来得到这一行”。现在，我们来看一个全局索引，即JOB上的全局索引：

sys@JINGYONG> delete from plan_table;

已删除3行。

sys@JINGYONG> explain plan for

2 select empno,job,loc from emp where job=:x;

已解释。

sys@JINGYONG> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 475001586

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

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

| 0 | SELECT STATEMENT | | 1000 | 27000 | 5 (0)| 00:00:01 | | |

| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| EMP | 1000 | 27000 | 5 (0)| 00:00:01 | ROWID | ROWID |

|* 2 | INDEX RANGE SCAN | EMP_JOB_IDX | 400 | | 1 (0)| 00:00:01 | | |

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

Predicate Information (identified by operation id):

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

2 - access("JOB"=:X)

已选择14行。

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

最后，下面来看可用性方面。Oracle文档指出，与局部分区索引相比，全局分区索引更有利于“不那么可用”的数据。在OLTP系统中，全局分区索引与局部分区索引有着同样的高度可用性。考虑以下例子：

sys@JINGYONG> alter tablespace p1 offline;

表空间已更改。

sys@JINGYONG> select empno,job,loc from emp where empno=7782;

EMPNO JOB LOC

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

7782 MANAGER NEW YORK

在此，即使表中大多数底层数据都不可用，还是可以通过索引访问任何可用的数据。只要我们想要的EMPNO在可用的表空间中，而且GLOBAL索引可用，就可以利用GLOBAL索引来访问数据。另一方面，如果一直使用在前面“高度可用”的局部索引，倒有可能不允许访问数据！这是因为我们在LOC上分区而需要按EMPNO查询，所以会导致这样一个副作用。我们必须探查每一个局部索引分区，而遭遇到不可用的索引分区时就会失败。

不过，在这种情况下，其他类型的查询不会（而且不能）工作：

sys@JINGYONG> select empno,job,loc from emp where job='CLERK';

select empno,job,loc from emp where job='CLERK'

第 1 行出现错误:

ORA-00376: file 6 cannot be read at this time

ORA-01110: data file 6: '/u01/app/oracle/product/10.2.0/oradata/jingyong/jingyon

g/p101.dbf'

所有分区中都有CLERK数据，由于1个表空间离线，这一点确实会对我们带来影响。这是不可避免的，除非我们在JOB上分区，但是这样一来，就会像按LOC分区查询数据一样出现同样的问题。需要由多个不同的“键”来访问数据时就会有这个问题。Oracle 会“尽其所能”地为你提供数据。

不过，要注意，如果可以由索引来回答查询，就要避免TABLE ACCESS BY ROWID，数据不可用的事实并不重要：

sys@JINGYONG> select count(*) from emp where job='CLERK';

COUNT(*)

----------

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

对总体查询性能来说，分区的影响无非有以下三种可能：

使你的查询更快

根本不影响查询的性能

使你的查询更慢，而且与未分区实现相比，会占用多出几倍的资源

在一个数据仓库中，基于数据相关问题的了解，很可能是以上第一种情况。如果查询频繁地全面扫描很大的数据表，通过消除大段的数据，分区能够对这些查询有很好的影响。假设你有一个100万行的表，其中有一个时间戳属性。你的查询要从这个表中获取一年的数据（其中有10年的数据）。查询使用了一个全表扫描来获取这个数据。如果按时间戳分区，例如每个月一个分区，就可以只对1/10的数据进行全面扫描（假设各年的数据是均匀分布的）。通过分区消除，90%的数据都可以不考虑。你的查询往往会运行得更快。

现在，再来看如果OLTP系统中有一个类似的表。在这种应用中，你肯定不会获取100万行表中10%的数据，因此，尽管数据仓库中可以得到大幅的速度提升，但这种提升在事务性系统中得不到。不同系统中做的工作是不一样的，所用不可能有同样的改进。因此，一般来说，在OLTP系统中达不到第一种情况（不会是查询更快），你不会主要因为提供性能而应用分区。就算是要应用分区，也往往是为了提供可用性以及得到管理上的易用性。但是需要指出，在一个OLTP系统中，即使是要确保达到第二点（也就是说，对查询的性能没有影响，而不管是负面影响还是正面影响），也并非轻而易举，而需要付出努力。很多时候，你的目标可能只是应用分区而不影响查询响应时间。

参考:Oracle.Database.Architecture.9i.and.10g.Programming.Techniques.and.Solutions

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26015009/viewspace-773694/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26015009/viewspace-773694/