sundry

OceanBase subquery

In the data, the sub query can be divided into sub queries and the dependence is not dependent on the sub query. A dependence of the sub query means' variable 'execution of the sub query on the external query, so the subquery will usually be counted multiple times. No dependence of the sub query means' variable 'the sub query execution does not depend on the external query, the subquery generally only need to calculate a. This shows a no dependence of the sub queries and a dependency relationship between the sub query.

  1. OceanBase (root@test) table t1 (a > create int primary key, B int, C int);
  2. Query OK, 0 rows affected (0.70 sec)
  3. OceanBase (root@test) table t2 (a > create int primary key, B int, C int);
  4. Query OK, 0 rows affected (0.92 sec)
  5. No dependence of the sub query
  6. OceanBase (root@test) select * from T1 where > t1.a in (select t2.a from T2);
  7. Empty Set (0.22 sec)
  8. - dependent sub queries, subqueries used in the outer query variable t1.b
  9. OceanBase (root@test) select * from T1 where > t1.a in (select t2.a from T2 where t2.b = t1.b);
  10. Empty Set (0.05 sec)

In Oceanbase, the sub query is executed by subplan filter operator. The Subplan filter operator will traverse the outer query in each row for each row traversal will check the corresponding sub query to determine whether they meet the conditions, which are similar to Nested Loop Join. In order to efficiently perform sub queries, to meet the specific conditions of sub query can be rewritten into the connect statement, so that when the optimizer in the choice of join algorithm can have more choice (in addition to Nested Loop Join, Hash Join and Merge can also choose Join). In Oceanbase, no dependency of the sub query will be rewritten into the connect statement. The dependence of the sub query, only to meet the specific conditions can be rewritten into the connect statement. The following figure shows a connection statement not rewritten subqueries and one does not have to be rewritten into connected sub query statement.

  1. OceanBase (root@test) table t1 (a > create int primary key, B int, C int);
  2. Query OK, 0 rows affected (0.70 sec)
  3. OceanBase (root@test) table t2 (a > create int primary key, B int, C int);
  4. Query OK, 0 rows affected (0.92 sec)
  5. - dependence of the sub query is rewritten into semi-join, and use hash semi-join to achieve
  6. OceanBase (root@test) select * from T1 > explain where t1.a in (select t2.c from T2 where t2.b = t1.b);
  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. Similiarly
  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. - dependence of the subquery cannot be rewritten as semi-join, using subplan filter to achieve
  24. OceanBase (root@test) select * from T1 where > explain t1.a > (select sum (t2.c) from T2 where t2.b = t1.b);
  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. Similiarly
  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 ( Thirteen )

This paper consists of Jichangxin This paper addresses the creation: Https://blog.isoyu.com/archives/oceanbasezichaxun.html
Use Creative Commons 4 The international license. In addition to indicate the transfer / origin, are the original or translation, please be sure to sign before reprint. The final edit time: September 16, 2018 at 04:36 PM

Key word:

Lively article

Comment:

5 comments, visitors: 4, bloggers: 1
  1.  Jichangxin
    Jichangxin Released:

    Early in the day.

Comment

[required]

See?

Please wait for 3 seconds after the submission to avoid submitting the success and repeat