MISTRAL: Processing Relational Queries using a Multidimensional Access Technique

Abstract

PrefaceClassical one dimensional B-trees have been the standard access method of all commercial database systems for many years. This dissertation is a very promising effort to introduce universal B-trees (UB-trees), the multidimensional variant of Btrees, as a basic access method into the core of fundamental DB-technology. The universal relevance of UB-trees is a consequence of the fact, that every relation can be considered as a set of points in multidimensional space. UB-trees organize this space for efficient processing of the data, resulting for many types of queries in orders of magnitude improvement over classical methods.The thesis lays the theoretical foundations of UB-tress, predicts their performance by analytical models and validates these models by experiments using large real world databases and real life applications and queries from the field of datawarehousing. The need to sort data and intermediate results frequently is an annoying drawback of today' s query processing methods. In combination with the Tetris algorithm UBtrees allow to avoid sorting in most cases, leading to dramatic improvements of response time, storage requirement and overall query processing time.Adding a new access method requires to consider all aspects of database systems:-architecture of subsystems -query optimization -query processing -multiuser operation and synchronization -bulk loading -storage requirement -parallelism, etc.Since UB-trees rely on classical B-trees for their implementation, all of these issues can be solved in a satisfactory way and can be dealt with elegantly.The performance experiments reported in this thesis were carried out with an implementation of UB-trees as a middleware layer on top of SQL. Additional performance improvements can be gained by integrating the UB-tree technology in the kernel of database systems.This thesis is a cornerstone of the MISTRAL project. MISTRAL has the goal to introduce UB-trees as a new access method into database systems with the fascinating vision, to extend fundamental database technology in an essential way. MISTRAL is financially supported by SAP, Teijin, NEC, Hitachi, the European Commission, Project MDA, TAS, Gfk and Microsoft. Abstract:A multidimensional access method offering significant performance increases by intelligently partitioning the query space is applied to relational database management systems (RDBMS). We introduce a formal model for multidimensional partitioned relations and discuss several typical query patterns. The model identifies the significance of multidimensional range queries and sort operations. The discussion of current access methods gives rise to the need for a multidimensional partitioning of relations. A detailed analysis of space partitioning focussing especially on Z-ordering illustrates the principle benefits of multidimensional indexes. After describing the UB-Tree and its standard algorithms for insertion, deletion, point queries, and range queries, we introduce the spiral algorithm for nearest neighbor queries with UB-Tree...

Automatic schema design for co-clustered tables

“…Our automatic schema creation algorithms use the concept of Z-ordering introduced in [15] and previously applied in Mistral [8], which explores bit interleaving in Z-order for multi-dimensional clustering.…”

“…Following the UB-Tree work [8], we prefer roundrobin bit interleaving instead, storing tuples in Z-order. This eliminates the question of how to order a table's dimensions and distributes fast access among all dimensions.…”

“…This is for example the case for n nationkey with respect to r regionkey in the TPC-H schema. Though our implementation exploits hierarchy among dimensions in rewrites, it treats all dimensions independently, because hierarchical bin numbering schemes [8] , 2 x )). Algorithm 1 uses this to choose a count-table granularity with the byte-size in the highest density column A R for the vast majority of groups.…”

Bitwise dimensional co-clustering for analytical workloads

“…the tuples with common characteristics are stored together on disk. The UB-tree, MDC or MDAM for B+-trees supports access via multiple attributes, MDC and the UB-tree [25] even balance dimensions and this way do not lose performance for data access when the selections occur on minor dimensions. Multi-dimensional clustering [29] (MDC) in DB2 takes a physical approach by partitioning row-organized data according to multiple dimensions into separate disk pages.…”

Query Processing of Pre-partitioned Data Using Sandwich Operators

“…We prototyped our approach in Vectorwise and loaded the TPC-H SF100 data using z-ordering [8] with the dimensions ORDERDATE, CUSTOMER NATION, SUPPLIER NATION and PART. With this prototype we executed the 22 TPC-H queries with and without Sandwich Operators.…”