Query Transformation Research Articles

Grouping, Subsumption, and Disjunctive Join Optimizations in Oracle

Query optimization must evolve with new workloads. As analytic and data warehouse workloads become more ubiquitous, optimization techniques that reduce the amount of data processed during query execution, enable shared computation and avoid expensive data access and joins must be rigorously explored. In this paper, we present aggregate-decomposition techniques as enhancements to an existing query transformation that performs grouping before joins. Consequently, the transformation generates more query rewrite candidates and can also be applied to a larger set of queries. Further, we introduce two new query transformations, i) subsumption of views and subqueries that explores opportunities for sharing computation and ii) union-all duplicator transformation for queries with disjunctive join predicates that removes the need for multiple data access and joins. These techniques are applicable to commonly noticed query patterns in customer workloads and provide significant performance benefit as indicated in our performance study. They have been implemented in Oracle RDBMS.

Window Function Expression: Let the Self-Join Enter

Window function expressions (WFEs) became part of the SQL:2003 standard, and since then, they have often been implemented in database systems (DBS). They are especially essential to OLAP DBSs, and people use them daily. Even though WFEs are a heavily used part of the SQL language, the amount of research done on their optimization in the last two decades is not significant. WFE does not extend the expressive power of the SQL language, but it makes writing SQL queries easier and more transparent. DBSs always compile SQL queries with WFE using a sequence of partition-sort-compute operators, which we call a linear strategy. Plans resulting from the linear strategy are robust and, in many cases, efficient. This article introduces an alternative strategy using a self-join, which is not considered in the current DBSs. We call it the self-join strategy, and it is based on an SQL query transformation where the result query uses a self-join query plan to compute WFE. One output of this work is a tool that can automatically perform such SQL query transformations. We created a microbenchmark showing that the self-join strategy is more effective than the linear strategy in many cases. We also performed a cost-based experiment to evaluate the query optimizers' ability to select an appropriate strategy. The article's main aim is to show that usage of the self-join strategy for queries with WFE is beneficial if selected in a cost-based manner.

A Study on Performance Improvement of Prompt Engineering for Generative AI with a Large Language Model

In the realm of Generative AI, where various models are introduced, prompt engineering emerges as a significant technique within natural language processing-based Generative AI. Its primary function lies in effectively enhancing the results of sentence generation by large language models (LLMs). Notably, prompt engineering has gained attention as a method capable of improving LLM performance by modifying the structure of input prompts alone. In this study, we apply prompt engineering to Korean-based LLMs, presenting an efficient approach for generating specific conversational responses with less data. We achieve this through the utilization of the query transformation module (QTM). Our proposed QTM transforms input prompt sentences into three distinct query methods, breaking them down into objectives and key points, making them more comprehensible for LLMs. For performance validation, we employ Korean versions of LLMs, specifically SKT GPT-2 and Kakaobrain KoGPT-3. We compare four different query methods, including the original unmodified query, using Google SSA to assess the naturalness and specificity of generated sentences. The results demonstrate an average improvement of 11.46% when compared to the unmodified query, underscoring the efficacy of the proposed QTM in achieving enhanced performance.

A Detailed Study on Aggregation Methods used in Natural Language Interface to Databases (NLIDB)

Historically, databases have been the most crucial issue in the study of information systems, and they constitute an essential part of all information management systems. Since, it complicated due to restricting the number of potential users, particularly non-expert database users who must comprehend the database structure to submit such queries. Natural language interface (NLI), the simplest method to retrieve information, is one possibility for interacting with the database. The transformation of a natural language query into a Structured Query (SQL) in a database is known as a "Natural Language Interface to Database" (NLIDB). This study uses NLIDB to handle the works performed under various aggregations with aggregation functions, a grouping phrase, and a possessing clause. This study carefully examines the numerous systematic aggregation approaches utilized in the NLIDB. This review provides extensive information about the many methods, including query-based, pattern-based, general, keyword-based NLIDB, and grammar-based systems, to extract data for a dissertation from a generic module for use in such systems that support query execution utilizing aggregations.

Query Transformation for Processing Streams in Decision-making Agents

