人大金仓数据库解决了兼容oracle的索引失效问题

关键字

索引失效，禁用索引，启用索引，索引重建、人大金仓、

问题描述

Oracle能够通过语句设置，使索引失效，支持设置索引的启用禁用操作

create table t1(c1 int,c2 varchar(10));

create index idx_t1 on t1(c1);

--禁用索引，设置索引失效

alter index idx_t1 unusable;

--启用索引，重建索引

alter index idx_t1 rebuild;

问题分析

KES不具备设置索引启用，禁用的能力。

解决方案

create index语句添加usable、unusable支持创建索引时指定索引状态，未指定时默认为启用状态。

新增语法：

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ] ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ) [ INCLUDE ( column_name [, ...] ) ] [ WITH ( storage_parameter = value [, ... ] ) ] [ TABLESPACE tablespace_name ]

[ UNUSABLE | USABLE ] [ WHERE predicate ]

描述：

unusable 创建一个不可用状态的索引

usable 创建一个可用状态的索引，默认状态

ALTER INDEX name UNUSABLE

ALTER INDEX name REBUILD

--create index使用

create table tab_btree (id int,name text);

insert into tab_btree values(generate_series(1,100000),md5(random()::text));

create index i_btree on tab_btree using btree(id);

analyze;

explain (costs off) select * from tab_btree where id < 10;

-- alter index name unusable

alter index i_btree unusable;

explain (costs off) select * from tab_btree where id < 10;

-- alter index name rebuild

alter index i_btree rebuild;

explain (costs off) select * from tab_btree where id < 10;

参考资料