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

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）全局前缀分区索引

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')--为空

• 博文量
25
• 访问量
61830