An agent in pursuit of a task repeatedly perceives its environment through sensors, updates its state based on observations, and then decides which action to take, given the current state of the environment. Observations have in common that they are made at a given time point and thus referred to as temporal data. Usually, such temporal data is provided as stream data if the agent continuously receives the data, or it is provided as historic data if the stream data is stored in, for instance, a database the agent has access to. DBMSs are especially designed to process static data (i.e. non-temporal data) given a declarative query language such as SQL. However, if the aim is to exploit temporal data as required in time series analysis, SQL has its limits because it does not provide useful abstractions such as a window operator. Hence high-level declarative stream query languages, equipped with time-based window operators were designed. A challenge of those abstractions is the additional overhead of the algorithms that automatically transform high-level queries into low-level queries executable over DBMSs. If not handled properly those transformation algorithms may result in low-level queries with processing times too long for agents to make decisions. We describe a robust and optimized transformation algorithm for a high-level declarative stream query language and show that it leads to low-level queries with feasible processing times on real-world data.

Managing polyglot systems metadata with hypergraphs

A single type of data store can hardly fulfill every end-user requirements in the NoSQL world. Therefore, polyglot systems use different types of NoSQL datastores in combination. However, the heterogeneity of the data storage models makes managing the metadata a complex task in such systems, with only a handful of research carried out to address this. In this paper, we propose a hypergraph-based approach for representing the catalog of metadata in a polyglot system. Taking an existing common programming interface to NoSQL systems, we extend and formalize it as hypergraphs. Then, we define design constraints and query transformation rules for three representative data store types. Next, we propose a simple query rewriting algorithm from the metadata of the catalog to underlying data store specific ones and provide a prototype implementation. Furthermore, we introduce a storage statistics estimator on the underlying data stores. Finally, we show the feasibility of our approach on a use case of an existing polyglot system, and its usefulness in metadata and physical query path calculations.

Development of Natural Language Dialogue Software Systems

This paper discusses problems of natural language dialogue systems development. Here are considered different types of such program systems and their peculiarities based on our development experience. The systems discussed here are aimed at using graph ontological databases. Five basic types of natural language systems are highlighted: reference system, reference system with clarifying and technical questions, questionnaire system, active initiative system, control system. The first four of them are considered in detail. The reference system type, which is the basic type, is shown and discussed. Problems considered here are: natural language phrase to a formal query transformation, semantic analysis and answer formation. The ways are shown for these processes program implementation. For all the types the problem of the current dialogue subject keeping is considered, especially for an active initiative system where it is the most crucial. The different approaches are shown and compared for the principles of graph database (ontology) construction, which could be used in the dialogue systems.

Refining Boolean queries to identify relevant studies for systematic review updates.

Systematic reviews are important in health care but are expensive to produce and maintain. The authors explore the use of automated transformations of Boolean queries to improve the identification of relevant studies for updates to systematic reviews. A set of query transformations, including operator substitution, query expansion, and query reduction, were used to iteratively modify the Boolean query used for the original systematic review. The most effective transformation at each stage is identified using information about the studies included and excluded from the original review. A dataset consisting of 22 systematic reviews was used for evaluation. Updated queries were evaluated using the included and excluded studies from the updated version of the review. Recall and precision were used as evaluation measures. The updated queries were more effective than the ones used for the original review, in terms of both precision and recall. The overall number of documents retrieved was reduced by more than half, while the number of relevant documents found increased by 10.3%. Identification of relevant studies for updates to systematic reviews can be carried out more effectively by using information about the included and excluded studies from the original review to produce improved Boolean queries. These updated queries reduce the overall number of documents retrieved while also increasing the number of relevant documents identified, thereby representing a considerable reduction in effort required by systematic reviewers.

Applying and developing semantic web technologies for exploiting a corpus in history of science: The case study of the Henri Poincaré correspondence

The Henri Poincaré correspondence is a corpus of letters sent and received by this mathematician. The edition of this correspondence is a long-term project begun during the 1990s. Since 1999, a website is devoted to publish online this correspondence with digitized letters. In 2017, it has been decided to reforge this website using Omeka S. This content management system offers useful services but some user needs have led to the development of an RDFS infrastructure associated to it. Approximate and explained searches are managed thanks to SPARQL query transformations. A prototype for efficient RDF annotation of this corpus (and similar corpora) has been designed and implemented. This article deals with these three research issues and how they are addressed.

