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);