常与无常：SQL语句中常量的处理及性能差异解析
杨廷琨，网名 yangtingkun
云和恩墨技术总监，Oracle ACE Director，ACOUG 核心专家
在ITPUB论坛上看到一个有意思的问题：两个SQL语句的功能相同，执行结果相同，连执行计划也完全相同，但是两者的执行时间相差了将近一倍。
其实导致这个问题的原因是很多程序员在SQL时经常会遇到的常量处理问题。借此机会说说如何处理常量才可以使SQL语句运行得更快。
当CBO发现表达式中存在常量或常量表达式时，优化器会在SQL执行之前将表达式的值计算出来，避免在表达式中进行多次计算。但是优化器无法将等号一边的常量移动到等号的另一边。这里所说的等号是泛指，还包括不等号、大于号和小于号等。
举例如下：
COL = 1000 COL = 500 + 500 COL – 500 = 500
对于SQL而言，虽然上面的三个等式是等价的，但是CBO优化器只能将第二个等式转化为第一个等式，而对于第三个等式，优化器是没有办法优化的。
第三个等式由于对列进行了运算，因此不能使用这个列上的常规索引。当然这种情况可以使用函数索引，但是显然函数索引的通用性不好，而且要求函数索引的表达式与查询的表达式要完全匹配。对于这种情况，完全没有必要使用函数索引，而且如果使用函数索引除了增加系统的开销外，没有任何的好处。
CBO不使用索引本身就会极大地影响性能，但这还只是第三个等式的一个缺点而已。即使不考虑索引的因素，上面的第三个等式仍然是效率最低的。
假设上面例子中的COL列上没有索引，这样上面三个查询都必须执行全表扫描操作，这时第三个等式仍然是最费时的。执行全表扫描时，Oracle会根据等式的条件对表中每条记录进行过滤，对于等式1和等式2而言，Oracle进行的只是一个比较的操作。而对于等式3而言，Oracle必须将每条记录的值执行一个“—500”的操作，然后再与500进行比较。简单地说，全表扫描多少记录，就会执行多少次的减法操作，因此当数据量大的时候，必然会带来一定的性能损害。
下面通过一个简单的例子来直观地说明问题，首先构造一个大数据量的测试用表。
SQL> create tablet as select * from dba_objects;
表已创建。
SQL> insert into tselect * from t;
已创建6273行。
SQL> insert into tselect * from t;
已创建12546行。
SQL> insert into tselect * from t;
已创建25092行。
SQL> insert into tselect * from t;
已创建50184行。
SQL> insert into tselect * from t;
已创建100368行。
SQL> insert into tselect * from t;
已创建200736行。
SQL> commit;
提交完成。
SQL> select count(*)from t;
COUNT(*)
----------
401472
用于测试的表是根据DBA_OBJECTS视图创建的，一个普通的小数据库中80%以上的对象都是数据字典对象，而且这些对象是在数据库创建的那一天创建的：
SQL> select trunc(created),count(*) from t group by trunc(created)
2 having count(*) > 10000;
TRUNC(CREATED) COUNT(*) ------------------- ---------- 2004-06-29 00:00:00 358144 2004-11-15 00:00:00 34304
下面分别测试4条SQL语句，这4条SQL语句完全是等价的，都是统计2004年6月29日这一天每个用户下的对象个数。它们的执行计划也完全一样，都是全表扫描，然后分别执行这些语句并记录所需的时间。
为了避免数据缓存带来的误差，每个SQL都执行两次，这里列出的都是第二次执行的时间。
语句1：推荐写法，也是标准的写法。
SQL> set timing on
SQL> select count(*)
2 from t
3 where created >= to_date('2004-06-29 00:00:00','yyyy-mm-dd hh24:mi:ss')
4 and created < to_date('2004-06-30 00:00:00','yyyy-mm-dd hh24:mi:ss')
5 group by owner;
COUNT(*)
---------- 448 99968 223872 25472 8384
已用时间: 00: 00: 00.07
语句2：如果不能避免常量的计算或类型的转化，那么尽量让计算或转化在常量上执行，而不要对列进行计算或转化。
SQL> select count(*)
2 from t
3 where created >= to_date(to_char(to_date('2004-06-2900:00:00', 'yyyy-mm-dd hh24:mi:ss') - 123.456, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-ddhh24:mi:ss') + 123.456
4 and created < to_date(to_char(to_date('2004-06-3000:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1000, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-ddhh24:mi:ss') - 1000
5 groupby owner;
COUNT(*)
---------- 448 99968 223872 25472 8384
已用时间: 00: 00: 00.08
上面这个例子包含了比较复杂的运算和多次数据转化，但是常量的计算是在执行开始之前就计算好的。因此这些复杂的计算实际上只进行了一次，从而对查询带来的影响也是很有限的。
语句3：很多人都喜欢用的一个SQL语句，看上去最简单，通过一个限制条件就实现了SQL功能，但是运行结果如何呢：
SQL> select count(*)
2 from t
3 where to_char(created, 'yyyy-mm-dd') = '2004-06-29'
4 group by owner;
COUNT(*)
---------- 448 99968 223872 25472 8384
已用时间: 00: 00: 01.03
这条SQL与前两条SQL相比，效率简直是天壤之别。执行计划都是全表扫描，而SQL的运行时间差距居然如此之大，是由于这个SQL对列进行了转换操作，导致表中的每条记录都要对CREATED列的值进行TO_CHAR函数的调用，显然无论是对列进行运算，还是函数的调用都是相当耗时的。
语句4：最差的一种写法。
SQL> select count(*)
2 from t
3 where to_char(created, 'yyyy-mm-dd hh24:mi:ss')>= '2004-06-29 00:00:00'
4 and to_char(created, 'yyyy-mm-dd hh24:mi:ss') <='2004-06-29 23:59:59'
5 group by owner;
COUNT(*)
---------- 448 99968 223872 25472 8384
已用时间: 00: 00: 02.04
将语句4和语句3进行对比，就更能说明问题了。由于当前的SQL包含了两个查询条件，所以对于每条记录而言，要对CREATED列进行两次转化，而最终的SQL运行时间也恰好是两倍的关系。
这个例子说明在写SQL语句时，应该尽量避免列的操作。对列进行操作不仅会导致无法使用索引，而且还会增加执行过程中的成本，导致SQL语句执行速度变慢。