IPDS: A semantic mediator‐based system using Spark for the integration of heterogeneous proteomics data sources

SummaryWith the constant rise of data volumes in many disciplines, various new Big data management systems have emerged to provide scalable tools for efficient data integration, processing, and analysis. In this article, we provide an overview of biomedical data integration systems focusing on ontology‐based semantic systems and Big data technologies based systems such as Apache Spark. We also propose a new semantic data integration system, called Integrated Proteomics Data System (IPDS), which uses a mediator approach. IPDS provides users a unified interface for query processing and data exploration. This system takes advantage of the Apache Spark framework to perform the query transformation and execution needed to question the integrated data sources. We develop a domain ontology that allows the user to formulate its queries in terms defined in the ontology. IPDS is a case study of semantic proteomics data integration linking four data sources UniProt (protein annotation), String (protein‐protein interaction), PDB (protein structure), and Pubmed (biomedical citation).

Платформа комплексирования и тестирования средств трансформации естественно-языковых запросов в SPARQL-запросы

In the paper we discuss how to support the process of creating tools which transform natural language (NL) queries into SPARQL queries (hereinafter referred to as a transformation tool). In the introduction, we describe the relevance of the task of understanding natural language queries by information systems, as well as the advantages of using ontologies as a means of representing knowledge for solving this problem. This ontology-based data access approach can be also used in systems which provide natural language interface to databases. Based on the analysis of problems related to the integration and testing of existing transformation tools, as well as to support the creation and testing own transformation modules, the concept of a software platform that simplifies these tasks is proposed. The platform architecture satisfies the requirements for ease of connecting third party transformation tools, reusing individual modules, as well as integrating the resulting transformation tools into other systems, including testing systems. The building blocks of the created transformation systems are the individual transformation modules packaged in Docker containers. Program access to each module is carried out using gRPC. Modules loaded into the platform can be built into the transformation pipeline automatically or manually using the built-in third party SciVi data flow diagram editor. Compatibility of individual modules is controlled by automatic analysis of application programming interfaces. The resulting pipeline is combined according to specified data flow into a single multi-container application that can be integrated into other systems, as well as tested on extendable test suites. The expected and actual results of the query transformation are available for viewing in graphical form in the visualization tool developed earlier.

A Robust Optimization Approach of SQL-to-SPARQL Query Rewriting

In order to ensure the interoperability between semantic web and relational databases, several approaches have been developed to ensure SQL-to-SPARQL query transformation direction, but all these approaches have the same weakness. In fact, they convert directly the input SQL query to its equivalent SPARQL one without any pre-processing phase enabling the optimization of this input query filled by users before starting the conversion process. This weakness has motivated us to add a pretreatment phase aiming to optimize the most important SQL statements which seem to have the biggest impact on the effectiveness of the transformed queries. Our main contribution is to enrich these rewriting systems by adding an optimization layer that integrate a set of simplification rules of Left, Right and Full Outer Join in order to avoid, firstly unnecessary operations during the conversion process, and secondly SPARQL queries with a high complexity due to Optional patterns obtained from outer join in this conversion context.

Mind your dependencies for semantic query optimization

Semantic query optimization uses dependencies between attributes to formulate query transformations and revise the number of processed rows, with direct impact on performance. Commercial databases present facilities to define dependencies as not enforced constraints. The goal is to help the query optimizer in cases where the database is denormalized or simply lost dependencies in the design. However, feeding these facilities is a manual task which is tedious and error-prone. An attractive alternative is the automatic discovery of dependencies, but the cost of finding dependencies increases with the number of rows and attributes in the dataset. In this paper, we stick to the automatic discovery approach, but to reduce the cost we focus on dependencies matching the current queries in the pipe (ie., workload). Initially, we rely on a large set of functional dependencies computed in batch with state of the art algorithms in the literature. Over time our focused dependency selector (FDSel) chooses exemplars to feed the query optimizer. Therewith we eliminate further manual interactions. The automatically selected exemplars exhibit statistical properties that resemble those of the initial dependency set. This demonstrates the effectiveness of our proposed approach. In the best case scenario, by applying the FDSel for join elimination on a real-world database, we reduce query response time by more than one order of magnitude.

