Zone maps are a data access structure provided in multiple data warehousing products [1, 3, 5, 14, 20], that reduce the cost of data access for a query by advance pruning of irrelevant data blocks. This is achieved by dividing the table into zones and storing for each zone, the minimum and maximum value of selected columns. The structures used to store these values are called zone maps. Zone maps are most effective for a query if the data is sorted (or clustered) on a column that the query has filters on, and if it also has zone maps constructed on the column. More selective filters are likely to lead to better performance. But as filter predicates vary from query to query, it is not straightforward to identify a single clustering that is effective across the entire workload. This paper describes a novel method for automatically analyzing a workload and recommending a clustering solution, as well as the zone maps to construct for improved performance. It works for both linear and interleaved (z-order) clustering, the two most commonly used clustering methodologies. In our experiments on the TPCH bench- mark and customer datasets, this approach provides significant performance improvements over the baseline. To the best of our knowledge, this is the first work to address the automated cluster- ing (auto-clustering) problem. The method described here has been implemented and will be released in an upcoming version of the Oracle Autonomous Database.

Acknowledging the reality of streaming languages and platforms overlapping with SQL and database systems, in 2019 INCTIS Data Management established an Expert Group with the focused mission to initiate the process of standardizing streaming support in SQL. Over time, the roster included companies like Actian, Alibaba, Amazon Web Services, Confluent, dbt Labs, Google, Hazelcast, IBM, Materialize, Microsoft, Oracle, Snowflake, SQLstream and Timeplus. For the span of more than one year, representatives of each company have presented key features of their streaming product or, in some cases, multiple streaming products. These were live technical Q&A sessions accompanied by summary or position papers, which are unquestionably valuable. As expected, substantial time was spent in clarifying what common terms meant in each system and setting up a glossary. These sessions were followed by clarification notes and debates, and decisions that appeared mandatory to allow further progress. This first phase was followed by the next phase, which consisted of the group meetings, in which the expert group (EG) agreed on main exit criteria topics that a streaming solution in SQL must address, and position papers and follow-ups were written and discussed. This paper summarizes these group efforts, up to the summer of 2023.

Automated generation of a right set of materialized views is a challenging task. It is a highly desirable feature for autonomous databases. The selection of materialized views must be based on cost and verifiable in the actual database environment. This paper describes an automated system that generates, selects, verifies, and maintains materialized views in the Oracle RDBMS; it presents a novel technique, called the extended covering sub-expression algorithm, for the automated generation of materialized views. An extensive set of experiments is described that demonstrates the feasibility and efficiency of this approach. This system has been fully implemented and is going to be deployed on the Oracle Autonomous Database on the Cloud.

In recent years, the data warehouse industry has witnessed decreased use of indexing but increased use of compression and clustering of data facilitating efficient data access and data pruning in the query processing area. A classic example of data pruning is the partition pruning, which is used when table data is range or list partitioned. But lately, techniques have been developed to prune data at a lower granularity than a table partition or sub-partition. A good example is the use of data pruning structure called zone map. A zone map prunes zones of data from a table on which it is defined. Data pruning via zone map is very effective when the table data is clustered by the filtering columns. The database industry has offered support to cluster data in tables by its local columns, and to define zone maps on clustering columns of such tables. This has helped improve the performance of queries that contain filter predicates on local columns. However, queries in data warehouses are typically based on star/snowflake schema with filter predicates usually on columns of the dimension tables joined to a fact table. Given this, the performance of data warehouse queries can be significantly improved if the fact table data is clustered by columns of dimension tables together with zone maps that maintain min/max value ranges of these clustering columns over zones of fact table data. In recognition of this opportunity of significantly improving the performance of data warehouse queries, Oracle 12c release 1 has introduced the support for dimension based clustering of fact tables together with data pruning of the fact tables via dimension based zone maps.

This paper describes enhanced subquery optimizations in Oracle relational database system. It discusses several techniques -- subquery coalescing, subquery removal using window functions, and view elimination for group-by queries. These techniques recognize and remove redundancies in query structures and convert queries into potentially more optimal forms. The paper also discusses novel parallel execution techniques, which have general applicability and are used to improve the scalability of queries that have undergone some of these transformations. It describes a new variant of antijoin for optimizing subqueries involved in the universal quantifier with columns that may have nulls. It then presents performance results of these optimizations, which show significant execution time improvements.

This paper describes Continuous Queries (CQ) in Oracle RDBMS, a feature that incorporates stream and complex event processing into an RDBMS, the first such attempt in commercial databases. The feature is based on the concept of query difference and allows us to monitor real time changes to the query as the result of changes to its underlying tables. The result of a continuous query can be deposited into historical tables or queues for further asynchronous de-queuing, or can invoke a synchronous trigger for procedural processing. The main contribution of our CQ engine is that it allows us to react to complex scenarios of changes to data such as mixed INSERT, DELETE and UPDATE changes, unlike the existing stream processing systems that deal with INSERTS only. We support a wide range of query shapes including inner, semi and anti-joins, aggregates and window functions. More details are given to the efficient computation of query difference for general cases and their optimizations based on semantic constraints. They are shown to improve the response time for practical cases by more than an order of magnitude. We also show how delaying CQ re-computation can improve its performance by batch processing the changes to the base tables.