sundry

OceanBase subquery

In data, sub queries can be divided into dependent subqueries and non dependent subqueries. A dependent subquery is that the execution of the subquery depends on the 'variable' of the external query, so this seed query is usually counted many times. A subquery without dependency is that the execution of the sub query does not depend on the 'variable' of the external query. This seed query generally needs to be computed only once. The following graphs show a sub query with no dependencies and a dependent subquery.

  1. OceanBase (root@test) > create table t1 (a int primary key, B int, key);
  2. Query OK, 0 rows affected (0.70 sec)
  3. OceanBase (root@test) > CREATE TABLE t2 (a int primary key, B int, key);
  4. Query OK, 0 rows affected (0.92 sec)
  5. -- subqueries without dependencies
  6. OceanBase (root@test) > select * from T1 where t1.a in (select t2.a from T2);
  7. Empty Set (0.22 sec)
  8. A subquery with dependencies. The outer query variable t1.b is used in the subquery.
  9. OceanBase (root@test) > select * from T1 where t1.a in (select t2.a from, select = =);
  10. Empty Set (0.05 sec)

In Oceanbase, subqueries are executed by subplan filter operators. The Subplan filter operator traverses every row in the outer query, and for every row that traverses, the corresponding sub query is checked to determine whether the condition is satisfied, which is somewhat similar to the Nested Loop Join. In order to execute sub queries efficiently, subqueries satisfying specific conditions can be rewritten to join statements, so that optimizer can have more choices when selecting connection algorithms (except Nested Loop Join, Hash Join and Merge Join). In Oceanbase, subqueries without dependencies are rewritten to join statements. For dependent subqueries, they can be rewritten to join statements only if they satisfy certain conditions. The following figure shows a subquery that has not been rewritten as a join statement and a subquery that has not been rewritten as a join statement.

  1. OceanBase (root@test) > create table t1 (a int primary key, B int, key);
  2. Query OK, 0 rows affected (0.70 sec)
  3. OceanBase (root@test) > CREATE TABLE t2 (a int primary key, B int, key);
  4. Query OK, 0 rows affected (0.92 sec)
  5. A dependent subquery is rewritten to semi-join and implemented using hash semi-join.
  6. OceanBase (root@test) > explain select * from T1 where t1.a in (select t2.c, in = 0).
  7. = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
  8. |ID|OPERATOR |NAME|EST. ROWS|COST|
  9. ---------------------------------------
  10. |0 |HASH SEMI JOIN| |1 |2924|
  11. |1 TABLE SCAN |t1 |1000 |455
  12. |2 TABLE SCAN |t2 |1000 |455
  13. = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
  14.   
  15. Outputs & filters:
  16. -------------------------------------
  17. 0 - output ([t1.a], [t1.b], [t1.c]), filter (NIL),
  18. Equal_conds ([t1.a = t2.c], [t2.b = t1.b]), other_conds (NIL)
  19. 1 - output ([t1.b], [t1.a], [t1.c]), filter (NIL),
  20. Access ([t1.b], [t1.a], [t1.c]), partitions (P0)
  21. 2 - output ([t2.b], [t2.c]), filter (NIL).
  22. Access ([t2.b], [t2.c]), partitions (P0)
  23. A dependent subquery can not be rewritten to semi-join, which is implemented by subplan filter.
  24. OceanBase (root@test) > explain select * from T1 where t1.a > (select sum (t2.c), select = = =);
  25. |ID|OPERATOR |NAME|EST. ROWS|COST
  26. -------------------------------------------
  27. |0 |SUBPLAN FILTER |334 |207683|
  28. |1 TABLE SCAN |t1 |334 |176
  29. |2 SCALAR GROUP BY| |1 |623
  30. |3 TABLE SCAN |t2 |2 |622
  31. = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
  32.   
  33. Outputs & filters:
  34. -------------------------------------
  35. 0 - output ([t1.a], [t1.b], [t1.c]), filter ([t1.a > subquery (1)]).
  36. Exec_params_ ([t1.b]), onetime_exprs_ (NIL), init_plan_idxs_ (NIL)
  37. 1 - output ([t1.b], [t1.a], [t1.c]), filter (NIL),
  38. Access ([t1.b], [t1.a], [t1.c]), partitions (P0)
  39. 2 - output ([T_FUN_SUM (t2.c)]), filter (NIL),
  40. Group (NIL), agg_func ([T_FUN_SUM (t2.c)])
  41. 3 - output ([t2.c]), filter ([t2.b =?]).
  42. Access ([t2.b], [t2.c]), partitions (P0)
Fabulous ( One thousand three hundred and two )

This article is composed of Ji Chang Xin Creation, article address: Https://blog.isoyu.com/archives/oceanbasezichaxun.html
Use Knowledge sharing signature 4 The international license agreement is licensed. In addition to the reprint / provenance, all originals or translations of this site must be signed before retransmission. The final editing time is September, 16, 2018 at 04:36 afternoon.

Key word:

Hot articles

Commentary:

5 comments, visitors: 4, blogger: 1
  1.  Ji Chang Xin
    Ji Chang Xin Published in:

    Morning.

Comment

[required]

Invisibility?

Please wait three seconds after submission, so as not to cause unsuccessful successes and duplication.