TO_DATE function causes table scan

From: <dhart_at_t2systems.com>
Date: 30 Mar 2006 11:33:35 -0800
Message-ID: <1143747215.798872.213180_at_i39g2000cwa.googlegroups.com>

Hi,
[Quoted] [Quoted] I have a query that has to retrieve all the rows that occur after a specific date/time. This is an ad hoc query where the date/time is generated on the fly. It is '03-19-2006 06:26:45' in the example query below. The problem is since I have to use the TO_DATE function to filter by a time the index on the ALM_DATE column doesn't get used and a table scan is used instead.

I have already tried using Function Based Indexes and query hints but I guess my approach is flawed. Any help would be greatly appreciated.

Thanks,
Dave

SELECT

  ALM.ALM_UID AS ZAJ_UID,
  ALM.ALM_DATE AS ZAJ_MODIFY_DATE,
  ITL.ITL_DESCRIPTION,
  ITC.ICL_UID_LEVEL,
  FAC.FAC_DESCRIPTION,
  FGL.FGL_DESCRIPTION,
  LAN.LAN_DESCRIPTION,
  AAK.AAK_DATE AS ZAJ_RESPONSE_DATE_TIME,
  USR.USR_NAME AS ZAJ_RESPONDER_NAME,

  (SELECT MAX(ACS_UID_STATUS) FROM ALARM_ACKNOWLEDGEMENT WHERE ALM_UID_ALARM = ALM_UID) AS ACS_UID_STATUS,   LCT.LCT_LANE_NUMBER,
  USR.USR_UID AS USR_UID_RESPONDENT
FROM ALARM ALM
INNER JOIN INCIDENT INC
    ON ALM.INC_UID_INCIDENT = INC.INC_UID INNER JOIN INCIDENT_TYPE_MLKP ITL
    ON INC.ITL_UID_INCIDENT_TYPE = ITL.ITL_UID INNER JOIN INCIDENT_TYPE_CATEGORY_LKP ITC     ON ITL.ITC_UID_CATEGORY = ITC.ITC_UID LEFT OUTER JOIN FACILITY FAC
    ON INC.FAC_UID_FACILITY = FAC.FAC_UID LEFT OUTER JOIN FACILITY_GROUP_LKP FGL
    ON INC.FGL_UID_FACILITY_GROUP = FGL.FGL_UID LEFT OUTER JOIN LANE LAN
    ON INC.LAN_UID_LANE = LAN.LAN_UID
LEFT OUTER JOIN LANE_CONTROLLER LCT
    ON LAN.LCT_UID_LANE_CONTROLLER = LCT.LCT_UID LEFT OUTER JOIN ALARM_ACKNOWLEDGEMENT AAK     ON ALM.ALM_UID = AAK.ALM_UID_ALARM AND AAK.AAK_HAS_RESPONDED = 1 LEFT OUTER JOIN USER_ACCOUNT USR
    ON AAK.USR_UID_RESPONDENT = USR.USR_UID where ALM_DATE >= TO_DATE('03-19-2006 06:26:45', 'MM/DD/YYYY HH24:MI:SS') Received on Thu Mar 30 2006 - 21:33:35 CEST