oracle 主键_【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 sysdbaalter 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方面内容，感兴趣的朋友可以关注下！