Q:

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

A:

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.

A:

Indexing Expressions
I believe that the solution you are looking for pertains to building an index on an expression, rather than on the original data itself.  For outside references, you can consult Wikipedia or the Oracle documentation, where the link here contains more info and subsequent links in the 'Function-Based Indexes' section. There are associated costs and benefits to using such an index, so it is up to you to see if it matches your use case.
Contriving a generic example, let's say you had a table order_table, having a date column named order_date and a varchar(250) column name order_info, but you want to frequently query based on the month of the order.  Then in this case, running
SELECT order_info, EXTRACT(month FROM order_date) "month"
FROM order_table
WHERE month = ....

inherently requires extraction of all "month" values to determine if they satisfy the WHERE predicate.  Even if you had an index built on the order_date column, the index cannot help because the month would still need to be calculated for all entries of the index. (Aside: So, I can envision a smart query plan where the optimizer chooses to scan the index, extract the valid months, and then read from the table the relevant RowIDs.  In this case, having an index would help, but not as much as indexing the expression...)
If this query pattern is very common and you can justify the increased costs of maintaining such an index, then it would benefit you to simply index the output of the expression:
CREATE INDEX idx_month_order_date ON order_table (EXTRACT(month FROM order_date));

Then, when you run a query such as above, the planner should be able to make use of the index for much faster querying.
Hope this helps!

