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!

kd


On Wed, Jul 24, 2013 at 2:31 AM, Svein Erling Tysv�r <
svein.erling.tysvaer@...> wrote:

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

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