SQL相同变量不同，执行时间相差甚远，什么原因？

朋友发来一个案例，我把它整理了一下：

两个SQL，使用不同的变量，执行计划相同，效率相差上万倍：44秒 vs 4毫秒

SQL简化如下，3表关联，M表REF_NO字段上有主键，S表记录数大概900万，C表是一个很小的表，只有几百条记录：

执行时间44秒时使用的变量：

SELECT

......

FROM PT_TRAN_MSG M, PT_MSG_CODE C, IBPS_RECV_SEND_MSG S

WHERE C.MSG_CODE = M.MSG_CODE

AND TRIM (UPPER (S.MSG_CODE)) = M.MSG_CODE

AND M.REF_NO = '2016081423767756';

执行时间4毫秒时使用的变量：

SELECT

......

FROM PT_TRAN_MSG M, PT_MSG_CODE C, IBPS_RECV_SEND_MSG S

WHERE C.MSG_CODE = M.MSG_CODE

AND TRIM (UPPER (S.MSG_CODE)) = M.MSG_CODE

AND M.REF_NO = '2016081523836033';

两个SQL的执行计划是相同的：

从执行计划来看，sql执行时间44秒比较正常，因为执行计划ID=6的步骤，S表这张大表的全表扫描确实需要一定的时间（E-Time显示的时间也将近39秒），反倒是4毫秒的执行时间有点异常。

补充一个重要的信息是：两个SQL执行后，都没有结果集返回。

因为sqlhc会收集执行时间超过5秒的sql monitor，44秒的这个SQL执行计划会更清晰一些（包含了执行过程中实际返回的行数）：

根据上述证据，可以得出下面推论：

执行时间短的SQL，没有做最后一步的全表扫描。

什么情况才会出现SQL忽略最后的全表扫描步骤呢？

一定是前面两个表join之后的结果集为空。一个空结果集去和另外一个表做join，不需要扫描另一个表就可以返回空结果集了。

而执行时间长的SQL，前面两个表join之后返回了一条记录的结果集（sql monitor中有显示），所以最后一张大表的全表扫描无法避免，虽然最终join结果仍为空，但是大表全表扫描耗时44秒却是正常情况。

验证推论：

根据上述推论，用2016081523836033这个变量（执行时间4毫秒），只做M和C的关联查询，确实没有返回任何结果集，验证了上述推论。

优化方法：

可不可以在S表的MSG_CODE上创建一个索引（函数索引）呢？

这个情况比较特殊：

S表MSG_CODE字段的唯一值为4，字段上没有收集直方图信息，我们也假设数据是平均分布的。如果创建了索引，需要使用hint才能用到该索引（优化器通过计算cost，不会使用选择性这么差的索引）。

M与C两个表join有无结果返回，决定了下一步S表使用索引（需要hint强制）效率高还是使用全表扫描效率高。

如果这个SQL的最终结果集可以通过rownum<=n限制返回少量记录，那么就可以通过使用index的hint来强制使用索引，不管前面两个表join之后是否有结果集，都会得到一个很好的执行效率。

如果不能通过rownum来限制返回结果集，可能会返回225万的结果集，那么就要根据SQL最终有无结果集的频率来决定是否强制使用索引：如果无结果集的情况多，那么就可以强制使用索引，但是有结果集的时候使用这个索引就会比全表扫描效率要差一些；如果有结果集的情况多，那么也没有必要创建索引了。

总结：

看SQL执行效率，执行计划至关重要，根据执行计划可以初步判断SQL的正常执行时间。如果执行时间异常，可以反推出执行计划哪些步骤没有执行。