对韩峰著《SQL优化最佳实践》P7 案例的质疑
事先申明下，我的DB环境是Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production，如果与作者环境不同而导致结论差异则另当别论。
该案例做了一个id为varchar类型的两种查询对比，我模拟了一下。
我是这样建表的：
create table tb_varchar2id( id varchar2(20) primary key, name nvarchar2(20), sal number(5,0) ) insert into tb_varchar2id select rownum,dbms_random.string('*',dbms_random.value(6,20)),dbms_random.value(1000,30000) from dual connect by level<=2000000 order by dbms_random.random
原作中是320万，我机器受限只能弄200万，这个差别不影响作者的思路和我的结论。
建表完提交后，开始第一个查询并观察其执行计划：
SQL> select * from tb_varchar2id where id>='1900000'; 已用时间: 00: 00: 00.00 执行计划 ---------------------------------------------------------- Plan hash value: 3377844066 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1006K| 45M| 3602 (2)| 00:00:44 | |* 1 | TABLE ACCESS FULL| TB_VARCHAR2ID | 1006K| 45M| 3602 (2)| 00:00:44 | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID">='1900000') Note ----- - dynamic sampling used for this statement (level=2)
这里走的也是全表扫描，cost是3602，作者那边不同的是8927.
再看封闭范围的查询及执行计划：
SQL> select * from tb_varchar2id where id between '1900000' and '2000000'; 已用时间: 00: 00: 00.00 执行计划 ---------------------------------------------------------- Plan hash value: 1409398992 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 104K| 4773K| 399 (0)| 00:00:05 | | 1 | TABLE ACCESS BY INDEX ROWID| TB_VARCHAR2ID | 104K| 4773K| 399 (0)| 00:00:05 | |* 2 | INDEX RANGE SCAN | SYS_C0011453 | 104K| | 357 (0)| 00:00:05 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID">='1900000' AND "ID"<='2000000') Note ----- - dynamic sampling used for this statement (level=2)
这把走的是索引范围扫描，cost是399，原著中是5（作者加了索引SYS_C0025295）。
就本例而言，换了查询方式后cost从3602降到399，似乎有了数量级的提升，而原著中8927到5，更是令人瞠目结舌！
很多看官看到这里都要作者所云以为开放(>=)区间查询要次于封闭区间(between)查询了，还以为作者给出了一条可行的优化之路。
但是，下面两条SQL执行结果是不一样的。
select * from tb_varchar2id where id>='1900000';
select * from tb_varchar2id where id between '1900000' and '2000000';
让我们看看它们的数量：
SQL> set autotrace off; SQL> select count(*) from tb_varchar2id where id>='1900000'; COUNT(*) ---------- 999995 已用时间: 00: 00: 00.21 SQL> select count(*) from tb_varchar2id where id between '1900000' and '2000000'; COUNT(*) ---------- 111113 已用时间: 00: 00: 00.00
前者是将近一百万条，后者是十一万条，数据量有一个数量级的差距，cost自然也有一个数量级的差距。
为什么会这样？因为id是varchar2类型，不是number类型，上面SQL在搞字符串比较呢。
就比如运行select * from tb_varchar2id where id>='1900000' and rownum<20;
SQL> select * from tb_varchar2id where id>='1900000' and rownum<20; ID NAME SAL -------------------- ---------------------------------------- ---------- 1900000 YQJQLHKTYVLSZX 12533 1900001 SPLMMLXO 18104 1900002 TYGGIMJCSIWOWUX 6383 1900003 SYYYNRXSL 15890 1900004 GEGQAG 9448 1900005 SFGBZMMPOSEVMNEHQ 20339 1900006 OMQGZZWVEPRWIMTYK 13421 1900007 PWHATEOVY 11135 1900008 TLBRFDWDCEMXFYUXYH 15930 1900009 ZUIQECXIRQXBTO 15961 190001 WKEAMSE 25082 ID NAME SAL -------------------- ---------------------------------------- ---------- 1900010 CMPQCVUBXSMBCMI 17296 1900011 QDPNUNBDXBKV 17393 1900012 OYQBIBRADGE 12009 1900013 VIRWDAKEE 18760 1900014 NQJYHGKREUKGENWH 28990 1900015 IKUUFL 7899 1900016 ACQDSR 1195 1900017 NXIECMAVNE 4208 已选择19行。
连190001都混迹其中，这不应该是符合两个SQL意图的记录。
所以，这是不同SQL在比较性能，这有意义吗？
作者一开头就出这么一个让人费解的地方,或是有些细节没有明写在书里，引起读者疑惑，有点不应该。
--2020年1月31日--