SQL执行异常系列之——数据转换

生产环境中有个sql执行时间超长，导致was有相关告警，sql中主要有个步骤涉及到格式转换的问题，特此做相关测试记录一下：

 

****************

创建表

****************

drop table tab;
create table tab(id number(20),datetime date);

**************

插入数据

**************

declare
i number;
dd date;
BEGIN
dd := sysdate;
i:= 0;
for x in 1..5000 loop
--if mod(i,100)=0 then
insert into tab(id,datetime) values(i,dd);
dd := dd+1;
i := i+1;
end loop;
END;
/

 

****************

执行查询

****************

select * from tab where id=1285 and to_char(datetime,'yyyymmdd')='20220429';

SQL> select * from tab where id=1285 and to_char(datetime,'yyyymmdd')='20220429';

    ID DATETIME
---------- ------------------
      1285 29-APR-22


Execution Plan
----------------------------------------------------------
Plan hash value: 1995730731

--------------------------------------------------------------------------
| Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |     |     1 |    22 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TAB  |     1 |    22 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=1285 AND TO_CHAR(INTERNAL_FUNCTION("DATETIME"),'yyyym
          mdd')='20220429')

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
      8  recursive calls
      0  db block gets
     40  consistent gets
     18  physical reads
      0  redo size
    599  bytes sent via SQL*Net to client
    519  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      2  sorts (memory)
      0  sorts (disk)
      1  rows processed

***************************
创建普通索引之后再执行查询
***************************

SQL> select * from tab where id=1285 and to_char(datetime,'yyyymmdd')='20220429';

    ID DATETIME
---------- ------------------
      1285 29-APR-22

ID DATETIME
---------- ------------------
1285 29-APR-22

Execution Plan
----------------------------------------------------------
Plan hash value: 4124977589

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| TAB_INDEX | 1 | 22 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("ID"=1285)
filter(TO_CHAR(INTERNAL_FUNCTION("DATETIME"),'yyyymmdd')='2022042
9')

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
2 physical reads
0 redo size
599 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

***************************
创建函数索引之后再执行查询
***************************

create index tab_index on tab(id,to_char(datetime,'yyyymmdd'));

    ID DATETIME
---------- ------------------
      1285 29-APR-22

select * from tab where id=1285 and to_char(datetime,'yyyymmdd')='20220429';



Execution Plan
----------------------------------------------------------
Plan hash value: 4028735706

-----------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows    | Bytes | Cost (%CPU)| Time    |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |        |     1 |    28 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB    |     1 |    28 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN        | TAB_INDEX |     1 |    |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1285 AND TO_CHAR(INTERNAL_FUNCTION("DATETIME"),'yyyymmdd')='20
          220429')

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      4  consistent gets
      7  physical reads
      0  redo size
    599  bytes sent via SQL*Net to client
    519  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

 

可以看到创建复合函数索引之后，少了filter的过程。采用直接acess表。

source：E:\TS\201810\dayu1015\dayu1015\pmpidb1