Re: performance issue using SQL Plus

Hi.

As I get it you have an outer join from tblStudents to tblPeople and from there another outer join to zipcode. In that case the ONLY possible order of joining the tables (for optimizer) is:
1. Make tblStudents the driving table. There is NO conditions in WHERE concerning that table (except JOIN) , so FULL TABLE SCAN must be used. 2. Outer Join tblPeople. I think you have an index or PK on PPLID column, so that index may be used, however if you are using cost-based-optimizer and you have large enough SORT_AREA_SIZE and/or HASH_AREA_SIZE then SORT-MERGE or HASH join may be used as well. Only now elimination of unnecessary data ( NOT LIKE 'SMITH%') will be done.
3. Outer join ZIPCODE table on zipzip column.

As I see it the only possible solution ( to avoid FTS on tblStudents) is :
1. Give up on OUTER join from tblStudents to tblPeople. 2. Check that there is an index on tblPeople ( on pplUlname column ).

The query is supposed to look like that:

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

HTH. Michael. Received on Mon Oct 01 2001 - 21:12:48 CEST