Are date literals faster than using to_date?

Question

Which of these is faster? Are there any other differences?

A.

SELECT
    *
FROM
    my_table
WHERE
    my_date >= DATE '2013-01-17';

B.

SELECT
    *
FROM
    my_table
WHERE
    my_date >= TO_DATE('17/01/2013','dd/mm/yyyy');

Answer 1

It is highly unlikely to be meaningfully different. If your bottleneck is how quickly you can convert string literals to dates, I would tend to declare victory and toast the first perfectly tuned application in history. Or seriously question what I'm doing wrong that forces so many literals to need to be converted into dates.

Theoretically, it should be a bit faster to use a date literal simply because the format is fixed so you don't have the complexity of first parsing the format mask and then parsing the string. But that assumes that internally Oracle bothered writing the optimized date literal path rather than internally going through the to_date code path (which may depend on the specific Oracle version). And, like I said, if you're in a position where this difference is material, you've either done something very wrong or you've tuned the application so thoroughly that it's almost certainly time to stop.

Answer 2

Yes, date literals are faster. TO_DATE is a bug and should be avoided. (It's not always a bug but it's helpful to always be skeptical of it.)

Based on Stackoverflow questions, date handling is one of the trickiest parts of Oracle. Believing strings and dates are the same thing causes a lot of problems. Understanding the importance of literals and always using the correct data type will indirectly lead to improved performance. For example, Entity-Attribute-Value anti-patterns look more suspicious when developers stop constantly casting strings to dates.

It's true that the time to process a date literal and a TO_DATE are probably the same or irrelevant. It's also true that date literals are internally converted to a TO_DATE anyway. But there's more to performance than that. From a compiler and optimizer perspective, date literals make it easier to understand the code, which leads to improved performance.

Here are two examples of where date literals can significantly improve performance.

Reduced Hard Parsing

National Language Support (NLS) makes it difficult for Oracle to precisely distinguish between variables and literals, when TO_DATE is used.

We may be able to assume that we're coding for English language, in America, with a Gregorian calendar. And we may often (unfairly) take advantage of American coding hegemony and assume other developers understand our system. But the optimizer must assume the worst case and check date strings very carefully.

Take this statement:

select * from dual where trunc(sysdate) = to_date('06-AUG-2014', 'DD-MON-YYYY');

Oracle parses it, and then stores a SQL_ID that is a hash of the entire string. But with dates, the exact same statement has a different meaning if one of the many NLS parameters change. Changing NLS parameters can lead to increased hard-parsing with ambiguous TO_DATE. Date literals always parse the same way.

Better Execution Plans

Similar to the parsing issue, when Oracle can't quite understand the meaning of a predicate it can lead to poor execution plans. For example, in this answer I show has a trivial change in TO_DATE formats can produce changes between static and dynamic partition pruning. That change leads to worse statistics, which leads to a bad execution plan.

Summary

Always use date literals instead of TO_DATE. Performance is usually the same. But date literals are significantly faster in some cases, indirectly lead to better coding practices, are more standardized (ISO 8601), can be checked at compile-time instead of run-time, are less ambiguous to an international audience, and are easier to type.

Using date literals is one of the few coding style decisions you can make that is better in every possible way.