SQL Halp!

[Danical]

So, I've been told with the newest version of Oracle SQL that it's up to 6 times more performant to rewrite subqueries as outer joins. Since I deal with millions of records per term, it would be insanely helpful to get at this performance break. So, for example:



select studentDetail.term_code_key "termCode",
studentDetail.pidm_key "studentId",
studentDetail.crn_key "sectionId",
studentDetail.subj_code || studentDetail.crse_number "sectionName"
from BANINST1.AS_ACADEMIC_HISTORY_DETAIL studentDetail
where studentDetail.pidm_key in ( select studentDetail2.pidm_key
from BANINST1.AS_ACADEMIC_HISTORY_DETAIL studentDetail2
where (studentDetail2.subj_code || studentDetail2.crse_number) in ( select distinct (sectionAttribute.scrattr_subj_code || sectionAttribute.scrattr_crse_numb)
from scrattr sectionAttribute
where sectionAttribute.SCRATTR_ATTR_CODE IN ('B','P') )
and studentDetail2.term_code_key between :selectCohortStartTerm.termCode and :selectCohortEndTerm.termCode
)
and studentDetail.term_code_key between to_number(:selectCohortStartTerm.termCode, 999999) and (to_number(:selectCohortEndTerm.termCode, 999999) + :selectCohortLength.durationYears)
order by 1, 2, 3


How do I make the 2 nested subqueries into joins in the FROM clause?

Note: anything trailing a ':' is a variable parameter set on the form before execution.

[Celephais]

select studentDetail.term_code_key "termCode",
studentDetail.pidm_key "studentId",
studentDetail.crn_key "sectionId",
studentDetail.subj_code || studentDetail.crse_number "sectionName"
from BANINST1.AS_ACADEMIC_HISTORY_DETAIL studentDetail
LEFT JOIN BANINST1.AS_ACADEMIC_HISTORY_DETAIL studentDetail2
ON studentDetail.pidm_key = studentDetail2.pidm_key
LEFT JOIN scrattr sectionAttribute
ON (studentDetail2.subj_code = sectionAttribute.scrattr_subj_code || studentDetail2.crse_number = sectionAttribute.scrattr_subj_code || studentDetail2.subj_code = sectionAttribute.scrattr_crse_numb || studentDetail2.crse_number = sectionAttribute.scrattr_crse_numb)
where sectionAttribute.SCRATTR_ATTR_CODE IN ('B','P') )
and studentDetail2.term_code_key between :selectCohortStartTerm.termCode and :selectCohortEndTerm.termCode
)
and studentDetail.term_code_key between to_number(:selectCohortStartTerm.termCode, 999999) and (to_number(:selectCohortEndTerm.termCode, 999999) + :selectCohortLength.durationYears)
order by 1, 2, 3

Done very quickly, may have messed up some parenthesis in there, and you may want to fix the second join clause (I matched the "logic" of your original join, but looking at it, I don't think you want all four of those conditions, I think you only want two of them).

You will likely not see a performance gain in the way what I gave you was written if your indexes are not setup properly to match that query.

ETA: To simply take a nested query and move it into a join, you just need to join the tables, and make the ON clause match the part of the where clause that related the nested query. If you want the best performance possible, it's rarely that simple.

[Danical]

Yeah, I'm not sure why I made the inner most query (sectionAttribute schtuffs) when I could have done it with only one subquery. :confused:

I'll give this a test when I get a chance though.

Appreciate the halp!