【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;
--插入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;
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);
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';
t1
对于普通的采用数值类型的字段,范围查询就是正常的索引范围扫描,执行效率很高。
对于文本类型字段的表,范围查询就是对应的全扫,效率比较低。
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';
这里将sql语句由开发区间扫描(>=)修改为封闭区间(between xx and max_value),使得数据在索引局部顺序是"对的"。如果这样还是走全扫,可以进一步细化分段或者采用逐条提取+批绑定的方法。
上面演示的是不好的数据类型导致执行计划异常的实验,可以看出数据结构设计是很重要的,建议在设计初期引入数据库审核,可以避免很多问题。
后面会分享更多DBA方面内容,感兴趣的朋友可以关注下!