Just remove any view you use in your statement and try again.
Fb is very slow when resolving queryes that use views joining other tables
Em 24/07/2013 12:07, "Leyne, Sean" <Sean@...> escreveu:

> **
>
>
>
>
> > 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
>
>
>

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