来源：http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6144022300346417212

* Local prefixed indexes: These are indexes whereby the partition keys are on the leading edge of the index definition. For example, if a table is range partitioned on a column named LOAD_DATE, a local prefixed index on that table would have LOAD_DATE as the first column in its column list.

本地前缀索引：索引中的第一列就是分区列。

* Local nonprefixed indexes: These indexes do not have the partition key on the leading edge of their column list. The index may or may not contain the partition key columns.

本地非前缀索引：索引列中第一列不是分区列，但是在后面的列中可以包含或者不包含分区列。

实验：

第一步创建分区表，分区列是a，范围分区

ops$tkyte@ORA11GR2> 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 /

第二步创建索引

create index local_prefixed on partitioned_table (a,b) local; 本地前缀索引

create index local_nonprefixed on partitioned_table (b) local; 本地非前缀索引

第三步初始化数据

ops$tkyte@ORA11GR2> insert into partitioned_table

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

3 from all_objects;

72771 rows created.

第四步收集统计信息

ops$tkyte@ORA11GR2> begin

2 dbms_stats.gather_table_stats

3 ( user,

4 'PARTITIONED_TABLE',

5 cascade=>TRUE );

6 end;

7 /

第五步使用前缀索引来验证分区排除

alter tablespace p2 offline; 只查询part_1分区的数据，先离线p2表空间

select * from partitioned_table where a = 1 and b = 1; --查询OK

执行计划

ops$tkyte@ORA11GR2> explain plan for
  2  select * from partitioned_table where a = 1 and b = 1;
Explained.

ops$tkyte@ORA11GR2> 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)

使用了a=1的条件，走了前缀索引，pstart和pstop都是1，排除了分区2.

第六步查询b=1

ops$tkyte@ORA11GR2> select * from partitioned_table where b = 1;

ERROR:

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

ORA-01110: data file 13: '/home/ORA11GR2/.../o1_mf_p2_1dzn8jwp_.dbf'

no rows selected

没有进行分区排除，用到了part_2分区，所以报错找不到数据文件。

ops$tkyte@ORA11GR2> delete from plan_table;
4 rows deleted.

ops$tkyte@ORA11GR2> explain plan for
  2  select * from partitioned_table where b = 1;
Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
| Operation                          | Name              | Rows  | Pstart| Pstop |
----------------------------------------------------------------------------------
| SELECT STATEMENT                   |                   |     1 |       |       |
|  PARTITION RANGE ALL               |                   |     1 |     1 |     2 |
|   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)

清除执行计划后，重新查询，使用了非前缀索引，访问了2个分区。

第七步删除前缀索引

	ops$tkyte@ORA11GR2> drop index local_prefixed;

	Index dropped.

	ops$tkyte@ORA11GR2> select * from partitioned_table where a = 1 and b = 1;

	         A          B DATA

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

	         1          1 x

ops$tkyte@ORA11GR2> delete from plan_table;
4 rows deleted.

ops$tkyte@ORA11GR2> explain plan for
  2  select * from partitioned_table where a = 1 and b = 1;
Explained.

ops$tkyte@ORA11GR2> 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)

删除之前缀索引之后，还是进行了分区排除。说明即使只有非前缀索引，优化器也可以执行分区排除。

是否排除分区主要取决于查询条件，如果第一步就能使用条件来排除分区，那么两种索引是没区别的。

ITPub博客

Local prefixed indexes和 Local noprefixed indexes

最新文章