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

I found this query in my server log:
            select nnr.*
            from (select notifications.notification_id,
                  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:

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

And here for an inner join:

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


Yes, on our system, where we make HEAVY use of notifications this query was being fired off every 15 minutes, but it would not complete in 15 minutes.  It was pegging our Oracle server at 100% CPU.

I think we made some optimizations to fix this, but I'm not sure if they got submitted as patches.  Hopefully Brad or Jon Griffin will chime in here and remind me what we did in the end.

Yep, it was really nasty.

So is the outer join necessary or not?
And could you post your query please. Is is located in notifications/tcl/sweep-procs-oracle.xql.

Thanks a lot.

Are you running the most recent versions of the clean-up query?  Once upon a time a bug in that caused the notifications table to grow in unbounded fashion ...

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.

On further inspection it looks like notification_user_map holds all sent notifications. See the proc mark_sent in notification-procs.tcl.
The query named "notification::sweep::cleanup_notifications.select_notification_ids"  in tcl/sweep-procs-oracle.xql is supposed to clean up that table...
I just got back from out of the country, but the problem was in the exists  vs in thing. The query went to 10 seconds or so.