[20220124]开发不应该这样写sql3.txt

--//在sql优化中,遇到sql语句出现这样的写法:
select
...
(case
                 when basyxx.icu_start is not null then
                  to_date(replace(replace(replace(replace(replace(replace(icu_start,
                                                                          '年',
                                                                          '-'),
                                                                  '月',
                                                                  '-'),
                                                          '日',
                                                          ' '),
                                                  '时',
                                                  ':'),
                                          '分',
                                          ''),
                                  '  ',
                                  ' '),
                          'yyyy-mm-dd hh24:mi')
                 else
                  null
               end) as scs_cutd_inpool_time,
...
from ...;

--//很明显开发想把一个日期格式的字符串转化为日期类型.使用6个replace.我尝试写的更加简洁一些.

1.环境:
SCOTT@78> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.测试:
SCOTT@78> select to_date('2022年01月24日08时01分23秒','YYYY年MM月DD日HH24时MI分SS秒') from dual;
select to_date('2022年01月24日08时01分23秒','YYYY年MM月DD日HH24时MI分SS秒') from dual
                                            *
ERROR at line 1:
ORA-01821: date format not recognized

--//不识别这样的方式.

SCOTT@78> select translate('2022年01月24日08时01分23秒','年月日时分秒' ,'---::') c20 from dual ;
C20
--------------------
2022-01-24-08:01:23
--//日 应该转换为' '.

SCOTT@78> select to_date(translate('2022年1月24日08时1分23秒','年月日时分秒' ,'---::'),'yyyy-mm-dd hh24:mi:ss') c20 from dual ;
C20
--------------------
2022-01-24 08:01:23
--//不过转换没有问题.这样讲我这样写更加灵活.

SCOTT@78> select to_date(translate('2022年1月09日08时1分23秒','年月日时分秒' ,'---::'),'yyyy-mm-dd hh24:mi:ss') c20 from dual ;
C20
--------------------
2022-01-09 08:01:23

SCOTT@78> select to_date(translate('2012年06月22日 12时','年月日时分秒' ,'---::'),'yyyy-mm-dd hh24:mi') c20 from dual ;
C20
--------------------
2012-06-22 12:00:00

--//这样写我仅仅使用两个函数.至于效率如何简单测试看看.
create table t1 as select '2022年1月24日08时1分' v1 from dual connect by level<1e6;

SCOTT@78> create table t1 as select '2022年1月24日08时1分' v1 from dual connect by level<1e6;
Table created.

$ cat aa.txt
set timing on
select sysdate from dual;
set term off
select to_date(translate(v1,'年月日时分' ,'-- : '),'yyyy-mm-dd hh24:mi:ss') c20 from t1 ;
--select to_date(replace(replace(replace(replace(replace(replace(v1,
--                                                                          '年',
--                                                                          '-'),
--                                                                  '月',
--                                                                  '-'),
--                                                          '日',
--                                                          ' '),
--                                                  '时',
--                                                  ':'),
--                                          '分',
--                                          ''),
--                                  '  ',
--                                  ' '),
--                          'yyyy-mm-dd hh24:mi') from t1;
set term on
select sysdate from dual;


SCOTT@78> @ aa.txt
SYSDATE
-------------------
2022-01-24 09:28:20
Elapsed: 00:00:00.01
SYSDATE
-------------------
2022-01-24 09:28:37
Elapsed: 00:00:00.01

--//17秒.

SCOTT@78> @ aa.txt
SYSDATE
-------------------
2022-01-24 09:30:14

Elapsed: 00:00:00.02
SYSDATE
-------------------
2022-01-24 09:30:30
Elapsed: 00:00:00.01

--//16秒.视乎使用replace更快一些,不过从简洁性讲使用translate更好看一些.
--//取消后面的:ss,修改如下测试:
select to_date(translate(v1,'年月日时分' ,'-- : '),'yyyy-mm-dd hh24:mi') c20 from t1 ;
--//测试结果16秒.换一个方式测试:

SCOTT@78> @ tpt/sl all
alter session set statistics_level = all;
Session altered.

