【oracle实验】--测试主键字段类型对执行计划的影响

概述

一般我们主键类型都为数字的,但是还是有些奇葩系统设计主键字段类型为varchar,下面看看这两个类型对执行计划的影响。


1、准备测试数据

grant select on dba_objects to scott;
conn scott/tiger;
create table t1 as select * from dba_objects where 1=0;
alter table t1 add id int primary key;
create table t2 as select * from dba_objects where 1=0;
alter table t2 add id varchar2(10) primary key;

【oracle实验】--测试主键字段类型对执行计划的影响

--插入300万数据

insert into t1 select 'test','test','test',rownum,rownum,'test',sysdate,sysdate,'test','t','t','t','t',rownum,'test',rownum from dual connect by rownum<=3000000;
insert into t2 select 'test','test','test',rownum,rownum,'test',sysdate,sysdate,'test','t','t','t','t',rownum,'test',rownum from dual connect by rownum<=3000000;
commit;

【oracle实验】--测试主键字段类型对执行计划的影响


2、收集统计信息

execute dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T1',cascade=>true,estimate_percent=>100);

execute dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T2',cascade=>true,estimate_percent=>100);

【oracle实验】--测试主键字段类型对执行计划的影响

3、查看执行计划

conn / as sysdba
alter system flush shared_pool;
conn scott/tiger;
set autotrace traceonly;
set line 1000;
select * from t1 where id>=2999990;
select * from t2 where id>='2999990';

【oracle实验】--测试主键字段类型对执行计划的影响

t1

对于普通的采用数值类型的字段,范围查询就是正常的索引范围扫描,执行效率很高。

【oracle实验】--测试主键字段类型对执行计划的影响

对于文本类型字段的表,范围查询就是对应的全扫,效率比较低。


4、结论

1、字符类型在索引中是乱序的,字符类型的排序方式与我们的预期不同,注意select * from t2 where id>='2999990'执行返回777788行,不是10条记录,这是表设计时需要注意的。

2、字符类型还导致了聚簇因子很大,原因是插入顺序与排序顺序不同,其实就是按照数字类型插入(1...3000000),按字符类型('1'....'3000000')t排序

3、在对字符类型使用大于运算符时,会导致优化器认为需要扫描索引大部分数据且聚簇因子很大,最终选择全表扫描方式。

ps:解决办法

select * from t2 where id between '2999990' and '3000000';

【oracle实验】--测试主键字段类型对执行计划的影响

这里将sql语句由开发区间扫描(>=)修改为封闭区间(between xx and max_value),使得数据在索引局部顺序是"对的"。如果这样还是走全扫,可以进一步细化分段或者采用逐条提取+批绑定的方法。


上面演示的是不好的数据类型导致执行计划异常的实验,可以看出数据结构设计是很重要的,建议在设计初期引入数据库审核,可以避免很多问题。

后面会分享更多DBA方面内容,感兴趣的朋友可以关注下!

【oracle实验】--测试主键字段类型对执行计划的影响

相关推荐