最近正在温习ORACLE分区表，下面是关于全局分区索引和局部分区索引，以及前缀局部分区索引和非前缀局部分区索引的概念和需要注意的地方
全局分区索引：索引分区不按照表的分区进行，索引的分区可以指向任何表分区
局部分区索引：索引分区完成按照表的分区进行
前缀局部分区索引：分区键在索引定义的第一列上
非前缀局部分区索引：分区键不在索引定义的第一列上，可能根本不包含分区键
1、这里先说明下分区修剪，以及前缀局部分区索引和非前缀局部分区索引的关系
何为分区修剪？其实这个是分区表的主要功能，表示可以通过分区，ORACLE会判断查询的数据在那个分区中
从而不会去访问未包含数据的分区，从而提高性能。
前缀局部分区索引总是可以很好的利用分区修剪，而非前缀局部分区索引可能在利用上有一些问题。
因为根据分区键总能准确的判定出数据所在的分区，而非前缀局部分区索引因为不包含分区键所以不会很好的
利用，因为它判定不出分区中是否有需要的数据只能全分区扫描。
列子如下；
建立分区表
SQL> CREATE TABLE pp
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 tbs1,
9 PARTITION part_2 VALUES LESS THAN(3) tablespace tbs2
10 ) ;
表已创建。
前缀局部分区索引
SQL> create index pp_local on pp(a,b) local;
索引已创建。
非前缀局部分区索引
SQL> create index pp_nolocal on pp(b) local;
索引已创建。
插入数据
SQL> insert into pp
2 select mod(rownum-1,2)+1, rownum, 'x'
3 from all_objects;
已创建49580行。
分析表和索引
SQL> execute dbms_stats.gather_table_stats(ownname => 'PP',tabname => 'PP',cascade => true);
PL/SQL 过程已成功完成。
现在进行查看执行计划
SQL> select * from pp where a=1 and b=1;
A B DATA
---------- ---------- --------------------
1 1 x
执行计划
----------------------------------------------------------
Plan hash value: 1330722014
----------------------------------------------------------------------------------------------------
| 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| PP | 1 | 28 | 2 (0)| 00:00:01 | 1
|* 3 | INDEX RANGE SCAN | PP_LOCAL | 1 | | 1 (0)| 00:00:01 | 1 | 1 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"=1 AND "B"=1)
PSTART PSTOP都是1这里忽略了分区2
如果我是用以下的查询
SQL> select * from pp where b=1;
A B DATA
---------- ---------- --------------------
1 1 x
执行计划
----------------------------------------------------------
Plan hash value: 1993740852
----------------------------------------------------------------------------------------------------
| 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| PP | 1 | 28 | 4 (0)| 00:00:01 | 1 |
|* 3 | INDEX RANGE SCAN | PP_NOLOCAL | 1 | | 3 (0)| 00:00:01 | 1 | 2 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"=1)
大家都知道其实返回的值都是一样这里却扫描的全分区PSTART=1,PSTOP=2因为没有分区键索引这里
只能进行全分区扫描了。其实感觉这里和索引的类型没有多大的关系。关键在于查询中是否有分区键。
另外一个限制局部分区索引只会保证分居中的数据唯一性，而不会去关注全分区的数据唯一性，索引
即使对表进行了分区，加入主键的时候主键对应的索引一样不是分区的，列子
SQL> CREATE TABLE pp2
2 (
3 id int,
4 ic_id int,
5 constraint partitioned_pk primary key(id)
6 )
7 PARTITION BY RANGE (id)
8 (
9 PARTITION part_1 VALUES LESS THAN (20000) tablespace tbs1,
10 PARTITION part_2 VALUES LESS THAN (70000) tablespace tbs2
11 ) ;
Table created
SQL> insert into pp2
2 select rownum,mod(rownum-1,2)+1
3 from all_objects;
49587 rows inserted
SQL>
SQL> execute dbms_stats.gather_table_stats(ownname => 'PP100',tabname => 'PP2',cascade => true);
PL/SQL procedure successfully completed
SQL> select * from user_objects;
SQL> CREATE TABLE pp2
2 (
3 id int,
4 ic_id int,
5 constraint partitioned_pk primary key(id)
6 )
7 PARTITION BY RANGE (id)
8 (
9 PARTITION part_1 VALUES LESS THAN (20000) tablespace tbs1,
10 PARTITION part_2 VALUES LESS THAN (70000) tablespace tbs2
11 ) ;
Table created
SQL> insert into pp2
2 select rownum,mod(rownum-1,2)+1
3 from all_objects;
49587 rows inserted
SQL>
SQL> execute dbms_stats.gather_table_stats(ownname => 'PP100',tabname => 'PP2',cascade => true);
PL/SQL procedure successfully completed
SQL> select * from user_objects;
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY
-------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- ---------
PP2 PART_2 58189 58189 TABLE PARTITION 2010-9-3 14 2010-9-3 14:5 2010-09-03:14:56:19 VALID N N N
PP2 PART_1 58188 58188 TABLE PARTITION 2010-9-3 14 2010-9-3 14:5 2010-09-03:14:56:19 VALID N N N
PP2 58187 TABLE 2010-9-3 14 2010-9-3 14:5 2010-09-03:14:56:19 VALID N N N
PARTITIONED_PK 58190 58190 INDEX 2010-9-3 14 2010-9-3 14:5 2010-09-03:14:56:19 VALID N N N
ORACLE这样做是为了提高性能，如果每次插入数据的时候ORACLE都必须去检查每个分区，这样导致的结果是性能地下，分区越多性能就越差。
2、全局分区索引
这个分区索引只有前缀这一种方式，全局分区索引可以用于保证主键的惟一性，这样一来，即使不包括表的分区键，也可以有能保证惟一性的分区索引
