WL#12470: Volcano iterator semijoin
Affects: Server-8.0 — Status: Complete
Building on WL#12074, this worklog aims to implement all forms of semijoins in the iterator executor.
No (new) functional requirements. All queries should remain the same. Even performance schema results should be the same. Some warnings may end up on different line numbers. No (new) non-functional requirements. Performance should not regress.
We currently have four semijoin strategies, which map reasonably well to the iterator model: - First match: Extend NestedLoopIterator with a new semijoin join type, which works like an inner join except that it stops after the first match on the right-hand side. Some corner cases will need to be handled by inserting LIMIT 1 instead. - Semijoin materialization: Straightforward using MaterializeIterator. - Duplicate weedout (join regularly, then remove duplicates): Make a new iterator that reuses the logic from do_sj_dups_weedout(). Some work is needed for the rare cases when the join optimizer sets up non-hierarchical weedouts that happen to interfere with outer joins. - Loose scan (remove duplicates from ordered row set, then join regularly): Make a new RemoveDuplicatesIterator to remove duplicates in a streaming fashion. For loose scan involving multiple tables, we need a new combined nestloop + loose scan iterator to maintain the current efficiency.
Copyright (c) 2000, 2023, Oracle Corporation and/or its affiliates. All rights reserved.