| set hive.cbo.enable=true; |
| set hive.explain.user=false; |
| set hive.tez.dynamic.partition.pruning=false; |
| set hive.vectorized.execution.enabled=true; |
| SET hive.auto.convert.join=true; |
| set hive.fetch.task.conversion=none; |
| |
| -- SORT_QUERY_RESULTS |
| |
| -- 2 Strings |
| create table test_1_n1 (`id` string, `lineid` string) stored as orc; |
| |
| insert into table test_1_n1 values ('one','1'), ('seven','1'); |
| |
| explain vectorization expression |
| select * from test_1_n1 where struct(`id`, `lineid`) |
| IN ( |
| struct('two','3'), |
| struct('three','1'), |
| struct('one','1'), |
| struct('five','2'), |
| struct('six','1'), |
| struct('eight','1'), |
| struct('seven','1'), |
| struct('nine','1'), |
| struct('ten','1') |
| ); |
| |
| select * from test_1_n1 where struct(`id`, `lineid`) |
| IN ( |
| struct('two','3'), |
| struct('three','1'), |
| struct('one','1'), |
| struct('five','2'), |
| struct('six','1'), |
| struct('eight','1'), |
| struct('seven','1'), |
| struct('nine','1'), |
| struct('ten','1') |
| ); |
| |
| explain vectorization expression |
| select `id`, `lineid`, struct(`id`, `lineid`) |
| IN ( |
| struct('two','3'), |
| struct('three','1'), |
| struct('one','1'), |
| struct('five','2'), |
| struct('six','1'), |
| struct('eight','1'), |
| struct('seven','1'), |
| struct('nine','1'), |
| struct('ten','1') |
| ) as b from test_1_n1 ; |
| |
| select `id`, `lineid`, struct(`id`, `lineid`) |
| IN ( |
| struct('two','3'), |
| struct('three','1'), |
| struct('one','1'), |
| struct('five','2'), |
| struct('six','1'), |
| struct('eight','1'), |
| struct('seven','1'), |
| struct('nine','1'), |
| struct('ten','1') |
| ) as b from test_1_n1 ; |
| |
| |
| -- 2 Integers |
| create table test_2_n1 (`id` int, `lineid` int) stored as orc; |
| |
| insert into table test_2_n1 values (1,1), (7,1); |
| |
| explain vectorization expression |
| select * from test_2_n1 where struct(`id`, `lineid`) |
| IN ( |
| struct(2,3), |
| struct(3,1), |
| struct(1,1), |
| struct(5,2), |
| struct(6,1), |
| struct(8,1), |
| struct(7,1), |
| struct(9,1), |
| struct(10,1) |
| ); |
| |
| select * from test_2_n1 where struct(`id`, `lineid`) |
| IN ( |
| struct(2,3), |
| struct(3,1), |
| struct(1,1), |
| struct(5,2), |
| struct(6,1), |
| struct(8,1), |
| struct(7,1), |
| struct(9,1), |
| struct(10,1) |
| ); |
| |
| explain vectorization expression |
| select `id`, `lineid`, struct(`id`, `lineid`) |
| IN ( |
| struct(2,3), |
| struct(3,1), |
| struct(1,1), |
| struct(5,2), |
| struct(6,1), |
| struct(8,1), |
| struct(7,1), |
| struct(9,1), |
| struct(10,1) |
| ) as b from test_2_n1; |
| |
| select `id`, `lineid`, struct(`id`, `lineid`) |
| IN ( |
| struct(2,3), |
| struct(3,1), |
| struct(1,1), |
| struct(5,2), |
| struct(6,1), |
| struct(8,1), |
| struct(7,1), |
| struct(9,1), |
| struct(10,1) |
| ) as b from test_2_n1; |
| |
| -- 1 String and 1 Integer |
| create table test_3 (`id` string, `lineid` int) stored as orc; |
| |
| insert into table test_3 values ('one',1), ('seven',1); |
| |
| explain vectorization expression |
| select * from test_3 where struct(`id`, `lineid`) |
| IN ( |
| struct('two',3), |
| struct('three',1), |
| struct('one',1), |
| struct('five',2), |
| struct('six',1), |
| struct('eight',1), |
| struct('seven',1), |
| struct('nine',1), |
| struct('ten',1) |
| ); |
| |
| select * from test_3 where struct(`id`, `lineid`) |
| IN ( |
| struct('two',3), |
| struct('three',1), |
| struct('one',1), |
| struct('five',2), |
| struct('six',1), |
| struct('eight',1), |
| struct('seven',1), |
| struct('nine',1), |
| struct('ten',1) |
| ); |
| |
| explain vectorization expression |
| select `id`, `lineid`, struct(`id`, `lineid`) |
| IN ( |
| struct('two',3), |
| struct('three',1), |
| struct('one',1), |
| struct('five',2), |
| struct('six',1), |
| struct('eight',1), |
| struct('seven',1), |
| struct('nine',1), |
| struct('ten',1) |
| ) as b from test_3; |
| |
| select `id`, `lineid`, struct(`id`, `lineid`) |
| IN ( |
| struct('two',3), |
| struct('three',1), |
| struct('one',1), |
| struct('five',2), |
| struct('six',1), |
| struct('eight',1), |
| struct('seven',1), |
| struct('nine',1), |
| struct('ten',1) |
| ) as b from test_3; |
| |
| -- 1 Integer and 1 String and 1 Double |
| create table test_4 (`my_bigint` bigint, `my_string` string, `my_double` double) stored as orc; |
| |
| insert into table test_4 values (1, "b", 1.5), (1, "a", 0.5), (2, "b", 1.5); |
| |
| explain vectorization expression |
| select * from test_4 where struct(`my_bigint`, `my_string`, `my_double`) |
| IN ( |
| struct(1L, "a", 1.5D), |
| struct(1L, "b", -0.5D), |
| struct(3L, "b", 1.5D), |
| struct(1L, "d", 1.5D), |
| struct(1L, "c", 1.5D), |
| struct(1L, "b", 2.5D), |
| struct(1L, "b", 0.5D), |
| struct(5L, "b", 1.5D), |
| struct(1L, "a", 0.5D), |
| struct(3L, "b", 1.5D) |
| ); |
| |
| select * from test_4 where struct(`my_bigint`, `my_string`, `my_double`) |
| IN ( |
| struct(1L, "a", 1.5D), |
| struct(1L, "b", -0.5D), |
| struct(3L, "b", 1.5D), |
| struct(1L, "d", 1.5D), |
| struct(1L, "c", 1.5D), |
| struct(1L, "b", 2.5D), |
| struct(1L, "b", 0.5D), |
| struct(5L, "b", 1.5D), |
| struct(1L, "a", 0.5D), |
| struct(3L, "b", 1.5D) |
| ); |
| |
| explain vectorization expression |
| select `my_bigint`, `my_string`, `my_double`, struct(`my_bigint`, `my_string`, `my_double`) |
| IN ( |
| struct(1L, "a", 1.5D), |
| struct(1L, "b", -0.5D), |
| struct(3L, "b", 1.5D), |
| struct(1L, "d", 1.5D), |
| struct(1L, "c", 1.5D), |
| struct(1L, "b", 2.5D), |
| struct(1L, "b", 0.5D), |
| struct(5L, "b", 1.5D), |
| struct(1L, "a", 0.5D), |
| struct(3L, "b", 1.5D) |
| ) as b from test_4; |
| |
| select `my_bigint`, `my_string`, `my_double`, struct(`my_bigint`, `my_string`, `my_double`) |
| IN ( |
| struct(1L, "a", 1.5D), |
| struct(1L, "b", -0.5D), |
| struct(3L, "b", 1.5D), |
| struct(1L, "d", 1.5D), |
| struct(1L, "c", 1.5D), |
| struct(1L, "b", 2.5D), |
| struct(1L, "b", 0.5D), |
| struct(5L, "b", 1.5D), |
| struct(1L, "a", 0.5D), |
| struct(3L, "b", 1.5D) |
| ) as b from test_4; |