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:

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


SELECT sample_id,
       TO_CHAR(sample_date, 'mm/dd/yyyy  HH:MI AM') "DATE SAMPLED"
FROM sample
WHERE test_type_id = 5 /* 5: SPECIAL */
  • 1
    Join V_SPECIAL_SAMPLES with the underlying table SAMPLE (using sample_id) and filter using sample_date instead of the function used in the view? Hard to tell/experiment without knowing the cardinality of the data etc.
    – Philᵀᴹ
    May 15, 2012 at 15:49

1 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.

  • Option 1 or Phil's option above would work. Option 2 and 3 would work, but don't meet the requirements given. May 15, 2012 at 17:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.