Commercial DBMSs Research Articles

[formula omitted]JUpdate: An update language for time-varying JSON data

Time-varying JSON data are being used and exchanged in various today’s application frameworks like IoT platforms, Web services, cloud computing, online social networks, and mobile systems. However, in the state-of-the-art of JSON data management, there is neither a consensual nor a standard language for updating (i.e., inserting, modifying, and deleting) temporal JSON data, like the TSQL2 or SQL:2016 languages for temporal relational data. Moreover, existing JSON-based NoSQL DBMSs (e.g., MongoDB, Couchbase, CouchDB, OrientDB, and Riak) and both commercial relational DBMSs (e.g., IBM DB2 12, Oracle 19c, and MS SQL Server 2019) and open-source ones (e.g., PostgreSQL 15, and MySQL 8.0) supporting JSON documents do not provide any facility for maintaining temporal JSON data. Also in our previously proposed temporal JSON framework, called τJSchema, there was no feature for temporal JSON instance updates. For these reasons, we propose in this article a temporal update language, named τJUpdate (Temporal JUpdate), for JSON data in the τJSchema environment. We define it as a temporal extension of our previously introduced non-temporal JSON update language, named JUpdate (JSON Update). Both the syntax and the operational semantics of the data modification operations of JUpdate have been extended to support temporal aspects. τJUpdate allows to specify temporal JSON updates in an expressive and user-friendly manner, and to efficiently execute them in the τJSchema environment.

ProRes: Proactive re-selection of materialized views

Materialized View Selection is one of the most studied problems in the database field, covering SQL and NoSQL technologies as well as different deployment infrastructures (centralized, parallel, cloud). This problem has become more complex with the arrival of data warehouses, being coupled with the physical design phase that aims at optimizing query performance. Selecting the best set of materialized views to optimize query performance is a challenging task. Given their importance and the complexity of their selection, several research efforts both from academia and industry have been conducted. Results are promising ? some solutions are being implemented by commercial and open-source DBMSs ?, but they do not factor in the following properties of nowadays analytical queries: (i) largescale queries, (ii) their dynamicity, and (iii) their high interaction. Studies to date fail to consider that complete set of properties. Considering the three properties simultaneously is crucial regarding today?s analytical requirements, which involve dynamic and interactive queries. In this paper, we first present a concise state of the art of the materialized view selection problem (VSP) by analyzing its ecosystem. Secondly, we propose a proactive re-selection approach that considers the three properties concurrently. It features two main phases: offline and online. In the offline phase, we manage a set of the first queries based on a given threshold ? by selecting materialized views through a hypergraph structure. The second phase manages the addition of new queries by scheduling them, updates the structure of the hypergraph, and selects new views by eliminating the least beneficial ones. Finally, extensive experiments are conducted using the Star Schema Benchmark data set to evaluate the effectiveness and efficiency of our approach.

Learned cardinality estimation

Cardinality estimation is core to the query optimizers of DBMSs. Non-learned methods, especially based on histograms and samplings, have been widely used in commercial and open-source DBMSs. Nevertheless, histograms and samplings can only be used to summarize one or few columns, which fall short of capturing the joint data distribution over an arbitrary combination of columns, because of the oversimplification of histograms and samplings over the original relational table(s). Consequently, these traditional methods typically make bad predictions for hard cases such as queries over multiple columns, with multiple predicates, and joins between multiple tables. Recently, learned cardinality estimators have been widely studied. Because these learned estimators can better capture the data distribution and query characteristics, empowered by the recent advance of (deep learning) models, they outperform non-learned methods on many cases. The goals of this paper are to provide a design space exploration of learned cardinality estimators and to have a comprehensive comparison of the SOTA learned approaches so as to provide a guidance for practitioners to decide what method to use under various practical scenarios.

Plan stitch

