优化一个奇葩表设计上的全表扫描SQL
之前在一个比较繁忙的系统抓到的耗时长、消耗CPU多的一条SQL，如下：
SELECT * FROM Z_VISU_DATA_ALARM_LOG T
WHERE TO_DATE(T.T_TIMESTR, 'MM/DD/YY HH24:MI:SS'))<=(TO_DATE(TO_CHAR(SYSDATE, 'yyyy-mm-dd HH24:mi:ss'),'yyyy-mm-dd HH24:mi:ss') - 1800 * 1000 / 1440/60/1000
1.先看看奇葩的表设计：设计表的同学看来很喜欢varchar2这种数据类型，以及128这个数字。
SQL> desc Z_VISU_DATA_ALARM_LOG
Name Type Nullable Default Comments
-------------- ------------- -------- ------- --------
T_DESC VARCHAR2(128) Y
T_ERRORSTRING VARCHAR2(128) Y
T_KEY VARCHAR2(128) Y
T_POINTNAME VARCHAR2(128) Y
T_PTNAMEEXT VARCHAR2(128) Y
T_PTNAMELONG VARCHAR2(128) Y
T_PTTIME VARCHAR2(128) Y
T_PTTIMEMS VARCHAR2(128) Y
T_RAWSTATUS VARCHAR2(128) Y
T_RETURNSTATUS VARCHAR2(128) Y
T_STATUS VARCHAR2(128) Y
T_TIMEMSSTR VARCHAR2(128) Y
T_TIMESTR VARCHAR2(128) Y
T_UNITS VARCHAR2(128) Y
T_VALSTR VARCHAR2(128) Y
T_VALUE VARCHAR2(128) Y
2.再看看记录数:看到这么多数据再加上表名，猜测这个是一个记录alarm log的大表，真想问一下历史数据归档是怎么做的，虽然明知道得不到答案。
SQL> select count(*) from Z_VISU_DATA_ALARM_LOG;
COUNT(*)
----------
7971800
3.最后看下这个SQL的执行计划：其实不用看执行计划也能猜到是全表扫描。因为SQL写的太随意了！写的时候只是为了完成功能，而不去考虑性能。奇葩的表设计+800W记录+SQL做全表扫描能不慢?能不占用cpu高？
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3652682256
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
--------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 701K| 1683K| 42632 (22)
| 1 | DELETE | Z_VISU_DATA_ALARM_LOG | | |
|* 2 | TABLE ACCESS FULL| Z_VISU_DATA_ALARM_LOG | 701K| 1683K| 42632 (22)
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter((TO_DATE(TO_CHAR(SYSDATE@!,'yyyy-mm-dd HH24:mi:ss'),'yyyy-mm-dd
HH24:mi:ss')-TO_DATE("T"."T_TIMESTR",'MM/DD/YY HH24:MI:SS'))*24*60
15 rows selected
SQL>
那要怎么优化这个SQL呢？
1.表设计的时候，时间字段还是用date或者timestamp吧。BTW，t_desc只有128个字符够吗？
2.根据查询的时间间隔来做分区表，这样对表只需要做ALTER TABLE xxx EXCHANGE就可以完成历史数据归档，也可以降低不必要的IO开销
3.直接优化这个SQL：
(1)首先改写，将=号左右两边做下数学变换+移动一下位置
SELECT * FROM Z_VISU_DATA_ALARM_LOG T
WHERE TO_DATE(T.T_TIMESTR, 'MM/DD/YY HH24:MI:SS'))<=(TO_DATE(TO_CHAR(SYSDATE, 'yyyy-mm-dd HH24:mi:ss'), 'yyyy-mm-dd HH24:mi:ss') - 1800 * 1000 / 1440/60/1000
(2)对 TO_DATE(T.T_TIMESTR, 'MM/DD/YY HH24:MI:SS'))建立函数索引。
这个有个梗，有可能会遇到ORA-01743：only pure functions can be indexed 。
对于这个错误是因为创建TO_DATE(T.T_TIMESTR, 'MM/DD/YY HH24:MI:SS'))的时候因为最后年份YY只取了后两位，这是一个不确定的值，故而会报错。故需要改成对TO_DATE(T.T_TIMESTR, 'yyyy-mm-dd HH24:MI:SS'))创建函数索引，当然sql也需要改。
PS：以下是tom大师对ORA-01743错误的一个说明
One quirk I have noticed with function-based indexes is that if you create one on the built-in
function TO_DATE, it will not succeed in some cases, for example:
ops$tkyte@ORA10GR1> create table t ( year varchar2(4) );
Table created.
ops$tkyte@ORA10GR1> create index t_idx on t( to_date(year,'YYYY') );
create index t_idx on t( to_date(year,'YYYY') )
*
ERROR at line 1:
ORA-01743: only pure functions can be indexed
This seems strange, since we can sometimes create a function using TO_DATE, for example:
ops$tkyte@ORA10GR1> create index t_idx on t( to_date('01'||year,'MMYYYY') );
Index created.
The error message that accompanies this isn’t too illuminating either:
ops$tkyte@ORA10GR1> !oerr ora 1743
01743, 00000, "only pure functions can be indexed"
// *Cause: The indexed function uses SYSDATE or the user environment.
// *Action: PL/SQL functions must be pure (RNDS, RNPS, WNDS, WNPS). SQL
// expressions must not use SYSDATE, USER, USERENV(), or anything
// else dependent on the session state. NLS-dependent functions
// are OK.
We are not using SYSDATE. We are not using the “user environment” (or are we?). No
PL/SQL functions are used, and nothing about the session state is involved. The trick lies in
the format we used: YYYY. That format, given the same exact inputs, will return different
, anytime in the month of May
ops$tkyte@ORA10GR1> select to_char( to_date('2005','YYYY'),
2 'DD-Mon-YYYY HH24:MI:SS' )
3 from dual;
TO_CHAR(TO_DATE('200
--------------------
01-May-2005 00:00:00
the YYYY format will return May 1, in June it will return June 1, and so on. It turns out that
TO_DATE, when used with YYYY, is not deterministic! That is why the index cannot be created: it
would only work correctly in the month you created it in (or insert/updated a row in). So, it is
due to the user environment, which includes the current date itself.
To use TO_DATE in a function-based index, you must use a date format that is unambigu-
ous and deterministic—regardless of what day it is currently.
