Query Rewrite refers to rewriting an SQL query into another one that is easier to optimize.
OceanBase Database supports two query rewrite rules: rule-based rewrite and cost-based rewrite.
Rule-based query rewrite always makes an SQL query better, making it possible to keep optimizing the SQL query. A typical rule-based rewrite is to join result sets of subqueries into the same result set. Before the rewrite, Nested Loop Join is the only option to execute subqueries. After the rewrite, the optimizer has two more options: Hash Join and Merge Join.
Cost-based query rewrite does not necessarily make a SQL query better. A cost model is required for evaluation. A typical cost-based rewrite is OR-Expansion.
Usually, a rewrite rule of a database can be implemented only when it meets specific conditions. Many rewrite rules are interactive, meaning that the rewrite of a rule triggers that of another. In OceanBase Database, rule-based query rewrites are classified into multiple sets of rules. Each set of rules in OceanBase Database is iteratively rewritten until the SQL query cannot be rewritten any more, or the number of iterations reaches the preset threshold. The same applies to cost-based rewrites.
Note that a cost-based rewrite may in turn trigger a rule-based rewrite. In other words, the cost-based rewrite and the rule-based rewrite are iteratively executed.