Statisticum

We introduce a new concept of leveraging traditional data statistics as dynamic data integrity constraints. These data statistics produce transient database constraints, which are valid as long as they can be proven to be consistent with the current data. We denote this type of data statistics by constraint data statistics , their properties needed for consistency checking by consistency metadata , and their implied integrity constraints by implied data statistics constraints ( implied constraints for short). Implied constraints are valid integrity constraints which are powerful query optimization tools employed, just as traditional database constraints, in semantic query transformation (aka query reformulation), partition pruning, runtime optimization, and semi-join reduction, to name a few. To our knowledge, this is the first work introducing this novel and powerful concept of deriving implied integrity constraints from data statistics. We discuss theoretical aspects of the constraint data statistics concept and their integration into query processing. We present the current architecture of data statistics management in SAP HANA and detail how constraint data statistics are designed and integrated into this architecture. As an instantiation of this framework, we consider dynamic partition pruning for data aging scenarios. We discuss our current implementation for constraint data statistics objects in SAP HANA which can be used for dynamic partition pruning. We enumerate their properties and show how consistency checking for implied integrity constraints is supported in the data statistics architecture. Our experimental evaluations on the TPC-H benchmark and a real customer application confirm the effectiveness of the implied integrity constraints; (1) for 59% of TPC-H queries, constraint data statistics utilization results in pruning cold partitions and reducing memory consumption, and (2) we observe up to 3 orders of magnitude speed-up in query processing time, for a real customer running an S/4HANA application.

Adaptive statistics in Oracle 12c

Database Management Systems (DBMS) continue to be the foundation of mission critical applications, both OLTP and Analytics. They provide a safe, reliable and efficient platform to store and retrieve data. SQL is the lingua franca of the database world. A database developer writes a SQL statement to specify data sources and express the desired result and the DBMS will figure out the most efficient way to implement it. The query optimizer is the component in a DBMS responsible for finding the best execution plan for a given SQL statement based on statistics, access structures, location, and format. At the center of a query optimizer is a cost model that consumes the above information and helps the optimizer make decisions related to query transformations, join order, join methods, access paths, and data movement. The final execution plan produced by the query optimizer depends on the quality of information used by the cost model, as well as the sophistication of the cost model. In addition to statistics about the data, the cost model also relies on statistics generated internally for intermediate results, e.g. size of the output of a join operation. This paper presents the problems caused by incorrect statistics of intermediate results, survey the existing solutions and present our solution introduced in Oracle 12c. The solution includes validating the generated statistics using table data and via the automatic creation of auxiliary statistics structures. We limit the overhead of the additional work by confining their use to cases where it matters the most, caching the computed statistics, and using table samples. The statistics management is automated. We demonstrate the benefits of our approach based on experiments using two SQL workloads, a benchmark that uses data from the Internal Movie Data Base (IMDB) and a real customer workload.

Optimisation of language-integrated queries by query unnesting

Native functional-style querying extensions for programming languages (e.g., LINQ or Java 8 streams) are widely considered as declarative. However, their very limited degree of optimisation when dealing with local collection processing contradicts this statement. We show that developers constructing complex LINQ queries or combining queries expose themselves to the risk of severe performance deterioration. For an inexperienced programmer, a way of getting an appropriate query form can be too complicated. Also, a manual query transformation is justified by the need of improving performance, but achieved at the expense of reflecting an actual business goal. As a result, benefits from a declarative form and an increased level of abstraction are lost.In this paper, we claim that moving of selected methods for automated optimisation elaborated for declarative query languages to the level of imperative programming languages is possible and desired. Our approach is based on the assumption that programmer is able distinguish whether a language-integrated query is intentionally used to introduce some side-effects or its sole purpose is to only query the data. We propose two optimisation procedures through query unnesting designed to avoid unnecessary multiple calculations in collection-processing constructs based on higher-order functions. We have implemented and verified this idea as a simple proof-of-concept LINQ optimiser library.

Path index based keywords to SPARQL query transformation for semantic data federations

