人大金仓数据库KingbaseES 索引可用性介绍

关键字：

索引；可用；不可用、人大金仓、KingbaseES

索引可用性的概念

一个不可用索引不被DML操作维护，且被优化器忽略，能够提升批量加载的性能，代替删除和重建索引，可以使索引不可用再重建它。不可用索引和索引分区不消耗空间，当你让可用索引变得不可用时，数据库会删除它的索引段。

索引分类

普通索引，默认情况下，数据库创建的B-tree索引。

位图索引，该索引将与之关键值相关联的rowids存储为位图。

分区索引，由包含每个值条目的分区组成，出现在表的索引列中。

函数索引，基于表达式的索引，能够构建一个评估表达式返回值的查询，可能包括内置或用户定义的功能。

CREATE INDEX语句

1、以下语句创建了表test_table的索引test_index，对id列创建索引：

建表：create table test_table(id number,name varchar(100));

插入数据：

insert into test_table values(1,'john');

insert into test_table values(2,'jane');

insert into test_table values(3,'Bob');

insert into test_table values(4,'alice');

insert into test_table values(5,'David');

insert into test_table values(6,'james');

查看表中数据：

select * from test_table;

2、创建普通索引为不可用：

create index test_index on test_table(id) unusable;

查看索引状态：

\d+ test_index

由于索引不可用，强制使用索引扫描被优化器忽略：

set enable_seqscan to off;

set enable_indexonlyscan to on;

explain plan for select /*+ index(test_table test_index) */ * from test_table where id=4;

create extension dbms_xplan;

select * from table(DBMS_XPLAN.DISPLAY);

修改索引状态为可用并查看索引状态：

alter index test_index rebuild;

强制使用test_index索引扫描：

explain plan for select /*+ index(test_table test_index) */ * from test_table where id=4;

select * from table(DBMS_XPLAN.DISPLAY);

结论：当索引不可用时，强制使用索引扫描仍被优化器忽略，当索引可用时，优化器能使用索引进行扫描。

3、创建函数索引为不可用并查看索引状态：

CREATE INDEX upper_ix ON test_table (UPPER(id)) unusable;
\d+ upper_ix

4、创建分区表：

create table sales_test(amount_sold int,price numeric) partition by range (amount_sold)(partition p_table1 values less than (100),partition p_table2 values less than (150),partition p_table3 values less than (200));

insert into sales_test values(40,23);

insert into sales_test values(120,66);

insert into sales_test values(179,90);

创建全局分区索引为不可用并查看索引状态：

CREATE INDEX cost_ix ON sales_test (amount_sold) global unusable;

创建本地分区索引为不可用并查看索引状态：

create index cost_ix_local on sales_test(price) local unusable;

5、创建位图索引为不可用并查看索引状态：

create index sales_index on sales_test USing bitmap(price) unusable;

\d+ sales_index

7、临时表支持创建索引为不可用：

create global temporary table temp_table(name varchar(50));

create index temp_index on temp_table(name) unusable;

\d+ temp_index

ALTER INDEX语句

1、修改索引为可用：alter index index_name rebuild；

修改索引为不可用：alter index index_name unusable;

如修改函数索引upper_ix为可用：

alter index upper_ix rebuild;

\d+ upper_ix

2、对于分区索引，能将其整体重建使其可用：

alter index cost_ix rebuild;

分区索引创建时指定为不可用，每个索引分区也不可用：

查看有哪些分区：

查看分区表每个分区的索引状态：

\d+ sales_test_p_table1

\d+ sales_test_p_table2

\d+ sales_test_p_table3

重建分区索引为可用时，对于分区表上的每个分区上的索引状态都改变为可用：

alter index cost_ix_local rebuild;

\d+ sales_test_p_table1

\d+ sales_test_p_table2

\d+ sales_test_p_table3

修改分区表其中一个分区的索引可用性不影响另一个分区：

alter index sales_test_p_table1_price_idx unusable;

第一个分区的索引为不可用状态：

第二个分区的索引仍为可用状态：

DROP INDEX语句

使用drop index语句删除数据库中的索引。需要保证索引在自己的模式中且具备删除索引的系统权限。

无论索引状态为可用还是不可用，都能使用drop index index_name;将其删除。

删除可用索引：

删除不可用索引：

可见列和不可见列创建索引

概念：不可见列是用户指定的隐藏列，要显示或为一个不可见列分配值，必须显示地指定其名称。SELECT语句不会显示一个不可见列，如果SELECT选择语句列表中包含了不可见列，然后该列将会显示。在VALUE子句中，不能隐式地指定一个不可见列的值插入语句，必须在列列表中指定不可见列。

创建可见列和不可见列：

添加非可见列：alter table test_table add (age int invisible);

添加可见列：alter table test_table add (hobby varchar(50) visible);

不可见列上创建不可用索引: create index test_index on test_table(age) unusable;

重建索引使其可用：alter index test_index rebuild;

可见列上创建不可用索引：

create index test_index_1 on test_table(hobby) unusable;

重建索引使其可用：alter index test_index_1 rebuild;

结论：可见列和不可见列上都支持创建索引并指定其可用性，也能修改可用状态。

物化视图创建索引

创建物化视图：create materialized view test_view as select * from test_table where id > 2;

物化视图上创建不可用索引：create index view_index on test_view(id) unusable;

重建索引使其可用：alter index view_index rebuild;

结论：物化视图上支持创建索引并指定其可用性。

约束与索引可用性的关系

1、创建主键约束：create table test_1(id int primary key,name varchar(50));

修改索引的可用性状态: alter index test_1_pkey unusable;

结论：创建主键约束会创建对应索引，默认为可用，能修改其可用性状态。

2、创建唯一约束：create table test_2(id int, name varchar(50), constraint test2_constraint unique(id));

修改索引的可用性状态：alter index test2_constraint unusable;

结论：创建唯一约束会创建对应索引，默认为可用，能修改其可用性状态。

3、创建外键约束：

create table customers(customer_id serial primary key,customer_name text);

create table orders(order_id serial,order_number text,customer_id int,foreign key(customer_id) references customers(customer_id));

结论：创建外键约束不会自动创建相关索引。

4、创建检查约束：create table test_4(id int,name varchar(50),constraint test4_constraint check (id>0));

结论：创建检查约束不会自动创建相关索引。

索引可用性的存储

1. 索引可用性信息存储在ALL_INDEXES、DBA_INDEXES、USER_INDEXES视图中，其中ALL_INDEXES描述了当前用户可以访问的索引，DBA_INDEXES描述了数据库中所有的索引，USER_INDEXES描述了当前用户拥有的索引。
2. select index_name,status from all_indexes where index_name='TEST_INDEX';

select index_name,status from DBA_indexes where index_name='TEST_INDEX';

select index_name,status from user_indexes where index_name='TEST_INDEX';

alter index test_index unusable;

参考资料

KingbaseES V8手册