对表的索引进行均匀分布－也称为局部索引。
对每一个表分区都将有一个索引分区，这个索引分区只对其相应的表分区进行索引。
在给定的索引分区中，所有的项目都指向单个表分区，并且在单个表分区中的所有行都表示在单个的索引分区中。
主要用于数据仓库
按范围对索引进行分区－也称为全局索引。
在这里，表的索引按范围进行分区，而且单个索引分区可能指向任何(且是全部)表分区。
局部前缀索引 --- 这是分区码位于索引定义前沿的索引。
局部非前缀索引 --- 这些索引在表列的前沿没有分区码。索引可能包括也可能不包括用于分区码的列。
局部前缀索引比局部非前缀索引具有更好的性能，因为它们将检查的索引的数目最小化。
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)
)
阅读(1429) | 评论(0) | 转发(0) |
