How to avoid Internal_Function by TO_CHAR IN Oracle

Question

I'm using Oracle 12C and I have the following code:

SELECT *
FROM
    (
    SELECT
        *
    FROM
        t.tablea
    WHERE
        name = 'FIS'
) A
LEFT JOIN (
    SELECT
        *
    FROM
        t.tableb
    WHERE
        enabled = 1
) B ON b.id = a.id
       AND TO_CHAR(b.createdate, 'dd-mm-yy hh24:mi') = TO_CHAR(a.createdate, 'dd-mm-yy hh24:mi')

Both a and b createdate are timestamp datatype.

Optimizer return an internal_function at TO_CHAR(b.createdate, 'dd-mm-yy hh24:mi') = TO_CHAR(a.createdate, 'dd-mm-yy hh24:mi') in Execution Plan

If I compare like this: 'AND b.createdate = a.createdate', It will lost 1000 rows that look like this '11-JUN-18 04.48.34.269928000 PM'. And If I change 269928000 to 269000000 It will work

Now, I don't want to using to_char to avoid internal_function(must create Function-based-Index)

Anyone can help me?

Answer 1

If I compare like this: AND b.createdate = a.createdate, It will lost 1000 rows that look like this 11-JUN-18 04.48.34.269928000 PM. And If I change 269928000 to 269000000 It will work

Your values appear to have a fractional seconds component and would have the TIMESTAMP data type. If so, you can use TRUNC( timestamp_value, 'MI' ) to truncate to the nearest minute.

SELECT *
FROM   t.tablea a
       LEFT OUTER JOIN t.tableb b
       ON (   a.createdate >= TRUNC( b.createdate, 'MI' )
          AND a.createdate <  TRUNC( b.createdate, 'MI' ) + INTERVAL '1' MINUTE
          AND a.id         =  b.id
          AND b.enabled    =  1
          )
WHERE  a.name = 'FIS'

This will remove the need to apply a function to one of the two tables (a.createdate in this case but you could swap them).