Towards Multi-way Join Aware Optimizer in SAP HANA

Abstract

Existing binary join based plans may be suboptimal for important, emerging applications. Typical query optimizers enumerate plans using binary joins only. In this paper, we introduce the multi-way join aware optimizer in SAP HANA. The naive way to extend the existing query optimizer to be aware of multi-way joins (m-way joins for short) is to enumerate m-way joins on top of a traditional binary join enumeration framework. However, many different binary joins correspond to the same m-way join. Thus, unnecessary join enumerations would be required for such naive integration. To solve this problem, we introduce the new concept of an m-way join unit and explain how the construction of join units is plugged into the SAP HANA query optimizer. We also provide a series of optimizer enhancements by exploiting m-way join unit characteristics. Using TPC-H and our customer workloads, we showcase the superiority of our m-way join aware optimizer.