Re: TO_DATE function causes table scan
Date: Thu, 30 Mar 2006 14:55:48 -0500
Message-ID: <9uadnb4xU7taqrHZRVn-gw_at_comcast.com>
<dhart_at_t2systems.com> wrote in message
news:1143747215.798872.213180_at_i39g2000cwa.googlegroups.com...
: 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')
:
- post the entire explain plan
- post the version
- how are you maintining statistics?
- (maybe actually #1) to test your theory that the TO_DATE is the issue (likely not) try running the query which pl/sql using a DATE datatype variable instead of the TO_DATE expression -- if you get a different plan, then TO_DATE is the issue, if you get the same plan, then it's some other factor affecting the optimizer's decision
++ mcs Received on Thu Mar 30 2006 - 21:55:48 CEST