Query performance regression due to the query optimizer selecting a bad query execution plan is a major pain point in production workloads. Commercial DBMSs today can automatically detect and correct such query plan regressions by storing previously-executed plans and reverting to a previous plan which is still valid and has the least execution cost. Such reversion-based plan correction has relatively low risk of plan regression since the decision is based on observed execution costs. However, this approach ignores potentially valuable information of efficient subplans collected from other previously-executed plans. In this paper, we propose a novel technique, Plan Stitch, that automatically and opportunistically combines efficient subplans of previously-executed plans into a valid new plan, which can be cheaper than any individual previously-executed plan. We implement Plan Stitch on top of Microsoft SQL Server. Our experiments on TPC-DS benchmark and three real-world customer workloads show that plans obtained via Plan Stitch can reduce execution cost significantly, with a reduction of up to two orders of magnitude in execution cost when compared to reverting to the cheapest previously-executed plan.

Efficient Metric Indexing for Similarity Search and Similarity Joins

Spatial queries including similarity search and similarity joins are useful in many areas, such as multimedia retrieval, data integration, and so on. However, they are not supported well by commercial DBMSs. This may be due to the complex data types involved and the needs for flexible similarity criteria seen in real applications. In this paper, we propose a versatile and efficient disk-based index for metric data, the S pace-filling curve and Pivot-based B $^{+}$ -tree (SPB-tree). This index leverages the B $^+$ -tree, and uses space-filling curve to cluster data into compact regions, thus achieving storage efficiency. It utilizes a small set of so-called pivots to reduce significantly the number of distance computations when using the index. Further, it makes use of a separate random access file to support a broad range of data. By design, it is easy to integrate the SPB-tree into an existing DBMS. We present efficient algorithms for processing similarity search and similarity joins, as well as corresponding cost models based on SPB-trees. Extensive experiments using both real and synthetic data show that, compared with state-of-the-art competitors, the SPB-tree has much lower construction cost, smaller storage size, and supports more efficient similarity search and similarity joins with high accuracy cost models.

Data-Intensive Science and Engineering:Requirements and Challenges

Scientific exploration after experimental science,theoretical science and computational science phases,into data-intensive science phase,are accompanied by the arrival of the big data era.Generally,big data refers to a data set with a size of hundreds of TB,or several PB or even above,and it is often distributed,heterogeneous and in low-quality.It is critical to devise novel methods to manage big data since traditional database management techniques are unfeasible to manage big data efficiently and effectively,though such techniques,especially the commercial relational DBMSs,have achieved great success in the past decades.This paper discusses concrete requirements and realistic challenges of Data-Intensive Science and Engineering(DISE),ranging from data storage and organization,computational method,data analysis,to user interfaces.Meanwhile,data quality,data security and data curation should be paid more attentions.In this paper,we attempt to describe the architecture of DISE,review the recent progress,and discuss the challenges and future work briefly.

A formal characterization of SI-based ROWA replication protocols

Snapshot isolation (SI) is commonly used in some commercial DBMSs with a multiversion concurrency control mechanism since it never blocks read-only transactions. Recent database replication protocols have been designed using SI replicas where transactions are firstly executed in a delegate replica and their updates (if any) are propagated to the rest of the replicas at commit time; i.e. they follow the Read One Write All (ROWA) approach. This paper provides a formalization that shows the correctness of abstract protocols which cover these replication proposals. These abstract protocols differ in the properties demanded for achieving a global SI level and those needed for its generalized SI (GSI) variant — allowing reads from old snapshots. Additionally, we propose two more relaxed properties that also ensure a global GSI level. Thus, some applications can further optimize their performance in a replicated system while obtaining GSI.

Index interactions in physical design tuning

One of the key tasks of a database administrator is to optimize the set of materialized indices with respect to the current workload. To aid administrators in this challenging task, commercial DBMSs provide advisors that recommend a set of indices based on a sample workload. It is left for the administrator to decide which of the recommended indices to materialize and when. This decision requires some knowledge of how the indices benefit the workload, which may be difficult to understand if there are any dependencies or interactions among indices. Unfortunately, advisors do not provide this crucial information as part of the recommendation. Motivated by this shortcoming, we propose a framework and associated tools that can help an administrator understand the interactions within the recommended set of indices. We formalize the notion of index interactions and develop a novel algorithm to identify the interaction relationships that exist within a set of indices. We present experimental results with a prototype implementation over IBM DB2 that demonstrate the efficiency of our approach. We also describe two new database tuning tools that utilize information about index interactions. The first tool visualizes interactions based on a partitioning of the index-set into non-interacting subsets, and the second tool computes a schedule that materializes the indices over several maintenance windows with maximal overall benefit. In both cases, we provide strong analytical results showing that index interactions can enable enhanced functionality.

