Re: performance issue using SQL Plus

Your outerjoins indicate that there are students that are not people?

Can you avoid the outerjoins? Even by UNIONing a few equijoin statements...

Using a LIKE operator on the the unindexed column tblpeople.pplUlname is forcing a full table scan on tblpeople. Can you possibly use '=' ?

"Diane" <flored_at_mail.conservation.state.mo.us> wrote in message news:52086d5b.0109121108.4a357137_at_posting.google.com...
> I have the following sql statement that takes about 2.5 minutes to
> give me my data. The time it takes is an issue because this sql
> statement is used in my intranet asp page. But it even takes 2.5
> minutes in sql plus.
>
> SELECT stuHunterNum, stuBowNum, pplDOB, pplLName, pplFName,
> pplMName,zipCity, pplZip FROM mdhunted.tblPeople,
> mdhunted.tblStudents, mdhunted.zipcode
> WHERE tblpeople.pplid(+) = tblstudents.pplid AND pplzip = zipzip(+)
> AND
> pplUlname LIKE upper('Smith%') order by tblpeople.pplLName,
> tblpeople.pplFName, tblpeople.pplMName
>
> tblPeople has about 550,000 records and is indexed by pplid, lname,
> fname, and mname.
>
> tblStudents has also about 550,000 records and is indexed by pplid.
>
> It acts like it is using the indexes in tblPeople but not in
> tblStudents and therefore reading each record in tblStudents.
>
> Any suggestions?
Received on Mon Sep 17 2001 - 14:54:23 CEST