WL#13002: BKA in iterator executor
Affects: Server-8.0 — Status: Complete
Implement BKA (Batch Key Access) in the iterator executor, reusing as much as possible from the hash join design. We will only support inner joins in this worklog, since we need more functionality from hash join to get the other join types.
No (new) functional requirements. All queries should remain the same. No (new) non-functional requirements. Performance should not regress.
BKA is implemented with two iterators working in concert. The BKAIterator reads rows from the outer side into a buffer. When the buffer is full or we are out of rows, it then sets up the key ranges and hand it over to the MultiRangeRowIterator, which does the actual MRR request, and reads rows from it until there are none left. For each inner row returned, MultiRangeRowIterator loads the appropriate outer row(s) from the buffer, doing the actual join. The reason for this split is twofold. First, it allows us to accurately time (for EXPLAIN ANALYZE) the actual table read. Second, and more importantly, we can have other iterators between the BKAIterator and MultiRangeRowIterator, in particular FilterIterator. This makes a BKA join look like this in EXPLAIN: -> Batched key access inner join [BKAIterator] -> Batch input rows -> Table scan on t2 [can be an entire subtree] -> Filter [optional FilterIterator] -> Multi-range index lookup on t3 using PRIMARY (t2nr=t2.fld1) [MultiRangeReadIterator] Currently, we only support BKA inner joins. This will change in the future.
Copyright (c) 2000, 2023, Oracle Corporation and/or its affiliates. All rights reserved.