Join Queries Research Articles

Advanced Join Query Optimization Using Firefly and Reinforcement Learning Techniques on TPC-H Dataset

Advanced Join Query Optimization Using Firefly and Reinforcement Learning Techniques on TPC-H Dataset

Continual Observation of Joins under Differential Privacy

The problem of continual observation under differential privacy has been studied extensively in the literature. However, all existing works, with the exception of [28,51], have only studied the simple counting query and its derivatives. Join queries, which are arguably the most important class of queries in relational databases, have only been considered in [28,51], but the solutions offered there have two limitations: First, they only support a few specific graph pattern queries, which are special cases of joins. Second, they require hard degree/frequency constraints on the graph/database instance, and the privatized query answers have errors proportional to these constraints. In this paper, we propose a new differentially private mechanism for continual observation of joins that overcomes these two limitations. Our mechanism supports arbitrary joins and predicates, and do not require any constraints to be given in advance, even over an infinite stream. More importantly, it yields an error that is proportional to the actual maximum degree/frequencies in the graph/database instance at the current time of observation. Such an instance-specific utility guarantee is much preferred for the continual observation problem, where the database size and the query answer may change significantly over time.

Classic distance join queries using compact data structures

Distance-based Join Queries (DJQs) have multiple applications in spatial databases, Geographic Information Systems, and other areas. The K Closest Pairs Query (KCPQ) and the ε Distance Join Query (εDJQ) are well-known DJQs that have been widely studied and can be solved using plane-sweep techniques, which are efficient but must keep the whole datasets in main memory. In this work, we propose DJQ algorithms that work with data represented using a k2-tree, a compact data structure for binary grids. Our algorithms solve KCPQ and εDJQ queries, as well as several window-constrained variants, taking advantage of the indexing capabilities of k2-trees to efficiently answer queries without the need to decompress the data. Our experimental evaluation with large datasets shows that k2-tree algorithms are up to 5 times faster than plane-sweep algorithms in KCPQ, and 5–30 times faster in εDJQ. In variants that are window-constrained, our algorithms are competitive in most scenarios and faster for large windows. Additionally, our algorithms are not very affected by the distribution of the data and yield much more predictable query times, showing up to 30 times smaller variance in query times than plane sweep, depending on the location of the query window.

Thorough Data Pruning for Join Query in Database System

The improvement of robustness and efficiency for multi-way equijoin query is challenging, no-matter for centralized database systems or distributed database systems. Due to lots of unnecessary data existing during query processing, these two metrics will be seriously reduced. If we can thoroughly prune unnecessary data in advance, the robustness and efficiency will be highly improved. However, the pruning power of current strategies, such as predicate push-down and algebraic equivalence, is limited. We present deepDP, a powerful, generalized, and efficient strategy for data pruning. deepDP builds multiple independent pruning spaces by generating longest transitive closures and applies appropriate data pruning strategy for each pruning space. For thoroughly pruning unnecessary data, deepDP employs <inline-formula xmlns:mml="http://www.w3.org/1998/Math/MathML" xmlns:xlink="http://www.w3.org/1999/xlink"><tex-math notation="LaTeX">$\alpha \cdot \beta$</tex-math></inline-formula> pruning strategy to clean each pruning space based on a newly designed statistic information-Hollow Range and re-shuffles the elements in all pruned spaces for maximizing robustness and efficiency benefits meanwhile minimizing the invasion. We implement deepDP in PostgreSQL but are not limited to it, and evaluate deepDP on TPC-H, JOB, and our synthesis benchmark–DHR. The experiment results show that compared to traditional data pruning strategy, deepDP can improve multi-way equijoin query on efficiency by 3.5x.

ShadowAQP: Efficient Approximate Group-by and Join Query via Attribute-Oriented Sample Size Allocation and Data Generation

Approximate query processing (AQP) is one of the key techniques to cope with big data querying problem on account that it obtains approximate answers efficiently. To address non-trivial sample selection and heavy sampling cost issues in AQP, we propose ShadowAQP, an efficient and accurate approach based on attribute-oriented sample size allocation and data generation. We select samples according to group-by and join attributes, and determine the sample size for each group of unique value combinations to improve query accuracy. We design a conditional variational autoencoder model with automatic table data encoding and model update strategies. To further improve accuracy and efficiency, we propose a set of extensions, including parallel multi-round sampling aggregation, data outlier-aware sampling, and dimension reduction optimization. Evaluation results on diversified datasets show that, compared with SOTA approaches, ShadowAQP achieves 5.8× query speed performance improvement on average (up to 12.8×), while reducing query error by 74% on average (up to 95%) at the same time.

