Chinaunix首页 | 论坛 | 博客
  • 博客访问: 539897
  • 博文数量: 128
  • 博客积分: 4000
  • 博客等级: 上校
  • 技术积分: 1345
  • 用 户 组: 普通用户
  • 注册时间: 2008-01-22 21:43
文章分类

全部博文(128)

文章存档

2009年(30)

2008年(98)

我的朋友

分类: Oracle

2008-06-22 21:59:51

对表的索引进行均匀分布-也称为局部索引。
    对每一个表分区都将有一个索引分区,这个索引分区只对其相应的表分区进行索引。
    在给定的索引分区中,所有的项目都指向单个表分区,并且在单个表分区中的所有行都表示在单个的索引分区中。
    主要用于数据仓库

按范围对索引进行分区-也称为全局索引。
    在这里,表的索引按范围进行分区,而且单个索引分区可能指向任何(且是全部)表分区。


局部前缀索引   --- 这是分区码位于索引定义前沿的索引。
局部非前缀索引 --- 这些索引在表列的前沿没有分区码。索引可能包括也可能不包括用于分区码的列。

局部前缀索引比局部非前缀索引具有更好的性能,因为它们将检查的索引的数目最小化。


create table partitioned_table
(a int,
 b int
)
partition by range(a)
(
  partition part_1 values less than (2),
  partition part_2 values less than (3)
);


SQL> create index local_prefixed on partitioned_table(a, b) local;
Index created


SQL> create index local_nonprefixed on partitioned_table(b) local;
Index created

插入一些数据到其中一个分区并标明这些索引UNUSABLE;
设置这些索引分区为UNUSABLE将阻止Oracle访问这些特定的索引分区。让这些索引分区不可用


SQL> insert into partitioned_table values(1, 1);
 
1 row inserted

SQL> alter index local_prefixed modify partition part_2 unusable;
 
Index altered
 
SQL> alter index local_nonprefixed modify partition part_2 unusable;
 
Index altered



查看执行计划
SQL> set autotrace on explain;

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

         A          B
---------- ----------
         1          1


执行计划
----------------------------------------------------------
Plan hash value: 2062330191

---------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                |     1 |    26 |     2   (0)| 00:00:01 |       |    |
|   1 |  PARTITION RANGE SINGLE|                |     1 |    26 |     2   (0)| 00:00:01 |     1 |     1 |
|*  2 |   INDEX FAST FULL SCAN | LOCAL_PREFIXED |     1 |    26 |     2   (0)| 00:00:01 |     1 |     1 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("A"=1 AND "B"=1)

Note
-----
   - dynamic sampling used for this statement

查询成功,Oracle优化器能够把LOCAL_PREFIX的PART_2从考虑中排除,困为在查询中指定了A=1







SQL> select * from partitioned_table where b = 1;

         A          B
---------- ----------
         1          1


执行计划
----------------------------------------------------------
Plan hash value: 4095969474

---------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                   |     1 |    26 |     3   (0)| 00:00:01 |       |    |
|   1 |  PARTITION RANGE ALL|                   |     1 |    26 |     3   (0)| 00:00:01 |     1 |     2 |
|*  2 |   TABLE ACCESS FULL | PARTITIONED_TABLE |     1 |    26 |     3   (0)| 00:00:01 |     1 |     2 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("B"=1)

Note
-----
   - dynamic sampling used for this statement

Oracle优化器不能够将LOCAL_NONPREFIXED的PART_2从考虑中清除出去。对非前缀索引这里存在一个性能问题。




# 重建分区索引
alter index index_nonprefixed rebuild partition part_1;

alter index index_nonprefixed rebuild partition part_2;


# 局部前缀主码索引
  1  create table range_example1
  2  (range_key_column date,
  3   x int,
  4   data varchar2(20)
  5  )
  6  partition by range(range_key_column)
  7  (partition part_1 values less than
  8    (to_date('01-01-1995', 'dd-mm-yyyy')),
  9   partition part_2 values less than
 10    (to_date('01-01-1996', 'dd-mm-yyyy'))
 11* )
SQL> /

表已创建。

SQL> alter table range_example1
  2  add constraint range_example1_pk
  3  primary key (range_key_column, x)
  4  using index local;

表已更改。


# 查询任务驱动表
SQL> create table test (pk, range_key_column, x, constraint test_pk primary key(pk))
  2  as
  3  select rownum, range_key_column, x from range_example1;

表已创建。


SQL> select * from test, range_example1
  2  where test.pk = 1
  3  and test.range_key_column = range_example1.range_key_column
  4  and test.x = range_example1.x;

        PK RANGE_KEY_COLU          X RANGE_KEY_COLU          X DATA
---------- -------------- ---------- -------------- ---------- --------------------
         1 01-1月 -94              1 01-1月 -94              1 xxx


