Is using index on the date-column beneficial?

Plural · #1 (**permalink**) 12-06-11, 16:47

I have a few tables with date columns such astreatmentDate and scheduleDate.
Assume these tables will be used by receptionists in a clinic.
Although it cannot be transparent to them 100%...when they query for a specific date, will using an index be beneficial?

For example, one of my views:

Code:

CREATE VIEW DoctorSchedule_A AS
SELECT d.*, s.scheduleDate, s.clinic
FROM Doctor d, Schedule s
WHERE d.doctorID=s.doctorID;

Nurse querying for a specific/random date:

Code:

SELECT *
FROM DoctorSchedule_A
WHERE scheduleDate='01-JAN-1970';

SELECT *
FROM DoctorSchedule_A
WHERE scheduleDate BETWEEN '01-JAN-1970' AND '05-JAN-1970';

anacedent · #2 (**permalink**) 12-06-11, 18:03

>will using an index be beneficial?
It depends upon what data exists & what data being returned

With Oracle characters between single quote marks are STRINGS!
'This is a string, 2009-12-31, not a date'
When a DATE datatype is desired, then use TO_DATE() function.

what date is '07-08-09'?