Doquet: Differentially Oblivious Range and Join Queries with Private Data Structures

Most cloud service providers offer limited data privacy guarantees, discouraging clients from using them for managing their sensitive data. Cloud providers may use servers with Trusted Execution Environments (TEEs) to protect outsourced data, while supporting remote querying. However, TEEs may leak access patterns and allow communication volume attacks, enabling an honest-but-curious cloud provider to learn sensitive information. Oblivious algorithms can be used to completely hide data access patterns, but their high overhead could render them impractical. To alleviate the latter, the notion of Differential Obliviousness (DO) has been recently proposed. DO applies differential privacy (DP) on access patterns while hiding the communication volume of intermediate and final results; it does so by trading some level of privacy for efficiency. We present Doquet: D ifferentially O blivious Range and Join Que ries with Private Data Struc t ures, a framework for DO outsourced database systems. Doquet is the first approach that supports private data structures, indices, selection, foreign key join, many-to-many join, and their composition select-join in a realistic TEE setting, even when the accesses to the private memory can be eavesdropped on by the adversary. We prove that the algorithms in Doquet satisfy differential obliviousness. Furthermore, we implemented Doquet and tested it on a machine having a second generation of Intel SGX (TEE); the results show that Doquet offers up to an order of magnitude speedup in comparison with other fully oblivious and differentially oblivious approaches.

Technical Perspective: Revisiting Runtime Dynamic Optimization for Join Queries in Big Data Management Systems

Query optimization is the process of finding an efficient query execution plan for a given SQL query. The runtime difference between a good and a bad plan can be tremendous. For example, in the case of TPC-H query 5, a query with 5 joins, the difference between the best and the worst plan is more than 10,000×. Therefore, it is vital to avoid bad plans. The dominating factor which differentiates a good from a bad plan is their join order and whether this join order avoids large intermediate results.

Revisiting Runtime Dynamic Optimization for Join Queries in Big Data Management Systems

Effective query optimization remains an open problem for Big Data Management Systems. In this work, we revisit an old idea, runtime dynamic optimization, and adapt it to a big data management system, AsterixDB. The approach runs in stages (re-optimization points), starting by first executing all predicates local to a single dataset. The intermediate result created by a stage is then used to re-optimize the remaining query. This re-optimization approach avoids inaccurate intermediate result cardinality estimates, thus leading to much better execution plans. While it introduces overhead for materializing intermediate results, experiments show that this overhead is relatively small and is an acceptable price to pay given the optimization benefits.

In-Network Processing of Skyline Join Queries in Wireless Sensor Networks Using Synopses of Skyline Attribute Value Ranges.

We investigate the in-network processing of a skyline join query in wireless sensor networks (WSNs). While much research was conducted on processing skyline queries in WSNs, skyline join queries were dealt with only in traditional centralized or distributed database environments. However, such techniques cannot be applied to WSNs. Carrying out join filtering, as well as skyline filtering using them in WSNs, is infeasible due to limited memory in senor nodes and to excessive energy consumption in wireless communications. In this paper, we propose a protocol to process a skyline join query in WSNs energy efficiently with only a small amount of memory in each sensor node. It uses a synopsis of skyline attribute value ranges, which is a very compact data structure. The range synopsis is used both in the search of anchor points for skyline filtering and in 2-way semijoins for join filtering. We describe the structure of a range synopsis and present our protocol. To optimize our protocol, we solve some optimization problems. Through implementation and a set of detailed simulations, we show the effectiveness of our protocol. The range synopsis is confirmed to be compact enough for our protocol to work well with the limited memory and energy in each sensor node. For the correlated and random distributions, our protocol significantly outperforms other possible protocols, confirming the effectiveness of an in-network skyline as well as the join filtering capabilities of our protocol.

JQPro:Join Query Processing in a Distributed System for Big RDF Data Using the Hash-Merge Join Technique

In the last decade, the volume of semantic data has increased exponentially, with the number of Resource Description Framework (RDF) datasets exceeding trillions of triples in RDF repositories. Hence, the size of RDF datasets continues to grow. However, with the increasing number of RDF triples, complex multiple RDF queries are becoming a significant demand. Sometimes, such complex queries produce many common sub-expressions in a single query or over multiple queries running as a batch. In addition, it is also difficult to minimize the number of RDF queries and processing time for a large amount of related data in a typical distributed environment encounter. To address this complication, we introduce a join query processing model for big RDF data, called JQPro. By adopting a MapReduce framework in JQPro, we developed three new algorithms, which are hash-join, sort-merge, and enhanced MapReduce-join for join query processing of RDF data. Based on an experiment conducted, the result showed that the JQPro model outperformed the two popular algorithms, gStore and RDF-3X, with respect to the average execution time. Furthermore, the JQPro model was also tested against RDF-3X, RDFox, and PARJs using the LUBM benchmark. The result showed that the JQPro model had better performance in comparison with the other models. In conclusion, the findings showed that JQPro achieved improved performance with 87.77% in terms of execution time. Hence, in comparison with the selected models, JQPro performs better.

