如何正确处理查询约束中的日期

发布于 2024-10-15 17:06:24 字数 434 浏览 3 评论 0原文

我目前正在使用以下查询来完成 2010 年 6 月的所有检查：

select inspections.name
from inspections
where
  to_char(inspections.insp_date, 'YYYY') = 2010 and
  to_char(inspections.insp_date, 'MM') = 06;

但这感觉有点尴尬。难道没有更好的方法吗？查看 http://infolab.stanford.edu/~ullman/fcdb /oracle/or-time.html 似乎并非如此。我正在使用 Oracle，如果它有什么区别的话。

谢谢

I am currently using the following query to get all the inspections done on june 2010:

select inspections.name
from inspections
where
  to_char(inspections.insp_date, 'YYYY') = 2010 and
  to_char(inspections.insp_date, 'MM') = 06;

but this feels kinda awkward. Wouldn't there be a better way of doing this? Looking at http://infolab.stanford.edu/~ullman/fcdb/oracle/or-time.html it doesn't seem so. I am using Oracle, if it makes a difference.

Thanks

需要登录才能够评论，你可以免费注册一个本站的账号。

睫毛溺水了 2024-10-22 17:06:25

我喜欢尽可能使用范围比较，因为优化器可以将其用于索引扫描：

select inspections.name
  from inspections
 where inspections.date >= DATE '2010-06-01'
   and inspections.date < DATE '2010-07-01'

I like to use range comparison when possible since this can be used for index-scan by the optimizer:

select inspections.name
  from inspections
 where inspections.date >= DATE '2010-06-01'
   and inspections.date < DATE '2010-07-01'

假面具 2024-10-22 17:06:25

我同意文森特的答案，但为了完整起见，您可以将您的答案简化为：

select inspections.name
from inspections
where
  to_char(inspections.insp_date, 'YYYYMM') = '201006';

甚至可以使用索引，前提是索引是：

create index x on inspections (to_char(insp_date, 'YYYYMM'));

I agree with Vincent's answer, but for completeness you could simplify yours to:

select inspections.name
from inspections
where
  to_char(inspections.insp_date, 'YYYYMM') = '201006';

That could even use an index, provided the index were:

create index x on inspections (to_char(insp_date, 'YYYYMM'));

select inspections.name
from inspections
where
  extract(year from inspections.insp_date) = 2010 and
  extract(month from inspections.insp_date) = 6;

select inspections.name
from inspections
where
  extract(year from inspections.insp_date) = 2010 and
  extract(month from inspections.insp_date) = 6;

迷鸟归林 2024-10-22 17:06:25

另一种选择是

SELECT inspections.name
  FROM inspections
 WHERE TRUNC( inspections.insp_date, 'MM' ) = date '2010-06-01';

从效率的角度来看，

Vincent 的解决方案可能会在 INSP_DATE 上的索引上使用范围扫描（假设该表具有许多个月的数据，因此索引访问将是最有效的计划）。
假设该表有许多个月的数据，我的查询可能会再次在 TRUNC( insp_date, 'MM' ) 上使用基于函数的索引。如果不同月份的行数存在较大差异，则优化器的基数估计使用此基于函数的索引可能比直接 INSP_DATE 索引稍微准确，但这在查询中不太可能很重要就这么简单。但是，如果您开始将此查询嵌套在其他地方，它可能会发挥作用。但是，如果出于其他原因需要对 INSP_DATE 建立索引，则维护两个不同的索引可能会浪费时间和空间。
您的初始查询和 Lev 的查询都可能使用基于复合函数的索引（尽管您希望包含显式 TO_NUMBER 或与字符串而不是数字进行比较以避免隐式转换）。然而，综合指数的维护效率可能是最低的（尽管我们在这里讨论的是相对较小的差异），并且让我觉得它是最不干净的指数替代方案。

Another option would be

SELECT inspections.name
  FROM inspections
 WHERE TRUNC( inspections.insp_date, 'MM' ) = date '2010-06-01';

From an efficiency perspective

Vincent's solution can potentially use a range scan on an index on INSP_DATE (assuming that the table has many months of data such that index access would be the most efficient plan).
My query can potentially use a function-based index on TRUNC( insp_date, 'MM' ) again assuming that the table has many months of data. If there is a large variation in the number of rows for different months, the optimizer's cardinality estimates may be slightly more accurate with this function-based index than they would be on a straight INSP_DATE index but that is highly unlikely to be important in a query this simple. It could come into play, however, if you start nesting this query elsewhere. If you need to index INSP_DATE for other reasons, however, maintaining two different indexes is potentially a time and space waster.
Your initial query and Lev's query could both potentially use a composite function-based index (though you'd want to include an explicit TO_NUMBER or compare to strings rather than numbers to avoid implicit conversions). The composite index, though, is likely to be the least efficient to maintain (though we're talking here about relatively small differences) and strikes me as the least clean of the index alternatives.

~没有更多了~