고립화 수준을 검증하기 위한 트랜잭션의 시험

데이터베이스 응용 프로그램들은 DBMS가 관리하는 데이터베이스를 동시에 접근하기 때문에 동시성 문제가 발생하며 이를 해결하기 위해서 대부분의 상업용 DBMS들은 고립화 수준을 설정할 수 있게 한다. 데이터베이스 응용 프로그램을 구성하는 트랜잭션들의 고립화 수준에 따라서 데이터베이스의 일관성이 위반될 수도 있기 때문에 트랜잭션의 고립화 수준의 검증은 중요하다. 본 논문은 트랜잭션의 고립화 수준 설정 오류를 검증하기 위한 시험 도구를 제안하고 프로토타입을 구현한다. 시험 도구는 ESQL/C 프로그램을 분석하여 고립화 수준을 검증할 수 있는 코드를 추가하여 시험 트랜잭션을 실행시키고 고립화 수준의 오류를 검출할 수 있게 한다. Concurrency and synchronization problems are often caused by database applications concurrently accessing databases managed by DBMS. Most commercial DBMSs support isolation levels to resolve these problems. Verification of isolation levels are most important because consistency and integrity constraints of the database can be violated according to isolation levels of transactions that consists of database applications. We propose a test tool set to verify and reveal faulty settings of isolation levels and implement a prototype of the test tool set. The proposed tool set analyzes the SQL statements of ESQL/C programs, attaches the test codes to verify isolation levels, runs the test transactions and detects errors.

Efficient Storage and Querying of Horizontal Tables Using a PIVOT Operation in Commercial Relational DBMSs

In recent years, a horizontal table with a large number of attributes is widely used in OLAP or e-business applications to analyze multidimensional data efficiently. For efficient storing and querying of horizontal tables, recent works have tried to transform a horizontal table to a traditional vertical table. Existing works, however, have the drawback of not considering an optimized PIVOT operation provided (or to be provided) in recent commercial RDBMSs. In this paper we propose a formal approach that exploits the optimized PIVOT operation of commercial RDBMSs for storing and querying of horizontal tables. To achieve this goal, we first provide an overall framework that stores and queries a horizontal table using an equivalent vertical table. Under the proposed framework, we then formally define 1) a method that stores a horizontal table in an equivalent vertical table and 2) a PIVOT operation that converts a stored vertical table to an equivalent horizontal view. Next, we propose a novel method that transforms a user-specified query on horizontal tables to an equivalent PIVOT-included query on vertical tables. In particular, by providing transformation rules for all five elementary operations in relational algebra as theorems, we prove our method is theoretically applicable to commercial RDBMSs. Experimental results show that, compared with the earlier work, our method reduces storage space significantly and also improves average performance by several orders of magnitude. These results indicate that our method provides an excellent framework to maximize performance in handling horizontal tables by exploiting the optimized PIVOT operation in commercial RDBMSs.

Temporal databases: From theory to applications

Problems concerning the date and time representation in databases are well studied in scientific literature. Temporal databases are required in many applications including the conventional applications of DBMSs such as financial systems. However, commercial systems and standards for the query language do not support temporal data features. A simple and efficient approach for implementing temporal capabilities over conventional commercial DBMSs is proposed and various aspects of its practical applications are described.

Generalization of strategies for fuzzy query translation in classical relational databases

