I've got a query optimization mystery I need some help with. The short version is I've got two tables that are very similar, but when I join each of them to a third table, I get different plans - one runs fast and the other runs slow. Here are the queries:
select sp.STUDENTSEQ, a.User_ID from schlhist sp join Advocate a on sp.ADVOCATE_CODE=a.Advocate_Code where a.USER_ID=37 select sp.STUDENTSEQ, a.User_ID from supprog sp join Advocate a on sp.ADVOCATE_CODE=a.Advocate_Code where a.USER_ID=37 The first runs with plan "PLAN JOIN (A INDEX (IDX_ADVOCATE1), SP INDEX (USV_SCHLHIST_ADVOCATE_CODE))" which is fast. The second runs with plan "PLAN JOIN (SP NATURAL, A INDEX (ADVOCATE_))" which is slow. If I change the plan on the second to "PLAN JOIN (A INDEX (IDX_ADVOCATE1), SP INDEX (USV_supprog_ADVOCATE_CODE))" it also runs fast. I don't really understand "statistics" but USV_SUPPROG_ADVOCATE_CODE has 0.029412 and USV_SCHLHIST_ADVOCATE_CODE has 0.000422. That seems like a potentially important difference, but I'm not sure what it means or what to do about it. Here's the ddl (I've chopped out a lot fields I didn't think were relevant): CREATE TABLE ADVOCATE( ADVOCATE_CODE varchar(15), ADVOCATE varchar(20) COLLATE EN_US, TEACHINGCERT varchar(1) COLLATE EN_US, GENDATE timestamp, MODDATE timestamp, CHANGESTATUSFLAG smallint, REGIONCODE smallint, RETIREDCODE varchar(1), USER_ID integer ); CREATE UNIQUE INDEX ADVOCATE_ ON ADVOCATE (ADVOCATE_CODE); CREATE INDEX ADVOCATE_ADVOCATE ON ADVOCATE (ADVOCATE); CREATE INDEX ADVOCATE_REGIONCODE ON ADVOCATE (REGIONCODE); CREATE INDEX IDX_ADVOCATE1 ON ADVOCATE (USER_ID); CREATE TABLE SCHLHIST( STUDENTSEQ integer, DOMID varchar(2) COLLATE EN_US, DBID smallint, SHSEQ integer, FACILITYID varchar(6) COLLATE EN_US, LQMDATE date, RESDATE date, FUNDINGDATE date, ENROLLDATE date, WITHDRAWDATE date, GENDATE timestamp, ADVOCATE_CODE varchar(15), "COMMENT" blob sub_type 1 ); CREATE UNIQUE INDEX SCHLHIST_ ON SCHLHIST (STUDENTSEQ,DOMID,DBID,SHSEQ); CREATE INDEX SCHLHIST_FACILITYIDINDEX ON SCHLHIST (FACILITYID); CREATE INDEX SCHLHIST_MOSTRECENTINDEX ON SCHLHIST (STUDENTSEQ,RESDATE,FUNDINGDATE,GENDATE); CREATE INDEX SCHLHIST_STUDENTSEQINDEX ON SCHLHIST (STUDENTSEQ); CREATE INDEX SCHLHIST_STUFACINDEX ON SCHLHIST (STUDENTSEQ,FACILITYID); CREATE INDEX USV_SCHLHIST_ADVOCATE_CODE ON SCHLHIST (ADVOCATE_CODE); CREATE TABLE SUPPROG( STUDENTSEQ integer, DOMID varchar(2) COLLATE EN_US, DBID smallint, SHSEQ integer, SPKEY varchar(15) COLLATE EN_US, SPCODE varchar(3) COLLATE EN_US, ADVOCATE_CODE varchar(15), OWNER_USER_ID integer ); CREATE UNIQUE INDEX SUPPROG_ ON SUPPROG (STUDENTSEQ,DOMID,DBID,SHSEQ,SPKEY); CREATE INDEX USV_SUPPROG_ADVOCATE_CODE ON SUPPROG (ADVOCATE_CODE); CREATE INDEX USV_SUPPROG_OWNER_USER_ID ON SUPPROG (OWNER_USER_ID);
