VastbaseG100

基于openGauss内核开发的企业级关系型数据库。

Menu

CREATE INDEX

功能描述

在指定的表上创建索引。索引可以用来提高数据库查询性能,但是不恰当的使用将导致数据库性能下降。

本文档只介绍CREATE index语句兼容Oracle的特性,原Vastbase的CREATE INDEX语法未做删除和修改,详见CREATE INDEX

分区索引可以分为本地索引(local index)和全局索引(global index),建立分区索引可以分散数据库的I/O压力。就像分区表一样,分区索引提高了可管理性、可用性、性能和可伸缩性。它们既可以独立分区(全局索引),也可以自动链接到表的分区(本地索引)。

Vastbase在Oracle兼容模式下,支持在创建全局分区索引时支持函数表达式。可参见示例2。在分区表创建全局索引时使用函数表达式。

注意事项

支持函数表达式功能仅在数据库兼容模式为Oracle时支持(即数据库初始化时指定DBCOMPATIBILITY='A')。

语法格式

CREATE [ UNIQUE ] INDEX [ [schema_name.] index_name ] ON table_name [ USING method ]
( {{ column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS LAST ] }[, ...] )
[GLOBAL];

详细语法请参考CREATE INDEX

参数说明

  • UNIQUE

    指定 UNIQUE为唯一值索引。

  • schema_name

    模式名称。

  • index_name

    自定义的索引名称(可由模式名修饰)。

  • table_name

    创建索引所基于的表的名称。

  • USING method

    指定创建索引的方法。

  • column_name

    表中需要创建索引的列的名称(字段名)。如果索引方式支持多字段索引,可以声明多个字段。全局索引最多可以声明31个字段,其他索引最多可以声明32个字段。

  • expression

    创建一个基于该表的一个或多个字段的表达式索引,通常必须写在圆括弧中。如果表达式有函数调用的形式,圆括弧可以省略。表达式索引可用于获取对基本数据的某种变形的快速访问。比如,一个在upper(col)上的函数索引将允许WHERE upper(col) = 'JIM'子句使用索引。在创建表达式索引时,如果表达式中包含IS NULL子句,则这种索引是无效的。此时,建议用户尝试创建一个部分索引。

  • COLLATE collation

    COLLATE子句指定列的排序规则(该列必须是可排列的数据类型)。如果没有指定,则使用默认的排序规则。排序规则可以使用select * from pg_collation;命令从pg_collation系统表中查询,默认的排序规则为查询结果中以default开始的行。

  • opclass

    操作符类的名称。对于索引的每一列可以指定一个操作符类,操作符类标识了索引那一列的使用的操作符。例如一个B-tree索引在一个四字节整数上可以使用int4_ops;这个操作符类包括四字节整数的比较函数。实际上对于列上的数据类型默认的操作符类是足够用的。操作符类主要用于一些有多种排序的数据。例如,用户想按照绝对值或者实数部分排序一个复数。能通过定义两个操作符类然后当建立索引时选择合适的类。

  • ASC|DESC

    创建索引时指定按照升序或者降序进行排序,默认升序。

    • ASC:升序。

    • DESC:降序。

  • GLOBAL

    指定创建的索引为全局索引。

  • NULLS LAST

    指定空值在排序中排在非空值之后,未指定DESC排序时,本选项为默认的。

参数详细内容请参考CREATE INDEX

示例

示例1 创建全局分区索引。

1、创建一个测试分区表。

create table t_part_1(
id int,
col int
)
partition by range (id)
(
partition p1 values less than (10),
partition p2 values less than (20),
partition p3 values less than (30),
partition p4 values less than (maxvalue)
);

2、创建全局分区索引。

create index idx_tp_1 on t_part_1(id) global;

当结果返回如下,则表示全局索引创建成功:

CREATE INDEX

示例2 创建全局分区索引时使用函数表达式。

1、创建list分区表。

create table my_table (name varchar2(40))
PARTITION BY list (name)(
PARTITION p1 values ('Bob'),
PARTITION p2 values ('BoB'),
PARTITION p3 values ('bob'),
PARTITION p4 values('BOB'),
PARTITION p5 values ('bobcat')
);

2、向分区表中插入数据。

insert all
into my_table values ('Bob')
into my_table values ('BoB')
into my_table values ('bob')
into my_table values ('BOB')
into my_table values ('bobcat')
select *from dual;

3、开启参数,进行优化器方法配置,详情参见优化器方法配置

set enable_seqscan to off;
set enable_bitmapscan to off;

4、在分区表上创建全局索引,并在创建时使用upper函数表达式。

create index i_my_table on my_table (upper(name));

5、查看查询语句的执行计划。

explain analyze
select *
from my_table
where upper(name) = upper('bob');

返回结果为如下:

                                 QUERY PLAN
--------------------------------------------------------------------------
 Index Scan using i_my_table on my_table  (cost=0.00..16.31 rows=3 width=
98) (actual time=0.164..0.186 rows=4 loops=1)
   Index Cond: (upper((name)::text) = 'BOB'::text)
 Total runtime: 0.324 ms
(3 rows)

6、删除全局索引。

drop index i_my_table;

7、在分区表上创建全局索引,并在创建时使用lower函数表达式。

create index i_my_table on my_table (lower(name));

8、查看查询语句的执行计划。

explain analyze
select *
from my_table
where lower(name) like lower('bob') || '%';

返回结果为如下:

                                            QUERY PLAN
-----------------------------------------------------------------------------------
 Partition Iterator  (cost=10000000000.00..1000000001957.00 rows=3 width=
98) (actual time=0.021..0.041 rows=5 loops=1)
   Iterations: 5
   ->  Partitioned Seq Scan on my_table  (cost=10000000000.00..1000000001
957.00 rows=3 width=98) (actual time=0.023..0.023 rows=5 loops=5)
         Filter: (lower((name)::text) ~~ 'bob%'::text)
         Selected Partitions:  1..5
 Total runtime: 0.223 ms
(6 rows)

相关链接

分区表优化器方法配置CREATE INDEX