WL#13476: BKA outer/semi/anti join in iterator executor
Affects: Server-8.0 — Status: Complete
Make the iterator executor support BKA outer joins, semijoins, and antijoins.
F1. Queries with non-inner BKA joins (semijoins, outer joins, antijoins) should no longer revert to the pre-iterator executor. F2. Queries should give the same results as before, except possibly for ordering (on queries without ORDER BY). No new non-functional requirements, except that performance should generally not regress.
The iterator executor needs (for completeness) support for the various outer join types in BKA. In terms of setting up the iterators from the existing executor structures, this largely builds on the support for outer hash join. The actual implementation is broadly similar to BKA in the pre-iterator executor; each outer row in the buffer gets a "match flag" that says whether we've matched it to an inner row or not. This allows us to: * For outer joins: Output NULL-complemented rows for any unmatched outer rows (scan all the outer rows to check for match flags after the buffer is exhausted). * For semijoins: Output at most one inner row for each outer row (check the match flag before outputting a new row). * For antijoins: Like outer joins, except don't output matched rows at all (only NULL-complemented rows). Unlike the pre-iterator executor, we don't store the match flags in the rows themselves; this allows us to use less space for them (only a single bit), and also keeps the pack/unpack functions simpler.
Copyright (c) 2000, 2023, Oracle Corporation and/or its affiliates. All rights reserved.