Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 */
share|improve this question
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 '12 at 15:49
up vote 2 down vote accepted

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.

share|improve this answer
Option 1 or Phil's option above would work. Option 2 and 3 would work, but don't meet the requirements given. – Leigh Riffel May 15 '12 at 17:12

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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