sqloracle-databaseoracle11g

Which side gets type cast when the WHERE clause and the column type mismatch?

Let's say we have, some table of data with N number of rows and a VARCHAR column that I want to apply a WHERE clause to

So we have the user enterable argument in the WHERE clause like:

SELECT *
FROM table 
WHERE COL = &userdate -- here, userdate is a number, but COL is VARCHAR

In the case that the user entered an integer, would Oracle be running TO_NUMBER N times (lets say N=999999)

Or would it just convert the user argument to varchar, and then compare it that way?

Or possibly both?
Would this actually have a performance impact that is noticeable or would it be totally negligible?

(Tagged 11g since I am using Oracle and needed to tag it with some kind of version (actually I'm using some later version) but will be trying PostgreSQL soon as well)

Solution

  • See the docs for the actual rules Oracle will apply to such cases; 'Implicit Data Conversion' is about halfway:

    Oracle recommends that you specify explicit conversions, rather than rely on implicit or automatic conversions, for these reasons:
    SQL statements are easier to understand when you use explicit data type conversion functions.
    Implicit data type conversion can have a negative impact on performance, especially if the data type of a column value is converted to that of a constant rather than the other way around.
    Implicit conversion depends on the context in which it occurs and may not work the same way in every case. For example, implicit conversion from a datetime value to a VARCHAR2 value may return an unexpected year depending on the value of the NLS_DATE_FORMAT parameter.
    Algorithms for implicit conversion are subject to change across software releases and among Oracle products. Behavior of explicit conversions is more predictable.
    If implicit data type conversion occurs in an index expression, then Oracle Database might not use the index because it is defined for the pre-conversion data type. This can have a negative impact on performance. ... When comparing a character value with a numeric value, Oracle converts the character data to a numeric value.

    The performance penalty could be high depending on the type of conversion, and the number of rows requiring it, especially in cases it can't use an index while using it would be essential.

    I haven't searched for PostgreSQL but I am fairly sure that the same considerations apply; just search for implicit type conversion rules.