1

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
  • 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 '12 at 15:49
2

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. – Leigh Riffel May 15 '12 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.