分区表之局部索引的分区消除问题

oracle局部索引分为：

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

局部非前缀索引（local noprefixed index）,这些索引不以分区键做为其列列表的前几列。

环境准备：

create table partitioned_table
(a int,
b int,
data char(20)
)
partition by range (a)
(
partition p_1 values less than(2) tablespace p1,
partition p_2 values less than(3) tablespace p2
)

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;

1.

SQL> delete from plan_table;

0 rows deleted.

SQL>  
SQL>  
SQL> explain plan for select  * from partitioned_table where a=1 and b=1;

Explained.

SQL> select * from table(dbms_xplan.display);

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

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |     1 |    28 |     2     (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE         |                 |     1 |    28 |     2     (0)| 00:00:01 |     1 |     1 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE |     1 |    28 |     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 rows selected.

使用local_prefixed 的查询成功了。优化器消除了local_prefixed的part_2 不予考虑，因为在查询中指定了a=1,而且在在计划中可以清楚地看到PSTART和PSTOP都等于1.

2.

SQL> 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/orcl/p2.dbf'



no rows selected

SQL> delete from plan_table;

4 rows deleted.

SQL>  
SQL>  
SQL> set lines 500
SQL> explain plan for select  * from partitioned_table where b=1;

Explained.

SQL> select * from table(dbms_xplan.display);

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

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |     1 |    28 |     4     (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL             |                 |     1 |    28 |     4     (0)| 00:00:01 |     1 |     2 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE |     1 |    28 |     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 rows selected.

查询失败，因为优化器不能不考虑local_nonprefixed的part_2,为了查看是否有b=1.在此，局部非前缀索引存在一个性能问题：它不能象前缀索引一样，在谓词中使用分区键。要使用非前缀索引，必须使用一个允许分区消除的查询（理解为a=1,可以消除第二个分区）。

3.删除前缀索引local_prefixed,再次查询

SQL> drop index local_prefixed;

Index dropped.

SQL>  
delete from plan_table;

4 rows deleted.

explain plan for select  * from partitioned_table where a=1 and b=1;

Explained.

SQL> select * from table(dbms_xplan.display);

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

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |     1 |    28 |     2     (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE         |                 |     1 |    28 |     2     (0)| 00:00:01 |     1 |     1 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE |     1 |    28 |     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 rows selected.

此时可以查询，并且使用了非前缀分区索引。因为谓词a 分区消除。

结论：

如果频繁的用以下查询来先前的表

select  * from partitioned_table where a=:a and b=:b;

select  * from partitioned_table where b=:b;

可以考虑在（b,a)上使用一个局部非前缀索引。这个索引对于前面的两个查询都是有用的。（a,b)上的局部前缀索引只对第一个查询有用。

---end.整理来自Thomas Kyte的文章

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

转载于:http://blog.itpub.net/30060321/viewspace-1472517/