I've got a query that I threw together to serve as a search in a web app.
My first cut of it used "containing" operators for the user's search terms
and was taking 15-20 seconds to run, which didn't surprise me. I figured I
would have to do something smarter than "containing". As I began to play
around with optimization, I came to find that the "containing" operators
weren't my problem. My problem was my "distinct" and my joins.

I prowled through the mailing list last Friday looking for leads, tried a
lot of things, but never could get just the joins, without search criteria,
running in much under about 14 seconds.

select distinct s.StudentSeq, s.LastName, s.FirstName,
s.BirthDate, s.MiddleName, p.StudentID
from student s
join PrfStuID p on s.StudentSeq=p.StudentSeq
join AliasStu a on s.StudentSeq=a.StudentSeq
join SchlHist h on s.StudentSeq=h.StudentSeq
join Facility f on h.FacilityID=f.FacilityID
join District d on f.DistrictCode=d.DistrictCode

This database is old and hoary. I'll include the DDL, at least for the
important fields and indexes. There are no primary keys on any tables,
although every table has a single unique index. The plan looks like this:

PLAN SORT (JOIN (F NATURAL, D INDEX (DISTRICT_), H INDEX
(SCHLHIST_FACILITYID), S INDEX (STUDENT_), P INDEX (PRFSTUID_), A INDEX
(ALIASSTU_STUDENTSEQINDEX)))

Any ideas or leads appreciated.

Thanks,
Kevin

DDL -

CREATE TABLE STUDENT(
STUDENTSEQ integer,
FAMILYSEQ integer,
CURRENTFAMILYSEQ integer,
LASTNAME varchar(50) COLLATE EN_US,
FIRSTNAME varchar(15) COLLATE EN_US,
MIDDLENAME varchar(15) COLLATE EN_US,
SEX varchar(1) COLLATE EN_US,
BIRTHDATE date,
...);
CREATE UNIQUE INDEX STUDENT_ ON STUDENT (STUDENTSEQ);
CREATE INDEX STUDENT_FAMILYINDEX ON STUDENT (FAMILYSEQ);
CREATE INDEX STUDENT_OPT_BIRTHDATE ON STUDENT (BIRTHDATE);

CREATE TABLE PRFSTUID(
STUDENTSEQ integer,
STUDENTID varchar(20) COLLATE EN_US,
SORTORDER smallint,
ORIGINCODE smallint);
CREATE UNIQUE INDEX PRFSTUID_ ON PRFSTUID (STUDENTSEQ);
CREATE INDEX PRFSTUID_STUDENTIDINDEX ON PRFSTUID (STUDENTID);

CREATE TABLE ALIASSTU(
STUDENTID varchar(20) COLLATE EN_US,
ORIGINCODE smallint,
STUDENTSEQ integer,
...);
CREATE UNIQUE INDEX ALIASSTU_ ON ALIASSTU (STUDENTID,ORIGINCODE);
CREATE INDEX ALIASSTU_ORIGINCODEINDEX ON ALIASSTU (STUDENTSEQ,ORIGINCODE);
CREATE INDEX ALIASSTU_STUDENTSEQINDEX ON ALIASSTU (STUDENTSEQ);

CREATE TABLE SCHLHIST(
STUDENTSEQ integer,
DOMID varchar(2) COLLATE EN_US,
DBID smallint,
SHSEQ integer,
FACILITYID varchar(6) COLLATE EN_US,
...);
CREATE UNIQUE INDEX SCHLHIST_ ON SCHLHIST (STUDENTSEQ,DOMID,DBID,SHSEQ);
CREATE INDEX SCHLHIST_COESEQINDEX ON SCHLHIST (COESEQ);
CREATE INDEX SCHLHIST_FACILITYID ON SCHLHIST (FACILITYID);
CREATE INDEX SCHLHIST_MOSTRECENTINDEX ON SCHLHIST
(STUDENTSEQ,RESDATE,FUNDINGDATE,GENDATE);

CREATE TABLE FACILITY(
FACILITYID varchar(6) COLLATE EN_US,
DISPLAYCODE varchar(6) COLLATE EN_US,
FACILITYNAME varchar(55) COLLATE EN_US,
DISTRICTCODE integer,
...);
CREATE UNIQUE INDEX FACILITY_ ON FACILITY (FACILITYID);
CREATE INDEX FACILITY_DISPLAYCODEINDEX ON FACILITY (DISPLAYCODE);
CREATE INDEX FACILITY_DISTRICTCODE ON FACILITY (DISTRICTCODE);
CREATE INDEX FACILITY_DISTRICTINDEX ON FACILITY (DISTRICTCODE,FACILITYNAME);
CREATE INDEX FACILITY_NAMEINDEX ON FACILITY (FACILITYNAME);

CREATE TABLE DISTRICT(
DISTRICTCODE integer,
DISTRICTNAME varchar(50) COLLATE EN_US,
REGIONCODE smallint,
...);
CREATE UNIQUE INDEX DISTRICT_ ON DISTRICT (DISTRICTCODE);
CREATE INDEX DISTRICT_LOCALNAMEINDEX ON DISTRICT (LOCALFLAG,DISTRICTNAME);
CREATE INDEX DISTRICT_NAMEINDEX ON DISTRICT (DISTRICTNAME);


[Non-text portions of this message have been removed]