Is it possible to build a Oracle Function Based Index for a TIMESTAMP column cast as DATE within a view?

Question

I have searched the Oracle 11g documentation and forums for an example of how to get this view and query to use a function based index and I have come up empty so far. I appreciate any assistance.

I am using an application that does not support the TIMESTAMP datatype queried directly but does support the datatype if it is Cast as DATE within a view. However, I must then query this cast column using the timestamp function. It's a bit of a round trip. The 3rd-party application sends the identical query I am using in SQL*Plus/SQL Developer and have displayed below.

I do not actually require the fractional second granularity of the TIMESTAMP datatype, it is simply required to be used within this database for data vendor reasons.

I understand that a column index is not used when a function is applied to that column. What I cannot accomplish is building a proper function based index to to avoid a full table scan of millions of rows (between 2 million and 600 million rows depending on the table). I would prefer not to use a hint but at this point, anything is better than a full table scan and I am open to all suggestions.

Here is the desired view:

SELECT
CAST(SAMPLE_TABLE.TIMESTAMP_COLUMN as DATE) as TIMESTAMP_COLUMN
FROM TEST_USER.SAMPLE_TABLE;

Here is an example query with an explain plan prefixed:

explain plan for
select * FROM SAMPLE_VIEW WHERE TIMESTAMP_COLUMN = timestamp '2010-08-10 12:00:00';

select plan_table_output
from table(dbms_xplan.display('plan_table',null,'all'));
-----------------------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |   200K|  1562K|   169K  (1)| 00:33:56 |       |       |
|   1 |  PARTITION LIST ALL  |              |   200K|  1562K|   169K  (1)| 00:33:56 |     1 |     6 |
|   2 |   PARTITION RANGE ALL|              |   200K|  1562K|   169K  (1)| 00:33:56 |     1 |    49 |
|*  3 |    TABLE ACCESS FULL | SAMPLE_TABLE |   200K|  1562K|   169K  (1)| 00:33:56 |     1 |   294 |
-----------------------------------------------------------------------------------------------------     
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------    
   1 - SEL$F5BB74E1
   3 - SEL$F5BB74E1 / SAMPLE_TABLE@SEL$2  
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(INTERNAL_FUNCTION(CAST(INTERNAL_FUNCTION("TIMESTAMP_COLUMN") AS DATE))=TIMESTAMP_COLUMN' 
              2010-08-10 12:00:00.000000000')  
Column Projection Information (identified by operation id):
-----------------------------------------------------------   
   1 - "TIMESTAMP_COLUMN"[TIMESTAMP WITH LOCAL TIME ZONE,11]
   2 - "TIMESTAMP_COLUMN"[TIMESTAMP WITH LOCAL TIME ZONE,11]
   3 - "TIMESTAMP_COLUMN"[TIMESTAMP WITH LOCAL TIME ZONE,11]

Of course as soon as I remove the Cast function from the view, the index is used as expected:

Altered view:

SELECT
TIMESTAMP_COLUMN
FROM TEST_USER.SAMPLE_TABLE;

Same Query:

explain plan for
select * FROM SAMPLE_VIEW WHERE TIMESTAMP_COLUMN = timestamp '2010-08-10 12:00:00';

select plan_table_output
from table(dbms_xplan.display('plan_table',null,'all'));
------------------------------------------------------------------------------------
| Id  | Operation        | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                   |     2 |    22 |     3   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TIMESTAMP_COL_IDX |     2 |    22 |     3   (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("TIMESTAMP_COLUMN"=TIMESTAMP' 2010-08-10 12:00:00.000000000')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "TIMESTAMP_COLUMN"[TIMESTAMP WITH LOCAL TIME ZONE,11]

Here is the normal index DDL (TIMESTAMP_COL_IDX) that is used without the cast function. I built this using the GUI of SQL Developer:

CREATE INDEX "TEST_USER"."TIMESTAMP_COL_IDX" ON "TEST_USER"."SAMPLE_TABLE" ("TIMESTAMP_COLUMN") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TEST" ;

Here is the function based index DDL that I am not able to call and is probably written improperly:

CREATE INDEX "TEST_USER"."TIMESTAMP_FBI_IDX" ON "TEST_USER"."SAMPLE_TABLE" (CAST("TIMESTAMP_COLUMN" AS DATE)) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TEST" ;

I have gathered statistics on the table the view is based on, though in 11g I don't think that is "required" after building an index. It did not alter the execution plan.

Thank you.

Edit #1

When I said: "However, I must then query this cast column using the timestamp function. It's a bit of a round trip."...

...I should have clarified the entire workflow I am required to use that is the cause of this scenario. Here is the workflow chronologically:

1. Data is delivered to Oracle database in TIMESTAMP format and must be stored as such.
2. Web publication application does not support TIMESTAMP but allows DATE, so the Cast is used.
3. Web querying application must query as TIMESTAMP or use the timestamp function on DATE.

This is what I meant by the "round trip" the data is going through; the database and web app both utilize TIMESTAMP but the middleware that publishes to the web app cannot, thus causing this expensive cast scenario that leads to a full table scan.

Answer 1

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_VIEW
WHERE 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 for
select * FROM SAMPLE_VIEW
WHERE TIMESTAMP_COLUMN = cast(timestamp '2010-08-10 12:00:00' as date);

select plan_table_output
from 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 AS
SELECT TIMESTAMP_COLUMN,
  CAST(SAMPLE_TABLE.TIMESTAMP_COLUMN as DATE) as DATE_COLUMN
FROM 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_TABLE
WHERE 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.