Set Operation in DBMS

Set operations like UNION, INTERSECTION, MINUS etc are implemented using hashing or merge join methods. But it needs tables to be sorted before. Let us see how SET operator is applied using hashing method.

Initially it partitions both the tables by using hash function as DTi, and then it generates the in-memory hash index for each record in each partition of table T. Then it processes the table S by generating the hash index as follows.

UNION (U)

This operation combines the records in both the tables and eliminates the duplicate records. Hence it takes all the records from first table (T) and then checks if each records of second table (S) exist in T. If does not exists then it add record of S to result.

Check the hash index generated for each record of S exists in hash index of T. If it does not exist, then add hash index of S to the result set.

INTERSECTION (∩)

This operation selects the records which are common to both the tables. So it checks each record of T with each record of S to verify if they are same. If yes, then it adds the record to result.

Check the hash index generated for each record of S exists in hash index of T. If it exists, then it adds hash index of S to result set.

MINUS (-)

This operation selects the record from first table which does not exist in the second table. Hence it checks each record of T with each record of S to verify if they exist in both the tables. If it exists, then delete the record in the result.

Check the hash function generated for each record of S exists in hash index of T. If does not exist, then add hash index of T to result set.


Next > < Prev
Scroll to Top