Oracle 租户下包含 rownum 时查看执行计划中相关的信息

OceanBase 数据库 Oracle 模式下，SQL 使用 rownum 作为过滤条件时，如何从执行计划中查看有关该 rownum 条件的信息。

适用版本

OceanBase 数据库 V2.x、V3.x 版本。

操作步骤

一般在如下 3 中算子中：

• TABLE SCAN 算子的 limit()。
• LIMIT 算子的 limit()、offset()。
• 多层子查询中，SUBPLAN SCAN 算子的 filter()。

在使用 rownum 作为过滤条件时，可通过以下步骤查看执行计划中的相关信息。

1. 部分情况下，rownum 相关的过滤条件会被下压至 TABLE SCAN 算子的 limit() 中，limit(?) 代表 SQL 中含有 rownum < 或 <= x 条件，offset(?) 表示 SQL 中含有 rownum > 或 >= x 条件。

   obclient [CHUER]> explain extended select * from T_A where rownum<10\G
   *************************** 1. row ***************************
   Query Plan: =============================================
   |ID|OPERATOR            |NAME|EST. ROWS|COST|
   ---------------------------------------------
   |0 |EXCHANGE IN REMOTE  |    |9        |48  |
   |1 | EXCHANGE OUT REMOTE|    |9        |46  |
   |2 |  TABLE SCAN        |T_A |9        |46  |
   =============================================
   
   Outputs & filters: 
   -------------------------------------
   0 - output([T_A.A_ID(0x7fa8088c8140)], [T_A.B_ID(0x7fa8088c8440)], [T_A.A_VALUE(0x7fa8088c8740)]), filter(nil)
   1 - output([T_A.A_ID(0x7fa8088c8140)], [T_A.B_ID(0x7fa8088c8440)], [T_A.A_VALUE(0x7fa8088c8740)]), filter(nil)
   2 - output([T_A.A_ID(0x7fa8088c8140)], [T_A.B_ID(0x7fa8088c8440)], [T_A.A_VALUE(0x7fa8088c8740)]), filter(nil), 
       access([T_A.A_ID(0x7fa8088c8140)], [T_A.B_ID(0x7fa8088c8440)], [T_A.A_VALUE(0x7fa8088c8740)]), partitions(p0), 
       limit(?), offset(nil), 
       is_index_back=false, 
       range_key([T_A.__pk_increment(0x7fa80895a420)]), range(MIN ; MAX)always true
   
   Used Hint:
   -------------------------------------
   /*+
   */
   
   Outline Data:
   -------------------------------------
   /*+
       BEGIN_OUTLINE_DATA
       FULL(@"SEL$2" "CHUER.T_A"@"SEL$2")
       END_OUTLINE_DATA
   */   
   

2. 若执行计划中含有 LIMIT 算子（非 fetch 生成的），则与 rownum 相关的过滤条件可在该 LIMIT 算子的 limit()、offset() 中粗略查看。

   () 中的值共有两种：? 或 nil，nil 表示没有相关的信息，? 代表一个常量；offset() 对应过滤条件中的 rownum 大于（等于），limit() 对应过滤条件中的 rownum 小于（等于）。因此，offset(?) 表示从距离当前位置的偏移行数，代表过滤条件中含有 rownum > x 或 rownum >= x；limit(?) 表示限制读取的行数，代表条件中含有 rownum < x 或 rownum <= x；limit(nil)、offset(nil) 代表无相应 rownum 过滤条件。

   obclient [CHUER]> explain SELECT * FROM (
       ->    SELECT ROWNUM RN, t.* FROM t_a t
       -> ) WHERE RN <= 10 \G
   *************************** 1. row ***************************
   Query Plan: =============================================
   |ID|OPERATOR            |NAME|EST. ROWS|COST|
   ---------------------------------------------
   |0 |EXCHANGE IN REMOTE  |    |10       |49  |
   |1 | EXCHANGE OUT REMOTE|    |10       |47  |
   |2 |  LIMIT             |    |10       |47  |
   |3 |   COUNT            |    |10       |46  |
   |4 |    TABLE SCAN      |T   |10       |46  |
   =============================================
   
   Outputs & filters: 
   -------------------------------------
   0 - output([rownum()], [T.A_ID], [T.B_ID], [T.A_VALUE]), filter(nil)
   1 - output([rownum()], [T.A_ID], [T.B_ID], [T.A_VALUE]), filter(nil)
   2 - output([rownum()], [T.A_ID], [T.B_ID], [T.A_VALUE]), filter(nil), limit(?), offset(nil)
   3 - output([rownum()], [T.A_ID], [T.B_ID], [T.A_VALUE]), filter(nil)
   4 - output([T.A_ID], [T.B_ID], [T.A_VALUE]), filter(nil), 
       access([T.A_ID], [T.B_ID], [T.A_VALUE]), partitions(p0)
   
   1 row in set (0.006 sec)
   

