初步认识Oracle分区索引概念（看了好多遍概念才慢慢理清一丁点）

创建一个表partitioned，以timestamp分区：
CREATE TABLE partitioned
 ( timestamp date,
 id int
 )
 PARTITION BY RANGE (timestamp)
(
 PARTITION part_1 VALUES LESS THAN( to_date('01-jan-2000','dd-mon-yyyy') ) ,
 PARTITION part_2 VALUES LESS THAN( to_date('01-jan-2001','dd-mon-yyyy') )
 )

分区索引为：
1.本地（局部）分区索引
1）本地 前缀分区索引
创建索引的前导列，是表的分区列
create index local_qz on  partitioned(timestamp) local;
select index_name,index_type,status from user_indexes where index_name=upper('local_qz')
LOCAL_QZ NORMAL N/A

select index_name,status from dba_ind_partitions where index_name=upper('local_qz')

LOCAL_QZ USABLE
LOCAL_QZ USABLE

select locality,alignment from dba_part_indexes where index_name=upper('local_qz')
LOCAL PREFIXED

2）本地 非前缀分区索引
创建索引的前导列，不是表的分区列
create index local_fqz on  partitioned(id) local;
select index_name,index_type,status from user_indexes where index_name=upper('local_fqz')
LOCAL_FQZ NORMAL N/A

select index_name,status from dba_ind_partitions where index_name=upper('local_fqz')

LOCAL_FQZ USABLE
LOCAL_FQZ USABLE

select locality,alignment from dba_part_indexes where index_name=upper('local_fqz')
LOCAL NON_PREFIXED

2.全局分区索引
1）全局前缀分区索引
创建索引的列前导列，必须要是是索引分区的列，全局分区索引数不必要和表分区对应，可以1对多，可以1多对1
create index ind_glo on partitioned(id) GLOBAL
partition by range(id)
(
 partition ind_1 values less than(1),
 partition ind_2 values less than(2),
 partition ind_3 values less than(3),
 partition ind_4 values less than(4),
partition ind_5 values less than (MAXVALUE)
)

select index_name,index_type,status from user_indexes where index_name=upper('ind_glo')
IND_GLO NORMAL N/A

select partition_name,index_name,status from dba_ind_partitions where index_name=upper('ind_glo')
IND_1 IND_GLO USABLE
IND_2 IND_GLO USABLE
IND_3 IND_GLO USABLE
IND_4 IND_GLO USABLE
IND_5 IND_GLO USABLE
select locality,alignment from dba_part_indexes where index_name=upper('ind_glo')
GLOBAL PREFIXED 

2）全局不分区索引

create index ind_glo on partitioned(id) global;
IND_GLO NORMAL VALID

select index_name,status from dba_ind_partitions where index_name=upper('ind_glo')--为空
select locality,alignment from dba_part_indexes where index_name=upper('ind_glo')--为空


做个笔记，后续慢慢补充

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30536096/viewspace-2039273/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30536096/viewspace-2039273/