Efficient distributed algorithms for distance join queries in spark-based spatial analytics systems

ABSTRACT Apache Sedona (formerly GeoSpark) is a new in-memory cluster computing system for processing large-scale spatial data, which extends the core of Apache Spark to support spatial datatypes, partitioning techniques, spatial indexes, and spatial operations (e.g. spatial range, nearest neighbor, and spatial join queries). Distance-based Join Queries (DJQs), like nearest neighbor join (kNNJQ) or closest pairs queries (kCPQ), are not supported by it. Therefore, in this paper, we investigate how to design and implement efficient DJQ distributed algorithms in Apache Sedona, using the most appropriate spatial partitioning and other optimization techniques. The results of an extensive set of experiments with real-world datasets are presented, demonstrating that the proposed kNNJQ and kCPQ distributed algorithms are efficient, scalable, and robust in Apache Sedona. Finally, Sedona is also compared to other similar cluster computing systems, showing the best performance for kCPQ and competitive results for kNNJQ.

Survey on Exact kNN Queries over High-Dimensional Data Space.

k nearest neighbours (kNN) queries are fundamental in many applications, ranging from data mining, recommendation system and Internet of Things, to Industry 4.0 framework applications. In mining, specifically, it can be used for the classification of human activities, iterative closest point registration and pattern recognition and has also been helpful for intrusion detection systems and fault detection. Due to the importance of kNN queries, many algorithms have been proposed in the literature, for both static and dynamic data. In this paper, we focus on exact kNN queries and present a comprehensive survey of exact kNN queries. In particular, we study two fundamental types of exact kNN queries: the kNN Search queries and the kNN Join queries. Our survey focuses on exact approaches over high-dimensional data space, which covers 20 kNN Search methods and 9 kNN Join methods. To the best of our knowledge, this is the first work of a comprehensive survey of exact kNN queries over high-dimensional datasets. We specifically categorise the algorithms based on indexing strategies, data and space partitioning strategies, clustering techniques and the computing paradigm. We provide useful insights for the evolution of approaches based on the various categorisation factors, as well as the possibility of further expansion. Lastly, we discuss some open challenges and future research directions.

Comparative Evaluation of Techniques for n-way Stream Joins in Wireless Sensor Networks

In wireless sensor networks, sensor data are accessed using relational queries. Join queries are commonly used to retrieve the data from multiple tables stored in different parts of a wireless sensor network. However, such queries require large amounts of energy. Many studies have intended to reduce query energy consumption. However, most of the proposed techniques addressed binary joins which are performed between static tables. N-way joins between data streams were rarely considered. Join queries using data streams work continuously and require increasing energy, which is why n-way joins involving several tables consume so much energy. Thus, the challenge lies in reducing energy dissipation. Additionally, it is necessary to determine the appropriate execution order for an n-way join. The number of possible implementations of an n-way join grows exponentially with the tables’ number. In this paper, interesting approaches for n-way joins between streams of data are evaluated. The methods that have been compared are extern-join, Sens-join of Stern et al, and the two techniques NSLJ (N-way Stream Local Join) and NSLSJ (N-way Stream Local Semi-Join). Comparisons are conducted according to several parameters to determine which use case is appropriate for each technique. NSLSJ works best for join queries with low join selectivity factors, while extern-join is more suitable for queries with very high selectivity factors.

Ranked enumeration of join queries with projections

Join query evaluation with ordering is a fundamental data processing task in relational database management systems. SQL and custom graph query languages such as Cypher offer this functionality by allowing users to specify the order via the ORDER BY clause. In many scenarios, the users also want to see the first k results quickly (expressed by the LIMIT clause), but the value of k is not predetermined as user queries are arriving in an online fashion. Recent work has made considerable progress in identifying optimal algorithms for ranked enumeration of join queries that do not contain any projections. In this paper, we initiate the study of the problem of enumerating results in ranked order for queries with projections. Our main result shows that for any acyclic query, it is possible to obtain a near-linear (in the size of the database) delay algorithm after only a linear time preprocessing step for two important ranking functions: sum and lexicographic ordering. For a practical subset of acyclic queries known as star queries, we show an even stronger result that allows a user to obtain a smooth tradeoff between faster answering time guarantees using more preprocessing time. Our results are also extensible to queries containing cycles and unions. We also perform a comprehensive experimental evaluation to demonstrate that our algorithms, which are simple to implement, improve up to three orders of magnitude in the running time over state-of-the-art algorithms implemented within open-source RDBMS and specialized graph databases.

