分区索引分2类:有前缀(prefix)的分区索引和无前缀(nonprefix)的分区索引:
(1)有前缀的分区索引指包含了分区键,并且将其作为引导列的索引。
如:
create index i_id_global on PDBA(id) global --引导列
partition by range(id) --分区键
(
partition p1 values less than (200),
partition p2 values less than (maxvalue)
);
这里的ID 就是分区键,并且分区键id 也是索引的引导列。
(2)无前缀的分区索引的列不是以分区键开头,或者不包含分区键列。
如:
create index ix_custaddr_local_id_p on custaddr(id)
local (
partition t_list556 tablespace icd_service,
partition p_other tablespace icd_service
)
这个分区是按照areacode来的。但是索引的引导列是ID。 所以它就是非前缀分区索引。
全局分区索引不支持非前缀的分区索引,如果创建,报错如下:
SQL> create index i_time_global on PDBA(id) global --索引引导列
partition by range(time) --分区建
(
partition p1 values less than (TO_DATE('2010-12-1', 'YYYY-MM-DD')),
partition p2 values less than (maxvalue)
);
partition by range(time)
*
第 2 行出现错误:
ORA-14038: GLOBAL 分区索引必须加上前缀
4.1 Local 本地索引
对于local索引,当表的分区发生变化时,索引的维护由Oracle自动进行。
示例:
sql> create index ix_custaddr_local_id on custaddr(id) local;
索引已创建。
和下面SQL 效果相同,因为local索引就是分区索引:
create index ix_custaddr_local_id_p on custaddr(id)
local (
partition t_list556 tablespace icd_service,
partition p_other tablespace icd_service
)
SQL> create index ix_custaddr_local_areacode on custaddr(areacode) local;
索引已创建。
验证2个索引的类型:
SQL> select index_name,table_name,partitioning_type,locality,ALIGNMENT from user_part_indexes where table_name='CUSTADDR';
index_name table_name partition locali alignment
------------------------------ ---------- --------- ------ ------------
ix_custaddr_local_areacode custaddr list local prefixed
ix_custaddr_local_id custaddr list local non_prefixed
注意:Oracle只支持2中类型的全局分区索引:
range partitioned 和 Hash Partitioned.
官网的说明如下:
Global Partitioned Indexes
Oracle offers two types of global partitioned index: range partitioned and hash partitioned.
(1)Global Range Partitioned Indexes
Global range partitioned indexes are flexible in that the degree of partitioning and the partitioning key are independent from the table's partitioning method. They are commonly used for OLTP environments and offer efficient access to any individual record.
The highest partition of a global index must have a partition bound, all of whose values are MAXVALUE. This ensures that all rows in the underlying table can be represented in the index. Global prefixed indexes can be unique or nonunique.
You cannot add a partition to a global index because the highest partition always has a partition bound of MAXVALUE. If you wish to add a new highest partition, use the ALTER INDEX SPLIT PARTITION statement. If a global index partition is empty, you can explicitly drop it by issuing the ALTER INDEX DROP PARTITION statement. If a global index partition contains data, dropping the partition causes the next highest partition to be marked unusable. You cannot drop the highest partition in a global index.
(2)Global Hash Partitioned Indexes
Global hash partitioned indexes improve performance by spreading out contention when the index is monotonically growing. In other words, most of the index insertions occur only on the right edge of an index.
(3)Maintenance of Global Partitioned Indexes
By default, the following operations on partitions on a heap-organized table mark all global indexes as unusable:
ADD (HASH)
COALESCE (HASH)
DROP
EXCHANGE
MERGE
MOVE
SPLIT
TRUNCATE
示例1 全局索引,全局索引对所有分区类型都支持:
sql> create index ix_custaddr_ global_id on custaddr(id) global;
索引已创建。
示例2:全局分区索引,只支持Range 分区和Hash 分区:
(1)创建2个测试分区表:
sql> create table pdba (id number, time date) partition by range (time)
(
partition p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')),
partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')),
partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')),
partition p4 values less than (maxvalue)
);
表已创建。
SQL> create table Thash
(
id number primary key,
item_id number(8) not null
)
partition by hash(id)
(
partition part_01,
partition part_02,
partition part_03
);
表已创建。
(2)创建分区索引
示例2:全局分区索引
SQL> create index i_id_global on PDBA(id) global
partition by range(id)
(partition p1 values less than (200),
partition p2 values less than (maxvalue)
);
索引已创建。
--这个是有前缀的分区索引。
SQL> create index i_time_global on PDBA(id) global
partition by range(time)
(partition p1 values less than (TO_DATE('2010-12-1', 'YYYY-MM-DD')),
partition p2 values less than (maxvalue)
);
partition by range(time)
*
第 2 行出现错误:
ORA-14038: GLOBAL 分区索引必须加上前缀
SQL> create index i_time_global on PDBA(time) global
partition by range(time)
(partition p1 values less than (TO_DATE('2010-12-1', 'YYYY-MM-DD')),
partition p2 values less than (maxvalue)
);
索引已创建。
--有前缀的分区索引
create index i_Thash_global_index on Thash(item_id) global
partition by hash(item_id)
(
partition p1,
partition p2
);
索引已创建。
create index i_range_global_index on Thash(item_id) global
partition by range(item_id)
(
partition p1 values less than (200),
partition p2 values less than (maxvalue)
)
SQL> select index_name,table_name,partitioning_type,locality,ALIGNMENT from user_part_indexes where table_name='PDBA';
index_name table_name partition locali alignment
------------- ---------- --------- ------ ------------
i_id_global pdba range global prefixed
i_time_global pdba range global prefixed
SQL> CREATE INDEX ix_hash ON PDBA (id,time) GLOBAL
PARTITION BY HASH (id)
(PARTITION p1,
PARTITION p2,
PARTITION p3,
PARTITION p4);
索引已创建。