[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
--//完全就是简单的中小学数学没有学好。
--//前几天测试写的例子如下
$ (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
--//完全就是简单的中小学数学没有学好。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· [翻译] 为什么 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