深入理解Oracle索引(15):日期转换函数的格式参数大小写规则对函数索引的影响
来源:互联网 发布:农村淘宝 大米 编辑:程序博客网 时间:2024/04/30 04:42
规则如下:
依据这个规则、只有 3 种输出:大写、小写、首字母大写
测试如下:
sys@ORCL> select to_char(sysdate,'Month') from dual;TO_CHAR(SYSDATE,'MONTH')------------------------------------Junesys@ORCL> select to_char(sysdate,'MOnth') from dual;TO_CHAR(SYSDATE,'MONTH')------------------------------------JUNEsys@ORCL> select to_char(sysdate,'month') from dual;TO_CHAR(SYSDATE,'MONTH')------------------------------------june
下面做个测试、确认这个规则对函数索引的影响:
sys@ORCL> drop table t purge;Table dropped.sys@ORCL> create table t as select object_id,sysdate+rownum as create_date from dba_objects where rownum<=5000;Table created.sys@ORCL> analyze table t compute statistics;Table analyzed.sys@ORCL> set autot trace expsys@ORCL> select * from t where to_char(create_date,'yyyy-mm-dd')='2011-06-02';Execution Plan----------------------------------------------------------Plan hash value: 1601196873--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 50 | 500 | 5 (20)| 00:00:01 ||* 1 | TABLE ACCESS FULL| T | 50 | 500 | 5 (20)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(TO_CHAR(INTERNAL_FUNCTION("CREATE_DATE"),'yyyy-mm-dd')='20 11-06-02')sys@ORCL> create index idx_t on t (to_char(create_date,'YYYY-MM-DD'));Index created.sys@ORCL> analyze index idx_t validate structure;Index analyzed.sys@ORCL> select * from t where to_char(create_date,'yyyy-mm-dd')='2013-06-02';Execution Plan----------------------------------------------------------Plan hash value: 1601196873--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 50 | 500 | 5 (20)| 00:00:01 ||* 1 | TABLE ACCESS FULL| T | 50 | 500 | 5 (20)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(TO_CHAR(INTERNAL_FUNCTION("CREATE_DATE"),'yyyy-mm-dd')='20 13-06-02')sys@ORCL> set autot offsys@ORCL> create index idx_t_001 on t (to_char(create_date,'yyyy-mm-dd'));Index created.sys@ORCL> analyze index idx_t_001 validate structure;Index analyzed.sys@ORCL> select * from t where to_char(create_date,'yyyy-mm-dd')='2013-06-02';no rows selectedsys@ORCL> set autot trace exp第一种情况:小写sys@ORCL> select * from t where to_char(create_date,'yyyy-mm-dd')='2013-06-02';Execution Plan----------------------------------------------------------Plan hash value: 512271049-----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 50 | 500 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| T | 50 | 500 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_T_001 | 20 | | 1 (0)| 00:00:01 |-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access(TO_CHAR(INTERNAL_FUNCTION("CREATE_DATE"),'yyyy-mm-dd')='2013-06-02' )第二种情况:首字母大写sys@ORCL> select * from t where to_char(create_date,'yyyy-mm-Dd')='2013-06-02';Execution Plan----------------------------------------------------------Plan hash value: 1601196873--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 50 | 500 | 5 (20)| 00:00:01 ||* 1 | TABLE ACCESS FULL| T | 50 | 500 | 5 (20)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(TO_CHAR(INTERNAL_FUNCTION("CREATE_DATE"),'yyyy-mm-Dd')='20 13-06-02')第三种情况:大写sys@ORCL> select * from t where to_char(create_date,'yyyy-mm-DD')='2013-06-02';Execution Plan----------------------------------------------------------Plan hash value: 1601196873--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 50 | 500 | 5 (20)| 00:00:01 ||* 1 | TABLE ACCESS FULL| T | 50 | 500 | 5 (20)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(TO_CHAR(INTERNAL_FUNCTION("CREATE_DATE"),'yyyy-mm-DD')='20 13-06-02')
- 深入理解Oracle索引(15):日期转换函数的格式参数大小写规则对函数索引的影响
- 深入理解Oracle索引(18):函数索引的陷阱以及如何避免索引被污染
- 深入理解Oracle索引(18):函数索引的陷阱以及如何避免索引被污染
- Oracle隐式数据类型转换对索引的影响
- mysql 类型转换对索引的影响
- DBISAM 索引大小写敏感对查询的影响
- 【Oracle】-【ROWNUM与索引】-索引对ROWNUM检索的影响
- Oracle之函数索引之各种列的函数转换
- 不同规则的中文分词对Lucene索引的影响
- 不同规则的中文分词对Lucene索引的影响
- 不同规则的中文分词对Lucene索引的影响
- 不同规则的中文分词对Lucene索引的影响
- 深入理解Oracle索引(17):Cost 值相同 CBO 对索引的选择
- Oracle直方图的深入理解-和测试-直返图直接影响sql是否走正确的索引?
- 【Oracle】-【COMMIT对索引的影响】-从trace看COMMIT对索引的影响
- 深入探讨Oracle函数索引
- oracle 基于函数的索引
- Oracle基于函数的索引
- COM技术初探(一)
- default argument given of parameter 的问题
- 插入新结点到指定结点的指定位置
- 使用过滤器来解决中文乱码
- 内中断
- 深入理解Oracle索引(15):日期转换函数的格式参数大小写规则对函数索引的影响
- 第一篇文章,不写技术
- FMDB_demo
- android中实现简单双方聊天的代码
- 【audio play音频】 android 简单的MP3播放器代码
- 新型的按键扫描程序
- hibernate学习笔记13--级联设置
- 点点滴滴0
- 安联锐视H.264格式监控数据恢复程序 1.0