schema/spacewalk/oracle/packages/rhn_cache.pkb | 20 ++++++++++----------
1 file changed, 10 insertions(+), 10 deletions(-)
New commits:
commit 948c7619c31def952d949f5b11824583c848403a
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Thu Nov 5 12:17:44 2009 +0100
532683 - optimized delete
no need to go throught rhnUserServerPerms twice
diff --git a/schema/spacewalk/oracle/packages/rhn_cache.pkb
b/schema/spacewalk/oracle/packages/rhn_cache.pkb
index af6dd0e..c854a84 100644
--- a/schema/spacewalk/oracle/packages/rhn_cache.pkb
+++ b/schema/spacewalk/oracle/packages/rhn_cache.pkb
@@ -34,21 +34,21 @@ is
);
end update_perms_for_server;
+ -- update rhnUserServerPerms cache from rhnUserServerPermsDupes
procedure update_perms_for_user(
user_id_in in number
) is
begin
- delete from rhnUserServerPerms
- where user_id = user_id_in
- and server_id in
- (select server_id
- from rhnUserServerPerms
- where user_id = user_id_in
- minus
- select server_id
- from rhnUserServerPermsDupes uspd
- where uspd.user_id = user_id_in);
+ -- first delete rows which are not in rhnUserServerPermsDupes
+ delete from rhnUserServerPerms up
+ where user_id = user_id_in
+ and not exists (
+ select 1
+ from rhnUserServerPermsDupes uspd
+ where uspd.user_id = up.user_id
+ and uspd.server_id = up.server_id);
+ -- then insert rest of rows from rhnUserServerPermsDupes
insert into rhnUserServerPerms (user_id, server_id)
select distinct user_id_in, server_id
from rhnUserServerPermsDupes uspd
Show replies by date