Users of information systems would like to express flexible queries over the data possibly retrieving imperfect items when the perfect ones, which exactly match the selection conditions, are not available. Most commercial DBMSs are still based on the SQL for querying. Therefore, providing some flexibility to SQL can help users to improve their interaction with the systems without requiring them to learn a completely novel language. Based on the fuzzy set theory and the α-cut operation of fuzzy number, this paper presents the generic fuzzy queries against classical relational databases and develops the translation of the fuzzy queries. The generic fuzzy queries mean that the query condition consists of complex fuzzy terms as the operands and complex fuzzy relations as the operators in a fuzzy query. With different thresholds that the user chooses for the fuzzy query, the user’s fuzzy queries can be translated into precise queries for classical relational databases.

VideoAcM: a transitive and temporal access control mechanism for collaborative video database production applications

Access control models play an important role in database management systems. In general, there are three basic access control models: Discretionary Access Control (DAC), Mandatory Access Control (MAC), and Non-Discretionary Access Control (NAC). Currently, the majority of commercial DBMSs provide only DAC, and some temporal access control models have been derived based on either DAC or NAC. In the context of video database applications, since the structure of video data is complex in nature, it requires a specific and tailor-made access control mechanism which should include MAC as well as DAC and NAC. However, only few efforts have been put on access control models for video database systems. In this paper, a transitive and temporal access control mechanism for collaborative video database production applications has been proposed, which subsumes the properties of DAC, MAC, and NAC. Moreover, our proposed mechanism is integrated with the intellectual property concerns by constructing an access control hierarchy of video data with authorization rules. In particular, our mechanism can derive novel authorization rules not only on conventional client-data access control, but also on data---data access control. Besides video data, the proposed model is applicable to other data types which exhibit a hierarchical data structure.

A comparative study of some concurrency control algorithms for cluster-based communication networks

This paper presents a comparative study of some concurrency control algorithms for distributed databases of computer clusters which emphasize high availability and high performance requirements. For this purpose, we have analyzed some concurrency control algorithms which are used in commercial DBMSs, such as the pessimistic locking algorithm as it verifies transaction conflicts early in their execution phase, and the optimistic algorithm which investigates the presence of conflicts after the execution phase. A new algorithm is proposed and implemented by a simulation program. The three algorithms were tested using different configurations. Simulation results showed that the locking algorithm performed better than the optimistic method in presence of conflicts between transactions, while the optimistic algorithm provided better results in the absence of conflicts. Furthermore, in a distributed database with a certain probability of conflicts, the locking algorithm can be used to guarantee strong consistency and an acceptable level of performance. However, if this probability is negligible, the system performance can be improved by using the optimistic algorithm. The proposed algorithm offers improved performance in numerous cases. As a result, it can be used in a distributed database to guarantee a satisfactory level of performance in the presence of conflicts.

Analytical response time estimation in parallel relational database systems

Techniques for performance estimation in parallel database systems are well established for parameters such as throughput, bottlenecks and resource utilisation. However, response time estimation is a complex activity which is difficult to predict and has attracted research for a number of years. Simulation is one option for predicting response time but this is a costly process. Analytical modelling is a less expensive option but requires approximations and assumptions about the queueing networks built up in real parallel database machines which are often questionable and few of the papers on analytical approaches are backed by results from validation against real machines. This paper describes a new analytical approach for response time estimation that is based on a detailed study of different approaches and assumptions. The approach has been validated against two commercial parallel DBMSs running on actual parallel machines and is shown to produce acceptable accuracy.

What database management system(s) should be employed in bioinformatics applications?

