Forum OpenACS Q&A: Notifications: weird outer join to notification_user_map

            select nnr.*
            from (select notifications.notification_id,
                    notifications.notif_subject,
                    notifications.notif_text,
                    notifications.notif_html,
                    notification_requests.user_id,
                    notification_requests.type_id,
                    notification_requests.delivery_method_id,
                    notification_requests.request_id,
                    notifications.response_id,
                    notifications.notif_date
                  from notifications, notification_requests
                  where notifications.type_id = notification_requests.type_id
                    and notifications.object_id = notification_requests.object_id
                    and notification_requests.interval_id = :interval_id) nnr,
              notification_user_map, acs_objects
            where nnr.notification_id = notification_user_map.notification_id(+)
              and nnr.user_id = notification_user_map.user_id(+)
              and notification_user_map.sent_date is null
              and acs_objects.object_id = nnr.request_id
              and acs_objects.creation_date <= nnr.notif_date
            order by nnr.user_id, nnr.type_id

It is the second-most expensive query on a loaded system.

Now what I find fairly fishy is that the join to notification_user_map is an outer join. Why is that so? If there is no user for the notification, I cannot send it all, right?

Why is that an issue? Here's the cost for the query with an outer join:

Statistics
----------------------------------------------------------
          0  recursive calls
         11  db block gets
    1296647  consistent gets
        654  physical reads

And here for an inner join:

Statistics
----------------------------------------------------------
          0  recursive calls
          7  db block gets
        774  consistent gets
          8  physical reads

Whopping!

I went through these queries with a fine-toothed comb a few months ago and I believe the outer join is necessary, though I don't remember why off the top of my head.