索引分区（本地索引和全局索引）

--索引分区（本地索引和全局索引）

对索引分区有以下两种方法：
	按表分区的方式对索引分区：这也称为本地索引（local index）。每个表分区都有一个索引分区，而且这个索引分区只会对这个表分区中的数据进行索引。
							  同一索引分区中的所有条目都指向一个表分区，同一表分区中的所有行都会放在一个索引分区中。
	按区间或散列对索引分区：这也称为全局分区索引（globally partitioned index）。索引可以按区间分区，一个索引分区可以指向任何表分区。

--本地索引

本地索引分为两类：
	本地前缀索引（local prefixed index）：在这类索引中，分区键在索引定义的前几列上。
	本地非前缀索引（local nonprefixed index）：这些索引不以表的分区键作为它的前几列。索引可能包含表的分区键列，也可能不包含。

1.分区修剪行为：如果查询首先访问索引，他是否能够修建分区完全取决于查询中的谓词。

EODA@PROD1> 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  /

Table created.

EODA@PROD1> 
EODA@PROD1> create index local_prefixed on partitioned_table (a,b) local;  --创建本地前缀索引

Index created.

EODA@PROD1> create index local_nonprefixed on partitioned_table (b) local;	--创建本地非前缀索引

Index created.

EODA@PROD1> 
EODA@PROD1> insert into partitioned_table
  2    select mod(rownum-1,2)+1, rownum, 'x'
  3    from dual connect by level <= 70000;

70000 rows created.

EODA@PROD1> begin
  2  	  dbms_stats.gather_table_stats
  3  	  ( user,
  4  	   'PARTITIONED_TABLE',
  5  	    cascade=>TRUE );
  6  end;
  7  /

PL/SQL procedure successfully completed.

EODA@PROD1> alter tablespace p2 offline;  --讲p2放于离线状态，模拟介质故障

Tablespace altered.

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

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

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

Explained.

EODA@PROD1> set lines 132
EODA@PROD1> select * from table(dbms_xplan.display)；  --PSTART分区开始,PSTOP准确显示哪些分区在线且可用

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

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

EODA@PROD1> select * from partitioned_table where b=1;  --这个查询却失败了
ERROR:
ORA-00376: file 13 cannot be read at this time
ORA-01110: data file 13: '/u01/app/oracle/oradata/PROD1/PROD1/datafile/o1_mf_p2_d24hxpq0_.dbf'

no rows selected

EODA@PROD1> explain plan for select * from partitioned_table where b = 1;

Explained.

EODA@PROD1> 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 |    29 |     4	 (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL		   |		       |     1 |    29 |     4	 (0)| 00:00:01 |     1 |     2 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE |     1 |    29 |     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.
--PSTOP为2，说明在使用本地非前缀索引的时候必须考虑part_2，但是表空间已经离线所以失败。

EODA@PROD1> drop index local_prefixed;  --然后删除本地前缀索引

Index dropped.

EODA@PROD1> select * from partitioned_table where a = 1 and b = 1;  --再次查询成功的查询

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

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

Explained.

EODA@PROD1> 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 |