Oracle 带有日期列数据类型 (存储日期时间) 的表在获取结果时会引起问题，并且在与to_date和to_char一起使用时会引起性能问题。

你好，汤姆，

早上好。

这是我第一次发布问题，我总是从您的帖子中获得大多数问题的帮助。

需要您的帮助来理解尊重性能的好方法。

我有一个带有日期数据类型列 (register_date) 的表，但是它也存储了日期时间戳
1.当我直接从参数数据中的函数传递日期时，由于小时: 分钟: 秒不同。
其中register_date = ai_registerdate -- (变量格式dd-mon-YYYY为nls_parameter)。
或者
where register_date = TO_DATE(ai_registerdate) -- Not giving data as register_date having timestamp which is different f或者entire day.

2. We can use below options but not able to get which one is better 或者c或者rect one.
其中to_Date(register_date) = to_date(ai_registerdate)
或者
其中to_char (注册日期，'YYYYMMDD') = to_char(ai_date，'YYYYMMDD')

I got few post f或者similar question on this query but I did not get any answer what will be impact on perf或者mance using function in where clause and how we can improve perf或者mance of it as this is main column in my query, want index to be hit f或者this column.Consider this table will have 10 Million rec或者ds and each day will have 1 to 3 Million rec或者ds.

Thanks f或者you assistance in advance.

首先，有几件事:

-Oracle数据库没有 “日” 数据类型。日期也总是包括时间。您可以使用以下方法有效地从日期开始删除时间:

trunc(dt)

但这确实是午夜时间的约会。

-NLS设置仅影响显示和隐式转换。只要你比较日期和日期，这些是什么都没关系。

请注意，无论NLS日期格式如何，下面的代码如何从表中选择一行:

create table t (
  d date
);
declare
  dt date;
  procedure count_matching as
    ct pls_integer;
  begin
    select count(*) into ct 
    from   t where d = dt;
  
    dbms_output.put_line('Found ' || ct || ' for date ' || dt);
  end count_matching;
begin
  dt := sysdate;
  insert into t values (dt);
  execute immediate q'|alter session set nls_date_format = 'yyyy-mm-dd'|';
  
  count_matching();  

  execute immediate q'|alter session set nls_date_format = 'dd-MON-yyyy hh24:mi:ss'|';
  
  count_matching();
end;
/

Found 1 for date 2017-11-13
Found 1 for date 13-NOV-2017 03:12:09

所以:

1.如果您有特定时间的列，并且您通过了 “日期” (带有午夜时间的日期)，则要查找这些行，您应该:

-检查列是否大于或等于参数，并且
-严格小于参数加一天

例如:

where register_date >= ai_registerdate and register_date < ai_registerdate + 1

您可以trunc() 参数和列。但这会影响您的索引。下面的更多信息。

2.都 “错” 了!

第一个将具有隐式转换。第二个从日期到字符串进行不必要的转换。

假设ai_registerdate具有数据类型date，您需要的是:

where register_date = ai_registerdate

如果它是字符串，则应根据需要对参数进行日期设置。

where register_date = to_date(ai_registerdate, 'fmt mask')

将函数应用于该列会停止使用该列上的索引的优化器。所以如果你有

create index i on t (dt);

和查询:

where to_date(dt) = ...

您将获得全表扫描，而不是索引范围扫描。所以通常你想避免在where子句中你的列上的函数。如果您确实在索引中应用了一个函数，那么您将希望在SQL中使用完全相同的函数。

您可以在本文中阅读有关索引如何工作的更多信息:

https://blogs.oracle.com/sql/how-to-create-and-use-indexes-in-oracle-database#choose

搜索 “基于功能的索引”，以获取有关此的更多讨论。