[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 --//完全就是简单的中小学数学没有学好。