Semantic web is a highly emerging research domain. Enhancing the ability of keyword query processing on Semantic Web data provides a huge support for familiarizing the usefulness of Semantic Web to the general public. Most of the existing approaches focus on just user keyword matching to RDF graphs and output the connecting elements as results. Semantic Web consists of SPARQL query language which can process queries more accurately and efficiently than general keyword matching. There are only about a couple of approaches available for transforming keyword queries to SPARQL. They basically rely on real time graph traversals? for identifying subgraphs which can connect user keywords. Those approaches are either limited to query processing on a single data store or a set of interlinked data sets. They have not focused on query processing on a federation of independent data sets which belongs to the same domain. This research proposes a Path Index based approach eliminating real time graph traversal for transforming keyword queries to SPARQL. We have introduced an ontology alignment based approach for keyword query transforming on a federation of RDF data stored using multiple heterogeneous vocabularies. Evaluation shows that the proposed approach have the ability to generate SPARQL queries which can provide highly relevant results for user keyword queries. The Path Index based query transformation approach has also achieved high efficiency compared to the existing approach.

Development of Query Transformation Method by Cost Optimization

The transformation time among queries in the database management system (DBMS) is responsible for the execution time of users’ queries, because a conventional DBMS does not consider the transformation cost when queries are transformed for execution. To reduce the transformation time (cost reduction) during execution, we propose an optimal query transformation method by exploring queries from a cost-based point of view. This cost-based point of view means considering the cost whenever queries are transformed for execution. Toward that end, we explore and compare set off heuristic, linear, and exhaustive costbased transformations. Further, we describe practical methods of cost-based transformation integration and some query transformation problems. Our results show that, some cost-based transformations significantly improve query execution time. For instance, linear and heuristic transformed queries work 43% and 74% better than exhaustive queries.

Expertization Level Ranking for Query Transformation

Objectives: To develop an approach to find one’s expertization level in a given field. Methods/Statistical Analysis: The search engines were utilized to extract the expert’s data available in the internet. The results generated by the search engines were downloaded in the database. Intern these results were used as an input to the relevance and expertise mapping process. We have used Relevance Algorithm and Expertise Mapping Algorithm to process the data generated by the search engines. These process yields one’s expertization level on a particular area. Findings: E-learning systems were automated to identify an expert by using two methods self-classification and document-based-relevance. These methods assume that relevance of one’s specified keywords or documents to the query is positively related to their expertise. In reality, one can be identified as an expert in a specific domain by the contribution made on that particular domain. The expert expertization data available in websites could be utilized by the e-learning systems to evaluate the expert’s. Hence it is proposed to ensure the expertise level of an expert using a Dynamic Expertization Estimating System (DEES) in an e-learning environment. In this approach, search engines were utilized as an agent to extract the expert’s data available in the internet and weightage were given to the expert according to their contribution made by the expert towards the given expertise area. The results retrieved by applying this mechanism yielded data with high accuracy levels to ensure the expertization level of an expert. Application/Improvements: Connecting the Dynamic Expertization Estimating System (DEES) with social media to fetch more data pertaining to the expert expertization and produce accurate expertise level for each expert.

Incremental computations over strongly distributed databases

SummaryThis contribution deals with systematic exploitation of logical reduction techniques to big distributed data handling. The particular applications are views and parallel updates over large‐scale distributed databases as well as handling of queries over different generations of databases. Logical reduction techniques come in two favors. The first one: the syntactically defined translation schemes, which describe transformations of database schemes. They give rise to two induced maps, translations and transductions. Transductions describe the induced transformation of database instances and the translations describe the induced transformations of queries. The second one: Feferman‐Vaught reductions, which are applied in situations of distributed databases. The reduction describes how the queries over a distributed database can be computed from queries over the components and queries over the index set. Combination and development of these techniques allow us to introduce the notion of strongly distributed databases. For such databases, we extend and generalize the known propagation techniques. The method allows unification of the distributed and parallel computation and communication as well as significant reduction of the communication load. The proposed general approach may be easily adopted to other distributed objects and their integration into large‐scale systems. Copyright © 2015 John Wiley & Sons, Ltd.