1. No DBMS: Although most computer scientists would dismiss this alternative as ridiculous, I would wager that a significant number of the hundreds of molecular biology DBs that now exist are not constructed using any DBMS. This should make us all stop and think. Either the biologists who are constructing these DBs know something that computer scientists don’t know, or they don’t know something that computer scientists do know. I believe the latter is the case—I believe that the vast majority of biologists have not received even the most elementary education in DBs, and have little idea of how DBMSs can help them, nor of how to go about using a DBMS. Note that the situation today is far improved over that of ten years ago. Today most large DB projects do employ a DBMS, whereas 10 years ago virtually none did so. Bioinformatics is still suffering from those mistakes. 2. Open-source DBMSs: Many of the molecular-biology DB projects that are employing DBMSs are employing open-source DBMSs to save money and because they believe that access to the source code will be useful in fixing any DBMS bugs that are discovered, or in extending the DBMS. My limited understanding of these DBMSs is that they have significant technical limitations compared to commercial DBMS product, yet little hard information about this issue is circulating in the bioinformatics community. We should obtain and circulate such information, whether from the literature or by empirical study. I also believe that the DBMSs are sufficiently complex that access to their source code is essentially useless to any but the most experienced DBMS hacker (and perhaps not even to them), and that this point must be articulated to the community. 3. Commercial DBMSs: Many projects use commercial relational DBMSs, specifically, Oracle. Virtually no projects use commercial object-oriented DBMSs (OO DBMSs). Why? OO DBMSs were developed to overcome limitations of relational DBMSs, and have now existed for about a decade. Little hard information about this issue is circulating in the bioinformatics community. We should obtain and circu-

An access control model supporting periodicity constraints and temporal reasoning

Access control models, such as the ones supported by commercial DBMSs, are not yet able to fully meet many application needs. An important requirement derives from the temporal dimension that permissions have in many real-world situations. Permissions are often limited in time or may hold only for specific periods of time. In this article, we present an access control model in which periodic temporal intervals are associated with authorizations. An authorization is automatically granted in the specified intervals and revoked when such intervals expire. Deductive temporal rules with periodicity and order constraints are provided to derive new authorizations based on the presence or absence of other authorizations in specific periods of time. We provide a solution to the problem of ensuring the uniqueness of the global set of valid authorizations derivable at each instant, and we propose an algorithm to compute this set. Moreover, we address issues related to the efficiency of access control by adopting a materialization approach. The resulting model provides a high degree of flexibility and supports the specification of several protection requirements that cannot be expressed in traditional access control models.

DBMS support for nonmetric measurement systems

In commercial DBMSs, it is possible to model only primitive numeric data types and perform arithmetic between them. In practice, however, organizations need to store and manipulate more complex numeric elements that could be interpreted as representing quantities of a non-numeric measurement system like that in which a distance is expressed in feet and inches, or like that in which a weight is expressed in quarters, stones, pounds, and ounces. This implies that users have to choose between two options, either to abandon the nonmetric system and completely adapt their applications to the limited capabilities of the DBMS, or to write their own pieces of code for the management of such more complex numeric data types. The first approach is principally unacceptable, and at the same time, there is a loss in the precision of arithmetic operations, because quantities have to be expressed as real numbers. The second one is tedious, because distinct pieces of code have to be written for handling different nonmetric units. Furthermore, integrity checking for these pieces of data has to be performed by application programs rather than by the DBMS. To overcome these problems, a new generic data type is proposed, the composite number, whose support automatically enables the use of any nonmetric measurement system. Functions and operations are defined for the management of composites. Because, in practice, time is usually expressed in many distinct nonmetric measurement units whose choice depends on the particular application, temporal databases represent one of the many application areas of the proposed formalization. >

Query optimization for parallel execution

The decreasing cost of computing makes it economically viable to reduce the response time of decision support queries by using parallel execution to exploit inexpensive resources. This goal poses the following query optimization problem: Minimize response time subject to constraints on throughput , which we motivate as the dual of the traditional DBMS problem. We address this novel problem in the context of Select-Project-Join queries by extending the execution space, cost model and search algorithm that are widely used in commercial DBMSs. We incorporate the sources and deterrents of parallelism in the traditional execution space. We show that a cost model can predict response time while accounting for the new aspects due to parallelism. We observe that the response time optimization metric violates a fundamental assumption in the dynamic programming algorithm that is the linchpin in the optimizers of most commercial DBMSs. We extend dynamic programming and show how optimization metrics which correctly predict response time may be designed.

A DBMS for large design automation databases

Large capacity Design Automation (CAD/CAM) database management systems require special capabilities over and above what commercial DBMSs or small workstation-based CAD/CAM systems provide. This paper describes one such system, Tacoma, used at Amdahl Corporation for the storage and retrieval of LSI and VLSI mainframe computer designs Tacoma is based on the relational model with additional object-oriented database features.