Is index performance on a TIMESTAMP(6) column impacted when converting it to a number

If I have an index on a TIMESTAMP(6) column, am I forced to use this column as a TIMESTAMP(6) to get the benefits of the index? Can I convert this column to a number with second precision (YYYYMMDDHHmmss) and still realize the index performance?

For example:

SELECT * FROM MYTABLE
 WHERE TO_NUMBER(TO_CHAR(MYCOLUMN, 'YYYYMMDDHH24MISS'), '99999999999999') > 20110622105738
   AND TO_NUMBER(TO_CHAR(MYCOLUMN, 'YYYYMMDDHH24MISS'), '99999999999999') <= 20150622105738
 ORDER BY MYCOLUMN

This is for Oracle 11

If you have a simple b-tree index on mycolumn, then yes, you would need to avoid calling functions on that column in order to be able to use the index to filter rows. In this case, it would seem to make much more sense to convert your numeric literals to timestamps than to do the reverse

SELECT * FROM MYTABLE
 WHERE my_column > to_timestamp( to_char(20110622105738), 'YYYYMMDDHH24MISS' )
   AND my_column <= to_timestamp( to_char(20150622105738), 'YYYYMMDDHH24MISS' ) 
 ORDER BY MYCOLUMN

Now, you could also create a function-based index

create index idx_my_fbi
    on mytable( TO_NUMBER(TO_CHAR(MYCOLUMN, 'YYYYMMDDHH24MISS'), '99999999999999') );

which your original query could use.

Of course, in either case, whether Oracle actually uses the index will depend on how selective it expects the predicate to be. Since you are returning 4 years worth of data, it seems unlikely that an index would be beneficial even if it was eligible to be used. Unless your table has hundreds of years worth of data in it, a table scan (or a full scan of one or more partitions if the table is partitioned) would seem like a more appropriate query plan.

StackExchange Database Administrators Q#104797, answer score: 6