Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2011
    Posts
    12

    Unanswered: Is using index on the date-column beneficial?

    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';

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >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'?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2011
    Posts
    12
    So...those were created using the DATE datatype...do I still need to use the TO_DATE('07-08-2009','MON-DD-YYYY')?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >do I still need to use the TO_DATE('07-08-2009','MON-DD-YYYY')?
    yes, it is poor & sloppy programming to depend upon implicit datatype conversion
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    to answer your original question, an index on the date columns if you query using the dates will be beneficial.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •