> Your query:
> 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
>
> results in plan:
> 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)))
>
> and increases runtime by about 30% from about 1.5s to 2s.
>
> Does that give you any clues about what to try next? Thanks for your effort!

1- Please do not top post/reply.

2 - I think that you may need to create an SP to optimize any further. Your views of Views with several Views using the common AGNTROLE and ACTION_ tables between them and then the DISTINCTs and UNIONs make it difficult to see "the forest for the trees" for humans and the FB optimizer.


Sean