[20220124]開發不應該這樣寫sql3.txt
[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更快一些.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2853749/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20220121]開發不應該這樣寫sql2.txtSQL
- [20220109]開發不應該這樣寫SQL語句.txtSQL
- [20231207]開發不應該這樣寫sql4.txtSQL
- 寫這段程式碼的人該不該被開除?
- markdown裡的微積分中dt應該這樣寫
- 面試？或許你應該這樣面試
- 作為一個IT技術開發，應該樹立這樣一個信念！
- Clipped.js | Webpack 應該這樣用JSWeb
- MVP設計模式應該這樣掌握MVP設計模式
- mac 鍵盤應該這樣改鍵Mac
- 一份優秀的資料分析報告應該這樣寫
- 轉行做Java開發應該怎樣學習？Java
- MySQL備份指令碼，應該這麼寫MySql指令碼
- 現在寫 PHP，你應該知道這些PHP
- “掃蕩”、“自動尋路”這樣的簡化遊戲設計該不該存在？遊戲設計
- Yann LeCun：未來的AI晶片應該這樣做Yann LeCunAI晶片
- 年終了，程式設計師應該這樣談加薪！程式設計師
- 企業通關必備，iPaaS應該這樣搭建
- 谷歌重回中國的姿勢應該是這樣谷歌
- 教育小程式原始碼開發熱度不減，教培機構應該抓住這個機遇原始碼
- PHP做api開發離不開簽名驗證，我這樣設計PHPAPI
- 如果開發一個綜合性專案應該怎樣準備
- 當代前端應該怎麼寫這個hello world？前端
- 這就是我心目中獨立遊戲應該有的樣子遊戲
- Python就該這樣學？Python
- 像我這樣的人搞程式開發
- Python遠端登陸伺服器應該這樣玩Python伺服器
- 關於 JavaScript 中的特殊函式，你應該這樣看JavaScript函式
- Remoting服務端和客戶端程式該這樣模式來寫REM服務端客戶端模式
- SQL Story（十）————遊標的應該與不應該 (轉)SQL
- 跨境電商app應該如何開發？APP
- Laravel 路由這樣寫 "{article}"Laravel路由
- 還在為找資料而發愁嗎？看完這篇應該再也不會了
- 這樣delete居然不走索引delete索引
- 為什麼我們應該像蓋房子那樣寫軟體？
- 這樣的開源應用你相信它嗎?
- vue3+ts+vite2環境變數應該這樣使用VueVite變數
- 作為遊戲開發商，應該怎樣去提升遊戲的變現能力？遊戲開發