前文《OceanBase SQL 执行打算解读(二)──── 表连贯和子查问》介绍了子查问的执行打算特点,还没有齐全说完。本文持续介绍子查问的执行打算以及剖析函数(窗口函数)的执行打算特点。
相熟罕用 SQL 的执行打算是为了反过来疾速解读剖析简单 SQL 的执行打算。
子查问
本文不探讨非相干子查问。
标量子查问表达式(Scalar Subquery Expression)是一类从一行返回一列值的子查问。标量子查问表达式的值是子查问的查问列的值。如果子查问返回 0 行,则标量子查问表达式的值是 NULL。如果子查问返回多行,则标量子查问表达式返回一个谬误。
SUBPLAN FILTER 和 SCALAR GROUP BY
EXPLAIN extended_noaddr
SELECT (SELECT w_name FROM BMSQL_WAREHOUSE w WHERE w.w_id = c.C_W_ID) ware_name
, c.C_D_ID ,c.C_FIRST ,c.C_LAST
, (SELECT count(*) FROM BMSQL_OORDER o WHERE o.O_C_ID =c.C_ID ) order_cnt
, (SELECT sum(o.O_OL_CNT) FROM BMSQL_OORDER o WHERE o.O_C_ID =c.C_ID ) item_cnt
FROM BMSQL_CUSTOMER c
;
0 – output([subquery(1)], [C.C_D_ID], [C.C_FIRST], [C.C_LAST], [subquery(2)], [subquery(3)]), filter(nil),
exec_params_([C.C_W_ID], [C.C_ID], [C.C_ID]), onetime_exprs_(nil), init_plan_idxs_(nil)
1 – output([C.C_W_ID], [C.C_D_ID], [C.C_FIRST], [C.C_LAST], [C.C_ID]), filter(nil),
access([C.C_W_ID], [C.C_D_ID], [C.C_FIRST], [C.C_LAST], [C.C_ID]), partitions(p0),
is_index_back=false,
range_key([C.C_W_ID], [C.C_D_ID], [C.C_ID]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
2 – output([W.W_NAME]), filter(nil),
access([W.W_NAME]), partitions(p0),
is_index_back=false,
range_key([W.W_ID]), range(MIN ; MAX)always true,
range_cond([W.W_ID = ?])
3 – output([T_FUN_COUNT(*)]), filter(nil),
group(nil), agg_func([T_FUN_COUNT(*)])
4 – output([1]), filter(nil),
access([O.O_C_ID]), partitions(p0),
is_index_back=false,
range_key([O.O_C_ID], [O.O_W_ID], [O.O_D_ID], [O.O_ID]), range(MIN,MIN,MIN,MIN ; MAX,MAX,MAX,MAX)always true,
range_cond([O.O_C_ID = ?])
5 – output([T_FUN_SUM(O.O_OL_CNT)]), filter(nil),
group(nil), agg_func([T_FUN_SUM(O.O_OL_CNT)])
6 – output([O.O_OL_CNT]), filter(nil),
access([O.O_OL_CNT]), partitions(p0),
is_index_back=true,
range_key([O.O_C_ID], [O.O_W_ID], [O.O_D_ID], [O.O_ID]), range(MIN,MIN,MIN,MIN ; MAX,MAX,MAX,MAX)always true,
range_cond([O.O_C_ID = ?])
SUBPLAN FILTER 用于驱动表达式中的子查问,OceanBase 会以 NESTED-LOOP 算法来执行 SUBPLAN FILTER 算子。即循环遍历右边的记录集,而后去左边后果集中取数据。所以,子查问是否能命中索引,对性能影响很大。
阐明:
标量子查问要求只返回一笔记录。能够间接取列,也能够用统计函数( count 、min、max、sum)。
算子 0 是 SUBPLAN FILTER 。 output 示意输入列,前面包含 3 个子查问后果。filter示意算子上的过滤条件,这里是空(nil)。 exec_params_ 示意左表(后果集)传递给右表(后果集)的参数,个别关联子查问这里都是连贯条件,如果是非关联子查问,这里就是空(nil)。onetime_exprs_示意只计算一次的对象(如子查问1),通常非关联的子查问后果集只须要计算一次。这里是关联子查问,所以值是空(nil)。
算子 2 是第一个子查问,间接主键拜访,用 TABLE GET .
算子 3 和 4 是第二个子查问,扫描索引(TABLE SCAN),而后再聚合 。不过这里没有分组逻辑,所以 group 参数是空。
算子 SCALAR GROUP BY 是聚合函数生成标量后果罕用的算法,用在没有 GROUP BY 语句的时候。当有GROUP BY语句时,应用的就是 HASH GROUP BY 或者 MERGE GROUP BY 算子。
EXPLAIN extended_noaddr
SELECT c.C_W_ID , count(*)
FROM BMSQL_CUSTOMER c
GROUP BY c.C_W_ID
HAVING count(*) > 1000;
0 – output([C.C_W_ID], [T_FUN_COUNT()]), filter([T_FUN_COUNT() > 1000]),
group([C.C_W_ID]), agg_func([T_FUN_COUNT(*)])
1 – output([C.C_W_ID]), filter(nil),
access([C.C_W_ID]), partitions(p0),
is_index_back=false,
range_key([C.C_W_ID], [C.C_D_ID], [C.C_ID]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
阐明:
如上,有显著的 GROUP BY子句,应用的是 MERGE GROUP BY算子, 分组表达式是 C.C_W_ID 。( group([C.C_W_ID]) )
有HAVING 子句,会在算子 MERGE GROUP BY 上产生一个 filter 。
MERGE GROUP BY 和 HASH GROUP BY
有时候,没有 GROUP BY子句,也会用到 MERGE GROUP BY算子。
EXPLAIN extended_noaddr
SELECT c.C_W_ID ,c.C_D_ID ,c.C_FIRST ,c.C_LAST ,c.C_BALANCE ,c.C_PAYMENT_CNT
FROM BMSQL_CUSTOMER c
WHERE (SELECT count(*) FROM BMSQL_OORDER o WHERE o.O_C_ID=c.C_ID) > 10;
0 – output([C.C_W_ID], [C.C_D_ID], [C.C_FIRST], [C.C_LAST], [C.C_BALANCE], [C.C_PAYMENT_CNT]), filter([CASE WHEN (T_OP_IS_NOT, VIEW1.O.O_C_ID, NULL, 0) THEN VIEW1.COUNT(*) ELSE 0 END > 10]),
equal_conds([VIEW1.O.O_C_ID = C.C_ID]), other_conds(nil)
1 – output([VIEW1.COUNT(*)], [VIEW1.O.O_C_ID]), filter(nil),
access([VIEW1.COUNT(*)], [VIEW1.O.O_C_ID])
2 – output([T_FUN_COUNT(*)], [O.O_C_ID]), filter(nil),
group([O.O_C_ID]), agg_func([T_FUN_COUNT(*)])
3 – output([O.O_C_ID]), filter(nil),
access([O.O_C_ID]), partitions(p0),
is_index_back=false,
range_key([O.O_C_ID], [O.O_W_ID], [O.O_D_ID], [O.O_ID]), range(MIN,MIN,MIN,MIN ; MAX,MAX,MAX,MAX)always true
4 – output([C.C_ID], [C.C_W_ID], [C.C_D_ID], [C.C_FIRST], [C.C_LAST], [C.C_BALANCE], [C.C_PAYMENT_CNT]), filter(nil),
access([C.C_ID], [C.C_W_ID], [C.C_D_ID], [C.C_FIRST], [C.C_LAST], [C.C_BALANCE], [C.C_PAYMENT_CNT]), partitions(p0),
is_index_back=false,
range_key([C.C_W_ID], [C.C_D_ID], [C.C_ID]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
阐明:
没有 GROUP BY子句,然而针对 WHERE 条件中的关联子查问,优化器改写了算法为 HASH RIGHT OUTER JOIN ,当时将子查问后果分组统计进去 (按 o.o_c_id 做 GROUP BY),所以有算子 3 MERGE GROUP BY 。应用 MERGE 是利用了索引的有序性。
算子 1 SUBPLAN SCAN 从子查问视图扫描数据。在算子 0 HASH RIGHT OUTER JOIN 应用 filter 利用子查问的过滤条件 (>10) .
如果子查问中后果集没有好的索引能够应用,优化器算法会调整为应用 HASH GROUP BY 。
EXPLAIN extended_noaddr
SELECT c.C_W_ID ,c.C_D_ID ,c.C_FIRST ,c.C_LAST ,c.C_BALANCE ,c.C_PAYMENT_CNT
FROM BMSQL_CUSTOMER c
WHERE (SELECT count(*) FROM BMSQL_HISTORY h WHERE h.H_C_ID = c.C_ID) > 100;
0 – output([C.C_W_ID], [C.C_D_ID], [C.C_FIRST], [C.C_LAST], [C.C_BALANCE], [C.C_PAYMENT_CNT]), filter([CASE WHEN (T_OP_IS_NOT, VIEW1.H.H_C_ID, NULL, 0) THEN VIEW1.COUNT(*) ELSE 0 END > 100]),
equal_conds([VIEW1.H.H_C_ID = C.C_ID]), other_conds(nil)
1 – output([VIEW1.COUNT(*)], [VIEW1.H.H_C_ID]), filter(nil),
access([VIEW1.COUNT(*)], [VIEW1.H.H_C_ID])
2 – output([T_FUN_COUNT(*)], [H.H_C_ID]), filter(nil),
group([H.H_C_ID]), agg_func([T_FUN_COUNT(*)])
3 – output([H.H_C_ID]), filter(nil),
access([H.H_C_ID]), partitions(p0),
is_index_back=false,
range_key([H.__pk_increment]), range(MIN ; MAX)always true
4 – output([C.C_ID], [C.C_W_ID], [C.C_D_ID], [C.C_FIRST], [C.C_LAST], [C.C_BALANCE], [C.C_PAYMENT_CNT]), filter(nil),
access([C.C_ID], [C.C_W_ID], [C.C_D_ID], [C.C_FIRST], [C.C_LAST], [C.C_BALANCE], [C.C_PAYMENT_CNT]), partitions(p0),
is_index_back=false,
range_key([C.C_W_ID], [C.C_D_ID], [C.C_ID]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
剖析函数
剖析函数(某些数据库下也叫做窗口函数)与汇集函数相似,计算总是基于一组行的汇合,不同的是,汇集函数一组只能返回一行,而剖析函数每组能够返回多行,组内每一行都是基于窗口的逻辑计算的后果。剖析函数能够显著优化须要 self-join的查问。有些剖析函数也能够当汇集函数应用。
剖析函数包含:
MAX 、MIN 、AVG
COUNT、SUM
GROUP_CONCAT 、 LISTAGG
ROW_NUMBER 、RANK、DENSE_RANK、PERCENT_RANK
CUME_DIST
FIRST_VALUE、LAST_VALUE
NTH_VALUE、NTILE
LEAD、LAG
算子 WINDOW_FUNCTION
如上面示例,统计各个仓库下的各个区的销量在本仓库内的排名。
EXPLAIN extended_noaddr
SELECT d.D_W_ID , d.D_ID , d.D_NAME , d.D_YTD ,ROW_NUMBER () OVER (PARTITION BY d.D_W_ID ORDER BY d.D_YTD DESC ) rn
FROM BMSQL_DISTRICT d
ORDER BY rn ;
;
0 – output([D.D_W_ID], [D.D_ID], [D.D_NAME], [D.D_YTD], [T_WIN_FUN_ROW_NUMBER()]), filter(nil), sort_keys([T_WIN_FUN_ROW_NUMBER(), ASC])
1 – output([D.D_W_ID], [D.D_ID], [D.D_NAME], [D.D_YTD], [T_WIN_FUN_ROW_NUMBER()]), filter(nil),
win_expr(T_WIN_FUN_ROW_NUMBER()), partition_by([D.D_W_ID]), order_by([D.D_YTD, DESC]), window_type(RANGE), upper(UNBOUNDED PRECEDING), lower(UNBOUNDED FOLLOWING)
2 – output([D.D_W_ID], [D.D_ID], [D.D_NAME], [D.D_YTD]), filter(nil), sort_keys([D.D_W_ID, ASC], [D.D_YTD, DESC]), prefix_pos(1)
3 – output([D.D_W_ID], [D.D_ID], [D.D_NAME], [D.D_YTD]), filter(nil),
access([D.D_W_ID], [D.D_ID], [D.D_NAME], [D.D_YTD]), partitions(p0),
is_index_back=false,
range_key([D.D_W_ID], [D.D_ID]), range(MIN,MIN ; MAX,MAX)always true
阐明:
剖析函数对应的算子是 WINDOW FUNCTION ,依赖上层算子的有序输入,有分区表达式和排序表达式。
output 是算子的输入表达式,蕴含剖析函数的后果, filter 固定为 nil。
win_expr 示意在窗口中应用哪个窗口函数 ,partition_by 示意窗口内的分组表达式, order_by 示意窗口每组外部统计时的排序表达式 。window_type
window_type 示意窗口类型,有两种:range 和 rows 。range 示意依照逻辑地位偏移进行计算窗口高低界线,rows 示意依照理论物理地位偏移进行计算窗口高低界线;默认应用 range 形式。
upper 和 lower 别离定义窗口的下限和上限。UNBOUNDED 示意无边界,依照最大的抉择(默认)。CURRENT ROW 示意从以后行开始,如果呈现数字则示意挪动的行数。PRECEDING 示意向前取边界,FOLLOWING 则示意向后取边界。
算子 2 的 SORT 会蕴含分区窗口表达式和窗口内的排序表达式。
算子 0 的 SORT 是最外层的排序表达式。
上面能够看看不同剖析函数下执行打算里算子 WINDOW_FUNCTION 的各个参数。
EXPLAIN extended_noaddr
SELECT w.W_STATE, w_id, w_name, w_ytd, max(w_ytd) over (partition by W_STATE order by w_ytd desc rows between 1 preceding and 1 following) max_ytd_in_3
from BMSQL_WAREHOUSE w
ORDER BY w.W_STATE ;
0 – output([W.W_STATE], [W.W_ID], [W.W_NAME], [W.W_YTD], [T_FUN_MAX(W.W_YTD)]), filter(nil),
win_expr(T_FUN_MAX(W.W_YTD)), partition_by([W.W_STATE]), order_by([W.W_YTD, DESC]), window_type(ROWS), upper(1 PRECEDING), lower(1 FOLLOWING)
1 – output([W.W_STATE], [W.W_ID], [W.W_NAME], [W.W_YTD]), filter(nil), sort_keys([W.W_STATE, ASC], [W.W_YTD, DESC])
2 – output([W.W_STATE], [W.W_ID], [W.W_NAME], [W.W_YTD]), filter(nil),
access([W.W_STATE], [W.W_ID], [W.W_NAME], [W.W_YTD]), partitions(p0),
is_index_back=false,
range_key([W.W_ID]), range(MIN ; MAX)always true
阐明:
window_type 是 ROWS ,upper 是同一个分组内向前一笔, lower是同一个分组外向后一笔。如果同一个分组内没有向前或向后一笔,那就是空。
上面输入各个仓库的销量以及包含前2笔在内的最大销量。
EXPLAIN extended_noaddr
SELECT w.W_STATE, w_id, w_name, w_ytd, max(w_ytd) over (order by w_ytd desc rows between 2 PRECEDING AND current row ) max_ytd_in_3
from BMSQL_WAREHOUSE w
ORDER BY w.W_YTD DESC ;
0 – output([W.W_STATE], [W.W_ID], [W.W_NAME], [W.W_YTD], [T_FUN_MAX(W.W_YTD)]), filter(nil),
win_expr(T_FUN_MAX(W.W_YTD)), partition_by(nil), order_by([W.W_YTD, DESC]), window_type(ROWS), upper(2 PRECEDING), lower(CURRENT ROW)
1 – output([W.W_STATE], [W.W_ID], [W.W_NAME], [W.W_YTD]), filter(nil), sort_keys([W.W_YTD, DESC])
2 – output([W.W_STATE], [W.W_ID], [W.W_NAME], [W.W_YTD]), filter(nil),
access([W.W_STATE], [W.W_ID], [W.W_NAME], [W.W_YTD]), partitions(p0),
is_index_back=false,
range_key([W.W_ID]), range(MIN ; MAX)always true
阐明:
分区表达式 partition_by 并不是必须的,能够为空。
上面看看行转列函数的执行打算。
EXPLAIN extended_Noaddr
SELECT d.D_W_ID , d.D_ID ,d.d_name, d.D_YTD , listagg(d.D_NAME,’,’) WITHIN GROUP (ORDER BY d.D_YTD DESC ) OVER (PARTITION BY d.D_W_ID) d_names
FROM BMSQL_DISTRICT d
WHERE d.D_W_ID = 10
;
0 – output([D.D_W_ID], [D.D_ID], [D.D_NAME], [D.D_YTD], [T_FUN_GROUP_CONCAT(D.D_NAME, ‘,’) order_items(D.D_YTD)]), filter(nil),
win_expr(T_FUN_GROUP_CONCAT(D.D_NAME, ',') order_items(D.D_YTD)), partition_by([D.D_W_ID]), order_by(nil), window_type(RANGE), upper(UNBOUNDED PRECEDING), lower(UNBOUNDED FOLLOWING)
1 – output([D.D_W_ID], [D.D_ID], [D.D_NAME], [D.D_YTD]), filter(nil),
access([D.D_W_ID], [D.D_ID], [D.D_NAME], [D.D_YTD]), partitions(p0),
is_index_back=false,
range_key([D.D_W_ID], [D.D_ID]), range(10,MIN ; 10,MAX),
range_cond([D.D_W_ID = 10])
阐明:
应用 LISTAGG 语法时,窗口函数表达式是 T_FUN_GROUP_CONCAT 。
再看一个 窗口类型为 RANGE 的示例。
EXPLAIN extended_noaddr
SELECT w.W_STATE, w_id, w_name, w_ytd, max(w_ytd) over (order by w_ytd desc range between 1000000 PRECEDING AND current row ) max_ytd_in_3
from BMSQL_WAREHOUSE w
ORDER BY w.W_YTD DESC ;
0 – output([W.W_STATE], [W.W_ID], [W.W_NAME], [W.W_YTD], [T_FUN_MAX(W.W_YTD)]), filter(nil),
win_expr(T_FUN_MAX(W.W_YTD)), partition_by(nil), order_by([W.W_YTD, DESC]), window_type(RANGE), upper(1000000 PRECEDING), lower(CURRENT ROW)
1 – output([W.W_STATE], [W.W_ID], [W.W_NAME], [W.W_YTD], [W.W_YTD + 1000000], [W.W_YTD – 1000000]), filter(nil), sort_keys([W.W_YTD, DESC])
2 – output([W.W_STATE], [W.W_ID], [W.W_NAME], [W.W_YTD]), filter(nil),
access([W.W_STATE], [W.W_ID], [W.W_NAME], [W.W_YTD]), partitions(p0),
is_index_back=false,
range_key([W.W_ID]), range(MIN ; MAX)always true
阐明:
window_type 是 RANGE 。是按理论值计算窗口大小,不是按行数固定窗口大小。算子 1 的output 里多了两列 ( [W.W_YTD + 1000000], [W.W_YTD – 1000000] )。
剖析函数的代价
剖析函数看起来很酷,不过也有代价,那就是每次调用剖析函数都可能会有一次排序,排序须要内存,可能须要增大外部参数 _sort_area_size 的值。为了性能还倡议应用并行( OB 的并行会在下篇文章介绍)。
上面这个示例会波及到一次全表扫描和三次排序。
EXPLAIN extended_noaddr
SELECT c_w_id, c_d_id, c_id, c.C_LAST ,c.C_FIRST , C_YTD_PAYMENT ,
rank() OVER (PARTITION BY C_W_ID, c_d_id ORDER BY C_YTD_PAYMENT DESC ) rank_in_district,
rank() OVER (PARTITION BY c_w_id ORDER BY C_YTD_PAYMENT DESC ) rank_in_warehouse,
rank() OVER (ORDER BY C_YTD_PAYMENT DESC ) rank_in_all
FROM BMSQL_CUSTOMER c
WHERE c.C_YTD_PAYMENT >= 1000000
ORDER BY c.C_YTD_PAYMENT DESC ;
;
0 – output([C.C_W_ID], [C.C_D_ID], [C.C_ID], [C.C_LAST], [C.C_FIRST], [C.C_YTD_PAYMENT], [T_WIN_FUN_RANK()], [T_WIN_FUN_RANK()], [T_WIN_FUN_RANK()]), filter(nil),
win_expr(T_WIN_FUN_RANK()), partition_by(nil), order_by([C.C_YTD_PAYMENT, DESC]), window_type(RANGE), upper(UNBOUNDED PRECEDING), lower(UNBOUNDED FOLLOWING)
1 – output([C.C_W_ID], [C.C_D_ID], [C.C_ID], [C.C_LAST], [C.C_FIRST], [C.C_YTD_PAYMENT], [T_WIN_FUN_RANK()], [T_WIN_FUN_RANK()]), filter(nil), sort_keys([C.C_YTD_PAYMENT, DESC])
2 – output([C.C_W_ID], [C.C_D_ID], [C.C_ID], [C.C_LAST], [C.C_FIRST], [C.C_YTD_PAYMENT], [T_WIN_FUN_RANK()], [T_WIN_FUN_RANK()]), filter(nil),
win_expr(T_WIN_FUN_RANK()), partition_by([C.C_W_ID]), order_by([C.C_YTD_PAYMENT, DESC]), window_type(RANGE), upper(UNBOUNDED PRECEDING), lower(UNBOUNDED FOLLOWING)
3 – output([C.C_W_ID], [C.C_D_ID], [C.C_ID], [C.C_LAST], [C.C_FIRST], [C.C_YTD_PAYMENT], [T_WIN_FUN_RANK()]), filter(nil), sort_keys([C.C_W_ID, ASC], [C.C_YTD_PAYMENT, DESC]), prefix_pos(1)
4 – output([C.C_W_ID], [C.C_D_ID], [C.C_ID], [C.C_LAST], [C.C_FIRST], [C.C_YTD_PAYMENT], [T_WIN_FUN_RANK()]), filter(nil),
win_expr(T_WIN_FUN_RANK()), partition_by([C.C_W_ID], [C.C_D_ID]), order_by([C.C_YTD_PAYMENT, DESC]), window_type(RANGE), upper(UNBOUNDED PRECEDING), lower(UNBOUNDED FOLLOWING)
5 – output([C.C_W_ID], [C.C_D_ID], [C.C_ID], [C.C_LAST], [C.C_FIRST], [C.C_YTD_PAYMENT]), filter(nil), sort_keys([C.C_W_ID, ASC], [C.C_D_ID, ASC], [C.C_YTD_PAYMENT, DESC]), prefix_pos(2)
6 – output([C.C_YTD_PAYMENT], [C.C_W_ID], [C.C_D_ID], [C.C_ID], [C.C_LAST], [C.C_FIRST]), filter([C.C_YTD_PAYMENT >= 1000000]),
access([C.C_YTD_PAYMENT], [C.C_W_ID], [C.C_D_ID], [C.C_ID], [C.C_LAST], [C.C_FIRST]), partitions(p0),
is_index_back=false, filter_before_indexback[false],
range_key([C.C_W_ID], [C.C_D_ID], [C.C_ID]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
执行后果如下图:
阐明:
执行的程序,首先算子 6 是针对表的扫描,先执行过滤条件(filter)。
算子 5 是第一次排序,排序列是分区列加上排序列(sort_keys([C.C_W_ID, ASC], [C.C_D_ID, ASC], [C.C_YTD_PAYMENT, DESC]))。
算子 4 是第一个窗口函数,分区列是 partition_by([C.C_W_ID], [C.C_D_ID]) , 排序列是 order_by([C.C_YTD_PAYMENT, DESC])。
算子 3 是一个优化,利用了第一个窗口函数的后果持续进行排序。
算子 1 在算子 2 后果集根底上进一步排序。
下面示例 3 个剖析函数应用的窗口函数算子都是 T_WIN_FUN_RANK,只是分区列不同所以还是有三次排序。如果分区列和排序列一样的话,是能够躲避屡次排序的。如上面示例。
EXPLAIN extended_noaddr
SELECT * FROM (
SELECT c_w_id, c_d_id, c_id, c.C_LAST ,c.C_FIRST , C_YTD_PAYMENT
,rank() OVER (PARTITION BY c_w_id,c_d_id ORDER BY C_YTD_PAYMENT) ytd_rank
,first_value(C_YTD_PAYMENT) OVER (PARTITION BY c_w_id,c_d_id ORDER BY C_YTD_PAYMENT) first_ytd
— ,last_value(C_YTD_PAYMENT) OVER (PARTITION BY C_W_ID,c_d_id ORDER BY C_YTD_PAYMENT ) last_ytd
,last_value(C_YTD_PAYMENT) OVER (PARTITION BY C_W_ID,c_d_id ORDER BY C_YTD_PAYMENT rows between unbounded preceding and unbounded following) last_ytd_all
FROM BMSQL_CUSTOMER c
WHERE c.C_YTD_PAYMENT >= 1000000
) t
WHERE t.c_w_id = 23
;
0 – output([T.C_W_ID], [T.C_D_ID], [T.C_ID], [T.C_LAST], [T.C_FIRST], [T.C_YTD_PAYMENT], [T.YTD_RANK], [T.FIRST_YTD], [T.LAST_YTD_ALL]), filter([T.C_D_ID = 10]),
access([T.C_W_ID], [T.C_D_ID], [T.C_ID], [T.C_LAST], [T.C_FIRST], [T.C_YTD_PAYMENT], [T.YTD_RANK], [T.FIRST_YTD], [T.LAST_YTD_ALL])
1 – output([C.C_W_ID], [C.C_D_ID], [C.C_ID], [C.C_LAST], [C.C_FIRST], [C.C_YTD_PAYMENT], [T_WIN_FUN_RANK()], [T_WIN_FUN_NTH_VALUE(C.C_YTD_PAYMENT,1)], [T_WIN_FUN_NTH_VALUE(C.C_YTD_PAYMENT,1)]), filter(nil),
win_expr(T_WIN_FUN_RANK()), partition_by([C.C_W_ID]), order_by([C.C_YTD_PAYMENT, ASC]), window_type(RANGE), upper(UNBOUNDED PRECEDING), lower(UNBOUNDED FOLLOWING)
win_expr(T_WIN_FUN_NTH_VALUE(C.C_YTD_PAYMENT,1)), partition_by([C.C_W_ID]), order_by([C.C_YTD_PAYMENT, ASC]), window_type(RANGE), upper(UNBOUNDED PRECEDING), lower(CURRENT ROW)
win_expr(T_WIN_FUN_NTH_VALUE(C.C_YTD_PAYMENT,1)), partition_by([C.C_W_ID]), order_by([C.C_YTD_PAYMENT, ASC]), window_type(ROWS), upper(UNBOUNDED PRECEDING), lower(UNBOUNDED FOLLOWING)
2 – output([C.C_W_ID], [C.C_D_ID], [C.C_ID], [C.C_LAST], [C.C_FIRST], [C.C_YTD_PAYMENT]), filter(nil), sort_keys([C.C_YTD_PAYMENT, ASC])
3 – output([C.C_YTD_PAYMENT], [C.C_W_ID], [C.C_D_ID], [C.C_ID], [C.C_LAST], [C.C_FIRST]), filter([C.C_YTD_PAYMENT >= 1000000]),
access([C.C_YTD_PAYMENT], [C.C_W_ID], [C.C_D_ID], [C.C_ID], [C.C_LAST], [C.C_FIRST]), partitions(p0),
is_index_back=false, filter_before_indexback[false],
range_key([C.C_W_ID], [C.C_D_ID], [C.C_ID]), range(23,MIN,MIN ; 23,MAX,MAX),
range_cond([C.C_W_ID = 23])

阐明:
这里 where 条件有两个(t.c_w_id = 23 AND t.c_d_id = 10),但理论条件下推到子查问里只有 t.c_w_id=23 。 这是因为子查问应用的剖析函数里的分区列只蕴含列t.c_w_id 。看算子 3 的 range_cond 和算子 1的 win_expr 。 最初一部的 filter 才是条件 t.c_d_id = 10。
算子 2 的 sort_keys([C.C_YTD_PAYMENT, ASC]) 只蕴含了列 c.c_ytd_payment 这是因为算子 3 返回的数据在列 c_w_id 上曾经是有序的。
算子 1 的一共用了 3 个窗口函数表达式(win_expr),别离是:T_WIN_FUN_RANK()、T_WIN_FUN_NTH_VALUE(C.C_YTD_PAYMENT,1)、T_WIN_FUN_NTH_VALUE(C.C_YTD_PAYMENT,1),它们的 order_by 条件都是一样的,只是窗口的上限不一样,能够共用一个 SORT 操作。
从这个例子还看出,默认的窗口范畴是 upper(UNBOUNDED PRECEDING), lower(CURRENT ROW) 。被正文掉的 last_value 应用默认的窗口范畴,只会返回以后行值。
欢送宽广 OceanBase 爱好者、用户和客户有任何问题分割咱们反馈:
社区版官网论坛:open.oceanbase.com/answer