Null aware anti-join

Abstract

Unnesting using anti-join of NOT IN/ALL subquery uses null-aware anti-join operation, resulting in a rewritten query that, when computed, produces results consistent with the NULL semantics of NOT IN/ALL subquery.

Description

BRIEF DESCRIPTION OF THE DRAWINGS

The present invention is illustrated by way of example, and not by way of limitation, in the figures of the accompanying drawings and in which like reference numerals refer to similar elements and in which:

FIG. 1 is a diagram of a query optimizer according to an embodiment of the present invention.

FIG. 2 depicts a procedure for performing a NULL aware sort-merge join according to an embodiment of the present invention.

FIG. 3 depicts a procedure for performing a hash join according to an embodiment of the present invention.

FIG. 4 depicts a computer system which may be used to implement an embodiment of the present invention.

Claims

1. A computer-implemented method, comprising: based on a join condition of an anti-join operation, executing the anti-join operation based on a left table and a right table;wherein said join condition is based on a joining column of said right table and a joining column of said left table; andwherein executing the anti-join operation qualifies: if said right table is empty, all rows from the left table;if at least one row from the right table includes a NULL value in the joining column of the right table, no rows; andif no row from the right table includes the NULL value in the joining column of the right table, rows from the left table that, based on the join condition, do not match rows from the right table and do not include a NULL value in the joining column of the left table.

2. The method of claim 1, wherein executing the anti-join operation includes: generating a sorted set of left-side rows from the left table;generating a sorted set of right-side rows from the right table;during generation of the sorted set of right-side rows, detecting that a row from the right table includes a NULL value in the joining column of the right table; andin response to detecting that a row from the right table includes a NULL value, qualifying no rows.

3. The method of claim 1, wherein executing the anti-join operation includes: generating a sorted set of left-side rows from the left table;generating a sorted set of right-side rows from the right table;wherein the joining column of the right-side rows includes no NULL value;establishing as qualified rows of the anti-join, rows from the set of left-side rows that: do not include a NULL value in the respective joining column; andbased on the join condition, do not match a row in the set of right-side rows.

4. The method of claim 1, the steps further including generating a sorted set of left-side rows from the left table;attempting to generate a sorted set of right-side rows from the right table;determining that the set of right side rows from the right table is an empty set; andin response to determining that the set of right side rows from the right table is an empty set, establishing as qualified rows of the anti-join all rows from the set of left-side rows.

5. The method of claim 1, the steps further including based on the joining column of the left table, generating a hash table of left-side rows from the left table;for each row of one or more rows from the right table, examining said each row, wherein examining each row includes: determining whether said each row contains a NULL value in the joining column of said each row; andif said each row contains a NULL value in the joining column of said each row, then establishing no rows from the left table as qualified for said anti-join.

6. The method of claim 5, wherein examining said each row of the one or more rows from the right table includes examining a particular row that does not have a NULL value in the joining column of the right table, wherein examining said particular row includes: determining whether said particular row matches a row from said left table based on the join condition; andif said particular row matches a row from said left table, removing said particular row from the hash table built for the left table.

7. The method of claim 1, the steps further including: based on the joining column of the left table, generating a hash table of left-side rows from the left table;wherein said joining column of said right table does not include a NULL value;for each row of one or more rows from the right table, examining said each row, wherein examining each row includes: determining whether said each row matches a row from said left table based on the join condition; andif said each row matches a left-side row from said left table, removing said left-side row from the hash table;establishing as qualified rows of the anti-join, left-side rows from the hash table that do not include a NULL value in the joining column of the left table.

8. The method of claim 1, the steps further including for each left-side row from the left table:determining whether said each joining column of the left-side row contains a NULL value;if said connecting column of said left-side row contains a NULL value, then disqualifying said each left-side row; andif said connection column of said left-side row does not contain a null value, then: performing a table scan of the right table; andqualifying a right side row scanned by said table scan with a joining column matching the joining column of the left table.

9. The method of claim 8, further including the step of determining whether any row from the right table contains a NULL value by executing a certain subquery that returns a value that indicates whether a right side row from the right table contains a NULL value in the connecting column.

10. The method of claim 1, wherein executing the anti-join operation includes executing a nested-loops join operation, said nested-loops join operation including: for a left-side row from the left table: performing an index probe of at least a portion of the right table to determine whether a right-side row matches the left-side row, andbased on the index probe, determining that the right table is empty; andin response to determining that the right table is empty, qualifying all rows in the left table.

11. A computer-implemented method, comprising steps of: rewriting a query that includes a NOT IN/ALL subquery based on a right table, a left table, and a connecting condition based on a connecting column of the right table and a connecting column of the left table;wherein rewriting said query comprises unnesting said query to produce a transformed query that specifies an anti-join operation based on the right table, the left table, and the connecting condition; andwherein rows from said left table include a NULL value in the connecting column of the left table.

12. The method of claim 11, wherein: said NOT IN/ALL subquery includes a filter condition that applies to the right table; andsaid rows from said left-side that include a NULL value also satisfy said filter condition.

13. The method of claim 11, wherein said anti-join operation qualifies: if said right table is empty, all rows from the left table;if at least one row from the right table includes a NULL value in the connecting column of the right table, no rows;if no row from the right table includes a NULL value in the connecting column of the right table, rows from the left table that, based on the connecting condition, do not match rows from the right table and do not include a NULL value in the connecting column of the left table.

14. The method of claim 11, wherein: rewriting said query includes generating a certain subquery within said transformed query that returns a value that indicates whether a row from the right table contains a NULL value in the connecting column;the steps further include generating a certain execution plan that performs a nested-loops join for said anti-join operation; andwherein said certain execution plan does not execute said nested-loops join if said certain subquery returns a value that indicates that a row from the right table contains a NULL value in the connecting column.

15. The method of claim 14, further including generating another execution plan that does not use a nested-loops join for said anti-join operation; andcomparing a cost of said another execution plan to a cost of said certain execution plan, wherein said cost of said certain execution plan includes a cost based on said subquery.

16. The method of claim 14, wherein the steps of generating said certain subquery and said execution plan are performed if said left table contains a NULL value in said connecting column of the left table.