最新版(13.8-23.4)的LightDB,在对分区表创建索引时,新增支持指定local|global属性,且新增的这组属性与[compress|nocompress]、[logging|nologging]、[tablespace tbs_name]这3组属性间是顺序无关的,即可以先指定[local|global]属性,也可以先指定[compress|nocompress]或其它属性。注:修改索引alter index暂未支持该属性。
创建索引的语法修改为如下:
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] [schema_name.]name ] ON [ ONLY ] table_name [ USING method ]
( { column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ INCLUDE ( column_name [, ...] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ LOCAL | GLOBAL ]
[ LOGGING | NOLOGGING ]
[ COMPRESS | NOCOMPRESS ]
[ TABLESPACE tablespace_name ]
[ WHERE predicate ]
这个功能使用比较简单,直接上例子:
--创建分区表
CREATE TABLE ORDERS (
O_ORDERKEY NUMBER(20,2) NOT NULL,
O_ORDERDATE DATETIME NOT NULL PRIMARY KEY,
O_NAME VARCHAR(79) NOT NULL) PARTITION BY RANGE (O_ORDERDATE) -- YEAR
(PARTITION PART_T01 VALUES LESS THAN(TO_DATE('2022-01-01', 'YYYY-MM-DD')));
insert into orders values (1,to_date('2021-11-11','yyyy-mm-dd'),'xiaoming');
insert into orders values (2,to_date('2021-01-11','yyyy-mm-dd'),'xiaogang');
insert into orders values (3,to_date('2021-02-11','yyyy-mm-dd'),'xiaoju');
--创建表空间(这样的命名规则不好,这里只是为了演示)
CREATE TABLESPACE local
OWNER oracle
LOCATION 'path1';
CREATE TABLESPACE compress
OWNER oracle
LOCATION 'path2';
--创建索引时指定属性,不同的组合,不同的顺序
create index i_xxxx on ORDERS(O_ORDERDATE) include(O_ORDERKEY) compress;
drop index if exists i_xxxx;
create index i_xxxx on ORDERS(O_ORDERDATE) include(O_ORDERKEY) local;
drop index if exists i_xxxx;
create index i_xxxx on ORDERS(O_ORDERDATE) include(O_ORDERKEY) logging;
drop index if exists i_xxxx;
create index i_xxxx on ORDERS(O_ORDERDATE) include(O_ORDERKEY) nocompress;
drop index if exists i_xxxx;
create index i_xxxx on ORDERS(O_ORDERDATE) include(O_ORDERKEY) global;
drop index if exists i_xxxx;
create index i_xxxx on ORDERS(O_ORDERDATE) include(O_ORDERKEY) nologging;
drop index if exists i_xxxx;
create index i_xxxx on ORDERS(O_ORDERDATE) include(O_ORDERKEY) tablespace local local nologging compress;
drop index i_xxxx;
create index i_xxxx on ORDERS(O_ORDERDATE) include(O_ORDERKEY) tablespace local nologging compress local;
drop index i_xxxx;
create index i_xxxx on ORDERS(O_ORDERDATE) include(O_ORDERKEY) tablespace compress logging compress local;
drop index i_xxxx;
create index i_xxxx on ORDERS(O_ORDERDATE) include(O_ORDERKEY) tablespace compress nologging compress local;
drop index i_xxxx;
--当然同一组属性不能在一个sql中指定2次,以下是反面案例
create index i_xxxx on ORDERS(O_ORDERDATE) include(O_ORDERKEY) local compress global logging tablespace logging;
create index i_xxxx on ORDERS(O_ORDERDATE) include(O_ORDERKEY) local compress global logging;
create index i_xxxx on ORDERS(O_ORDERDATE) include(O_ORDERKEY) logging compress nologging local;
create index i_xxxx on ORDERS(O_ORDERDATE) include(O_ORDERKEY) logging compress nologging local;