[20250106]关于日期输出格式问题2.txt

[20250106]关于日期输出格式问题2.txt

--//前几天测试写的例子如下
$ (echo set feed off head off ; echo -e {D,d}{A,a}{Y,y}"\n" | xargs -IQ echo "select 'Q' str ,to_char(sysdate,'Q') to_c from dual ;")| sqlplus -s -l / as sysdba | sed '/^$/d'
DAY MONDAY
DAy MONDAY
DaY Monday
Day Monday
dAY monday
dAy monday
daY monday
day monday
--//自己可以总结其中的差别:
--//1.格式前面2个字符大写的,输出全部大写。
--//2.格式第1个字符大写的,第2个小写的,输出第1个字符大写,其他小写。
--//3.格式第1个字符小写的,输出全部小写。
--//这些大概属于英语的习惯用法。

--//我也提及最近做优化,生产系统遇到的问题,发现写代码看谓词出现类型如下情况:
TO_CHAR (SCHEDULED_DATE_TIME, 'yyyyMMdd') = TO_CHAR (SYSDATE, 'yyyyMMdd')

OR (START_DATE_TIME >= :startTime AND START_DATE_TIME <= :endTime)
--//视乎是对方的一种编程风格,T大写视乎很容易看出前面start,end是否写错。
--//前面的MM例子也是这样,也许这样容易看清前面的y有4个。

--//我还看到另外一个风格:
TO_CHAR (SCHEDULED_DATE_TIME, 'yyyy-MM-dd') = :date_str

--//当我想当然按照的测试习惯建立TO_CHAR (SCHEDULED_DATE_TIME, 'yyyy-mm-dd')索引时(注意建立小写),发现并不会使用建立的索引。
--//生产系统不方便测试,在测试环境测试看看为什么?

