Forum OpenACS Q&A: More on the fly views in oracle.
>While porting the education module, I've come across loads of queries
with on the fly views like the following query:
> set sql "select map.department_id,
> department_name,
> subject_number,
> grad_p,
> count(admin_list.group_id) as
>department_admin_p
> from edu_subject_department_map map,
> edu_departments dept,
> (select group_id
> from user_group_map
> where user_id = $user_id
> and role = 'administrator')
admin_list
> where dept.department_id = map.department_id
> and map.subject_id = $subject_id
> and map.department_id = admin_list.group_id(+)
> group by map.department_id,
> department_name,
> subject_number,
> grad_p"
>I feel like I'm missing something. Does anyone know if there is any
advantage to doing it this way as opposed to doing a simple join like
the following:
> set sql "select map.department_id,
> department_name,
> subject_number,
> grad_p,
> count(admin_list.group_id) as
department_admin_p
> from edu_subject_department_map map,
> edu_departments dept,
> user_group_map admin_list
> where dept.department_id = map.department_id
> and admin_list.user_id = $user_id
> and admin_list.role = 'administrator'
> and map.subject_id = $subject_id
> and map.department_id = admin_list.group_id(+)
> group by map.department_id,
> department_name,
> subject_number,
> grad_p"
Don thought that ther was nothing wrong with this conversion, but
he
said that there were cases where it did matter. Here is an example
of
such a case:
set sql "select
ea.class_id,
assignment_id,
ea.assignment_name,
class_name,
teacher_id,
first_names,
last_name,
ea.description,
ea.date_assigned,
last_modified,
due_date,
grade_id,
weight,
electronic_submission_p,
requires_grade_p,
ver.version_id,
ver.file_extension,
ver.url,
ver.file_id,
sol.file_id as sol_file_id,
sol.version_id as sol_version_id,
sol.url as sol_url,
sol.file_extension as sol_file_extension,
(case when sign(sysdate-due_date) = 1 then 1 else 0
end) as past_due_p
from edu_assignments ea,
users,
edu_classes ec,
(select * from fs_versions_latest
where
ad_general_permissions.user_has_row_permission_p($user_id, 'read',
version_id, 'FS_VERSIONS') = 't') ver,
(select sol.file_id, version_id, file_extension, url,
task_id
from edu_task_solutions sol,
fs_versions_latest ver
where task_id = $assignment_id
and sol.file_id = ver.file_id
and
ad_general_permissions.user_has_row_permission_p($user_id, 'read',
version_id, 'FS_VERSIONS') = 't') sol
where ec.class_id = ea.class_id
and users.user_id = ea.teacher_id
and ea.assignment_id = $assignment_id
and ec.class_id = $class_id
and ea.file_id = sol.file_id(+)
and ea.file_id = ver.file_id(+)"
Converting to a simple join here will cause the query for
assignments to fail in the case where solution files are not
present. The solution in postgres is to remove all of the and
clauses
that correspond to the on-the-fly views for each of the not exists
clauses that correspond to the on the fly view. The result will
be
that some of the union segments will have the simple join
expansion removed.