3. 若 SQL 包含多层子查询且某些层子查询包含有关内层生成的 rownum 的过滤条件时，OceanBase 数据库会将部分外层的 rownum 过滤条件放至 SUBPLAN SCAN 算子的 filter() 中。

   obclient [CHUER]> explain WITH subquery AS (
       ->   SELECT ROW_NUMBER() OVER(ORDER BY b_id) rn, a_id FROM t_a WHERE rownum<10 
       -> )
       -> SELECT t1.a_id,t1.a_value, subquery.a_id
       -> FROM t_a t1, subquery
       -> WHERE t1.a_id = subquery.rn\G
   *************************** 1. row ***************************
   Query Plan: =================================================
   |ID|OPERATOR            |NAME    |EST. ROWS|COST|
   -------------------------------------------------
   |0 |EXCHANGE IN REMOTE  |        |9        |161 |
   |1 | EXCHANGE OUT REMOTE|        |9        |157 |
   |2 |  HASH JOIN         |        |9        |157 |
   |3 |   SUBPLAN SCAN     |SUBQUERY|9        |56  |
   |4 |    WINDOW FUNCTION |        |9        |56  |
   |5 |     SORT           |        |9        |52  |
   |6 |      SUBPLAN SCAN  |VIEW1   |9        |46  |
   |7 |       TABLE SCAN   |T_A     |9        |46  |
   |8 |   TABLE SCAN       |T1      |100      |53  |
   =================================================
   
   Outputs & filters: 
   -------------------------------------
   0 - output([T1.A_ID], [T1.A_VALUE], [SUBQUERY.A_ID]), filter(nil)
   1 - output([T1.A_ID], [T1.A_VALUE], [SUBQUERY.A_ID]), filter(nil)
   2 - output([T1.A_ID], [T1.A_VALUE], [SUBQUERY.A_ID]), filter(nil), 
       equal_conds([T1.A_ID = SUBQUERY.RN]), other_conds(nil)
   3 - output([SUBQUERY.RN], [SUBQUERY.A_ID]), filter(nil), 
       access([SUBQUERY.RN], [SUBQUERY.A_ID])
   4 - output([T_WIN_FUN_ROW_NUMBER()], [VIEW1.T_A.A_ID]), filter(nil), 
       win_expr(T_WIN_FUN_ROW_NUMBER()), partition_by(nil), order_by([VIEW1.T_A.B_ID, ASC]), window_type(RANGE), upper(UNBOUNDED PRECEDING), lower(UNBOUNDED FOLLOWING)
   5 - output([VIEW1.T_A.B_ID], [VIEW1.T_A.A_ID]), filter(nil), sort_keys([VIEW1.T_A.B_ID, ASC])
   6 - output([VIEW1.T_A.B_ID], [VIEW1.T_A.A_ID]), filter(nil), 
       access([VIEW1.T_A.B_ID], [VIEW1.T_A.A_ID])
   7 - output([T_A.B_ID], [T_A.A_ID]), filter(nil), 
       access([T_A.B_ID], [T_A.A_ID]), partitions(p0), 
       limit(?), offset(nil)
   8 - output([T1.A_ID], [T1.A_VALUE]), filter(nil), 
       access([T1.A_ID], [T1.A_VALUE]), partitions(p0)
   
   1 row in set (0.008 sec)