Optimization of correlate subquery based on distributed database

Chenyu Zhang,Yantao Yue,Tianze Pang,Wenjie Liu

doi:10.1051/jnwpu/20213940909

Abstract

Subquery is widely used in database. It can be divided into related subquery and non-related subquery according to whether it is dependent on the table of the parent query. For related subqueries, it is necessary to take a tuple from the parent query before executing the subquery, that is, the content of the subquery needs to be repeatedly operated. Disk access costs of this strategy is very big, in the distributed database, because of data communication overhead, in the parent query yuan set is too low efficiency, therefore, for the class sub queries, on the basis of the optimization of the existing query strategy, combining with the characteristics of distributed database, put forward by the subquery on to join queries, eliminate redundant clauses in the subquery, eliminate accumulation function method based on distributed database query optimization strategy, and the effectiveness of the present optimization strategy is verified by experiment.

Highlights

Subquery is widely used in database
It can be divided into related subquery
whether it is dependent on the table of the parent query

Summary

Introduction

例:SELECT∗FROM T1 WHERE T1.C2 = 1 AND EXISTS( SELECT COUNT( ∗) FROM T2 WHERE T1. C1 = T2.C1) ; 优化后: SELECT∗FROM T1 WHERE T1.C2 = 1; 2.3.3 子查询上拉由于在语义上,IN 相关子查询和 EXISTS 相关子查询是等价的,又都等价于半连接语义,所以可以考虑将子查询中的表与条件展开上拉到主查询中, 这样可以使得嵌套的子查询变成了一层的连接查询,减少了查询执行的次数。由于目前的 CBASE 中暂不支持半连接语义的查询,由 1.1.1 节的公式可知,2 个关系 R 与 S 在某一属性上的半连接,等价于对 R 与 S 在该属性上进行内连接后对关系 R 的属性进行投影。所以在这里采用内连接的策略,将 IN 相关子查询的内容,上拉合并到主查询中。将 IN 子查询的表作为连接表合并进主查询的 FROM 中,用 INNER JOIN 连接,将子查询中与主查询相关的过滤条件作为连接条件,其余过滤条件上拉合并至主查询的过滤条件中。对于 IN 相关的内外表达式,依次构造成等值过滤条件,添加进连接条件中, 对于主查询的目标, 需要进行额外的去重操作。但是这种优化策略仅支持简单的相关子查询, 要求子查询中不含有聚集函数,不含有 with 子句, 且子查询的 FROM 和 WHERE 内容不能为空,否则无法构造连接查询。具体如下: 例: SELECT C1 FROM T1 WHERE C2 IN

Results

Conclusion