OceanBase AI Database supports only one type of cost-based query rewrite: OR-EXPANSION.
Many advanced rewriting rules in databases, such as complex view merge and window function rewrite, require cost-based evaluation. Future versions of OceanBase AI Database will support these complex rewriting rules.
OR-EXPANSION
OR-EXPANSION rewrites a query to several subqueries, and the result sets of these subqueries are combined into the same result set through the UNION operator. This allows you to optimize each subquery. However, the rewrite also results in the execution of multiple subqueries. You must determine whether to perform the rewrite based on the cost analysis.
The OR-EXPANSION rewrite mainly serves the following purposes:
Allow each branch to use different indexes to speed up the query.
In the following example, query Q1 is rewritten to Q2, where
LNNVL(t1.a = 1), the predicate in Q2, ensures that the two subqueries do not generate duplicate results. Before the rewrite, Q1 generally accesses the primary table. After the rewrite, if indexes (a) and (b) are created on tablet1, subqueries of Q2 are allowed to use different indexes for data access.Q1: obclient> SELECT * FROM t1 WHERE t1.a = 1 OR t1.b = 1; Q2: obclient> SELECT * FROM t1 WHERE t1.a = 1 UNION ALL SELECT * FROM t1 WHERE t1.b = 1 AND LNNVL(t1.a = 1);Here is a complete example:
obclient> CREATE TABLE t1(a INT, b INT, c INT, d INT, e INT, INDEX IDX_a(a), INDEX IDX_b(b)); Query OK, 0 rows affected /*Without the OR-EXPANSION rewrite, this query can only use the primary table access path.*/ obclient> EXPLAIN SELECT/*+NO_REWRITE()*/ * FROM t1 WHERE t1.a = 1 OR t1.b = 1; +--------------------------------------------------------------+ | Query Plan | +--------------------------------------------------------------+ | =================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ----------------------------------- |0 |TABLE SCAN|t1 |4 |649 | =================================== Outputs & filters: ------------------------------------- 0 - output([t1.a], [t1.b], [t1.c], [t1.d], [t1.e]), filter([t1.a = 1 OR t1.b = 1]), access([t1.a], [t1.b], [t1.c], [t1.d], [t1.e]), partitions(p0) /*After the rewrite, each subquery can use a different index access path.*/ obclient>EXPLAIN SELECT * FROM t1 WHERE t1.a = 1 OR t1.b = 1; +------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------+ | ========================================= |ID|OPERATOR |NAME |EST. ROWS|COST| ----------------------------------------- |0 |UNION ALL | |3 |190 | |1 | TABLE SCAN|t1(idx_a)|2 |94 | |2 | TABLE SCAN|t1(idx_b)|1 |95 | ========================================= Outputs & filters: ------------------------------------- 0 - output([UNION(t1.a, t1.a)], [UNION(t1.b, t1.b)], [UNION(t1.c, t1.c)], [UNION(t1.d, t1.d)], [UNION(t1.e, t1.e)]), filter(nil) 1 - output([t1.a], [t1.b], [t1.c], [t1.d], [t1.e]), filter(nil), access([t1.a], [t1.b], [t1.c], [t1.d], [t1.e]), partitions(p0) 2 - output([t1.a], [t1.b], [t1.c], [t1.d], [t1.e]), filter([lnnvl(t1.a = 1)]), access([t1.a], [t1.b], [t1.c], [t1.d], [t1.e]), partitions(p0)Allow each branch to use different join algorithms to speed up the query and avoid Cartesian joins.
In the following example, query Q1 is rewritten to Q2. For Q1, the nested loop join, which results in a Cartesian product, is the only join option available. After the rewrite, nested loop join, hash join, and merge join are available for each subquery, providing more options for optimization.
Q1: obclient> SELECT * FROM t1, t2 WHERE t1.a = t2.a OR t1.b = t2.b; Q2: obclient> SELECT * FROM t1, t2 WHERE t1.a = t2.a UNION ALL SELECT * FROM t1, t2 WHERE t1.b = t2.b AND LNNVL(t1.a = t2.a);Here is a complete example:
obclient> CREATE TABLE t1(a INT, b INT); Query OK, 0 rows affected obclient> CREATE TABLE t2(a INT, b INT); Query OK, 0 rows affected /*If not rewritten, only a nested loop join can be used.*/ obclient> EXPLAIN SELECT/*+NO_REWRITE()*/ * FROM t1, t2 WHERE t1.a = t2.a OR t1.b = t2.b; +--------------------------------------------------------------------------+ | Query Plan | +--------------------------------------------------------------------------+ | =========================================== |ID|OPERATOR |NAME|EST. ROWS|COST | ------------------------------------------- |0 |NESTED-LOOP JOIN| |3957 |585457| |1 | TABLE SCAN |t1 |1000 |499 | |2 | TABLE SCAN |t2 |4 |583 | =========================================== Outputs & filters: ------------------------------------- 0 - output([t1.a], [t1.b], [t2.a], [t2.b]), filter(nil), conds(nil), nl_params_([t1.a], [t1.b]) 1 - output([t1.a], [t1.b]), filter(nil), access([t1.a], [t1.b]), partitions(p0) 2 - output([t2.a], [t2.b]), filter([? = t2.a OR ? = t2.b]), access([t2.a], [t2.b]), partitions(p0) /*After the rewrite, each subquery uses a hash join.*/ obclient> EXPLAIN SELECT * FROM t1, t2 WHERE t1.a = t2.a OR t1.b = t2.b; +--------------------------------------------------------------------------+ | Query Plan | +--------------------------------------------------------------------------+ |ID|OPERATOR |NAME|EST. ROWS|COST| ------------------------------------- |0 |UNION ALL | |2970 |9105| |1 | HASH JOIN | |1980 |3997| |2 | TABLE SCAN|t1 |1000 |499 | |3 | TABLE SCAN|t2 |1000 |499 | |4 | HASH JOIN | |990 |3659| |5 | TABLE SCAN|t1 |1000 |499 | |6 | TABLE SCAN|t2 |1000 |499 | ===================================== Outputs & filters: ------------------------------------- 0 - output([UNION(t1.a, t1.a)], [UNION(t1.b, t1.b)], [UNION(t2.a, t2.a)], [UNION(t2.b, t2.b)]), filter(nil) 1 - output([t1.a], [t1.b], [t2.a], [t2.b]), filter(nil), equal_conds([t1.a = t2.a]), other_conds(nil) 2 - output([t1.a], [t1.b]), filter(nil), access([t1.a], [t1.b]), partitions(p0) 3 - output([t2.a], [t2.b]), filter(nil), access([t2.a], [t2.b]), partitions(p0) 4 - output([t1.a], [t1.b], [t2.a], [t2.b]), filter(nil), equal_conds([t1.b = t2.b]), other_conds([lnnvl(t1.a = t2.a)]) 5 - output([t1.a], [t1.b]), filter(nil), access([t1.a], [t1.b]), partitions(p0) 6 - output([t2.a], [t2.b]), filter(nil), access([t2.a], [t2.b]), partitions(p0)Allow each branch to separately perform sorting elimination, which accelerates the retrieval of the TOP-K results.
In the following example, query Q1 is rewritten to Q2. For Q1, the only way of execution is to find the rows that fit the condition, sort them, and then retrieve the TOP-10 results. For Q2, if index (a,b) exists, both subqueries of Q2 can use the index to eliminate sorting, each retrieve the TOP-10 results, and then the final TOP-10 rows are obtained by sorting these 20 rows of data once.
Q1: obclient> SELECT * FROM t1 WHERE t1.a = 1 OR t1.a = 2 ORDER BY b LIMIT 10; Q2: obclient> SELECT * FROM (SELECT * FROM t1 WHERE t1.a = 1 ORDER BY b LIMIT 10 UNION ALL SELECT * FROM t1 WHERE t1.a = 2 ORDER BY b LIMIT 10) AS TEMP ORDER BY temp.b LIMIT 10;Here is a complete example:
obclient> CREATE TABLE t1(a INT, b INT, INDEX IDX_a(a, b)); Query OK, 0 rows affected /*If not rewritten, sorting is required to obtain the top K results.*/ obclient> EXPLAIN SELECT/*+NO_REWRITE()*/ * FROM t1 WHERE t1.a = 1 OR t1.a = 2 ORDER BY b LIMIT 10; +-------------------------------------------------------------------------+ | Query Plan | +-------------------------------------------------------------------------+ | ========================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------------ |0 |LIMIT | |4 |77 | |1 | TOP-N SORT | |4 |76 | |2 | TABLE SCAN|t1(idx_a)|4 |73 | ========================================== Outputs & filters: ------------------------------------- 0 - output([t1.a], [t1.b]), filter(nil), limit(10), offset(nil) 1 - output([t1.a], [t1.b]), filter(nil), sort_keys([t1.b, ASC]), topn(10) 2 - output([t1.a], [t1.b]), filter(nil), access([t1.a], [t1.b]), partitions(p0) /*If rewritten, the sorting operator can be eliminated, ultimately obtaining the TOP-K results.*/ obclient>EXPLAIN SELECT * FROM t1 WHERE t1.a = 1 OR t1.a = 2 ORDER BY b LIMIT 10; +-------------------------------------------------------------------------+ | Query Plan | +-------------------------------------------------------------------------+ | =========================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------------- |0 |LIMIT | |3 |76 | |1 | TOP-N SORT | |3 |76 | |2 | UNION ALL | |3 |74 | |3 | TABLE SCAN|t1(idx_a)|2 |37 | |4 | TABLE SCAN|t1(idx_a)|1 |37 | =========================================== Outputs & filters: ------------------------------------- 0 - output([UNION(t1.a, t1.a)], [UNION(t1.b, t1.b)]), filter(nil), limit(10), offset(nil) 1 - output([UNION(t1.a, t1.a)], [UNION(t1.b, t1.b)]), filter(nil), sort_keys([UNION(t1.b, t1.b), ASC]), topn(10) 2 - output([UNION(t1.a, t1.a)], [UNION(t1.b, t1.b)]), filter(nil) 3 - output([t1.a], [t1.b]), filter(nil), access([t1.a], [t1.b]), partitions(p0), limit(10), offset(nil) 4 - output([t1.a], [t1.b]), filter([lnnvl(t1.a = 1)]), access([t1.a], [t1.b]), partitions(p0), limit(10), offset(nil)