SQL在DBA手里-变快篇

一、背景

  为什么SQL在DBA手里会这么快，因为：Oracle 总结：为什么不走索引（一）、Oracle 总结：为什么不走索引（二）看过后80%左右的SQL在你手里也很快。
  如下对话，在协助运营出全年汇总数据时，遇到关于TO_DATE与TO_TIMESTAMP两个函数执行效率不同的情况。因为这两个函数对于非DBA来说都是字符格式化日期的函数，卡日期条件时用哪个都OK。
  今天写文章展示一下他们俩个之间确实存在差距，希望对大家有所帮助。

二、测试准备

-- 建表
CREATE TABLE T1
(
  CREATE_DT       DATE,
  CREATE_TS       TIMESTAMP(6)
);
-- 数据量：
SQL> select count(*) from t1;
  COUNT(*)
----------
   6393727
-- 存在一定的空值
SQL> select count(*) from t1 where CREATE_TS is null ;
  COUNT(*)
----------
       549
SQL> select count(*) from t1 where CREATE_DT  is null;
  COUNT(*)
----------
       549
-- 创建索引
SQL> create index IDX_CREATE_DT on t1 (CREATE_dt);
SQL> create index IDX_CREATE_TS on t1 (CREATE_TS);

2.1、TO_TIMESTAMP ：字段类型【DATE】

select count(*) 
from t1 
where CREATE_dt>= TO_TIMESTAMP('2024-03-01','YYYY-MM-DD HH24:MI:SS')
and  CREATE_dt < TO_TIMESTAMP('2024-03-22','YYYY-MM-DD HH24:MI:SS');

扫描方式：TABLE ACCESS FULL

注：因隐式转换“INTERNAL_FUNCTION”，导致全表扫描

2.2、TO_DATE：字段类型【DATE】

select count(*) 
from t1 
where CREATE_dt>=  to_date('2024-03-01','YYYY-MM-DD HH24:MI:SS')
and  CREATE_dt < to_date('2024-03-22','YYYY-MM-DD HH24:MI:SS');

扫描方式：INDEX RANGE SCAN

2.3、TO_DATE：字段类型【TIMESTAMP】

select count(*) 
from t1 
where CREATE_TS>=  to_date('2024-03-01','YYYY-MM-DD HH24:MI:SS')
and  CREATE_TS < to_date('2024-03-22','YYYY-MM-DD HH24:MI:SS');

扫描方式：INDEX RANGE SCAN

2.4、TO_TIMESTAMP：字段类型【TIMESTAMP】

select count(*) 
from t1 
where CREATE_TS>= TO_TIMESTAMP('2024-03-01','YYYY-MM-DD HH24:MI:SS')
and  CREATE_TS < TO_TIMESTAMP('2024-03-22','YYYY-MM-DD HH24:MI:SS');

扫描方式：INDEX RANGE SCAN

三、总结：

• 1、字段类型【DATE】使用TO_TIMESTAMP会因隐式转换导致全表扫描，执行效率差；
• 2、字段类型【TIMESTAMP】使用TO_TIMESTAMP、TO_DATE都会走索引，执行效率相同；
• 3、为了避免隐匿转换导致执行效率差的问题，建议根据字段类型使用对应的函数，不要交给数据库自己转换；