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
V_SPECIAL_SAMPLES
with the underlying tableSAMPLE
(usingsample_id
) and filter usingsample_date
instead of the function used in the view? Hard to tell/experiment without knowing the cardinality of the data etc.