1.环境:
SCOTT@book01p> @ ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.测试:
--//实际上如下例子,就已经说明问题,可以不用做真实的测试:
SCOTT@book01p> select to_char(sysdate,'yyyyMondd'),to_char(sysdate,'yyyyMONdd'),to_char(sysdate,'yyyymondd') from dual ;
TO_CHAR(SYSD TO_CHAR(SYSD TO_CHAR(SYSD
------------ ------------ ------------
2025Jan05    2025JAN05    2025jan05

--//注意看月份格式的输出,Mon,MON,Mon 分别对应Jan,JAN,jan。
--//也就是前面的总结是对的,只不过要将年,月,日分开考虑。

--//换一句话将我给建立TO_CHAR (SCHEDULED_DATE_TIME, 'yyyy-MM-dd')的函数索引。

create table t as select * from all_objects;

SCOTT@book01p> create index i_t_CREATED on t(to_char(CREATED,'yyyyMMdd'));
Index created.


SCOTT@book01p> select * from t where to_char(CREATED,'yyyymmdd') ='20241220';
no rows selected

SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  8wmffwvbr1huy, child number 0
-------------------------------------
select * from t where to_char(CREATED,'yyyymmdd') ='20241220'
Plan hash value: 1601196873
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |   415 (100)|          |      0 |00:00:00.04 |    1482 |   1478 |
|*  1 |  TABLE ACCESS FULL| T    |      1 |    700 |    98K|   415   (1)| 00:00:01 |      0 |00:00:00.04 |    1482 |   1478 |
-----------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / "T"@"SEL$1"
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_CHAR(INTERNAL_FUNCTION("CREATED"),'yyyymmdd')='20241220')
--//不会使用建立的函数索引。

SCOTT@book01p> select * from t where to_char(CREATED,'yyyyMMdd') ='20241220';
no rows selected

SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  a46sjp6pk2y46, child number 0
-------------------------------------
select * from t where to_char(CREATED,'yyyyMMdd') ='20241220'
Plan hash value: 958280510
------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |      1 |        |       |     9 (100)|          |      0 |00:00:00.01 |       2 |      1 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T           |      1 |    700 |    98K|     9   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |      1 |
|*  2 |   INDEX RANGE SCAN                  | I_T_CREATED |      1 |    280 |       |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |      1 |
------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / "T"@"SEL$1"
   2 - SEL$1 / "T"@"SEL$1"
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T"."SYS_NC00028$"='20241220')
--//可以使用函数索引。

--//写成如下也可以使用。
select * from t where to_char(CREATED,'yYyyMMdd') ='20241220';
select * from t where to_char(CREATED,'yYyyMMdD') ='20241220';

3.trunc的问题:
--//生产系统还遇到1个问题:
SYS@127.0.0.1:9106/xtdb/xtdb2> @ sql_id 740408wp7qtwx

-- SQL_ID = 740408wp7qtwx come from shared pool

SELECT A.patient_id patient_id
     , B.name name
     , A.visit_id visit_id
     , A.oper_id oper_id
     , A.operating_room_no bed_no
     , B.inp_no inp_no
  FROM MED_OPERATION_MASTER A
     , MED_PAT_MASTER_INDEX B
 WHERE (A.patient_id             = B.patient_id)
   AND (A.operating_room         = '136')
   AND (A.oper_status            > 0 )
   AND (A.IN_DATE_TIME is not null)
   AND (trunc(IN_DATE_TIME,'DD') = trunc(sysdate,'DD'))
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 order by A.operating_room_no desc;
--//做了格式化处理.注意下划线的执行条件,怎么做这样的查询.真心很无语.....
--//实际上DD是多余,缺省trunc就是截取到日。

SCOTT@book01p> select trunc(sysdate),trunc(sysdate,'DD') from dual ;
TRUNC(SYSDATE)      TRUNC(SYSDATE,'DD')
------------------- -------------------
2025-01-05 00:00:00 2025-01-05 00:00:00
--//开发写DD等于是画蛇添足。

SCOTT@book01p> drop index I_T_CREATED;
Index dropped.

SCOTT@book01p> create index i_t_CREATED on t(trunc(created,'DD'));
Index created.

SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  ah9pbz12r1591, child number 0
-------------------------------------
select * from t where trunc(CREATED,'DD') = trunc(sysdate)
Plan hash value: 958280510
------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |      1 |        |       |     9 (100)|          |      0 |00:00:00.01 |       2 |      1 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T           |      1 |    700 |    98K|     9   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |      1 |
|*  2 |   INDEX RANGE SCAN                  | I_T_CREATED |      1 |    280 |       |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |      1 |
------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / "T"@"SEL$1"
   2 - SEL$1 / "T"@"SEL$1"
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T"."SYS_NC00028$"=TRUNC(SYSDATE@!))
--//可以使用索引。

--//写成如下:
select * from t where trunc(CREATED,'Dd') = trunc(sysdate);
select * from t where trunc(CREATED,'dD') = trunc(sysdate);
select * from t where trunc(CREATED,'dd') = trunc(sysdate);
--//会发现这样居然oracle变得聪明了,居然都可以使用函数索引。

--//仔细查看建立的索引脚本,实际上是:
SCOTT@book01p> @ ddl I_T_CREATED
C300
-----------------------------------------------------------------------------
  CREATE INDEX "SCOTT"."I_T_CREATED" ON "SCOTT"."T" (TRUNC("CREATED",'fmdd'))
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

SCOTT@book01p> create index i_t_CREATED on t(trunc(created,'dD'));
Index created.

SCOTT@book01p> @ ddl I_T_CREATED
C300
-----------------------------------------------------------------------------
  CREATE INDEX "SCOTT"."I_T_CREATED" ON "SCOTT"."T" (TRUNC("CREATED",'fmdd'))
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

--//可以发现1个特点,无论建立使用什么格式,查看索引定义的格式是TRUNC("CREATED",'fmdd'),oracle内部已经将它转化为fmdd。
--//索引对于trunc+dd格式无论怎样写,都可以使用索引。
--//当然如果执行如下走的还是全表扫描:
select * from t where trunc(CREATED) = trunc(sysdate);

4.附带测试:
--//顺便提一下如果建立trunc(created)函数索引,在谓词条件是created = :datestr 或者 created between :D1 and d2 的条件下也
--//可以使用索引。

SCOTT@book01p> Select * from t where created between sysdate-1 and sysdate;
no rows selected

SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  5ggmhc41n6pt8, child number 0
-------------------------------------
Select * from t where created between sysdate-1 and sysdate
Plan hash value: 729645252
----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |      1 |        |       |     9 (100)|          |      0 |00:00:00.01 |       2 |
|*  1 |  FILTER                              |             |      1 |        |       |            |          |      0 |00:00:00.01 |       2 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T           |      1 |     60 |  8640 |     9   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |
|*  3 |    INDEX RANGE SCAN                  | I_T_CREATED |      1 |    315 |       |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / "T"@"SEL$1"
   3 - SEL$1 / "T"@"SEL$1"
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(SYSDATE@!>=SYSDATE@!-1)
   2 - filter(("CREATED">=SYSDATE@!-1 AND "CREATED"<=SYSDATE@!))
   3 - access("T"."SYS_NC00028$">=TRUNC(SYSDATE@!-1,'fmdd') AND "T"."SYS_NC00028$"<=TRUNC(SYSDATE@!,'fmdd'))
--//注意看执行条件的access,filter。
--//oracle会先转换成trunc(created,'dd') between TRUNC(SYSDATE@!-1,'fmdd') AND TRUNC(SYSDATE@!,'fmdd')).
--//然后在过滤"CREATED">=SYSDATE@!-1 AND "CREATED"<=SYSDATE@!符合条件的结果集。

--//我工作中遇到除了trunc外,还有substr函数,建立的索引必须是substr(col,1,N)的格式,必须是从1开始截取,否则普通字段查询不
--//会使用.以及12c出现的standard_hash函数,并且它仅仅适合等值查询,它特别适合超长字段的索引,使用standard_hash后键值变小。
--//参考链接:http://richardfoote.wordpress.com/2013/09/12/12c-indexing-extended-data-types-part-i-a-big-hurt/

5.小结:
--//自己可以总结其中的差别:
--//1.格式前面2个字符大写的,输出全部大写。
--//2.格式第1个字符大写的,第2个小写的,输出第1个字符大写,其他小写。
--//3.格式第1个字符小写的,输出全部小写。
--//4.将年,月,日分开考虑。
--//这些大概属于英语的习惯用法。
--//另外再次提醒开发在谓词条件尽量规避使用函数,避免不必要的偷懒。
TO_CHAR (SCHEDULED_DATE_TIME, 'yyyy-MM-dd') = :date_str
--//改写如下很难吗?
SCHEDULED_DATE_TIME bewteen to_date(:date_str,'yyyy-mm-dd') and to_date(:date_str,'yyyy-mm-dd')+86399/86400
--//完全就是简单的中小学数学没有学好。

posted @   lfree  阅读(2)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· [翻译] 为什么 Tracebit 用 C# 开发
· Deepseek官网太卡,教你白嫖阿里云的Deepseek-R1满血版
· 2分钟学会 DeepSeek API,竟然比官方更好用!
· .NET 使用 DeepSeek R1 开发智能 AI 客户端
· 刚刚!百度搜索“换脑”引爆AI圈,正式接入DeepSeek R1满血版
历史上的今天:
2022-01-05 [20220104]in list 几种写法性能测试.txt
2021-01-05 [20201231]RAC buffer states: XCUR, SCUR, PI,CR.txt
点击右上角即可分享
微信分享提示