ITPub博客

首页 > 数据库 > Oracle > 初步认识Oracle分区索引概念(看了好多遍概念才慢慢理清一丁点)

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

原创 Oracle 作者:jijyoracle 时间:2016-03-04 15:44:24 0 删除 编辑
创建一个表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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: ORA-04023
请登录后发表评论 登录
全部评论

注册时间:2016-01-15

  • 博文量
    25
  • 访问量
    61830