Is it possible to build a Oracle Function Based Index for a TIMESTAMP column cast as DATE within a view?
As Jokke Heikkilä pointed out, you're querying with a timestamp value. This causes the left-hand side of the comparison (i.e. your view's date column) to be implicitly converted to the same data type as the constant. You're effectively doing:
select * FROM SAMPLE_VIEWWHERE cast(TIMESTAMP_COLUMN as timestamp) = timestamp '2010-08-10 12:00:00';...and as you already pointed out an index isn't used when a function is applied to a column.
If you make the right-hand side a date then the index will be used:
explain plan forselect * FROM SAMPLE_VIEWWHERE TIMESTAMP_COLUMN = cast(timestamp '2010-08-10 12:00:00' as date);select plan_table_outputfrom table(dbms_xplan.display('plan_table',null,'all'));---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 9 | 1 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| TIMESTAMP_FBI_IDX | 1 | 9 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$F5BB74E1 / SAMPLE_TABLE@SEL$2 Predicate Information (identified by operation id): --------------------------------------------------- 1 - access(CAST(INTERNAL_FUNCTION("TIMESTAMP_COLUMN") AS date)=CAST(TIMESTAMP' 2010-08-10 12:00:00.000000000' AS date)) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - CAST(INTERNAL_FUNCTION("TIMESTAMP_COLUMN") AS date)[DATE,7] You don't have to cast the right-hand side, it just has to be a date; if you were looking for midnight then you could use a date literal, otherwise you coudl use to_date:
WHERE TIMESTAMP_COLUMN = to_date('2010-08-10 12:00:00', 'YYYY-MM-DD HH24:MI:SS');You sort of said the application you're using only supports dates, so presumably you can just pass a proper date value anyway; although I'm confused by you saying "I must then query this cast column using the timestamp function", and that the application is specifying a timestamp literal in the query it generates, which doesn't seem to tie up.
If you need to be able to query by either the date or timestamp data types then you could include both in your view:
CREATE OR REPLACE VIEW SAMPLE_VIEW ASSELECT TIMESTAMP_COLUMN, CAST(SAMPLE_TABLE.TIMESTAMP_COLUMN as DATE) as DATE_COLUMNFROM TEST_USER.SAMPLE_TABLE;and then query against the relevant column for the data type of the value you have:
WHERE TIMESTAMP_COLUMN = timestamp '2010-08-10 12:00:00'or
WHERE DATE_COLUMN = to_date('2010-08-10 12:00:00', 'YYYY-MM-DD HH24:MI:SS')The first will use timestamp_col_idx, the second will use timestamp_fbi_idx. Your select list would need to change to get the right column as well, of course.
As another potential alternative, if you can change the query being sent from the date-only layer, you could just cast that to timestamp and query the table directly:
select * FROM SAMPLE_TABLEWHERE TIMESTAMP_COLUMN = cast(to_date('2010-08-10 12:00:00', 'YYYY-MM-DD HH24:MI:SS') as timestamp);.. but it depends how the query is being constructed and how you supply the date value. You wouldn't need the view or FBI in that case though.