Join enumeration in a memory-constrained environment

In today´s computing environment, database technology can be found on virtually any device, from traditional mainframes to cellular phones. Sophisticated applications, whether enterprise information portals or sales force automation systems, can `push´ much of their complexity into the database itself; indeed, this represents one of the main benefits of database technology. The challenge, however, is to support these complex applications, and the queries they generate, on small computing devices. We describe a deterministic join enumeration algorithm for left-deep processing trees, currently implemented in Sybase SQL Anywhere, a small-footprint relational database system whose target market ranges from workgroup servers to small hand-held devices. The algorithm is able to efficiently optimize complex queries with high join degree by employing a novel approach to cost based pruning of the search space. We present some empirical performance results on several production queries obtained from SQL Anywhere customers, and show that our approach requires significantly less memory than other deterministic join enumeration algorithms which have been described in the literature