全局分区索引和局部分区索引

最新推荐文章于 2023-11-15 11:59:15 发布

cri5768

最新推荐文章于 2023-11-15 11:59:15 发布

阅读量414

点赞数

文章标签：数据库

最近正在温习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、全局分区索引
这个分区索引只有前缀这一种方式，全局分区索引可以用于保证主键的惟一性，这样一来，即使不包括表的分区键，也可以有能保证惟一性的分区索引