Oracle根据to_date(literal)格式使用或忽略索引列

2019-07-3011:27:03数据库教程Comments3,212 views字数 3645阅读模式

使用索引列作为过滤器,将其置于’两个文字值之间’. (该列位于索引的第二个位置,实际上使执行速度变慢;我将在稍后处理).令我困惑的是Oracle(11.2.0.3.0)根据提供给to_date的值的格式和格式字符串使用或忽略所述索引：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/14609.html

这忽略了索引：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/14609.html

  1. SQL> SELECT *
  2. 2 FROM gprs_history_import gh
  3. 3 WHERE start_call_date_time BETWEEN
  4. 4 to_date('20140610 000000','yyyymmdd hh24miss') AND
  5. 5 to_date('20140610 235959','yyyymmdd hh24miss')
  6. 6 /
  7.  
  8. Execution Plan
  9. ----------------------------------------------------------
  10. Plan hash value: 990804809
  11.  
  12. --------------------------------------------------------------------------------------------------------------
  13. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
  14. --------------------------------------------------------------------------------------------------------------
  15. | 0 | SELECT STATEMENT | | 350 | 219K| 242K (1)| 00:56:42 | | |
  16. | 1 | PARTITION RANGE SINGLE| | 350 | 219K| 242K (1)| 00:56:42 | 74 | 74 |
  17. | 2 | PARTITION LIST ALL | | 350 | 219K| 242K (1)| 00:56:42 | 1 | 3 |
  18. |* 3 | TABLE ACCESS FULL | GPRS_HISTORY_IMPORT | 350 | 219K| 242K (1)| 00:56:42 | 220 | 222 |
  19. --------------------------------------------------------------------------------------------------------------
  20.  
  21. Predicate Information (identified by operation id):
  22. ---------------------------------------------------
  23.  
  24. 3 - filter("START_CALL_DATE_TIME"<=TO_DATE(' 2014-06-10 23:59:59','syyyy-mm-dd hh24:mi:ss'))

这个确实使用了索引(注意第4行中日期部分之后的空格)：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/14609.html

  1. SQL> SELECT *
  2. 2 FROM gprs_history_import gh
  3. 3 WHERE start_call_date_time BETWEEN
  4. 4 to_date('20140610 ','yyyymmdd ') AND
  5. 5 to_date('20140610 235959','yyyymmdd hh24miss')
  6. 6 /
  7.  
  8. Execution Plan
  9. ----------------------------------------------------------
  10. Plan hash value: 464458373
  11.  
  12. ---------------------------------------------------------------------------------------------------------------------------------
  13. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
  14. ---------------------------------------------------------------------------------------------------------------------------------
  15. | 0 | SELECT STATEMENT | | 350 | 219K| 2795K (1)| 10:52:15 | | |
  16. |* 1 | FILTER | | | | | | | |
  17. | 2 | PARTITION RANGE ITERATOR | | 350 | 219K| 2795K (1)| 10:52:15 | KEY | 74 |
  18. | 3 | PARTITION LIST ALL | | 350 | 219K| 2795K (1)| 10:52:15 | 1 | 3 |
  19. | 4 | TABLE ACCESS BY LOCAL INDEX ROWID| GPRS_HISTORY_IMPORT | 350 | 219K| 2795K (1)| 10:52:15 | KEY | 222 |
  20. |* 5 | INDEX SKIP SCAN | GPRS_HISTORY_IMPORT_IDX1 | 1 | | 2795K (1)| 10:52:15 | KEY | 222 |
  21. ---------------------------------------------------------------------------------------------------------------------------------
  22.  
  23. Predicate Information (identified by operation id):
  24. ---------------------------------------------------
  25.  
  26. 1 - filter(TO_DATE('20140610 ','yyyymmdd ')<=TO_DATE(' 2014-06-10 23:59:59','syyyy-mm-dd hh24:mi:ss'))
  27. 5 - access("START_CALL_DATE_TIME">=TO_DATE('20140610 ','yyyymmdd ') AND "START_CALL_DATE_TIME"<=TO_DATE(' 2014-06-10
  28. 23:59:59','syyyy-mm-dd hh24:mi:ss'))
  29. filter("START_CALL_DATE_TIME">=TO_DATE('20140610 ','syyyy-mm-dd hh24:mi:ss'))

((1)中的过滤器似乎有点傻,好像Oracle不理解表达式)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/14609.html

同样,这个没有(我删除了尾随空格)：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/14609.html

  1. SQL> SELECT *
  2. 2 FROM gprs_history_import gh
  3. 3 WHERE start_call_date_time BETWEEN
  4. 4 to_date('20140610','yyyymmdd') AND
  5. 5 to_date('20140610 235959','syyyy-mm-dd hh24:mi:ss'))

在空间周围加上引号可以排除索引的使用.文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/14609.html

是什么赋予了？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/14609.html

解决方法

好的 – 我会试一试,这主要是从可用信息中扣除的：为什么Oracle选择不同的执行计划？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/14609.html

在您的第二个查询中,似乎有不寻常的日期格式,优化器不知道结果日期的值是什么.你看到过滤器谓词：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/14609.html

1 – 过滤器(TO_DATE(‘20140610′,’yyyymmdd’)< = TO_DATE('2014-06-10 23:59:59','syyyy-mm-dd hh24：mi：ss')) 这意味着优化器甚至不确定第一个日期是否小于第二个日期！这意味着优化器不知道返回行的数量,并且只使用通用计划而不考虑特定的统计信息.如果你有一个用户定义的函数xyt(),它将返回该范围的日期,这将是相同的.优化器无法知道将导致的日期值 – 这意味着您将获得一个通用的通用计划,对于指定的任何日期范围,该计划应该相当不错. 在第一种情况和第三种情况下,优化器似乎直接理解日期,并可以使用统计信息猜测日期范围内的行数.因此,虽然第二个查询对于优化器,如BETWEEN X和3,但此查询就像BETWEEN 1和3
所以他优化了预测返回行数的查询计划！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/14609.html

奇怪的事情似乎是,查询优化器有一个奇怪的日期格式的问题,可以作为一个错误/改进请求提交…文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/14609.html

但重要的一点：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/14609.html

>全表扫描不一定是BAD计划……使用索引并不总是更快！
>查询计划中的成本绝不与实际执行时间或性能直接相关 – 它是比较SAME QUERY的不同计划的内部度量(因此您无法比较查询等不同查询的成本1,2和3)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/14609.html

基本上,如果从表中返回大量行,则在没有索引访问的情况下进行全表扫描会在很多情况下快得多,尤其是在某些分区上运行时！ – 表扫描仅访问匹配日期范围的相关性 – 因此仅针对相关日期并返回此分区中的所有行.这比查询每一行的索引要快得多,然后通过索引访问来提取行…尝试分析查询 – 分区上的全表扫描速度应该快3倍,IO要少得多.文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/14609.html

文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/14609.html
  • 本站内容整理自互联网，仅提供信息存储空间服务，以方便学习之用。如对文章、图片、字体等版权有疑问，请在下方留言，管理员看到后，将第一时间进行处理。
  • 转载请务必保留本文链接：https://www.cainiaoxueyuan.com/sjk/14609.html

Comment

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定