Hi Kevin!

>I could use some guidance on how to optimize a query. The short version is
>that I have two queries that run fast, but when I combine them, the result
>is slow. The fast queries (with plans from FlameRobin) are:
>
>select * from COESTUD cs where cs.COESEQ=5000
>PLAN (CS INDEX (COESTUD_))
>
>select * from VIEWABLE_ENROLLMENTS ve
>join COESTUD cs on ve.STUDENTSEQ=cs.STUDENTSEQ
> and ve.DOMID=cs.DOMID
> and ve.DBID=cs.DBID and ve.SHSEQ=cs.SHSEQ
>where ve.USER_ID=1
>
>PLAN JOIN (JOIN (SORT (JOIN (JOIN (VE VF U INDEX (USERS_), VE VF AR INDEX
>(AGNTROLE_, AGNTROLE_), VE VF ACT INDEX (ACTION__, ACTION__)), VE VF F
>NATURAL))
>PLAN SORT (JOIN (SORT (JOIN (JOIN (VE VF AL U INDEX (USERS_), VE VF AL AR
>INDEX (AGNTROLE_, AGNTROLE_), VE VF AL ACT INDEX (ACTION__, ACTION__)), VE
>VF AL D NATURAL)), VE VF F INDEX (FACILITY_DISTRICTCODE))), VE H INDEX
>(SCHLHIST_FACILITYID), CS INDEX (COESTUD_SHSEQINDEX)))
>
>They both run in well under .1s but when I combine them it takes over 1.5s:
>
>select * from VIEWABLE_ENROLLMENTS ve
>join COESTUD cs on ve.STUDENTSEQ=cs.STUDENTSEQ
> and ve.DOMID=cs.DOMID
> and ve.DBID=cs.DBID and ve.SHSEQ=cs.SHSEQ
>where ve.USER_ID=1 and cs.COESEQ=5000
>PLAN JOIN (JOIN (SORT (JOIN (JOIN (VE VF U INDEX (USERS_), VE VF AR INDEX
>(AGNTROLE_, AGNTROLE_), VE VF ACT INDEX (ACTION__, ACTION__)), VE VF F
>NATURAL))
>PLAN SORT (JOIN (SORT (JOIN (JOIN (VE VF AL U INDEX (USERS_), VE VF AL AR
>INDEX (AGNTROLE_, AGNTROLE_), VE VF AL ACT INDEX (ACTION__, ACTION__)), VE
>VF AL D NATURAL)), VE VF F INDEX (FACILITY_DISTRICTCODE))), VE H INDEX
>(SCHLHIST_FACILITYID), CS INDEX (COESTUD_)))

Looking at your plan for the second 'good' query and the 'bad' query, the only difference I see is that the former uses COESTUD_SHSEQINDEX and the latter COESTUD_. Hence, I suspect that COESEQ is less selective than STUDENTSEQ and that in this particular query, COESTUD_ slows things down. Try adding +0 to COESEQ, i.e.

select * from VIEWABLE_ENROLLMENTS ve
join COESTUD cs on ve.STUDENTSEQ=cs.STUDENTSEQ
and ve.DOMID=cs.DOMID
and ve.DBID=cs.DBID and ve.SHSEQ=cs.SHSEQ
where ve.USER_ID=1 and cs.COESEQ+0=5000

HTH,
Set