Posted by rcamarda on 12/02/06 18:37

I have indexes build on all fields I use for where or joins. Engine
tuning could not recommend improvments.
Student_Status lives in the f_bi_student_summary_session table.
admrep_id lives in d_bi_student.
Your right about the left outer joins; inner joins would work. I should
only have registrations for students that exist in the student
dimension.
Ill do some testing using the inner join instead.

Your suggestion for the new SQL: Is your point that since I am using a
where clause, it is redundant to have the additional group by's?
Thanks for you input
Rob

Ed Murphy wrote:
> rcamarda wrote:
>
> > UPDATE F_BI_ADMISSIONS_OFFICER_SUMMARY_BY_SESSION_RT_CURRENT -- (RT =
> > rollingtotal)
> > SET NEW_REGISTRATION_COUNT =
> > (SELECT COUNT(DISTINCT STUDENT_ID)
> > FROM
> > (SELECT aa.session_id, aa.student_id, student_status
> > FROM F_BI_REGISTRATION_TRACKING AA
> > LEFT OUTER JOIN F_BI_STUDENT_SUMMARY_SESSION BB ON
> > AA.STUDENT_ID=BB.STUDENT_ID AND AA.SESSION_ID=BB.SESSION_ID
> > LEFT OUTER JOIN D_BI_STUDENT CC
> > ON AA.STUDENT_ID = CC.STUDENT_ID AND CC.CURR_IND
> > = 'Y'
> > WHERE AA.SESSION_ID = OA.SESSION_ID
> > AND ACTIVITY_DT <= OA.SESSION_DT
> > AND CC.TALISMA_AO_ID = OA.ADMREP_ID
> > AND STUDENT_STATUS = 'NEW'
> > GROUP BY AA.SESSION_ID, aa.STUDENT_ID, STUDENT_STATUS
> > HAVING SUM(ACTIVITY_COUNT) > 0
> > ) as ppp
> > )
> > FROM F_BI_ADMISSIONS_OFFICER_SUMMARY_BY_SESSION_rt_current OA
>
> Do you have indexes on all appropriate columns?
>
> Which table contains STUDENT_STATUS?
>
> Does the join to D_BI_STUDENT need to be left outer? What about the
> join to F_BI_STUDENT_SUMMARY_SESSION? If so, can you eliminate the
> need by adding dummy rows (with some sort of this-is-a-dummy-row
> indicator) to those tables?
>
> Can you replace this form (boiled down from the original):
>
> SELECT COUNT(DISTINCT STUDENT_ID) FROM (
> SELECT SESSION_ID, STUDENT_ID, STUDENT_STATUS
> FROM ...
> GROUP BY SESSION_ID, STUDENT_ID, STUDENT_STATUS
> HAVING SUM(ACTIVITY_COUNT) > 0
> )
>
> with this?
>
> SELECT COUNT(*) FROM (
> SELECT STUDENT_ID
> FROM ...
> GROUP BY STUDENT_ID
> HAVING SUM(ACTIVITY_COUNT) > 0
> )

[Back to original message]