广告传媒公司网站seo去哪里培训

背景

普通表转分区表，Oracle中的写法习惯索引名会使用大写并用双引号包起来。这导致LightDB 在匹配索引名时提示索引名不存在。

LightDB 23.3.02增量版本对此进行了支持。

示例

准备环境

create database test_oracle with lightdb_syntax_compatible_type  oracle;
\c test_oracle

创建一张普通表

create table tb_list1
(city_id integer not null,city_name varchar2(30) not null,city_state varchar2(20) not null,city_amount integer not null
);

创建索引

create index TB_LIST_I01 on tb_list1(city_amount);
create index TB_LIST_I02 on tb_list1(city_name);

将普通表转为分区表

alter table tb_list1 MODIFY partition by range(city_amount)(partition tb_list1_amount1 values less than (101),partition tb_list1_amount2 values less than (105)) ONLINE UPDATE INDEXES("TB_LIST_I01" global,"TB_LIST_I02" local);

在LightDB 23.03.02之前的版本会提示索引找不到，原因是创建索引时没有带引号，而ALTER时携带了引号。

查看表信息

lightdb@test_oracle=# \d+ tb_list1Partitioned table "public.tb_list1"Column    |     Type     | Collation | Nullable | Default | Storage  | Stats target | Description 
-------------+--------------+-----------+----------+---------+----------+--------------+-------------city_id     | integer      |           | not null |         | plain    |              | city_name   | varchar2(30) |           | not null |         | extended |              | city_state  | varchar2(20) |           | not null |         | extended |              | city_amount | integer      |           | not null |         | plain    |              | 
Partition key: RANGE (city_amount)
Indexes:"tb_list_i01" btree (city_amount)"tb_list_i02" btree (city_name)
Partitions: "tb_list1$p$tb_list1_amount1" FOR VALUES FROM (MINVALUE) TO (101),"tb_list1$p$tb_list1_amount2" FOR VALUES FROM (101) TO (105)