Join queries optimization in the distributed databases using a hybrid multi-objective algorithm

Join queries optimization in the distributed databases using a hybrid multi-objective algorithm

Join query optimisation in the distributed databases using a hybrid harmony search and artificial bee colony algorithm

The query optimiser is a vital part of any distributed database mechanism. Reducing the execution period of the query depends on reaching an ideal query execution plan. Due to this issue's NP-hard nature, a hybrid harmony search and an artificial bee colony algorithm can be useful. The harmony is used to call query plans and signify them by S-dimension real vectors. A harmony memory is a place for creating and storing a primary population of harmony vectors. Then, bees explore harmony memory as a food source. The production of a novel nominate harmony out of all query plans in the harmony memory requires a pitch adjustment principle, a memory consideration one, and a random re-initialisation. Lastly, the new candidate vector replaces the worst harmony vector when it works better. The simulation outcomes have indicated that the introduced method reduces the expenses of evaluating a query compared to the harmony search and bee colony optimisation algorithms. However, this method has a longer execution time.

Join query optimisation in the distributed databases using a hybrid harmony search and artificial bee colony algorithm

Join query optimisation in the distributed databases using a hybrid harmony search and artificial bee colony algorithm

Evaluating Top-N Join Queries with Real-time Entity Resolution

Using nonmonotone ranking functions in top-N queries is a challenge. Traditional techniques for top-N queries are based on clean data without entity resolution (ER). For dirty datasets with duplicate tuples referring to the same real-world entity, these techniques may yield top-N tuples duplicates for a query. Consequently, the effective size of the result set of the query is less than N, and some useful tuples may fail to be retrieved from the datasets, which leads to poor effectiveness. Using an ER-Index based on a divide-and-conquer mechanism and nonmonotone ranking functions, in this paper, we propose a method for processing top-N join queries with real-time ER. This method integrates ER with the processing of a top-N join query over dirty datasets on the fly. Extensive experiments are conducted to measure the effectiveness and efficiency of the method over dirty datasets.

Data Search Using Hash Join Query and Nested Join Query

In accessing or searching data by using Query or Join on applications that are connected to a database, the usefulness of the implementation of the data and the process time is important to note. There are many ways that a system management database can do in processing and generating answers to a query. All methods will eventually produce the same answer (output) but they would still have different values, such as the time to respond to data. Some queries that are often used for processing data are Hash Join and Nested Join, both queries have different algorithms but produce the same output. A client-based or network-based application was employed to test both algorithms or queries with running time parameters or the time to respond to data. The test was conducted by connecting the number of tables and the number of rows/records. The result of the study is that the query hash join’s speed in response to small amounts of data is better than the large data in the nested join.

Improving Distance-Join Query processing with Voronoi-Diagram based partitioning in SpatialHadoop

SpatialHadoop is an extended MapReduce framework supporting global indexing techniques that partition spatial datasets across several machines and improve spatial query processing performance compared to traditional Hadoop systems. SpatialHadoop supports several spatial operations (e.g., K Nearest Neighbor search, range query, spatial intersection join, etc.) and seven spatial partitioning techniques (Grid, Quadtree, STR, STR+, k-d tree, Z-curve and Hilbert-curve). Distance-Join Queries (DJQs), like the K Nearest Neighbors Join Query (KNNJQ) and K Closest Pairs Query (KCPQ), are common operations used in numerous spatial applications. DJQs are costly operations, since they combine spatial joins with distance-based search. Data partitioning improves the management of large datasets and speeds up query performance. Therefore, performing DJQs efficiently with new partitioning methods in SpatialHadoop is a challenging task. In this paper, a new data partitioning technique based on Voronoi-Diagrams is designed and implemented in SpatialHadoop. Moreover, improved KNNJQ and KCPQ MapReduce algorithms, using the new partitioning mechanism, are also designed and developed for SpatialHadoop. Finally, the results of an extensive set of experiments with real-world datasets are presented, demonstrating that the new partitioning technique and the improved DJQ MapReduce algorithms are efficient, scalable and robust in SpatialHadoop.