Re: TO_DATE function causes table scan

dhart_at_t2systems.com wrote:
> Hi,
> 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')

What version of Oracle?

Whether Oracle chooses to use an index is not so simple. What percentage of the data meets this criterion? How stale are the statistics? Have you run an Explain Plan? What is the clustering factor?

Oracle may be telling you that your index is not valuable for your query. And it is likely that >= is more critical to what is happening than TO_DATE.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)