We have a PeopleSoft (ver7.6) database running Oracle 8.1.7 on AIX
4.3. I have the following query that is giving me a headache.
Basically it is a search view. If I change the where clause from
"Clark,Z%" to a shorter name ie "Lee,C%', I get two completely
different explain paths. The longer length name returns much, much
quicker than the shorter one -- That I can somewhat understand, what I
really have a problem with is that I have a unique name that takes
forever, longer than 20 Minutes to return. The tables/Indexes under
the view are fully analyzed.
SELECT
DISTINCT EMPLID,
EMPL_RCD#,
NAME,
LAST_NAME_SRCH,
SETID_DEPT,
DEPTID,
NID_COUNTRY,
NID_DESCRSHORT,
NATIONAL_ID
FROM
DJC_PERS_SRCH_US
WHERE
OPRCLASS='ALLPANLS' AND
NAME LIKE 'Clark,Z%' ORDER BY NAME, EMPLID
Received on Wed Jun 05 2002 - 17:25:33 CDT
