OLAP and real-time analytic workloads in data management systems are dominated by joins, aggregations, scan and filtering costs. In-Memory columnar databases have successfully optimized scans by many orders of magnitude using compressed data formats and SIMD vectorization techniques, but have largely made little impact to the rest of the query execution plan. The Oracle Database In-Memory (DBIM) Option introduced new SQL execution operators that accelerate a wide range of analytic queries, delivering orders of magnitude performance improvement by optimizing aggregation over joins for star and similar schemas. Group-by expressions are pushed down into the scans of dimension tables, creating a unique key per distinct group called a Dense Grouping Key (DGK). A structure called a Key Vector is allocated that maps join keys to DGKs, which is used to filter non-matching rows during the fact table scan. Passing rows are then aggregated directly on compressed codes into DGK-indexed result buffers using SIMD and other novel aggregation techniques. Our innovative solution replaces traditional join and group-by processing (bloom filters, hash table build and probe, serial aggregation) with blazing fast inlined scan operators. And with DBIM's unique dual-format architecture, DML activity (inserts, updates, deletes) do not dampen the phenomenal gains we see with our solution. Using a set of aggregation-heavy queries against the Star Schema Benchmark (SSB) schema, we show that our technique can drastically reduce query elapsed time by more than 10x, making real-time analytics truly achievable.

The Oracle Database In-Memory Option allows Oracle to function as the industry-first dual-format in-memory database. Row formats are ideal for OLTP workloads which typically use indexes to limit their data access to a small set of rows, while column formats are better suited for Analytic operations which typically examine a small number of columns from a large number of rows. Since no single data format is ideal for all types of workloads, our approach was to allow data to be simultaneously maintained in both formats with strict transactional consistency between them.