随表对索引完成相应的分区，即本地分区索引。每个表分区都有一个索引分区，而且只索引该分区。一个给定索引分区中的所有条目都指向一个表分区，表分区中的所有行都表示在一个索引分区中。本地分区索引（local partitioned index）与其他类型分区索引相比较更易管理。本地分区索引适用于 DSS 系统，且具有较高的可用性。这是因为本地分区索引与其所在的分区表采用相同的分区方式：本地分区索引的每个分区都与分区表的一个分区相对应。因此，Oracle 能够自动地确保各个索引分区与相应的表分区同步，且使各个表-索引分区对（table-index pair）相互独立。当一个表分区内的数据发生变化时，只会影响一个索引分区。

用户不能显示地向本地分区索引中添加分区。当用户为分区表添加分区时，相应的索引分区会被自动添加。同样，用户也不能显示地移除本地分区索引中的分区。当用户从分区表中移除分区时，相应的索引分区会被自动移除。本地分区索引可以是唯一索引。但是，为了确保本地分区索引每个分区的唯一性，分区键必须是索引键的子集。通常唯一本地分区索引适用于 OLTP 系统。

局部前缀索引（local prefixed index），在这些索引中，分区键在索引定义的前几列上，例如，一个表在名为load_date的列上进行区间分区，该表上的局部前缀索引就以load_date作为其索引列列表中的第一列（允许分区消除）；局部非前缀索引（local nonprefixed index），这些索引不以分区索引键作为其列列表的前几列，索引可能包含分区键列，也可能不包含。（不保证分区消除，但只要索引包含分区键就支持分区消除）

分区消除行为：
如果查询首先访问索引，它是否能消除分区完全取决于查询中的谓词。
JEL@JEL >create table partitioned_table ( a int,b int,data char(20))
  2  partition by range(a)
  3  (
  4  partition part_1 values less than(2) tablespace ts1,
  5  partition part_2 values less than(3) tablespace ts2);

Table created.

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

Index created.

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

Index created.

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

9314 rows created.

JEL@JEL >exec dbms_stats.gather_table_stats('JEL','PARTITIONED_TABLE',cascade=>true);

PL/SQL procedure successfully completed.

JEL@JEL >alter tablespace ts2 offline;

Tablespace altered.

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

         A          B DATA
---------- ---------- --------------------
         1          1 x


JEL@JEL >delete from plan_table;

0 rows deleted.

JEL@JEL >explain plan for
  2  select * from partitioned_table where a=1 and b=1;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1622054381

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

| Id  | Operation                          | Name              | Rows  | Bytes |
 Cost (%CPU)| Time     | Pstart| Pstop |

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


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

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

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

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

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


Predicate Information (identified by operation id):
---------------------------------------------------

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

优化器消除了LOCAL_PREFIXED的part_2（已经离线）不予考虑，我们在查询中指定了a=1，而且在计划中科院看到pstart（分区开始）和pstop（分区结束）都等于1.

但如下查询却失败了：
JEL@JEL >select * from partitioned_table where b=1;
ERROR:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/u01/app/oracle/oradata/JEL/disk1/tbs2.dbf'

分析如下：
JEL@JEL >delete from plan_table;

4 rows deleted.

JEL@JEL >explain plan for
  2  select * from partitioned_table where b=1;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 440752652

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

| Id  | Operation                          | Name              | Rows  | Bytes |
 Cost (%CPU)| Time     | Pstart| Pstop |

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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                   |     1 |    27 |
     4   (0)| 00:00:01 |       |       |

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

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

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

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


Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("B"=1)

15 rows selected.

这样，优化器考虑了LOCAL_NONPREFIXED索引，part_1和part_2都必须检查。要想使用非前缀索引，必须使用一个允许分区消除的查询。

如下修改：
JEL@JEL >drop index local_prefixed;

Index dropped.

JEL@JEL >select * from partitioned_table where b=1;
ERROR:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/u01/app/oracle/oradata/JEL/disk1/tbs2.dbf'



no rows selected

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

         A          B DATA
---------- ---------- --------------------
         1          1 x

JEL@JEL >delete from plan_table;

4 rows deleted.

JEL@JEL >explain plan for
  2  select * from partitioned_table where b=1 and a=1;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 904532382

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

| Id  | Operation                          | Name              | Rows  | Bytes |
 Cost (%CPU)| Time     | Pstart| Pstop |

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


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

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

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

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("A"=1)
   3 - access("B"=1)

16 rows selected.

优化器对非前缀本地索引也能执行分区消除。