执行计划
----------------------------------------------------------
Plan hash value: 3768040647

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name              | Rows  | Bytes | Cost (%CPU)| Time  | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                   |     1 |    69 |     2   (0)| 00:00:01 |       |       |
|   1 |  NESTED LOOPS                       |                   |     1 |    69 |     2   (0)| 00:00:01 |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID       | TEST              |     1 |    35 |     1   (0)| 00:00:01 |       |       |
|*  3 |    INDEX UNIQUE SCAN                | TEST_PK           |     1 |       |     0   (0)| 00:00:01 |       |       |
|   4 |   PARTITION RANGE ITERATOR          |                   |   818 | 27812 |     1   (0)| 00:00:01 |   KEY |   KEY |
|   5 |    TABLE ACCESS BY LOCAL INDEX ROWID| RANGE_EXAMPLE1    |   818 | 27812 |     1   (0)| 00:00:01 |   KEY |   KEY |
|*  6 |     INDEX UNIQUE SCAN               | RANGE_EXAMPLE1_PK |     1 |       |     0   (0)| 00:00:01 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("TEST"."PK"=1)
   6 - access("TEST"."RANGE_KEY_COLUMN"="RANGE_EXAMPLE1"."RANGE_KEY_COLUMN" AND "TEST"."X"="RANGE_EXAMPLE1"."X")







SQL> alter table range_example1 drop constraint range_example1_pk;

表已更改。

  1  alter table range_example1
  2  add constraint range_example1_pk
  3  primary key(x, range_key_column)
  4* using index local
SQL> /

表已更改。

SQL> select * from test, range_example1
  2  where test.pk = 1
  3  and test.range_key_column = range_example1.range_key_column
  4  and test.x = range_example1.x;

        PK RANGE_KEY_COLU          X RANGE_KEY_COLU          X DATA
---------- -------------- ---------- -------------- ---------- --------------------
         1 01-1月 -94              1 01-1月 -94              1 xxx


执行计划
----------------------------------------------------------
Plan hash value: 3768040647

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name              | Rows  | Bytes | Cost (%CPU)| Time  | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                   |     1 |    69 |     2   (0)| 00:00:01 |       |       |
|   1 |  NESTED LOOPS                       |                   |     1 |    69 |     2   (0)| 00:00:01 |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID       | TEST              |     1 |    35 |     1   (0)| 00:00:01 |       |       |
|*  3 |    INDEX UNIQUE SCAN                | TEST_PK           |     1 |       |     0   (0)| 00:00:01 |       |       |
|   4 |   PARTITION RANGE ITERATOR          |                   |   818 | 27812 |     1   (0)| 00:00:01 |   KEY |   KEY |
|   5 |    TABLE ACCESS BY LOCAL INDEX ROWID| RANGE_EXAMPLE1    |   818 | 27812 |     1   (0)| 00:00:01 |   KEY |   KEY |
|*  6 |     INDEX UNIQUE SCAN               | RANGE_EXAMPLE1_PK |     1 |       |     0   (0)| 00:00:01 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("TEST"."PK"=1)
   6 - access("TEST"."X"="RANGE_EXAMPLE1"."X" AND "TEST"."RANGE_KEY_COLUMN"="RANGE_EXAMPLE1"."RANGE_KEY_COLUMN")




# 局部索引和惟一性

create table partitioned
  (timestamp date,
   id int primary key
  )
partition by range(timestamp)
  (
  partition part_1 values less than
   (to_date('01-01-2000', 'MM-DD-YYYY')),
  partition part_2 values less than
   (to_date('01-01-2001', 'MM-DD-YYYY'))
  )
SQL> /

表已创建。


# 这里Oracle使用了全局索引来保证唯一性,而不是局部索引
SEGMENT_NAME                                                                      PARTITION_NAME         SEGMENT_TYPE
--------------------------------------------------------------------------------- ------------------------------ --------------
SYS_C003035                                                                                              INDEX
PARTITIONED                                                                       PART_2                 TABLE PARTITION
PARTITIONED                                                                       PART_1                 TABLE PARTITION

索引SYS_C003035未被分区。这样会失去许多数据仓库分区可用性的特点。


create table partitioned
  (timestamp date,
   id int
  )
  partition by range(timestamp)
  (
    partition part_1 values less than
      (to_date('01-01-2000', 'MM-DD-YYYY')),
    partition part_2 values less than
      (to_date('01-01-2001', 'MM-DD-YYYY'))
  );


create index partioned_index
  on partitioned(id) local;

SQL> alter table partitioned
  2  add constraint partitioned_pk
  3  primary key(id);
alter table partitioned
*
第 1 行出现错误:
ORA-01408: 此列列表已索引








create table partitioned
  (timestamp date,
   id int primary key
  )
partition by range(id)
  (
  partition part_1 values less than (1000),
  partition part_2 values less than (2000)
  )
阅读(1424) | 评论(0) | 转发(0) |
0

上一篇:分区表二-分区模式

下一篇:并行模式

给主人留下些什么吧!~~