How to reduce the cost of this query?

Question

I have two tables, table1 and table2. table2 contains permissions for records in table1, i. e. one row in table1 can have multiple corresponding rows in table2.

Now I need to write a SQL statement that would retrieve those table1 records, which

1. have a particular primary key (table1.ID) and
2. do not have any of the specified permissions.

I came up with this query:

SELECT table1.id
FROM TABLE1 table1
WHERE table1.ID IN (<ID_List>) AND
(<Excluded_permission_List>) NOT IN (
    Select PERMISSION_NAME
    from TABLE2 table2 
    where table2.perm_owner_id = table1.id
);

where

• <ID_List> is the list of all table1 primary keys to search for, and
• <Excluded_permission_List> the list of all exluded permissions (i. e. if a table1 record has any of the permissions contained in <Excluded_permission_List>, it should not appear in the results).

Example:

SELECT table1.id
FROM TABLE1 table1
WHERE table1.ID IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) AND
('SUPERUSER-1', 'MODERATOR-2') NOT IN (
    Select PERMISSION_NAME
    from TABLE2 table2 
    where table2.perm_owner_id = table1.id
);

This query is supposed to return those table1 records, which

• have the IDs 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 and
• do not have neither the permission SUPERUSER-1, nor MODERATOR-2.

This query works, but according to EXPLAIN PLAN, the cost is 38 (see below).

How can reduce the cost of this query?

Answer 1

You have a correlated subquery on exists-clause as predicate which executes for each filtered parent row. Try this. CBO may rewrite predicate to anti-join (hash join or nested loops)

SELECT table1.id
FROM TABLE1 table1
WHERE table1.ID IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) AND
(('SUPERUSER-1', table1.id), ('MODERATOR-2', table1.id)) NOT IN (
    select table2.permission_name, table2.perm_owner_id
    from TABLE2 table2 
    where table2.perm_owner_id is not null and table2.permission_name is not null
);