set hive.stats.column.autogather=false;
set hive.mapred.mode=nonstrict;
set hive.explain.user=false;
SET hive.auto.convert.join=true;
SET hive.auto.convert.join.noconditionaltask=true;
SET hive.auto.convert.join.noconditionaltask.size=1000000000;
SET hive.vectorized.execution.enabled=true;
set hive.vectorized.execution.mapjoin.native.fast.hashtable.enabled=true;
set hive.fetch.task.conversion=none;
SET mapred.min.split.size=1000;
SET mapred.max.split.size=5000;

CREATE TABLE item_dim (key1 int, name string) stored as ORC;
CREATE TABLE orders_fact (nokey int, key2 int, dt timestamp) stored as ORC;

INSERT INTO item_dim values(101, "Item 101");
INSERT INTO item_dim values(102, "Item 102");

INSERT INTO orders_fact values(12345, 101, '2001-01-30 00:00:00');
INSERT INTO orders_fact values(23456, 104, '2002-02-30 00:00:00');
INSERT INTO orders_fact values(34567, 108, '2003-03-30 00:00:00');
INSERT INTO orders_fact values(45678, 102, '2004-04-30 00:00:00');
INSERT INTO orders_fact values(56789, 109, '2005-05-30 00:00:00');
INSERT INTO orders_fact values(67891, 110, '2006-06-30 00:00:00');

SET hive.optimize.scan.probedecode=true;

EXPLAIN VECTORIZATION DETAIL select key1, key2, name, dt from orders_fact join item_dim on (orders_fact.key2 = item_dim.key1);

-- two keys match, the remaining rows can be skipped
select key1, key2, name, dt from orders_fact join item_dim on (orders_fact.key2 = item_dim.key1);