分区索引

对索引进行分区有两种可能的方法：
         随表对索引完成相应的分区：这也称为局部分区索引 （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,
 da ta 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 DA TA
-- -------- ------------
 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: da ta 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博客 ” ，链接：http://blog.itpub.net/26851211/viewspace-757753/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26851211/viewspace-757753/