分区索引--global和local,prefix和non-prefix

最新推荐文章于 2024-08-24 12:16:43 发布

转载最新推荐文章于 2024-08-24 12:16:43 发布 · 960 阅读

本文详细解析了分区表和分区索引的概念，并通过实例对比了global与local分区索引的区别，阐述了prefix前缀索引的使用场景及其与非前缀索引的差异。

实战派 ESP32-S3，双模无线开发板

ESP32-S3 原生支持 ESP-IDF，WiFi + 蓝牙一次搞定

点击查看

1.分区表和分区索引的概念是分开的。分区表上建的索引就是分区索引，是错误的。
2.global分区索引也是可以建在非分区表上的，local分区索引只能建在分区表上。
3.关键字global和local都是针对分区索引，虽然非分区索引也可以用global关键字，但无意义。
4.global分区索引只能是prefix前缀的。
5.prefix前缀和non-prefix，global分区索引是否为prefix与索引键和索引分区的分区列有关。
6.local分区索引可以是prefix前缀的，也可以是non-prefix非前缀的。
7.prefix前缀和non-prefix，local分区索引是否为prefix与索引键和表分区列有关。

------下面进行测试说明：
--建立普通表
create table scott.test_normal
as select * from dba_objects where object_id is not null;
--建立分区表
create table scott.test_partition
partition by RANGE (object_id)
(
partition p1 values less than (10000) ,
partition p2 values less than (20000) ,
partition p3 values less than (maxvalue)
)
as select * from dba_objects where object_id is not null;
----1.分区表和分区索引的概念是分开的。分区表上建的索引就是分区索引，是错误的。
----2.global分区索引也是可以建在非分区表上的，local分区索引只能建在分区表上。
--普通表上创建global分区索引
create index scott.idx_test_normal_01
on scott.test_normal(object_id) global
partition by RANGE (object_id)
(
partition p1 values less than (10000) ,
partition p2 values less than (20000) ,
partition p3 values less than (maxvalue)
);
select index_owner,index_name,partition_name from dba_ind_partitions
where index_name=upper('idx_test_normal_01');
/*
SCOTT IDX_TEST_NORMAL_01 P1
SCOTT IDX_TEST_NORMAL_01 P2
SCOTT IDX_TEST_NORMAL_01 P3
*/
--普通表上创建local分区索引
create index scott.idx_test_normal_02
on scott.test_normal(object_id) local;
/*
ORA-14016 underlying table of a LOCAL partitioned index must be partitioned
*/
----3.关键字global和local都是针对分区索引，虽然非分区索引也可以用global关键字，但无意义。
--创建普通索引
create index scott.idx_test_normal_02
on scott.test_normal(object_name) ;
--创建加global关键字的索引
create index scott.idx_test_normal_03
on scott.test_normal(owner) global;
select dbms_metadata.get_ddl('INDEX\','IDX_TEST_NORMAL_02','SCOTT') from dual;
/*
CREATE INDEX \"SCOTT\".\"IDX_TEST_NORMAL_02\" ON \"SCOTT\".\"TEST_NORMAL\" (\"OBJECT_NAME\")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE \"USERS\"
*/
select dbms_metadata.get_ddl('INDEX\','IDX_TEST_NORMAL_03','SCOTT') from dual;
/*
CREATE INDEX \"SCOTT\".\"IDX_TEST_NORMAL_03\" ON \"SCOTT\".\"TEST_NORMAL\" (\"OWNER\")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE \"USERS\"
*/
----4.global分区索引只能是prefix前缀的。
----5.prefix前缀和non-prefix，global分区索引是否为prefix与索引键和索引分区的分区列有关。
--创建global prefix前缀的索引
create index scott.idx_test_partition_01
on scott.test_partition(data_object_id) global
partition by RANGE (data_object_id)
(
partition p1 values less than (10000) ,
partition p2 values less than (20000) ,
partition p3 values less than (maxvalue)
);
select index_owner,index_name,partition_name from dba_ind_partitions
where index_name=upper('idx_test_partition_01');
/*
SCOTT IDX_TEST_PARTITION_01 P1
SCOTT IDX_TEST_PARTITION_01 P2
SCOTT IDX_TEST_PARTITION_01 P3
*/
--创建global prefix非前缀的索引
create index scott.idx_test_partition_02
on scott.test_partition(owner) global
partition by RANGE (object_id)
(
partition p1 values less than (10000) ,
partition p2 values less than (20000) ,
partition p3 values less than (maxvalue)
);
/*
ORA-14038 GLOBAL partitioned index must be prefixed
*/
----6.local分区索引可以是prefix前缀的，也可以是non-prefix非前缀的。
----7.prefix前缀和non-prefix，local分区索引是否为prefix与索引键和表分区列有关。
--local prefix前缀
create index scott.idx_test_partition_03
on scott.test_partition(object_id,data_object_id) local;
select index_owner,index_name,partition_name from dba_ind_partitions
where index_name=upper('idx_test_partition_03');
/*
SCOTT IDX_TEST_PARTITION_01 P1
SCOTT IDX_TEST_PARTITION_01 P2
SCOTT IDX_TEST_PARTITION_01 P3
*/
--local prefix非前缀
create index scott.idx_test_partition_04
on scott.test_partition(data_object_id,owner) local;
select index_owner,index_name,partition_name from dba_ind_partitions
where index_name=upper('idx_test_partition_04');
/*
SCOTT IDX_TEST_PARTITION_04 P2
SCOTT IDX_TEST_PARTITION_04 P3
SCOTT IDX_TEST_PARTITION_04 P1
*/