Advanced query processing in databases

As random access memory gets cheaper, it becomes increasingly affordable to build computers with large main memories. However, main memory data processing is not as simple as increasing the buffer pool size. An important issue is the cache behavior. Cache optimization differs from buffer optimization in a disk-based system. Indexing structures can reduce overall computation time without using too much space. In this thesis, I studied the cache behavior of several existing indexing structures in main memory. I designed and implemented two kinds of cache sensitive indexing structures, namely the CSS-Trees and the CSB+-Trees. Using pointer elimination techniques, the number of cache misses is reduced significantly in both indexing structures. As a result, searching these cache conscious indexing structures is much faster than existing tree-based indexes. Unlike CSS-Trees, CSB+-Trees support incremental updates and are useful to a larger number of applications. In this thesis, I will also describe the Columbia Main Memory Database System. The goal of the system is to prototype a main memory based decision support system that provides fast query processing by improving the cache behavior. The system uses cache conscious data layout to store tables in main memory, which increases the spatial locality during table scans. It also employs cache conscious data processing algorithms. In particular, it uses cache sensitive indexing structures to perform indexed nested loop joins. The second part of this thesis focuses on complex query processing and optimization. Complex queries are common in decision support systems. As part of this thesis, I have designed and implemented a new “invariant” technique that can evaluate arbitrary correlated queries efficiently. The technique enhances the execution engine so that it recognizes the part of the subquery that is uncorrelated and tries to cache and reuse the invariant result. The method also teaches a conventional query optimizer to understand the invariant feature and thus allows it to generate better plans. The technique has been incorporated into a commercial release of Sybase IQ. (Abstract shortened by UMI.)