SCOTT@78> set timing on
SCOTT@78> with a as (select /*+ MATERIALIZE */ to_date(translate(v1,'年月日时分' ,'-- : '),'yyyy-mm-dd hh24:mi') c20 from t1) select count(distinct c20) from a;
COUNT(DISTINCTC20)
------------------
                 1

Elapsed: 00:00:03.47

SCOTT@78> with a as (select /*+ MATERIALIZE */ to_date(replace(replace(replace(replace(replace(replace(v1,'年', '-'), '月', '-'), '日', ' '), '时', ':'), '分', ''), '  ', ' '), 'yyyy-mm-dd hh24:mi') c20 from t1)
  2  select count(distinct c20 ) from a ;
COUNT(DISTINCTC20)
------------------
                 1

Elapsed: 00:00:03.55

--//视乎这样不使用函数,这样测试不行.
--//在toad下测试,视乎两者差别不大.

SCOTT@78> select to_date(translate(v1,'年月日时分' ,'-- : '),'yyyy-mm-dd hh24:mi') c20 from t1 group by to_date(translate(v1,'年月日时分' ,'-- : '),'yyyy-mm-dd hh24:mi');
C20
--------------------
2022-01-24 08:01:00
Elapsed: 00:00:02.33

SCOTT@78> @ dpc '' 'outline projection' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  fz9796dx1vajq, child number 0
-------------------------------------
select to_date(translate(v1,'年月日时分' ,'-- : '),'yyyy-mm-dd hh24:mi') c20
from t1 group by to_date(translate(v1,'年月日时分' ,'-- : '),'yyyy-mm-dd
hh24:mi')
Plan hash value: 136660032
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |  1024 (100)|          |      1 |00:00:02.33 |    3604 |       |       |          |
|   1 |  HASH GROUP BY     |      |      1 |      1 |    21 |  1024   (3)| 00:00:13 |      1 |00:00:02.33 |    3604 |    48M|  9689K|  929K (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |    999K|    20M|   999   (1)| 00:00:12 |    999K|00:00:00.10 |    3604 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T1@SEL$1
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      USE_HASH_AGGREGATION(@"SEL$1")
      END_OUTLINE_DATA
  */
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - TO_DATE(TRANSLATE("V1",'年月日时分','-- : '),'yyyy-mm-dd hh24:mi')[8]
   2 - "V1"[CHARACTER,20]

SCOTT@78> select to_date(replace(replace(replace(replace(replace(replace(v1,'年', '-'), '月', '-'), '日', ' '), '时', ':'), '分', ''), '  ', ' '), 'yyyy-mm-dd hh24:mi') c20 from t1
          group by to_date(replace(replace(replace(replace(replace(replace(v1,'年', '-'), '月', '-'), '日', ' '), '时', ':'), '分', ''), '  ', ' '), 'yyyy-mm-dd hh24:mi') ;
C20
--------------------
2022-01-24 08:01:00
Elapsed: 00:00:02.45

--//可以看出确实差别不大,好像使用translate更快一些.
SCOTT@78> select to_date(translate(v1,'年月日时分秒' ,'-- :: '),'yyyy-mm-dd hh24:mi:ss') c20 from t1 group by to_date(translate(v1,'年月日时分秒' ,'-- :: '),'yyyy-mm-dd hh24:mi:ss');
C20
--------------------
2022-01-24 08:01:00
Elapsed: 00:00:03.16

--//你可以看出如果我加入多了秒的替换,执行时间增加不少.

3.总结:
我开始测试加入秒的转换,测试使用translate确实慢一点点,没有注意这个细节,取消后两者基本一致.很明显我使用translate更加清晰明了.
我总觉觉得许多开发认为逻辑正确就ok了,丢失许多基本的算法与基础知识的东西.

SCOTT@78> select v1 c20 from t1 group by v1;
C20
--------------------
2022年1月24日08时1分
Elapsed: 00:00:00.32

--//仔细看可以发现原始语句多比我做了1次replace.视乎replace更快一些.