This join is used for comparing values within a column of a table. Since this operation involves a join of a table within itself, you need to give the table two temporary names, or correlation names which then are used to qualify the column names in the rest of the query.
Identify optimizations to conditions where the direct mode is possible during update. With the in-place update project, updates involving a self-join will always be done in the deferred mode. For example:
update t1 set t1.c1 = t1.c1 + 1 FROM t1 a, t1 b where a.c1 = b.c2 and delete t1 FROM t1 a, t1 b WHERE a.c1 = b.c2
will always be processed in deferred mode. This routine implements checks for the following rules that must be satisfied as necessary conditions to run the update in direct mode:
If no such self-join exists, but the query references more than one table in its FROM list, there is a potential to run this update in direct mode.
For an update query, if the query references more than one table in its FROM list, check if the read cursor table is the first table in the join order:
If the read cursor is the first one in the join order, the update can be run in direct mode, as all downstream tables will be scanned in existence join.
If its not the first one in the join order, check if all tables that appear before it in the join order are tables from flattened subqueries to which the optimizer has applied one of many techniques (unique join, tuple filtering and/or unique reformatting) to ensure that only one row will be fed into the final join plan.
If all the tables preceeding the read cursor table which are from the outer query block are known to return a single row, then every row from the read cursor table will only qualify once.
This guarantees that rows from the target table will only qualify once.
For a delete query, there is no requirement that the target table be the first table in the join order. This is because even if a row from the target table were to qualify multiple times due to the join conditions, by doing a direct delete, we will delete the row the "first" time it qualifies, and subsequent qualificiations will get a "row not found" condition.
The following queries can potentially be run in direct mode update when these rules are used.
Example 1: This is a non-flattened subquery which results in a table count in the main ROOT node of 3.
update t1 set t1.c1 = t1.c1 + 1 where t1.c2 NOT IN (select t2.c2 from t2)
Example 2: This is a flattened subquery
update t1 set t1.c1 = t1.c1 + 1 FROM t1 where t1.c2 IN (select t2.c2 from t2)
In both these cases, although the table count in the main ROOT node shows up as 3, these queries can potentially be run in direct update mode.
Example 3:
update t1 set t1.c1 = t1.c1 + 1 FROM t1, t2 where t1.c1 = t2.c1
Output:
Data is: t1 t2 --- --- 1 1 1 2
If the join order is t2 -> t1, and we do the update in direct mode, then the rows from t1 will become:
[(1), (1)] -> [(2), (2)]
