时间戳

首页 > 解决方案 > Oracle 索引时间戳

sql - Oracle 索引时间戳

问题描述

设想：

我有一个包含 568801 行的表和列上的索引TIM_RECEPT（TIMESTAMP）。索引生成为TRUNC("TIM_RECEPT")。

这张表是在 4 月 19 日用这个查询填满的：

INSERT INTO MY_TABLE SELECT <fields> FROM <tables>

接下来的几天，该表已加载：

INSERT INTO MY_TABLE SELECT <fields> 
FROM <tables> WHERE alias.tim_recept > TRUNC(SYSDATE -1)

问题：

当我在解释计划中使用索引时，返回：

EXPLAIN PLAN FOR
select *
from  MY_TABLE
where trunc(TIM_RECEPT) >= TO_DATE('22/11/2017', 'DD-MM-YYYY')
    and trunc(TIM_RECEPT) <= TO_DATE('26/04/2018', 'DD-MM-YYYY')
;

select * from table(dbms_xplan.display);

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           | 42112 |    13M|  8690   (1)| 00:01:45 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MY_TABLE  | 42112 |    13M|  8690   (1)| 00:01:45 |
|*  2 |   INDEX RANGE SCAN          | IMYTABLE1 | 42112 |       |   114   (0)| 00:00:02 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(TRUNC(INTERNAL_FUNCTION("TIM_RECEPT"))>=TO_DATE(' 2017-11-22 
              00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              TRUNC(INTERNAL_FUNCTION("TIM_RECEPT"))<=TO_DATE(' 2018-04-26 00:00:00', 
              'syyyy-mm-dd hh24:mi:ss'))

但是，如果我更改初始日期并休息一天，则不使用索引：

EXPLAIN PLAN FOR
select *
from  MY_TABLE
where trunc(TIM_RECEPT) >= TO_DATE('21/11/2017', 'DD-MM-YYYY')
    and trunc(TIM_RECEPT) <= TO_DATE('26/04/2018', 'DD-MM-YYYY')
;

select * from table(dbms_xplan.display);

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 42395 |    14M|  8739   (1)| 00:01:45 |
|*  1 |  TABLE ACCESS FULL| MY_TABLE | 42395 |    14M|  8739   (1)| 00:01:45 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TRUNC(INTERNAL_FUNCTION("TIM_RECEPT"))>=TO_DATE(' 
              2017-11-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              TRUNC(INTERNAL_FUNCTION("TIM_RECEPT"))<=TO_DATE(' 2018-04-26 00:00:00', 
              'syyyy-mm-dd hh24:mi:ss'))

对这个问题有任何想法吗？

标签： sqloracleoracle11gquery-performance

解决方案

Oracle 优化器不使用索引这一事实并不是问题。当访问所消耗的资源（在大多数情况下是经过的时间） FULL TABLE SCAN高于使用的替代执行计划INDEX ACCESS（您没有明确说明）时，这是一个问题。

从优化器的角度来看，两个执行计划都很好，并且导致的时间大致相同。

因此，如果这些查询的体验不同，并且实际经过的时间与估计 (01:45) 有很大差异，您（或您的 DBA）应该采取以下步骤：

1）验证表的优化器统计信息

陈旧的统计数据可能会欺骗优化器。尤其是在您在小表上收集统计信息并插入大量记录的情况下。

2) 验证优化器参数和系统统计信息

一些参数特别是DB_FILE_MULTIBLOCK_READ_COUNTFTS与和之间的选择高度相关INDEX ACCES。

类似对系统统计设置有效。

最后的评论

要访问大表的重要部分（比如 100 天中的 50 个），不需要INDEX ACCESS. 检查分区功能，它（范围分区）是为这种访问而设计的。

推荐阅读