Filter On Indexed, Formatted DATE

Question

How do I write my calling query (without modifying V_SPECIAL_SAMPLES) to use the index on sample.sample_date (filtering by date first)? I believe it's the TO_CHAR and TO_DATE conversions that prevent the use of the index.

Calling query:

SELECT * 
FROM V_SPECIAL_SAMPLES
WHERE TO_DATE ("DATE SAMPLED", 'mm/dd/yyyy  hh:mi am') > (SYSDATE - 1)

Table: sample

sample_id     NUMBER -- PK
test_type_id  NUMBER -- FK (test_type.test_type_id)
sample_date   DATE   -- indexed

View: V_SPECIAL_SAMPLES

SELECT sample_id,
       TO_CHAR(sample_date, 'mm/dd/yyyy  HH:MI AM') "DATE SAMPLED"
FROM sample
WHERE test_type_id = 5 /* 5: SPECIAL */
ORDER BY "DATE SAMPLED" DESC

Answer 1

You can't use the V_SPECIAL_SAMPLES with an existing index on the sample_date column because of the TO_CHAR in the view. Your options are

1) Don't use the view and go directly against the sample table.

2) Create a function based index on sample_date that does both the TO_CHAR and the TO_DATE

Something like

CREATE INDEX fbi_sample_date
    ON sample( to_date( to_char( sample_date, 
                                 'mm/dd/yyyy HH24:MI AM' ),
                        'mm/dd/yyyy HH24:MI AM' ));

3) Modify the v_special_samples view to have both the sample_date as a VARCHAR2 and to have the raw DATE value and filter on the raw DATE value.