变更列类型对索引有何影响

一、引言

近日，另一省市的同事贴出一张执行计划截图，询问关于索引定位相关问题，为什么绑定参数和常量两者的执行计划不同？（一个走索引定位，另一个全表扫描）
当时从执行计划可以看出，含常量过滤条件的查询语句对过滤列做了类型转换（exp_cast(account)=20230108），很明显可知过滤列类型转成整型数字，违背索引定义时的类型（即过滤条件不匹配索引的列类型），故走全表扫描。在达梦数据库中，整型数字转换的优先级比字符类型高，一旦数字和字符相遇，字符会被自动隐式转换为数字，至此能够解释前面同事所遇执行计划不一致的问题。为有效利用索引，避免类型转换，建议过滤条件表达式的两端操作数类型保持一致，或者使用函数索引。
前面一堆碎言描述，目的是为方便引出同事进一步的提问，修改表列的类型，是否会影响索引失效？？？ 可能大家平时很少关心这个细节问题，不能直接回复答案，那就拿实验论证猜想（1：索引失效，2：索引自动校正），与大家一起分享过程和结论。

---

二、实验论证

2.1 示例构造

-- 测试数据
drop table if exists fstest;
create table fstest (c1 int, c2 char(20));

insert into fstest values(110, '上海');
insert into fstest values(130, '北京');
insert into fstest values(120, '重庆');
insert into fstest values(140, '成都');
insert into fstest select convert(int,rand()*50+140), dbms_random.string('i', 4) from dual connect by level<=5;
commit;

select * from fstest;

-- 字符类型C1字段建普通索引
create index idx_INT_c1 on fstest(c1);

-- 查看执行计划：走索引idx_INT_c1
select * from fstest where c1 = '120'; -- 优先整型数字类型转换exp_cast('120')，索引仍然有效
/*
1   #NSET2: [1, 1, 60] 
2     #PRJT2: [1, 1, 60]; exp_num(3), is_atom(FALSE) 
3       #BLKUP2: [1, 1, 60]; IDX_INT_C1(FSTEST)
4         #SSEK2: [1, 1, 60]; scan_type(ASC), IDX_INT_C1(FSTEST), scan_range[exp_cast('120'),exp_cast('120')]
*/


select * from fstest where c1 = 120; -- 原汁原味的类型匹配，走索引理所当然
/*
1   #NSET2: [1, 1, 60] 
2     #PRJT2: [1, 1, 60]; exp_num(3), is_atom(FALSE) 
3       #BLKUP2: [1, 1, 60]; IDX_INT_C1(FSTEST)
4         #SSEK2: [1, 1, 60]; scan_type(ASC), IDX_INT_C1(FSTEST), scan_range[120,120]
*/

-- 修改C1字段类型：数字类型（int） ---> 字符类型（varchar）
alter table fstest modify (c1 varchar(10));

-- 再次查看执行计划
select * from fstest where c1 = '120';

-- 再次查看执行计划，比较数字型
select * from fstest where c1 = 120;

-- 更严谨检查索引状态
select 
       i.type$,i.subtype$,
       sf_get_schema_name_by_id(i.schid) owner,
       i.name index_name, 
       i.id, 
       t.name table_name, 
       i.pid, 
       i.valid 
from sysobjects i 
join sysobjects t on i.pid = t.id
where i.name='IDX_INT_C1';

2.2 再探函数索引

OLTP生产系统常用普通索引（B+树）、函数索引，位图索引尽量避开。
上一小节已经对普通索引做一次表列类型的变更对索引有何影响的测试，下一步继续深扒函数索引，尽可能周全考虑实验的参考价值。

--- 函数索引
drop table if exists fstest;
create table fstest (c1 varchar(10), c2 char(20));

insert into fstest values(120, '重庆');
insert into fstest values(140, '成都');
insert into fstest 
  select convert(int,rand()*50+140)||'', dbms_random.string('i', 4) 
  from dual connect by level<=4;
commit;

select * from fstest;

-- 建函数索引，like过滤
create index idx_func_c1 on fstest(position('重庆', c1));

select * from fstest where c1 like '%重庆%';

-- 试着变更c1字段类型
alter table fstest modify (c1 int);

--  将函数索引置为失效，再修改列类型
alter index idx_func_c1 UNUSABLE;
alter table fstest modify (c1 int);

-- 修改非相关索引的字段类型
alter table fstest modify (c2 varchar(50));

-- 最后只能删除函数索引，再验证修改表列类型
drop index idx_func_c1;
alter table fstest modify (c1 int);

2.3 实验结论

1. 修改表字段类型，不会对其相关的普通索引产生影响，数据库自动维护更新，索引不会失效，对执行计划影响不大（除关联查询涉及索引连接）。
2. 对已存在函数索引的相关字段不允许被修改其字段类型，必须删除其函数索引后方可修改字段类型，但可以修改非相关索引的其他字段类型。
3. 修改表字段的类型一定要参照达梦数据库手册，不是所有类型都可以相互转换。
4. 类型修改转换有是有诸多限制的，相容且合法的数据内容才可以直接修改，否则重建目标表字段类型，再导回数据。