对索引进行分区有两种可能的方法：
随表对索引完成相应的分区：这也称为局部分区索引（locally pertitioned index）。每个表分区都有一个索引分区，而且只索引该表分区。一个给定索引分区中的所有条目都指向一个表分区，表分区中的所有行都表示在一个索引分区中。
按区间对索引分区：这也称为全局分区索引（globally partitioned index）。在此，索引按区间分区（或者在Oracle 10g中该可以按散列分区），一个索引分区可能指向任何（和所有）表分区。

局部索引

Oracle划分了以下两类局部索引：
局部前缀索引（local prefixed index）：在这些索引中，分区键在索引定义的前几列上。例如，一个表在名为LOAD_DATE的列上进行区间分区，该表上的局部前缀索引就是LOAD_DATE作为其索引列列表中的第一列。
局部非前缀索引（local nonprefixed index）：这些索引不以分区键作为其列列表的前几列。索引可能包含分区键列，也可能不包含。

分区消除行为

如果查询首先访问索引，它是否能消除分区完全取决于查询中的谓词.
下面的代码创建了一个表PARTITIONED_TABLE，它在一个数字列A上进行区间分区，使得小于2的值都在分区PART_1中，小于3的值则都在分区PART_2中：
CREATE TABLE partitioned_table
( a int,
b int,
data char(20)
)
PARTITION BY RANGE (a)
(
PARTITION part_1 VALUES LESS THAN(2) tablespace p1,
PARTITION part_2 VALUES LESS THAN(3) tablespace p2
);

我们创建一个局部前缀索引LOCAL_PREFIXED和一个局部非前缀索引LOCAL_NONPREFIXED。
create index local_prefixed on partitioned_table (a,b) local;
create index local_nonprefixed on partitioned_table (b) local;

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

begin
dbms_stats.gather_table_stats( user,'PARTITIONED_TABLE',cascade=>TRUE );
end;
   alter tablespace p2 offline;
      表空间P2离线后，Oracle就无法访问这些特定的索引分区。这就好像是我们遭遇了“介质故障”，导致分区不可用。现在我们查询这个表，来看看不同的查询需要哪些索引分区。第一个查询编写为允许使用局部前缀索引：
SQL> select * from partitioned_table where a = 1 and b = 1;
A        B DATA
-- -------- ------------
1        1       x
    这个查询成功了，通过查看解释计划，可以看到这个查询为什么能成功。我们将使用内置包DBMS_XPLAN来查看这个查询访问了哪些分区。输出中的PSTART (分区开始)和PSTOP（分区结束）这两列准确地显示出，这个查询要想成功需要哪些分区必须在线而且可用：
explain plan for select * from partitioned_table where a = 1 and b = 1;
select * from table(dbms_xplan.display);
Execution Plan
----------------------------------------------------------
Plan hash value: 1622054381
--------------------------------------------------------------------------------
| Id | Operation                          | Name            | Rows              Pstart| Pstop|
----------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                     |    1         |       |

|   1 | PARTITION RANGE SINGLE            |                   |     1                                1 |     1 |

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

|* 3 | INDEX RANGE SCAN                | LOCAL_PREFIXED    |     1      1 |     1 |

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

SQL> select * from partitioned_table where b = 1;
select * from partitioned_table where b = 1
ORA-00376: file 10 cannot be read at this time
ORA-01110: data file 10: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\LIUTEST\P201.DBF'
explain plan for select * from partitioned_table where b = 1;
select * from table(dbms_xplan.display);
Execution Plan
----------------------------------------------------------
Plan hash value: 440752652
--------------------------------------------------------------------------------
| Id | Operation                          | Name              | Rows | |                                                    Pstart| Pstop |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                   |     1 |        |       |

|   1 | PARTITION RANGE ALL               |                   |     1 |                            1 |     2 |

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

|* 3 |    INDEX RANGE SCAN                | LOCAL_NONPREFIXED |     1 |                         1 |     2 |

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

ITPub博客

分区索引

最新文章