yiqijiu commented on PR #5556:
URL: https://github.com/apache/hive/pull/5556#issuecomment-2509796860
@okumin
```
create table explain_multiple_ptf_big_table
(
key1 string,
value_str1 string,
key3 string
);
create table explain_multiple_ptf_big_table2
(
key21 string,
value_str21 string,
key23 string
);
alter table explain_multiple_ptf_big_table
update statistics set('numRows' = '4611686036854775807',
'rawDataSize' = '922337203685477500');
alter table explain_multiple_ptf_big_table2
update statistics set('numRows' = '4611686036854775807',
'rawDataSize' = '9223372036854775800');
explain
select *,row_number() over (partition by key order by key2 desc) rn,
row_number() over (partition by key2 order by key desc) rn2,
max(value_str) over (partition by key2 order by key desc) max1,
max(value_str) over (partition by key order by key2 desc) max3,
min(value_str) over (partition by key2 order by key desc) min1,
min(value_str) over (partition by key order by key2 desc) min3,
last_value(value_str) over (partition by key) lv,
first_value(value_str) over (partition by key2) fv,
max(value_str) over (partition by key) fv21
from (select key1 key, value_str1 value_str, key3 key2 from
explain_multiple_ptf_big_table
union all select key21 key, value_str21 value_str, key23 key2 from
explain_multiple_ptf_big_table2) a1
;
```
```
+----------------------------------------------------+
| Explain |
+----------------------------------------------------+
| Plan optimized by CBO. |
| |
| Vertex dependency in root stage |
| Map 1 <- Union 2 (CONTAINS) |
| Map 8 <- Union 2 (CONTAINS) |
| Reducer 3 <- Union 2 (SIMPLE_EDGE) |
| Reducer 4 <- Reducer 3 (SIMPLE_EDGE) |
| Reducer 5 <- Reducer 4 (SIMPLE_EDGE) |
| Reducer 6 <- Reducer 5 (SIMPLE_EDGE) |
| Reducer 7 <- Reducer 6 (SIMPLE_EDGE) |
| |
| Stage-0 |
| Fetch Operator |
| limit:-1 |
| Stage-1 |
| Reducer 7 |
| File Output Operator [FS_26] |
| Select Operator [SEL_24] (rows=-9223372000000000002 width=0) |
|
Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10","_col11"]
|
| PTF Operator [PTF_23] (rows=-9223372000000000002 width=0) |
| Function
definitions:[{},{"name:":"windowingtablefunction","order by:":"_col8 ASC NULLS
FIRST","partition by:":"_col8"}] |
| Select Operator [SEL_22] (rows=-9223372000000000002 width=0) |
|
Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10"]
|
| <-Reducer 6 [SIMPLE_EDGE] |
| SHUFFLE [RS_21] |
| PartitionCols:_col7 |
| Select Operator [SEL_20] (rows=-9223372000000000002
width=0) |
|
Output:["first_value_window_7","_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9"]
|
| PTF Operator [PTF_19] (rows=-9223372000000000002
width=0) |
| Function
definitions:[{},{"name:":"windowingtablefunction","order by:":"_col9 ASC NULLS
FIRST","partition by:":"_col9"}] |
| Select Operator [SEL_18] (rows=-9223372000000000002
width=0) |
|
Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9"]
|
| <-Reducer 5 [SIMPLE_EDGE] |
| SHUFFLE [RS_17] |
| PartitionCols:_col8 |
| Select Operator [SEL_16]
(rows=-9223372000000000002 width=0) |
|
Output:["first_value_window_6","_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8"]
|
| PTF Operator [PTF_15] (rows=-9223372000000000002
width=0) |
| Function
definitions:[{},{"name:":"windowingtablefunction","order by:":"_col6 DESC NULLS
LAST","partition by:":"_col6"}] |
| Select Operator [SEL_14]
(rows=-9223372000000000002 width=0) |
|
Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8"]
|
| <-Reducer 4 [SIMPLE_EDGE] |
| SHUFFLE [RS_13] |
| PartitionCols:_col3 |
| Select Operator [SEL_12]
(rows=-9223372000000000002 width=0) |
|
Output:["row_number_window_1","max_window_2","min_window_4","_col0","_col1","_col2","_col3","_col4","_col5"]
|
| PTF Operator [PTF_11]
(rows=-9223372000000000002 width=0) |
| Function
definitions:[{},{"name:":"windowingtablefunction","order by:":"_col3 DESC NULLS
FIRST","partition by:":"_col5"}] |
| Select Operator [SEL_10]
(rows=-9223372000000000002 width=0) |
|
Output:["_col0","_col1","_col2","_col3","_col4","_col5"] |
| <-Reducer 3 [SIMPLE_EDGE] |
| SHUFFLE [RS_9] |
| PartitionCols:_col2 |
| Select Operator [SEL_8]
(rows=-9223372000000000002 width=0) |
|
Output:["row_number_window_0","max_window_3","min_window_5","_col0","_col1","_col2"]
|
| PTF Operator [PTF_7]
(rows=-9223372000000000002 width=0) |
| Function
definitions:[{},{"name:":"windowingtablefunction","order by:":"_col2 DESC NULLS
FIRST","partition by:":"_col0"}] |
| Select Operator [SEL_6]
(rows=-9223372000000000002 width=0) |
|
Output:["_col0","_col1","_col2"] |
| <-Union 2 [SIMPLE_EDGE] |
| <-Map 1 [CONTAINS]
vectorized |
| Reduce Output Operator
[RS_36] |
| PartitionCols:_col0 |
| Select Operator [SEL_35]
(rows=4611686036854775807 width=1) |
|
Output:["_col0","_col1","_col2"] |
| TableScan [TS_27]
(rows=4611686036854775807 width=1) |
|
default@explain_multiple_ptf_big_table,explain_multiple_ptf_big_table,Tbl:COMPLETE,Col:NONE,Output:["key1","value_str1","key3"]
|
| <-Map 8 [CONTAINS]
vectorized |
| Reduce Output Operator
[RS_43] |
| PartitionCols:_col0 |
| Select Operator [SEL_42]
(rows=4611686036854775807 width=1) |
|
Output:["_col0","_col1","_col2"] |
| TableScan [TS_31]
(rows=4611686036854775807 width=1) |
|
default@explain_multiple_ptf_big_table2,explain_multiple_ptf_big_table2,Tbl:COMPLETE,Col:NONE,Output:["key21","value_str21","key23"]
|
| |
+----------------------------------------------------+
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: gitbox-unsubscr...@hive.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscr...@hive.apache.org
For additional commands, e-mail: gitbox-h...@hive.apache.org
