DROP DATABASE IF EXISTS dbt3_s001; CREATE DATABASE dbt3_s001; use dbt3_s001; create index i_n_name on nation(n_name); analyze table nation, lineitem, customer, orders, part, supplier, partsupp, region persistent for all; Table Op Msg_type Msg_text dbt3_s001.nation analyze status Engine-independent statistics collected dbt3_s001.nation analyze status OK dbt3_s001.lineitem analyze status Engine-independent statistics collected dbt3_s001.lineitem analyze status OK dbt3_s001.customer analyze status Engine-independent statistics collected dbt3_s001.customer analyze status OK dbt3_s001.orders analyze status Engine-independent statistics collected dbt3_s001.orders analyze status OK dbt3_s001.part analyze status Engine-independent statistics collected dbt3_s001.part analyze status OK dbt3_s001.supplier analyze status Engine-independent statistics collected dbt3_s001.supplier analyze status OK dbt3_s001.partsupp analyze status Engine-independent statistics collected dbt3_s001.partsupp analyze status OK dbt3_s001.region analyze status Engine-independent statistics collected dbt3_s001.region analyze status OK # Pullout # ======= explain select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition 1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 1 PRIMARY orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (7%) Using where; Using rowid filter explain format=json select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')); EXPLAIN { "query_block": { "select_id": 1, "cost": 0.052271677, "nested_loop": [ { "table": { "table_name": "nation", "access_type": "ref", "possible_keys": ["PRIMARY", "i_n_name"], "key": "i_n_name", "key_length": "26", "used_key_parts": ["n_name"], "ref": ["const"], "loops": 1, "rows": 1, "cost": 0.002024411, "filtered": 100, "index_condition": "nation.n_name = 'PERU'" } }, { "table": { "table_name": "customer", "access_type": "ref", "possible_keys": ["PRIMARY", "i_c_nationkey"], "key": "i_c_nationkey", "key_length": "5", "used_key_parts": ["c_nationkey"], "ref": ["dbt3_s001.nation.n_nationkey"], "loops": 1, "rows": 6, "cost": 0.008193756, "filtered": 100 } }, { "table": { "table_name": "orders", "access_type": "ref", "possible_keys": ["i_o_orderdate", "i_o_custkey"], "key": "i_o_custkey", "key_length": "5", "used_key_parts": ["o_custkey"], "ref": ["dbt3_s001.customer.c_custkey"], "rowid_filter": { "range": { "key": "i_o_orderdate", "used_key_parts": ["o_orderDATE"] }, "rows": 108, "selectivity_pct": 7.2 }, "loops": 6, "rows": 15, "cost": 0.04205351, "filtered": 7.199999809, "attached_condition": "orders.o_orderDATE between '1992-01-01' and '1992-06-30'" } } ] } } select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')); o_orderkey o_totalprice 644 201268.06 2880 145761.99 3142 16030.15 5382 138423.03 5095 184583.99 737 12984.85 1729 12137.76 5121 150334.57 explain update orders set o_totalprice = o_totalprice-50 where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition 1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 1 PRIMARY orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (7%) Using where; Using rowid filter explain format=json update orders set o_totalprice = o_totalprice-50 where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')); EXPLAIN { "query_block": { "select_id": 1, "cost": 0.052271677, "nested_loop": [ { "table": { "table_name": "nation", "access_type": "ref", "possible_keys": ["PRIMARY", "i_n_name"], "key": "i_n_name", "key_length": "26", "used_key_parts": ["n_name"], "ref": ["const"], "loops": 1, "rows": 1, "cost": 0.002024411, "filtered": 100, "index_condition": "nation.n_name = 'PERU'" } }, { "table": { "table_name": "customer", "access_type": "ref", "possible_keys": ["PRIMARY", "i_c_nationkey"], "key": "i_c_nationkey", "key_length": "5", "used_key_parts": ["c_nationkey"], "ref": ["dbt3_s001.nation.n_nationkey"], "loops": 1, "rows": 6, "cost": 0.008193756, "filtered": 100 } }, { "table": { "table_name": "orders", "access_type": "ref", "possible_keys": ["i_o_orderdate", "i_o_custkey"], "key": "i_o_custkey", "key_length": "5", "used_key_parts": ["o_custkey"], "ref": ["dbt3_s001.customer.c_custkey"], "rowid_filter": { "range": { "key": "i_o_orderdate", "used_key_parts": ["o_orderDATE"] }, "rows": 108, "selectivity_pct": 7.2 }, "loops": 6, "rows": 15, "cost": 0.04205351, "filtered": 7.199999809, "attached_condition": "orders.o_orderDATE between '1992-01-01' and '1992-06-30'" } } ] } } update orders set o_totalprice = o_totalprice-50 where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')); select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')); o_orderkey o_totalprice 644 201218.06 2880 145711.99 3142 15980.15 5382 138373.03 5095 184533.99 737 12934.85 1729 12087.76 5121 150284.57 update orders set o_totalprice= o_totalprice+50 where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')); select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')); o_orderkey o_totalprice 644 201268.06 2880 145761.99 3142 16030.15 5382 138423.03 5095 184583.99 737 12984.85 1729 12137.76 5121 150334.57 explain select ps_partkey, ps_suppkey, ps_supplycost from partsupp where (ps_partkey, ps_suppkey) in (select p_partkey, s_suppkey from part, supplier where p_retailprice between 901 and 910 and s_nationkey in (select n_nationkey from nation where n_name='PERU')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition 1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 1 PRIMARY part ALL PRIMARY NULL NULL NULL 200 Using where; Using join buffer (flat, BNL join) 1 PRIMARY partsupp eq_ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 8 dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey 1 select ps_partkey, ps_suppkey, ps_supplycost from partsupp where (ps_partkey, ps_suppkey) in (select p_partkey, s_suppkey from part, supplier where p_retailprice between 901 and 910 and s_nationkey in (select n_nationkey from nation where n_name='PERU')); ps_partkey ps_suppkey ps_supplycost 1 8 357.84 3 8 645.4 4 1 444.37 5 8 50.52 6 1 642.13 7 8 763.98 8 1 957.34 explain update partsupp set ps_supplycost = ps_supplycost+2 where (ps_partkey, ps_suppkey) in (select p_partkey, s_suppkey from part, supplier where p_retailprice between 901 and 910 and s_nationkey in (select n_nationkey from nation where n_name='PERU')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition 1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 1 PRIMARY part ALL PRIMARY NULL NULL NULL 200 Using where 1 PRIMARY partsupp eq_ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 8 dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey 1 update partsupp set ps_supplycost = ps_supplycost+2 where (ps_partkey, ps_suppkey) in (select p_partkey, s_suppkey from part, supplier where p_retailprice between 901 and 910 and s_nationkey in (select n_nationkey from nation where n_name='PERU')); select ps_partkey, ps_suppkey, ps_supplycost from partsupp where (ps_partkey, ps_suppkey) in (select p_partkey, s_suppkey from part, supplier where p_retailprice between 901 and 910 and s_nationkey in (select n_nationkey from nation where n_name='PERU')); ps_partkey ps_suppkey ps_supplycost 1 8 359.84 3 8 647.4 4 1 446.37 5 8 52.52 6 1 644.13 7 8 765.98 8 1 959.34 update partsupp set ps_supplycost = ps_supplycost-2 where (ps_partkey, ps_suppkey) in (select p_partkey, s_suppkey from part, supplier where p_retailprice between 901 and 910 and s_nationkey in (select n_nationkey from nation where n_name='PERU')); select ps_partkey, ps_suppkey, ps_supplycost from partsupp where (ps_partkey, ps_suppkey) in (select p_partkey, s_suppkey from part, supplier where p_retailprice between 901 and 910 and s_nationkey in (select n_nationkey from nation where n_name='PERU')); ps_partkey ps_suppkey ps_supplycost 1 8 357.84 3 8 645.4 4 1 444.37 5 8 50.52 6 1 642.13 7 8 763.98 8 1 957.34 explain select ps_partkey, ps_suppkey, ps_supplycost from partsupp where ps_partkey in (select p_partkey from part where p_retailprice between 901 and 910) and ps_suppkey in (select s_suppkey from supplier where s_nationkey in (select n_nationkey from nation where n_name='PERU')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition 1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 1 PRIMARY part ALL PRIMARY NULL NULL NULL 200 Using where; Using join buffer (flat, BNL join) 1 PRIMARY partsupp eq_ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 8 dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey 1 select ps_partkey, ps_suppkey, ps_supplycost from partsupp where ps_partkey in (select p_partkey from part where p_retailprice between 901 and 910) and ps_suppkey in (select s_suppkey from supplier where s_nationkey in (select n_nationkey from nation where n_name='PERU')); ps_partkey ps_suppkey ps_supplycost 1 8 357.84 3 8 645.4 4 1 444.37 5 8 50.52 6 1 642.13 7 8 763.98 8 1 957.34 explain update partsupp set ps_supplycost = ps_supplycost+10 where ps_partkey in (select p_partkey from part where p_retailprice between 901 and 910) and ps_suppkey in (select s_suppkey from supplier where s_nationkey in (select n_nationkey from nation where n_name='PERU')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition 1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 1 PRIMARY part ALL PRIMARY NULL NULL NULL 200 Using where 1 PRIMARY partsupp eq_ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 8 dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey 1 update partsupp set ps_supplycost = ps_supplycost+10 where ps_partkey in (select p_partkey from part where p_retailprice between 901 and 910) and ps_suppkey in (select s_suppkey from supplier where s_nationkey in (select n_nationkey from nation where n_name='PERU')); select ps_partkey, ps_suppkey, ps_supplycost from partsupp where ps_partkey in (select p_partkey from part where p_retailprice between 901 and 910) and ps_suppkey in (select s_suppkey from supplier where s_nationkey in (select n_nationkey from nation where n_name='PERU')); ps_partkey ps_suppkey ps_supplycost 1 8 367.84 3 8 655.4 4 1 454.37 5 8 60.52 6 1 652.13 7 8 773.98 8 1 967.34 update partsupp set ps_supplycost = ps_supplycost-10 where ps_partkey in (select p_partkey from part where p_retailprice between 901 and 910) and ps_suppkey in (select s_suppkey from supplier where s_nationkey in (select n_nationkey from nation where n_name='PERU')); select ps_partkey, ps_suppkey, ps_supplycost from partsupp where ps_partkey in (select p_partkey from part where p_retailprice between 901 and 910) and ps_suppkey in (select s_suppkey from supplier where s_nationkey in (select n_nationkey from nation where n_name='PERU')); ps_partkey ps_suppkey ps_supplycost 1 8 357.84 3 8 645.4 4 1 444.37 5 8 50.52 6 1 642.13 7 8 763.98 8 1 957.34 explain select l_orderkey, l_linenumber, l_tax from lineitem where l_orderkey in (select o_orderkey from orders where o_custkey in (select c_custkey from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')) and o_orderDATE between '1992-06-30' and '1992-12-31') and (l_partkey, l_suppkey) in (select p_partkey, s_suppkey from part, supplier where p_retailprice between 901 and 1000 and s_nationkey in (select n_nationkey from nation where n_name='PERU')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition 1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 1 PRIMARY orders ref|filter PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (7%) Using where; Using rowid filter 1 PRIMARY lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where 1 PRIMARY part eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_partkey 1 Using where select l_orderkey, l_linenumber, l_tax from lineitem where l_orderkey in (select o_orderkey from orders where o_custkey in (select c_custkey from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')) and o_orderDATE between '1992-06-30' and '1992-12-31') and (l_partkey, l_suppkey) in (select p_partkey, s_suppkey from part, supplier where p_retailprice between 901 and 1000 and s_nationkey in (select n_nationkey from nation where n_name='PERU')); l_orderkey l_linenumber l_tax 4996 1 0.01 933 1 0.04 2500 2 0.02 2500 4 0.02 explain update lineitem set l_tax = (l_tax*100+1)/100 where l_orderkey in (select o_orderkey from orders where o_custkey in (select c_custkey from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')) and o_orderDATE between '1992-06-30' and '1992-12-31') and (l_partkey, l_suppkey) in (select p_partkey, s_suppkey from part, supplier where p_retailprice between 901 and 1000 and s_nationkey in (select n_nationkey from nation where n_name='PERU')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition 1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 1 PRIMARY orders ref|filter PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (7%) Using where; Using rowid filter 1 PRIMARY lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where 1 PRIMARY part eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_partkey 1 Using where update lineitem set l_tax = (l_tax*100+1)/100 where l_orderkey in (select o_orderkey from orders where o_custkey in (select c_custkey from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')) and o_orderDATE between '1992-06-30' and '1992-12-31') and (l_partkey, l_suppkey) in (select p_partkey, s_suppkey from part, supplier where p_retailprice between 901 and 1000 and s_nationkey in (select n_nationkey from nation where n_name='PERU')); select l_orderkey, l_linenumber, l_tax from lineitem where l_orderkey in (select o_orderkey from orders where o_custkey in (select c_custkey from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')) and o_orderDATE between '1992-06-30' and '1992-12-31') and (l_partkey, l_suppkey) in (select p_partkey, s_suppkey from part, supplier where p_retailprice between 901 and 1000 and s_nationkey in (select n_nationkey from nation where n_name='PERU')); l_orderkey l_linenumber l_tax 4996 1 0.02 933 1 0.05 2500 2 0.03 2500 4 0.03 update lineitem set l_tax = (l_tax*100-1)/100 where l_orderkey in (select o_orderkey from orders where o_custkey in (select c_custkey from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')) and o_orderDATE between '1992-06-30' and '1992-12-31') and (l_partkey, l_suppkey) in (select p_partkey, s_suppkey from part, supplier where p_retailprice between 901 and 1000 and s_nationkey in (select n_nationkey from nation where n_name='PERU')); select l_orderkey, l_linenumber, l_tax from lineitem where l_orderkey in (select o_orderkey from orders where o_custkey in (select c_custkey from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')) and o_orderDATE between '1992-06-30' and '1992-12-31') and (l_partkey, l_suppkey) in (select p_partkey, s_suppkey from part, supplier where p_retailprice between 901 and 1000 and s_nationkey in (select n_nationkey from nation where n_name='PERU')); l_orderkey l_linenumber l_tax 4996 1 0.01 933 1 0.04 2500 2 0.02 2500 4 0.02 # FirstMatch # ========== set optimizer_switch='materialization=off'; explain select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_regionkey in (1,2)) and c_custkey in (select o_custkey from orders where o_orderDATE between '1992-10-09' and '1993-06-08'); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY nation ALL PRIMARY,i_n_regionkey NULL NULL NULL 25 Using where 1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 1 PRIMARY orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (9%) Using where; FirstMatch(customer); Using rowid filter explain format=json select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_regionkey in (1,2)) and c_custkey in (select o_custkey from orders where o_orderDATE between '1992-10-09' and '1993-06-08'); EXPLAIN { "query_block": { "select_id": 1, "cost": 0.40015207, "nested_loop": [ { "table": { "table_name": "nation", "access_type": "ALL", "possible_keys": ["PRIMARY", "i_n_regionkey"], "loops": 1, "rows": 25, "cost": 0.013945725, "filtered": 40, "attached_condition": "nation.n_regionkey in (1,2)" } }, { "table": { "table_name": "customer", "access_type": "ref", "possible_keys": ["PRIMARY", "i_c_nationkey"], "key": "i_c_nationkey", "key_length": "5", "used_key_parts": ["c_nationkey"], "ref": ["dbt3_s001.nation.n_nationkey"], "loops": 10, "rows": 6, "cost": 0.08009436, "filtered": 100 } }, { "table": { "table_name": "orders", "access_type": "ref", "possible_keys": ["i_o_orderdate", "i_o_custkey"], "key": "i_o_custkey", "key_length": "5", "used_key_parts": ["o_custkey"], "ref": ["dbt3_s001.customer.c_custkey"], "rowid_filter": { "range": { "key": "i_o_orderdate", "used_key_parts": ["o_orderDATE"] }, "rows": 140, "selectivity_pct": 9.333333333 }, "loops": 60, "rows": 15, "cost": 0.306111985, "filtered": 6.666666508, "attached_condition": "orders.o_orderDATE between '1992-10-09' and '1993-06-08'", "first_match": "customer" } } ] } } select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_regionkey in (1,2)) and c_custkey in (select o_custkey from orders where o_orderDATE between '1992-10-09' and '1993-06-08'); c_name c_acctbal Customer#000000059 3458.6 Customer#000000106 3288.42 Customer#000000017 6.34 Customer#000000047 274.58 Customer#000000092 1182.91 Customer#000000101 7470.96 Customer#000000022 591.98 Customer#000000040 1335.3 Customer#000000064 -646.64 Customer#000000122 7865.46 Customer#000000028 1007.18 Customer#000000037 -917.75 Customer#000000091 4643.14 Customer#000000115 7508.92 Customer#000000067 8166.59 Customer#000000094 5500.11 Customer#000000103 2757.45 Customer#000000130 5073.58 Customer#000000139 7897.78 Customer#000000142 2209.81 Customer#000000025 7133.7 Customer#000000008 6819.74 Customer#000000061 1536.24 Customer#000000077 1738.87 Customer#000000097 2164.48 Customer#000000121 6428.32 Customer#000000133 2314.67 Customer#000000007 9561.95 Customer#000000019 8914.71 Customer#000000082 9468.34 Customer#000000124 1842.49 Customer#000000127 9280.71 explain update customer set c_acctbal = c_acctbal+10 where c_nationkey in (select n_nationkey from nation where n_regionkey in (1,2)) and c_custkey in (select o_custkey from orders where o_orderDATE between '1992-10-09' and '1993-06-08'); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY nation ALL PRIMARY,i_n_regionkey NULL NULL NULL 25 Using where 1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 1 PRIMARY orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (9%) Using where; FirstMatch(customer); Using rowid filter explain format=json update customer set c_acctbal = c_acctbal+10 where c_nationkey in (select n_nationkey from nation where n_regionkey in (1,2)) and c_custkey in (select o_custkey from orders where o_orderDATE between '1992-10-09' and '1993-06-08'); EXPLAIN { "query_block": { "select_id": 1, "cost": 0.40015207, "nested_loop": [ { "table": { "table_name": "nation", "access_type": "ALL", "possible_keys": ["PRIMARY", "i_n_regionkey"], "loops": 1, "rows": 25, "cost": 0.013945725, "filtered": 40, "attached_condition": "nation.n_regionkey in (1,2)" } }, { "table": { "table_name": "customer", "access_type": "ref", "possible_keys": ["PRIMARY", "i_c_nationkey"], "key": "i_c_nationkey", "key_length": "5", "used_key_parts": ["c_nationkey"], "ref": ["dbt3_s001.nation.n_nationkey"], "loops": 10, "rows": 6, "cost": 0.08009436, "filtered": 100 } }, { "table": { "table_name": "orders", "access_type": "ref", "possible_keys": ["i_o_orderdate", "i_o_custkey"], "key": "i_o_custkey", "key_length": "5", "used_key_parts": ["o_custkey"], "ref": ["dbt3_s001.customer.c_custkey"], "rowid_filter": { "range": { "key": "i_o_orderdate", "used_key_parts": ["o_orderDATE"] }, "rows": 140, "selectivity_pct": 9.333333333 }, "loops": 60, "rows": 15, "cost": 0.306111985, "filtered": 6.666666508, "attached_condition": "orders.o_orderDATE between '1992-10-09' and '1993-06-08'", "first_match": "customer" } } ] } } update customer set c_acctbal = c_acctbal+10 where c_nationkey in (select n_nationkey from nation where n_regionkey in (1,2)) and c_custkey in (select o_custkey from orders where o_orderDATE between '1992-10-09' and '1993-06-08'); select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_regionkey in (1,2)) and c_custkey in (select o_custkey from orders where o_orderDATE between '1992-10-09' and '1993-06-08'); c_name c_acctbal Customer#000000059 3468.6 Customer#000000106 3298.42 Customer#000000017 16.34 Customer#000000047 284.58 Customer#000000092 1192.91 Customer#000000101 7480.96 Customer#000000022 601.98 Customer#000000040 1345.3 Customer#000000064 -636.64 Customer#000000122 7875.46 Customer#000000028 1017.18 Customer#000000037 -907.75 Customer#000000091 4653.14 Customer#000000115 7518.92 Customer#000000067 8176.59 Customer#000000094 5510.11 Customer#000000103 2767.45 Customer#000000130 5083.58 Customer#000000139 7907.78 Customer#000000142 2219.81 Customer#000000025 7143.7 Customer#000000008 6829.74 Customer#000000061 1546.24 Customer#000000077 1748.87 Customer#000000097 2174.48 Customer#000000121 6438.32 Customer#000000133 2324.67 Customer#000000007 9571.95 Customer#000000019 8924.71 Customer#000000082 9478.34 Customer#000000124 1852.49 Customer#000000127 9290.71 update customer set c_acctbal = c_acctbal-10 where c_nationkey in (select n_nationkey from nation where n_regionkey in (1,2)) and c_custkey in (select o_custkey from orders where o_orderDATE between '1992-10-09' and '1993-06-08'); select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_regionkey in (1,2)) and c_custkey in (select o_custkey from orders where o_orderDATE between '1992-10-09' and '1993-06-08'); c_name c_acctbal Customer#000000059 3458.6 Customer#000000106 3288.42 Customer#000000017 6.34 Customer#000000047 274.58 Customer#000000092 1182.91 Customer#000000101 7470.96 Customer#000000022 591.98 Customer#000000040 1335.3 Customer#000000064 -646.64 Customer#000000122 7865.46 Customer#000000028 1007.18 Customer#000000037 -917.75 Customer#000000091 4643.14 Customer#000000115 7508.92 Customer#000000067 8166.59 Customer#000000094 5500.11 Customer#000000103 2757.45 Customer#000000130 5073.58 Customer#000000139 7897.78 Customer#000000142 2209.81 Customer#000000025 7133.7 Customer#000000008 6819.74 Customer#000000061 1536.24 Customer#000000077 1738.87 Customer#000000097 2164.48 Customer#000000121 6428.32 Customer#000000133 2314.67 Customer#000000007 9561.95 Customer#000000019 8914.71 Customer#000000082 9468.34 Customer#000000124 1842.49 Customer#000000127 9280.71 set optimizer_switch='materialization=default'; explain select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU') and c_custkey in (select o_custkey from orders where o_orderDATE between "1992-01-09" and "1993-01-08"); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition 1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 1 PRIMARY orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (14%) Using where; FirstMatch(customer); Using rowid filter select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU') and c_custkey in (select o_custkey from orders where o_orderDATE between "1992-01-09" and "1993-01-08"); c_name c_acctbal Customer#000000008 6819.74 Customer#000000035 1228.24 Customer#000000061 1536.24 Customer#000000097 2164.48 Customer#000000121 6428.32 Customer#000000133 2314.67 explain update customer set c_acctbal = c_acctbal+20 where c_nationkey in (select n_nationkey from nation where n_name='PERU') and c_custkey in (select o_custkey from orders where o_orderDATE between "1992-01-09" and "1993-01-08"); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition 1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 1 PRIMARY orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (14%) Using where; FirstMatch(customer); Using rowid filter update customer set c_acctbal = c_acctbal+20 where c_nationkey in (select n_nationkey from nation where n_name='PERU') and c_custkey in (select o_custkey from orders where o_orderDATE between "1992-01-09" and "1993-01-08"); select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU') and c_custkey in (select o_custkey from orders where o_orderDATE between "1992-01-09" and "1993-01-08"); c_name c_acctbal Customer#000000008 6839.74 Customer#000000035 1248.24 Customer#000000061 1556.24 Customer#000000097 2184.48 Customer#000000121 6448.32 Customer#000000133 2334.67 update customer set c_acctbal = c_acctbal-20 where c_nationkey in (select n_nationkey from nation where n_name='PERU') and c_custkey in (select o_custkey from orders where o_orderDATE between "1992-01-09" and "1993-01-08"); select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU') and c_custkey in (select o_custkey from orders where o_orderDATE between "1992-01-09" and "1993-01-08"); c_name c_acctbal Customer#000000008 6819.74 Customer#000000035 1228.24 Customer#000000061 1536.24 Customer#000000097 2164.48 Customer#000000121 6428.32 Customer#000000133 2314.67 # Materialization # =============== set optimizer_switch='firstmatch=off'; explain select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA')) and c_custkey in (select o_custkey from orders where o_orderDATE between "1992-01-09" and "1995-01-08"); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY nation range PRIMARY,i_n_name i_n_name 26 NULL 4 Using index condition 1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 3 MATERIALIZED orders ALL i_o_orderdate,i_o_custkey NULL NULL NULL 1500 Using where select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA')) and c_custkey in (select o_custkey from orders where o_orderDATE between "1992-01-09" and "1995-01-08"); c_name c_acctbal Customer#000000014 5266.3 Customer#000000059 3458.6 Customer#000000106 3288.42 Customer#000000067 8166.59 Customer#000000094 5500.11 Customer#000000103 2757.45 Customer#000000130 5073.58 Customer#000000139 7897.78 Customer#000000142 2209.81 Customer#000000025 7133.7 Customer#000000038 6345.11 Customer#000000098 -551.37 Customer#000000113 2912 Customer#000000008 6819.74 Customer#000000035 1228.24 Customer#000000061 1536.24 Customer#000000077 1738.87 Customer#000000097 2164.48 Customer#000000121 6428.32 Customer#000000133 2314.67 explain update customer set c_acctbal = c_acctbal+20 where c_nationkey in (select n_nationkey from nation where n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA')) and c_custkey in (select o_custkey from orders where o_orderDATE between "1992-01-09" and "1995-01-08"); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY nation range PRIMARY,i_n_name i_n_name 26 NULL 4 Using index condition 1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 3 MATERIALIZED orders ALL i_o_orderdate,i_o_custkey NULL NULL NULL 1500 Using where update customer set c_acctbal = c_acctbal+20 where c_nationkey in (select n_nationkey from nation where n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA')) and c_custkey in (select o_custkey from orders where o_orderDATE between "1992-01-09" and "1995-01-08"); select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA')) and c_custkey in (select o_custkey from orders where o_orderDATE between "1992-01-09" and "1995-01-08"); c_name c_acctbal Customer#000000014 5286.3 Customer#000000059 3478.6 Customer#000000106 3308.42 Customer#000000067 8186.59 Customer#000000094 5520.11 Customer#000000103 2777.45 Customer#000000130 5093.58 Customer#000000139 7917.78 Customer#000000142 2229.81 Customer#000000025 7153.7 Customer#000000038 6365.11 Customer#000000098 -531.37 Customer#000000113 2932 Customer#000000008 6839.74 Customer#000000035 1248.24 Customer#000000061 1556.24 Customer#000000077 1758.87 Customer#000000097 2184.48 Customer#000000121 6448.32 Customer#000000133 2334.67 update customer set c_acctbal = c_acctbal-20 where c_nationkey in (select n_nationkey from nation where n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA')) and c_custkey in (select o_custkey from orders where o_orderDATE between "1992-01-09" and "1995-01-08"); select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA')) and c_custkey in (select o_custkey from orders where o_orderDATE between "1992-01-09" and "1995-01-08"); c_name c_acctbal Customer#000000014 5266.3 Customer#000000059 3458.6 Customer#000000106 3288.42 Customer#000000067 8166.59 Customer#000000094 5500.11 Customer#000000103 2757.45 Customer#000000130 5073.58 Customer#000000139 7897.78 Customer#000000142 2209.81 Customer#000000025 7133.7 Customer#000000038 6345.11 Customer#000000098 -551.37 Customer#000000113 2912 Customer#000000008 6819.74 Customer#000000035 1228.24 Customer#000000061 1536.24 Customer#000000077 1738.87 Customer#000000097 2164.48 Customer#000000121 6428.32 Customer#000000133 2314.67 set optimizer_switch='firstmatch=default'; explain select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1993-03-08'); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY customer ALL PRIMARY NULL NULL NULL 150 1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED orders ALL i_o_orderdate,i_o_custkey NULL NULL NULL 1500 Using where explain format=json select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1993-03-08'); EXPLAIN { "query_block": { "select_id": 1, "cost": 0.382051418, "nested_loop": [ { "table": { "table_name": "customer", "access_type": "ALL", "possible_keys": ["PRIMARY"], "loops": 1, "rows": 150, "cost": 0.03493875, "filtered": 100 } }, { "table": { "table_name": "", "access_type": "eq_ref", "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "4", "used_key_parts": ["o_custkey"], "ref": ["func"], "rows": 1, "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, "nested_loop": [ { "table": { "table_name": "orders", "access_type": "ALL", "possible_keys": ["i_o_orderdate", "i_o_custkey"], "loops": 1, "rows": 1500, "cost": 0.2532975, "filtered": 16.13333321, "attached_condition": "orders.o_orderDATE between '1992-01-09' and '1993-03-08'" } } ] } } } } ] } } select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name c_acctbal Customer#000000001 711.56 Customer#000000002 121.65 Customer#000000005 794.47 Customer#000000007 9561.95 Customer#000000008 6819.74 Customer#000000010 2753.54 Customer#000000011 -272.6 Customer#000000013 3857.34 Customer#000000014 5266.3 Customer#000000016 4681.03 Customer#000000017 6.34 Customer#000000019 8914.71 Customer#000000022 591.98 Customer#000000023 3332.02 Customer#000000025 7133.7 Customer#000000028 1007.18 Customer#000000029 7618.27 Customer#000000031 5236.89 Customer#000000032 3471.53 Customer#000000034 8589.7 Customer#000000035 1228.24 Customer#000000037 -917.75 Customer#000000038 6345.11 Customer#000000040 1335.3 Customer#000000041 270.95 Customer#000000043 9904.28 Customer#000000044 7315.94 Customer#000000046 5744.59 Customer#000000047 274.58 Customer#000000049 4573.94 Customer#000000052 5630.28 Customer#000000053 4113.64 Customer#000000055 4572.11 Customer#000000056 6530.86 Customer#000000058 6478.46 Customer#000000059 3458.6 Customer#000000061 1536.24 Customer#000000062 595.61 Customer#000000064 -646.64 Customer#000000065 8795.16 Customer#000000067 8166.59 Customer#000000070 4867.52 Customer#000000071 -611.19 Customer#000000073 4288.5 Customer#000000074 2764.43 Customer#000000076 5745.33 Customer#000000079 5121.28 Customer#000000080 7383.53 Customer#000000082 9468.34 Customer#000000083 6463.51 Customer#000000085 3386.64 Customer#000000086 3306.32 Customer#000000088 8031.44 Customer#000000089 1530.76 Customer#000000091 4643.14 Customer#000000092 1182.91 Customer#000000094 5500.11 Customer#000000095 5327.38 Customer#000000097 2164.48 Customer#000000098 -551.37 Customer#000000100 9889.89 Customer#000000101 7470.96 Customer#000000103 2757.45 Customer#000000104 -588.38 Customer#000000106 3288.42 Customer#000000107 2514.15 Customer#000000109 -716.1 Customer#000000110 7462.99 Customer#000000112 2953.35 Customer#000000115 7508.92 Customer#000000116 8403.99 Customer#000000118 3582.37 Customer#000000121 6428.32 Customer#000000122 7865.46 Customer#000000127 9280.71 Customer#000000128 -986.96 Customer#000000130 5073.58 Customer#000000131 8595.53 Customer#000000133 2314.67 Customer#000000134 4608.9 Customer#000000136 -842.39 Customer#000000137 7838.3 Customer#000000139 7897.78 Customer#000000140 9963.15 Customer#000000142 2209.81 Customer#000000143 2186.5 Customer#000000145 9748.93 Customer#000000148 2135.6 Customer#000000149 8959.65 explain update customer set c_acctbal = c_acctbal+5 where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1993-03-08'); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY customer ALL PRIMARY NULL NULL NULL 150 1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED orders ALL i_o_orderdate,i_o_custkey NULL NULL NULL 1500 Using where explain format=json update customer set c_acctbal = c_acctbal+5 where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1993-03-08'); EXPLAIN { "query_block": { "select_id": 1, "cost": 0.382051418, "nested_loop": [ { "table": { "table_name": "customer", "access_type": "ALL", "possible_keys": ["PRIMARY"], "loops": 1, "rows": 150, "cost": 0.03493875, "filtered": 100 } }, { "table": { "table_name": "", "access_type": "eq_ref", "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "4", "used_key_parts": ["o_custkey"], "ref": ["func"], "rows": 1, "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, "nested_loop": [ { "table": { "table_name": "orders", "access_type": "ALL", "possible_keys": ["i_o_orderdate", "i_o_custkey"], "loops": 1, "rows": 1500, "cost": 0.2532975, "filtered": 16.13333321, "attached_condition": "orders.o_orderDATE between '1992-01-09' and '1993-03-08'" } } ] } } } } ] } } update customer set c_acctbal = c_acctbal+5 where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1993-03-08'); select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name c_acctbal Customer#000000001 716.56 Customer#000000002 126.65 Customer#000000005 799.47 Customer#000000007 9566.95 Customer#000000008 6824.74 Customer#000000010 2758.54 Customer#000000011 -267.6 Customer#000000013 3862.34 Customer#000000014 5271.3 Customer#000000016 4686.03 Customer#000000017 11.34 Customer#000000019 8919.71 Customer#000000022 596.98 Customer#000000023 3337.02 Customer#000000025 7138.7 Customer#000000028 1012.18 Customer#000000029 7623.27 Customer#000000031 5241.89 Customer#000000032 3476.53 Customer#000000034 8594.7 Customer#000000035 1233.24 Customer#000000037 -912.75 Customer#000000038 6350.11 Customer#000000040 1340.3 Customer#000000041 275.95 Customer#000000043 9909.28 Customer#000000044 7320.94 Customer#000000046 5749.59 Customer#000000047 279.58 Customer#000000049 4578.94 Customer#000000052 5635.28 Customer#000000053 4118.64 Customer#000000055 4577.11 Customer#000000056 6535.86 Customer#000000058 6483.46 Customer#000000059 3463.6 Customer#000000061 1541.24 Customer#000000062 600.61 Customer#000000064 -641.64 Customer#000000065 8800.16 Customer#000000067 8171.59 Customer#000000070 4872.52 Customer#000000071 -606.19 Customer#000000073 4293.5 Customer#000000074 2769.43 Customer#000000076 5750.33 Customer#000000079 5126.28 Customer#000000080 7388.53 Customer#000000082 9473.34 Customer#000000083 6468.51 Customer#000000085 3391.64 Customer#000000086 3311.32 Customer#000000088 8036.44 Customer#000000089 1535.76 Customer#000000091 4648.14 Customer#000000092 1187.91 Customer#000000094 5505.11 Customer#000000095 5332.38 Customer#000000097 2169.48 Customer#000000098 -546.37 Customer#000000100 9894.89 Customer#000000101 7475.96 Customer#000000103 2762.45 Customer#000000104 -583.38 Customer#000000106 3293.42 Customer#000000107 2519.15 Customer#000000109 -711.1 Customer#000000110 7467.99 Customer#000000112 2958.35 Customer#000000115 7513.92 Customer#000000116 8408.99 Customer#000000118 3587.37 Customer#000000121 6433.32 Customer#000000122 7870.46 Customer#000000127 9285.71 Customer#000000128 -981.96 Customer#000000130 5078.58 Customer#000000131 8600.53 Customer#000000133 2319.67 Customer#000000134 4613.9 Customer#000000136 -837.39 Customer#000000137 7843.3 Customer#000000139 7902.78 Customer#000000140 9968.15 Customer#000000142 2214.81 Customer#000000143 2191.5 Customer#000000145 9753.93 Customer#000000148 2140.6 Customer#000000149 8964.65 update customer set c_acctbal = c_acctbal-5 where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1993-03-08'); select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name c_acctbal Customer#000000001 711.56 Customer#000000002 121.65 Customer#000000005 794.47 Customer#000000007 9561.95 Customer#000000008 6819.74 Customer#000000010 2753.54 Customer#000000011 -272.6 Customer#000000013 3857.34 Customer#000000014 5266.3 Customer#000000016 4681.03 Customer#000000017 6.34 Customer#000000019 8914.71 Customer#000000022 591.98 Customer#000000023 3332.02 Customer#000000025 7133.7 Customer#000000028 1007.18 Customer#000000029 7618.27 Customer#000000031 5236.89 Customer#000000032 3471.53 Customer#000000034 8589.7 Customer#000000035 1228.24 Customer#000000037 -917.75 Customer#000000038 6345.11 Customer#000000040 1335.3 Customer#000000041 270.95 Customer#000000043 9904.28 Customer#000000044 7315.94 Customer#000000046 5744.59 Customer#000000047 274.58 Customer#000000049 4573.94 Customer#000000052 5630.28 Customer#000000053 4113.64 Customer#000000055 4572.11 Customer#000000056 6530.86 Customer#000000058 6478.46 Customer#000000059 3458.6 Customer#000000061 1536.24 Customer#000000062 595.61 Customer#000000064 -646.64 Customer#000000065 8795.16 Customer#000000067 8166.59 Customer#000000070 4867.52 Customer#000000071 -611.19 Customer#000000073 4288.5 Customer#000000074 2764.43 Customer#000000076 5745.33 Customer#000000079 5121.28 Customer#000000080 7383.53 Customer#000000082 9468.34 Customer#000000083 6463.51 Customer#000000085 3386.64 Customer#000000086 3306.32 Customer#000000088 8031.44 Customer#000000089 1530.76 Customer#000000091 4643.14 Customer#000000092 1182.91 Customer#000000094 5500.11 Customer#000000095 5327.38 Customer#000000097 2164.48 Customer#000000098 -551.37 Customer#000000100 9889.89 Customer#000000101 7470.96 Customer#000000103 2757.45 Customer#000000104 -588.38 Customer#000000106 3288.42 Customer#000000107 2514.15 Customer#000000109 -716.1 Customer#000000110 7462.99 Customer#000000112 2953.35 Customer#000000115 7508.92 Customer#000000116 8403.99 Customer#000000118 3582.37 Customer#000000121 6428.32 Customer#000000122 7865.46 Customer#000000127 9280.71 Customer#000000128 -986.96 Customer#000000130 5073.58 Customer#000000131 8595.53 Customer#000000133 2314.67 Customer#000000134 4608.9 Customer#000000136 -842.39 Customer#000000137 7838.3 Customer#000000139 7897.78 Customer#000000140 9963.15 Customer#000000142 2209.81 Customer#000000143 2186.5 Customer#000000145 9748.93 Customer#000000148 2135.6 Customer#000000149 8959.65 explain select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-06-09' and '1993-01-08'); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY customer ALL PRIMARY NULL NULL NULL 150 1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED orders range i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 114 Using index condition; Using where select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-06-09' and '1993-01-08'); c_name c_acctbal Customer#000000001 711.56 Customer#000000002 121.65 Customer#000000007 9561.95 Customer#000000008 6819.74 Customer#000000010 2753.54 Customer#000000011 -272.6 Customer#000000016 4681.03 Customer#000000017 6.34 Customer#000000019 8914.71 Customer#000000022 591.98 Customer#000000023 3332.02 Customer#000000025 7133.7 Customer#000000028 1007.18 Customer#000000029 7618.27 Customer#000000031 5236.89 Customer#000000034 8589.7 Customer#000000037 -917.75 Customer#000000040 1335.3 Customer#000000043 9904.28 Customer#000000044 7315.94 Customer#000000046 5744.59 Customer#000000047 274.58 Customer#000000049 4573.94 Customer#000000053 4113.64 Customer#000000055 4572.11 Customer#000000061 1536.24 Customer#000000064 -646.64 Customer#000000067 8166.59 Customer#000000070 4867.52 Customer#000000071 -611.19 Customer#000000073 4288.5 Customer#000000074 2764.43 Customer#000000076 5745.33 Customer#000000079 5121.28 Customer#000000080 7383.53 Customer#000000082 9468.34 Customer#000000083 6463.51 Customer#000000085 3386.64 Customer#000000086 3306.32 Customer#000000088 8031.44 Customer#000000091 4643.14 Customer#000000092 1182.91 Customer#000000095 5327.38 Customer#000000097 2164.48 Customer#000000100 9889.89 Customer#000000101 7470.96 Customer#000000103 2757.45 Customer#000000104 -588.38 Customer#000000106 3288.42 Customer#000000109 -716.1 Customer#000000110 7462.99 Customer#000000112 2953.35 Customer#000000118 3582.37 Customer#000000121 6428.32 Customer#000000122 7865.46 Customer#000000127 9280.71 Customer#000000130 5073.58 Customer#000000131 8595.53 Customer#000000133 2314.67 Customer#000000134 4608.9 Customer#000000136 -842.39 Customer#000000137 7838.3 Customer#000000139 7897.78 Customer#000000142 2209.81 Customer#000000143 2186.5 Customer#000000148 2135.6 Customer#000000149 8959.65 explain update customer set c_acctbal = c_acctbal+1 where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-06-09' and '1993-01-08'); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY customer ALL PRIMARY NULL NULL NULL 150 1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED orders range i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 114 Using index condition; Using where update customer set c_acctbal = c_acctbal+1 where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-06-09' and '1993-01-08'); select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-06-09' and '1993-01-08'); c_name c_acctbal Customer#000000001 712.56 Customer#000000002 122.65 Customer#000000007 9562.95 Customer#000000008 6820.74 Customer#000000010 2754.54 Customer#000000011 -271.6 Customer#000000016 4682.03 Customer#000000017 7.34 Customer#000000019 8915.71 Customer#000000022 592.98 Customer#000000023 3333.02 Customer#000000025 7134.7 Customer#000000028 1008.18 Customer#000000029 7619.27 Customer#000000031 5237.89 Customer#000000034 8590.7 Customer#000000037 -916.75 Customer#000000040 1336.3 Customer#000000043 9905.28 Customer#000000044 7316.94 Customer#000000046 5745.59 Customer#000000047 275.58 Customer#000000049 4574.94 Customer#000000053 4114.64 Customer#000000055 4573.11 Customer#000000061 1537.24 Customer#000000064 -645.64 Customer#000000067 8167.59 Customer#000000070 4868.52 Customer#000000071 -610.19 Customer#000000073 4289.5 Customer#000000074 2765.43 Customer#000000076 5746.33 Customer#000000079 5122.28 Customer#000000080 7384.53 Customer#000000082 9469.34 Customer#000000083 6464.51 Customer#000000085 3387.64 Customer#000000086 3307.32 Customer#000000088 8032.44 Customer#000000091 4644.14 Customer#000000092 1183.91 Customer#000000095 5328.38 Customer#000000097 2165.48 Customer#000000100 9890.89 Customer#000000101 7471.96 Customer#000000103 2758.45 Customer#000000104 -587.38 Customer#000000106 3289.42 Customer#000000109 -715.1 Customer#000000110 7463.99 Customer#000000112 2954.35 Customer#000000118 3583.37 Customer#000000121 6429.32 Customer#000000122 7866.46 Customer#000000127 9281.71 Customer#000000130 5074.58 Customer#000000131 8596.53 Customer#000000133 2315.67 Customer#000000134 4609.9 Customer#000000136 -841.39 Customer#000000137 7839.3 Customer#000000139 7898.78 Customer#000000142 2210.81 Customer#000000143 2187.5 Customer#000000148 2136.6 Customer#000000149 8960.65 update customer set c_acctbal = c_acctbal-1 where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-06-09' and '1993-01-08'); select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-06-09' and '1993-01-08'); c_name c_acctbal Customer#000000001 711.56 Customer#000000002 121.65 Customer#000000007 9561.95 Customer#000000008 6819.74 Customer#000000010 2753.54 Customer#000000011 -272.6 Customer#000000016 4681.03 Customer#000000017 6.34 Customer#000000019 8914.71 Customer#000000022 591.98 Customer#000000023 3332.02 Customer#000000025 7133.7 Customer#000000028 1007.18 Customer#000000029 7618.27 Customer#000000031 5236.89 Customer#000000034 8589.7 Customer#000000037 -917.75 Customer#000000040 1335.3 Customer#000000043 9904.28 Customer#000000044 7315.94 Customer#000000046 5744.59 Customer#000000047 274.58 Customer#000000049 4573.94 Customer#000000053 4113.64 Customer#000000055 4572.11 Customer#000000061 1536.24 Customer#000000064 -646.64 Customer#000000067 8166.59 Customer#000000070 4867.52 Customer#000000071 -611.19 Customer#000000073 4288.5 Customer#000000074 2764.43 Customer#000000076 5745.33 Customer#000000079 5121.28 Customer#000000080 7383.53 Customer#000000082 9468.34 Customer#000000083 6463.51 Customer#000000085 3386.64 Customer#000000086 3306.32 Customer#000000088 8031.44 Customer#000000091 4643.14 Customer#000000092 1182.91 Customer#000000095 5327.38 Customer#000000097 2164.48 Customer#000000100 9889.89 Customer#000000101 7470.96 Customer#000000103 2757.45 Customer#000000104 -588.38 Customer#000000106 3288.42 Customer#000000109 -716.1 Customer#000000110 7462.99 Customer#000000112 2953.35 Customer#000000118 3582.37 Customer#000000121 6428.32 Customer#000000122 7865.46 Customer#000000127 9280.71 Customer#000000130 5073.58 Customer#000000131 8595.53 Customer#000000133 2314.67 Customer#000000134 4608.9 Customer#000000136 -842.39 Customer#000000137 7838.3 Customer#000000139 7897.78 Customer#000000142 2209.81 Customer#000000143 2186.5 Customer#000000148 2135.6 Customer#000000149 8959.65 # Materialization SJM # =================== explain select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08' group by o_custkey having count(o_custkey) > 1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ALL distinct_key NULL NULL NULL 28 1 PRIMARY customer eq_ref PRIMARY PRIMARY 4 .o_custkey 1 2 MATERIALIZED orders range i_o_orderdate i_o_orderdate 4 NULL 28 Using index condition; Using temporary explain format=json select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08' group by o_custkey having count(o_custkey) > 1); EXPLAIN { "query_block": { "select_id": 1, "cost": 0.085533248, "nested_loop": [ { "table": { "table_name": "", "access_type": "ALL", "possible_keys": ["distinct_key"], "loops": 1, "rows": 28, "cost": 0.03691572, "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, "cost": 0.053826401, "having_condition": "count(orders.o_custkey) > 1", "temporary_table": { "nested_loop": [ { "table": { "table_name": "orders", "access_type": "range", "possible_keys": ["i_o_orderdate"], "key": "i_o_orderdate", "key_length": "4", "used_key_parts": ["o_orderDATE"], "loops": 1, "rows": 28, "cost": 0.035889016, "filtered": 100, "index_condition": "orders.o_orderDATE between '1992-01-09' and '1992-03-08'" } } ] } } } } }, { "table": { "table_name": "customer", "access_type": "eq_ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["c_custkey"], "ref": [".o_custkey"], "loops": 28, "rows": 1, "cost": 0.048617528, "filtered": 100 } } ] } } select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08' group by o_custkey having count(o_custkey) > 1); c_name c_acctbal Customer#000000013 3857.34 Customer#000000032 3471.53 Customer#000000037 -917.75 Customer#000000118 3582.37 Customer#000000056 6530.86 explain update customer set c_acctbal = c_acctbal-5 where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08' group by o_custkey having count(o_custkey) > 1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ALL distinct_key NULL NULL NULL 28 1 PRIMARY customer eq_ref PRIMARY PRIMARY 4 .o_custkey 1 2 MATERIALIZED orders range i_o_orderdate i_o_orderdate 4 NULL 28 Using index condition; Using temporary explain format=json update customer set c_acctbal = c_acctbal-5 where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08' group by o_custkey having count(o_custkey) > 1); EXPLAIN { "query_block": { "select_id": 1, "cost": 0.085533248, "nested_loop": [ { "table": { "table_name": "", "access_type": "ALL", "possible_keys": ["distinct_key"], "loops": 1, "rows": 28, "cost": 0.03691572, "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, "cost": 0.053826401, "having_condition": "count(orders.o_custkey) > 1", "temporary_table": { "nested_loop": [ { "table": { "table_name": "orders", "access_type": "range", "possible_keys": ["i_o_orderdate"], "key": "i_o_orderdate", "key_length": "4", "used_key_parts": ["o_orderDATE"], "loops": 1, "rows": 28, "cost": 0.035889016, "filtered": 100, "index_condition": "orders.o_orderDATE between '1992-01-09' and '1992-03-08'" } } ] } } } } }, { "table": { "table_name": "customer", "access_type": "eq_ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["c_custkey"], "ref": [".o_custkey"], "loops": 28, "rows": 1, "cost": 0.048617528, "filtered": 100 } } ] } } update customer set c_acctbal = c_acctbal-5 where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08' group by o_custkey having count(o_custkey) > 1); select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08' group by o_custkey having count(o_custkey) > 1); c_name c_acctbal Customer#000000013 3852.34 Customer#000000032 3466.53 Customer#000000037 -922.75 Customer#000000118 3577.37 Customer#000000056 6525.86 update customer set c_acctbal = c_acctbal+5 where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08' group by o_custkey having count(o_custkey) > 1); select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08' group by o_custkey having count(o_custkey) > 1); c_name c_acctbal Customer#000000013 3857.34 Customer#000000032 3471.53 Customer#000000037 -917.75 Customer#000000118 3582.37 Customer#000000056 6530.86 explain select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1993-03-08' group by o_custkey having count(o_custkey) > 5); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY customer ALL PRIMARY NULL NULL NULL 150 1 PRIMARY eq_ref distinct_key distinct_key 4 dbt3_s001.customer.c_custkey 1 2 MATERIALIZED orders ALL i_o_orderdate NULL NULL NULL 1500 Using where; Using temporary select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1993-03-08' group by o_custkey having count(o_custkey) > 5); c_name c_acctbal Customer#000000007 9561.95 Customer#000000016 4681.03 Customer#000000037 -917.75 Customer#000000046 5744.59 Customer#000000091 4643.14 Customer#000000103 2757.45 Customer#000000118 3582.37 Customer#000000133 2314.67 Customer#000000134 4608.9 explain update customer set c_acctbal = c_acctbal-1 where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1993-03-08' group by o_custkey having count(o_custkey) > 5); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY customer ALL PRIMARY NULL NULL NULL 150 1 PRIMARY eq_ref distinct_key distinct_key 4 dbt3_s001.customer.c_custkey 1 2 MATERIALIZED orders ALL i_o_orderdate NULL NULL NULL 1500 Using where; Using temporary update customer set c_acctbal = c_acctbal-1 where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1993-03-08' group by o_custkey having count(o_custkey) > 5); select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1993-03-08' group by o_custkey having count(o_custkey) > 5); c_name c_acctbal Customer#000000007 9560.95 Customer#000000016 4680.03 Customer#000000037 -918.75 Customer#000000046 5743.59 Customer#000000091 4642.14 Customer#000000103 2756.45 Customer#000000118 3581.37 Customer#000000133 2313.67 Customer#000000134 4607.9 update customer set c_acctbal = c_acctbal+1 where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1993-03-08' group by o_custkey having count(o_custkey) > 5); select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1993-03-08' group by o_custkey having count(o_custkey) > 5); c_name c_acctbal Customer#000000007 9561.95 Customer#000000016 4681.03 Customer#000000037 -917.75 Customer#000000046 5744.59 Customer#000000091 4643.14 Customer#000000103 2757.45 Customer#000000118 3582.37 Customer#000000133 2314.67 Customer#000000134 4608.9 # Pullout PS # ========== prepare stmt from " update orders set o_totalprice = o_totalprice+? where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')); "; select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')); o_orderkey o_totalprice 644 201268.06 2880 145761.99 3142 16030.15 5382 138423.03 5095 184583.99 737 12984.85 1729 12137.76 5121 150334.57 set @a1=-20; execute stmt using @a1; select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')); o_orderkey o_totalprice 644 201248.06 2880 145741.99 3142 16010.15 5382 138403.03 5095 184563.99 737 12964.85 1729 12117.76 5121 150314.57 set @a2=-10; execute stmt using @a2; select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')); o_orderkey o_totalprice 644 201238.06 2880 145731.99 3142 16000.15 5382 138393.03 5095 184553.99 737 12954.85 1729 12107.76 5121 150304.57 execute stmt using -(@a1+@a2); select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')); o_orderkey o_totalprice 644 201268.06 2880 145761.99 3142 16030.15 5382 138423.03 5095 184583.99 737 12984.85 1729 12137.76 5121 150334.57 deallocate prepare stmt; # FirstMatch PS # ============= set optimizer_switch='materialization=off'; prepare stmt from " update customer set c_acctbal = c_acctbal+? where c_nationkey in (select n_nationkey from nation where n_regionkey in (1,2)) and c_custkey in (select o_custkey from orders where o_orderDATE between '1992-10-09' and '1993-06-08'); "; select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_regionkey in (1,2)) and c_custkey in (select o_custkey from orders where o_orderDATE between '1992-10-09' and '1993-06-08'); c_name c_acctbal Customer#000000059 3458.6 Customer#000000106 3288.42 Customer#000000017 6.34 Customer#000000047 274.58 Customer#000000092 1182.91 Customer#000000101 7470.96 Customer#000000022 591.98 Customer#000000040 1335.3 Customer#000000064 -646.64 Customer#000000122 7865.46 Customer#000000028 1007.18 Customer#000000037 -917.75 Customer#000000091 4643.14 Customer#000000115 7508.92 Customer#000000067 8166.59 Customer#000000094 5500.11 Customer#000000103 2757.45 Customer#000000130 5073.58 Customer#000000139 7897.78 Customer#000000142 2209.81 Customer#000000025 7133.7 Customer#000000008 6819.74 Customer#000000061 1536.24 Customer#000000077 1738.87 Customer#000000097 2164.48 Customer#000000121 6428.32 Customer#000000133 2314.67 Customer#000000007 9561.95 Customer#000000019 8914.71 Customer#000000082 9468.34 Customer#000000124 1842.49 Customer#000000127 9280.71 set @a1=15; execute stmt using @a1; select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_regionkey in (1,2)) and c_custkey in (select o_custkey from orders where o_orderDATE between '1992-10-09' and '1993-06-08'); c_name c_acctbal Customer#000000059 3473.6 Customer#000000106 3303.42 Customer#000000017 21.34 Customer#000000047 289.58 Customer#000000092 1197.91 Customer#000000101 7485.96 Customer#000000022 606.98 Customer#000000040 1350.3 Customer#000000064 -631.64 Customer#000000122 7880.46 Customer#000000028 1022.18 Customer#000000037 -902.75 Customer#000000091 4658.14 Customer#000000115 7523.92 Customer#000000067 8181.59 Customer#000000094 5515.11 Customer#000000103 2772.45 Customer#000000130 5088.58 Customer#000000139 7912.78 Customer#000000142 2224.81 Customer#000000025 7148.7 Customer#000000008 6834.74 Customer#000000061 1551.24 Customer#000000077 1753.87 Customer#000000097 2179.48 Customer#000000121 6443.32 Customer#000000133 2329.67 Customer#000000007 9576.95 Customer#000000019 8929.71 Customer#000000082 9483.34 Customer#000000124 1857.49 Customer#000000127 9295.71 set @a2=5; execute stmt using @a2; select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_regionkey in (1,2)) and c_custkey in (select o_custkey from orders where o_orderDATE between '1992-10-09' and '1993-06-08'); c_name c_acctbal Customer#000000059 3478.6 Customer#000000106 3308.42 Customer#000000017 26.34 Customer#000000047 294.58 Customer#000000092 1202.91 Customer#000000101 7490.96 Customer#000000022 611.98 Customer#000000040 1355.3 Customer#000000064 -626.64 Customer#000000122 7885.46 Customer#000000028 1027.1799999999998 Customer#000000037 -897.75 Customer#000000091 4663.14 Customer#000000115 7528.92 Customer#000000067 8186.59 Customer#000000094 5520.11 Customer#000000103 2777.45 Customer#000000130 5093.58 Customer#000000139 7917.78 Customer#000000142 2229.81 Customer#000000025 7153.7 Customer#000000008 6839.74 Customer#000000061 1556.24 Customer#000000077 1758.87 Customer#000000097 2184.48 Customer#000000121 6448.32 Customer#000000133 2334.67 Customer#000000007 9581.95 Customer#000000019 8934.71 Customer#000000082 9488.34 Customer#000000124 1862.49 Customer#000000127 9300.71 execute stmt using -(@a1+@a2); select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_regionkey in (1,2)) and c_custkey in (select o_custkey from orders where o_orderDATE between '1992-10-09' and '1993-06-08'); c_name c_acctbal Customer#000000059 3458.6 Customer#000000106 3288.42 Customer#000000017 6.34 Customer#000000047 274.58 Customer#000000092 1182.91 Customer#000000101 7470.96 Customer#000000022 591.98 Customer#000000040 1335.3 Customer#000000064 -646.64 Customer#000000122 7865.46 Customer#000000028 1007.1799999999998 Customer#000000037 -917.75 Customer#000000091 4643.14 Customer#000000115 7508.92 Customer#000000067 8166.59 Customer#000000094 5500.11 Customer#000000103 2757.45 Customer#000000130 5073.58 Customer#000000139 7897.78 Customer#000000142 2209.81 Customer#000000025 7133.7 Customer#000000008 6819.74 Customer#000000061 1536.24 Customer#000000077 1738.87 Customer#000000097 2164.48 Customer#000000121 6428.32 Customer#000000133 2314.67 Customer#000000007 9561.95 Customer#000000019 8914.71 Customer#000000082 9468.34 Customer#000000124 1842.49 Customer#000000127 9280.71 deallocate prepare stmt; set optimizer_switch='materialization=default'; # Materialization PS # ================== prepare stmt from " update customer set c_acctbal = c_acctbal+? where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1993-03-08'); "; select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name c_acctbal Customer#000000001 711.56 Customer#000000002 121.65 Customer#000000005 794.47 Customer#000000007 9561.95 Customer#000000008 6819.74 Customer#000000010 2753.54 Customer#000000011 -272.6 Customer#000000013 3857.34 Customer#000000014 5266.3 Customer#000000016 4681.03 Customer#000000017 6.34 Customer#000000019 8914.71 Customer#000000022 591.98 Customer#000000023 3332.02 Customer#000000025 7133.7 Customer#000000028 1007.1799999999998 Customer#000000029 7618.27 Customer#000000031 5236.89 Customer#000000032 3471.53 Customer#000000034 8589.7 Customer#000000035 1228.24 Customer#000000037 -917.75 Customer#000000038 6345.11 Customer#000000040 1335.3 Customer#000000041 270.95 Customer#000000043 9904.28 Customer#000000044 7315.94 Customer#000000046 5744.59 Customer#000000047 274.58 Customer#000000049 4573.94 Customer#000000052 5630.28 Customer#000000053 4113.64 Customer#000000055 4572.11 Customer#000000056 6530.86 Customer#000000058 6478.46 Customer#000000059 3458.6 Customer#000000061 1536.24 Customer#000000062 595.61 Customer#000000064 -646.64 Customer#000000065 8795.16 Customer#000000067 8166.59 Customer#000000070 4867.52 Customer#000000071 -611.19 Customer#000000073 4288.5 Customer#000000074 2764.43 Customer#000000076 5745.33 Customer#000000079 5121.28 Customer#000000080 7383.53 Customer#000000082 9468.34 Customer#000000083 6463.51 Customer#000000085 3386.64 Customer#000000086 3306.32 Customer#000000088 8031.44 Customer#000000089 1530.76 Customer#000000091 4643.14 Customer#000000092 1182.91 Customer#000000094 5500.11 Customer#000000095 5327.38 Customer#000000097 2164.48 Customer#000000098 -551.37 Customer#000000100 9889.89 Customer#000000101 7470.96 Customer#000000103 2757.45 Customer#000000104 -588.38 Customer#000000106 3288.42 Customer#000000107 2514.15 Customer#000000109 -716.1 Customer#000000110 7462.99 Customer#000000112 2953.35 Customer#000000115 7508.92 Customer#000000116 8403.99 Customer#000000118 3582.37 Customer#000000121 6428.32 Customer#000000122 7865.46 Customer#000000127 9280.71 Customer#000000128 -986.96 Customer#000000130 5073.58 Customer#000000131 8595.53 Customer#000000133 2314.67 Customer#000000134 4608.9 Customer#000000136 -842.39 Customer#000000137 7838.3 Customer#000000139 7897.78 Customer#000000140 9963.15 Customer#000000142 2209.81 Customer#000000143 2186.5 Customer#000000145 9748.93 Customer#000000148 2135.6 Customer#000000149 8959.65 set @a1=7; execute stmt using @a1; select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name c_acctbal Customer#000000001 718.56 Customer#000000002 128.65 Customer#000000005 801.47 Customer#000000007 9568.95 Customer#000000008 6826.74 Customer#000000010 2760.54 Customer#000000011 -265.6 Customer#000000013 3864.34 Customer#000000014 5273.3 Customer#000000016 4688.03 Customer#000000017 13.34 Customer#000000019 8921.71 Customer#000000022 598.98 Customer#000000023 3339.02 Customer#000000025 7140.7 Customer#000000028 1014.1799999999998 Customer#000000029 7625.27 Customer#000000031 5243.89 Customer#000000032 3478.53 Customer#000000034 8596.7 Customer#000000035 1235.24 Customer#000000037 -910.75 Customer#000000038 6352.11 Customer#000000040 1342.3 Customer#000000041 277.95 Customer#000000043 9911.28 Customer#000000044 7322.94 Customer#000000046 5751.59 Customer#000000047 281.58 Customer#000000049 4580.94 Customer#000000052 5637.28 Customer#000000053 4120.64 Customer#000000055 4579.11 Customer#000000056 6537.86 Customer#000000058 6485.46 Customer#000000059 3465.6 Customer#000000061 1543.24 Customer#000000062 602.61 Customer#000000064 -639.64 Customer#000000065 8802.16 Customer#000000067 8173.59 Customer#000000070 4874.52 Customer#000000071 -604.19 Customer#000000073 4295.5 Customer#000000074 2771.43 Customer#000000076 5752.33 Customer#000000079 5128.28 Customer#000000080 7390.53 Customer#000000082 9475.34 Customer#000000083 6470.51 Customer#000000085 3393.64 Customer#000000086 3313.32 Customer#000000088 8038.44 Customer#000000089 1537.76 Customer#000000091 4650.14 Customer#000000092 1189.91 Customer#000000094 5507.11 Customer#000000095 5334.38 Customer#000000097 2171.48 Customer#000000098 -544.37 Customer#000000100 9896.89 Customer#000000101 7477.96 Customer#000000103 2764.45 Customer#000000104 -581.38 Customer#000000106 3295.42 Customer#000000107 2521.15 Customer#000000109 -709.1 Customer#000000110 7469.99 Customer#000000112 2960.35 Customer#000000115 7515.92 Customer#000000116 8410.99 Customer#000000118 3589.37 Customer#000000121 6435.32 Customer#000000122 7872.46 Customer#000000127 9287.71 Customer#000000128 -979.96 Customer#000000130 5080.58 Customer#000000131 8602.53 Customer#000000133 2321.67 Customer#000000134 4615.9 Customer#000000136 -835.39 Customer#000000137 7845.3 Customer#000000139 7904.78 Customer#000000140 9970.15 Customer#000000142 2216.81 Customer#000000143 2193.5 Customer#000000145 9755.93 Customer#000000148 2142.6 Customer#000000149 8966.65 set @a2=3; execute stmt using @a2; select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name c_acctbal Customer#000000001 721.56 Customer#000000002 131.65 Customer#000000005 804.47 Customer#000000007 9571.95 Customer#000000008 6829.74 Customer#000000010 2763.54 Customer#000000011 -262.6 Customer#000000013 3867.34 Customer#000000014 5276.3 Customer#000000016 4691.03 Customer#000000017 16.34 Customer#000000019 8924.71 Customer#000000022 601.98 Customer#000000023 3342.02 Customer#000000025 7143.7 Customer#000000028 1017.1799999999998 Customer#000000029 7628.27 Customer#000000031 5246.89 Customer#000000032 3481.53 Customer#000000034 8599.7 Customer#000000035 1238.24 Customer#000000037 -907.75 Customer#000000038 6355.11 Customer#000000040 1345.3 Customer#000000041 280.95 Customer#000000043 9914.28 Customer#000000044 7325.94 Customer#000000046 5754.59 Customer#000000047 284.58 Customer#000000049 4583.94 Customer#000000052 5640.28 Customer#000000053 4123.64 Customer#000000055 4582.11 Customer#000000056 6540.86 Customer#000000058 6488.46 Customer#000000059 3468.6 Customer#000000061 1546.24 Customer#000000062 605.61 Customer#000000064 -636.64 Customer#000000065 8805.16 Customer#000000067 8176.59 Customer#000000070 4877.52 Customer#000000071 -601.19 Customer#000000073 4298.5 Customer#000000074 2774.43 Customer#000000076 5755.33 Customer#000000079 5131.28 Customer#000000080 7393.53 Customer#000000082 9478.34 Customer#000000083 6473.51 Customer#000000085 3396.64 Customer#000000086 3316.32 Customer#000000088 8041.44 Customer#000000089 1540.76 Customer#000000091 4653.14 Customer#000000092 1192.91 Customer#000000094 5510.11 Customer#000000095 5337.38 Customer#000000097 2174.48 Customer#000000098 -541.37 Customer#000000100 9899.89 Customer#000000101 7480.96 Customer#000000103 2767.45 Customer#000000104 -578.38 Customer#000000106 3298.42 Customer#000000107 2524.15 Customer#000000109 -706.1 Customer#000000110 7472.99 Customer#000000112 2963.35 Customer#000000115 7518.92 Customer#000000116 8413.99 Customer#000000118 3592.37 Customer#000000121 6438.32 Customer#000000122 7875.46 Customer#000000127 9290.71 Customer#000000128 -976.96 Customer#000000130 5083.58 Customer#000000131 8605.53 Customer#000000133 2324.67 Customer#000000134 4618.9 Customer#000000136 -832.39 Customer#000000137 7848.3 Customer#000000139 7907.78 Customer#000000140 9973.15 Customer#000000142 2219.81 Customer#000000143 2196.5 Customer#000000145 9758.93 Customer#000000148 2145.6 Customer#000000149 8969.65 execute stmt using -(@a1+@a2); select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name c_acctbal Customer#000000001 711.56 Customer#000000002 121.65 Customer#000000005 794.47 Customer#000000007 9561.95 Customer#000000008 6819.74 Customer#000000010 2753.54 Customer#000000011 -272.6 Customer#000000013 3857.34 Customer#000000014 5266.3 Customer#000000016 4681.03 Customer#000000017 6.34 Customer#000000019 8914.71 Customer#000000022 591.98 Customer#000000023 3332.02 Customer#000000025 7133.7 Customer#000000028 1007.1799999999998 Customer#000000029 7618.27 Customer#000000031 5236.89 Customer#000000032 3471.53 Customer#000000034 8589.7 Customer#000000035 1228.24 Customer#000000037 -917.75 Customer#000000038 6345.11 Customer#000000040 1335.3 Customer#000000041 270.95 Customer#000000043 9904.28 Customer#000000044 7315.94 Customer#000000046 5744.59 Customer#000000047 274.58 Customer#000000049 4573.94 Customer#000000052 5630.28 Customer#000000053 4113.64 Customer#000000055 4572.11 Customer#000000056 6530.86 Customer#000000058 6478.46 Customer#000000059 3458.6 Customer#000000061 1536.24 Customer#000000062 595.61 Customer#000000064 -646.64 Customer#000000065 8795.16 Customer#000000067 8166.59 Customer#000000070 4867.52 Customer#000000071 -611.19 Customer#000000073 4288.5 Customer#000000074 2764.43 Customer#000000076 5745.33 Customer#000000079 5121.28 Customer#000000080 7383.53 Customer#000000082 9468.34 Customer#000000083 6463.51 Customer#000000085 3386.64 Customer#000000086 3306.32 Customer#000000088 8031.44 Customer#000000089 1530.76 Customer#000000091 4643.14 Customer#000000092 1182.91 Customer#000000094 5500.11 Customer#000000095 5327.38 Customer#000000097 2164.48 Customer#000000098 -551.37 Customer#000000100 9889.89 Customer#000000101 7470.96 Customer#000000103 2757.45 Customer#000000104 -588.38 Customer#000000106 3288.42 Customer#000000107 2514.15 Customer#000000109 -716.1 Customer#000000110 7462.99 Customer#000000112 2953.35 Customer#000000115 7508.92 Customer#000000116 8403.99 Customer#000000118 3582.37 Customer#000000121 6428.32 Customer#000000122 7865.46 Customer#000000127 9280.71 Customer#000000128 -986.96 Customer#000000130 5073.58 Customer#000000131 8595.53 Customer#000000133 2314.67 Customer#000000134 4608.9 Customer#000000136 -842.39 Customer#000000137 7838.3 Customer#000000139 7897.78 Customer#000000140 9963.15 Customer#000000142 2209.81 Customer#000000143 2186.5 Customer#000000145 9748.93 Customer#000000148 2135.6 Customer#000000149 8959.65 deallocate prepare stmt; # Materialization SJM PS # ====================== prepare stmt from " update customer set c_acctbal = c_acctbal+? where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08' group by o_custkey having count(o_custkey) > 1); "; select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08' group by o_custkey having count(o_custkey) > 1); c_name c_acctbal Customer#000000013 3857.34 Customer#000000032 3471.53 Customer#000000037 -917.75 Customer#000000118 3582.37 Customer#000000056 6530.86 set @a1=-2; execute stmt using @a1; select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08' group by o_custkey having count(o_custkey) > 1); c_name c_acctbal Customer#000000013 3855.34 Customer#000000032 3469.53 Customer#000000037 -919.75 Customer#000000118 3580.37 Customer#000000056 6528.86 set @a2=-1; execute stmt using @a2; select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08' group by o_custkey having count(o_custkey) > 1); c_name c_acctbal Customer#000000013 3854.34 Customer#000000032 3468.53 Customer#000000037 -920.75 Customer#000000118 3579.37 Customer#000000056 6527.86 execute stmt using -(@a1+@a2); select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08' group by o_custkey having count(o_custkey) > 1); c_name c_acctbal Customer#000000013 3857.34 Customer#000000032 3471.53 Customer#000000037 -917.75 Customer#000000118 3582.37 Customer#000000056 6530.86 deallocate prepare stmt; # Pullout SP # ========== create procedure p(d int) update orders set o_totalprice = o_totalprice+d where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')); select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')); o_orderkey o_totalprice 644 201268.06 2880 145761.99 3142 16030.15 5382 138423.03 5095 184583.99 737 12984.85 1729 12137.76 5121 150334.57 call p(-10); select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')); o_orderkey o_totalprice 644 201258.06 2880 145751.99 3142 16020.15 5382 138413.03 5095 184573.99 737 12974.85 1729 12127.76 5121 150324.57 call p(-20); select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')); o_orderkey o_totalprice 644 201238.06 2880 145731.99 3142 16000.15 5382 138393.03 5095 184553.99 737 12954.85 1729 12107.76 5121 150304.57 call p(10+20); select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')); o_orderkey o_totalprice 644 201268.06 2880 145761.99 3142 16030.15 5382 138423.03 5095 184583.99 737 12984.85 1729 12137.76 5121 150334.57 drop procedure p; # FirstMatch SP # ============= set optimizer_switch='materialization=off'; create procedure p(d int) update customer set c_acctbal = c_acctbal+d where c_nationkey in (select n_nationkey from nation where n_regionkey in (1,2)) and c_custkey in (select o_custkey from orders where o_orderDATE between '1992-10-09' and '1993-06-08'); select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_regionkey in (1,2)) and c_custkey in (select o_custkey from orders where o_orderDATE between '1992-10-09' and '1993-06-08'); c_name c_acctbal Customer#000000059 3458.6 Customer#000000106 3288.42 Customer#000000017 6.34 Customer#000000047 274.58 Customer#000000092 1182.91 Customer#000000101 7470.96 Customer#000000022 591.98 Customer#000000040 1335.3 Customer#000000064 -646.64 Customer#000000122 7865.46 Customer#000000028 1007.1799999999998 Customer#000000037 -917.75 Customer#000000091 4643.14 Customer#000000115 7508.92 Customer#000000067 8166.59 Customer#000000094 5500.11 Customer#000000103 2757.45 Customer#000000130 5073.58 Customer#000000139 7897.78 Customer#000000142 2209.81 Customer#000000025 7133.7 Customer#000000008 6819.74 Customer#000000061 1536.24 Customer#000000077 1738.87 Customer#000000097 2164.48 Customer#000000121 6428.32 Customer#000000133 2314.67 Customer#000000007 9561.95 Customer#000000019 8914.71 Customer#000000082 9468.34 Customer#000000124 1842.49 Customer#000000127 9280.71 call p(5); select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_regionkey in (1,2)) and c_custkey in (select o_custkey from orders where o_orderDATE between '1992-10-09' and '1993-06-08'); c_name c_acctbal Customer#000000059 3463.6 Customer#000000106 3293.42 Customer#000000017 11.34 Customer#000000047 279.58 Customer#000000092 1187.91 Customer#000000101 7475.96 Customer#000000022 596.98 Customer#000000040 1340.3 Customer#000000064 -641.64 Customer#000000122 7870.46 Customer#000000028 1012.1799999999998 Customer#000000037 -912.75 Customer#000000091 4648.14 Customer#000000115 7513.92 Customer#000000067 8171.59 Customer#000000094 5505.11 Customer#000000103 2762.45 Customer#000000130 5078.58 Customer#000000139 7902.78 Customer#000000142 2214.81 Customer#000000025 7138.7 Customer#000000008 6824.74 Customer#000000061 1541.24 Customer#000000077 1743.87 Customer#000000097 2169.48 Customer#000000121 6433.32 Customer#000000133 2319.67 Customer#000000007 9566.95 Customer#000000019 8919.71 Customer#000000082 9473.34 Customer#000000124 1847.49 Customer#000000127 9285.71 call p(15); select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_regionkey in (1,2)) and c_custkey in (select o_custkey from orders where o_orderDATE between '1992-10-09' and '1993-06-08'); c_name c_acctbal Customer#000000059 3478.6 Customer#000000106 3308.42 Customer#000000017 26.34 Customer#000000047 294.58 Customer#000000092 1202.91 Customer#000000101 7490.96 Customer#000000022 611.98 Customer#000000040 1355.3 Customer#000000064 -626.64 Customer#000000122 7885.46 Customer#000000028 1027.1799999999998 Customer#000000037 -897.75 Customer#000000091 4663.14 Customer#000000115 7528.92 Customer#000000067 8186.59 Customer#000000094 5520.11 Customer#000000103 2777.45 Customer#000000130 5093.58 Customer#000000139 7917.78 Customer#000000142 2229.81 Customer#000000025 7153.7 Customer#000000008 6839.74 Customer#000000061 1556.24 Customer#000000077 1758.87 Customer#000000097 2184.48 Customer#000000121 6448.32 Customer#000000133 2334.67 Customer#000000007 9581.95 Customer#000000019 8934.71 Customer#000000082 9488.34 Customer#000000124 1862.49 Customer#000000127 9300.71 call p(-(5+15)); select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_regionkey in (1,2)) and c_custkey in (select o_custkey from orders where o_orderDATE between '1992-10-09' and '1993-06-08'); c_name c_acctbal Customer#000000059 3458.6 Customer#000000106 3288.42 Customer#000000017 6.34 Customer#000000047 274.58 Customer#000000092 1182.91 Customer#000000101 7470.96 Customer#000000022 591.98 Customer#000000040 1335.3 Customer#000000064 -646.64 Customer#000000122 7865.46 Customer#000000028 1007.1799999999998 Customer#000000037 -917.75 Customer#000000091 4643.14 Customer#000000115 7508.92 Customer#000000067 8166.59 Customer#000000094 5500.11 Customer#000000103 2757.45 Customer#000000130 5073.58 Customer#000000139 7897.78 Customer#000000142 2209.81 Customer#000000025 7133.7 Customer#000000008 6819.74 Customer#000000061 1536.24 Customer#000000077 1738.87 Customer#000000097 2164.48 Customer#000000121 6428.32 Customer#000000133 2314.67 Customer#000000007 9561.95 Customer#000000019 8914.71 Customer#000000082 9468.34 Customer#000000124 1842.49 Customer#000000127 9280.71 drop procedure p; set optimizer_switch='materialization=default'; # Materialization SP # ================== create procedure p(d int) update customer set c_acctbal = c_acctbal+d where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1993-03-08'); select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name c_acctbal Customer#000000001 711.56 Customer#000000002 121.65 Customer#000000005 794.47 Customer#000000007 9561.95 Customer#000000008 6819.74 Customer#000000010 2753.54 Customer#000000011 -272.6 Customer#000000013 3857.34 Customer#000000014 5266.3 Customer#000000016 4681.03 Customer#000000017 6.34 Customer#000000019 8914.71 Customer#000000022 591.98 Customer#000000023 3332.02 Customer#000000025 7133.7 Customer#000000028 1007.1799999999998 Customer#000000029 7618.27 Customer#000000031 5236.89 Customer#000000032 3471.53 Customer#000000034 8589.7 Customer#000000035 1228.24 Customer#000000037 -917.75 Customer#000000038 6345.11 Customer#000000040 1335.3 Customer#000000041 270.95 Customer#000000043 9904.28 Customer#000000044 7315.94 Customer#000000046 5744.59 Customer#000000047 274.58 Customer#000000049 4573.94 Customer#000000052 5630.28 Customer#000000053 4113.64 Customer#000000055 4572.11 Customer#000000056 6530.86 Customer#000000058 6478.46 Customer#000000059 3458.6 Customer#000000061 1536.24 Customer#000000062 595.61 Customer#000000064 -646.64 Customer#000000065 8795.16 Customer#000000067 8166.59 Customer#000000070 4867.52 Customer#000000071 -611.19 Customer#000000073 4288.5 Customer#000000074 2764.43 Customer#000000076 5745.33 Customer#000000079 5121.28 Customer#000000080 7383.53 Customer#000000082 9468.34 Customer#000000083 6463.51 Customer#000000085 3386.64 Customer#000000086 3306.32 Customer#000000088 8031.44 Customer#000000089 1530.76 Customer#000000091 4643.14 Customer#000000092 1182.91 Customer#000000094 5500.11 Customer#000000095 5327.38 Customer#000000097 2164.48 Customer#000000098 -551.37 Customer#000000100 9889.89 Customer#000000101 7470.96 Customer#000000103 2757.45 Customer#000000104 -588.38 Customer#000000106 3288.42 Customer#000000107 2514.15 Customer#000000109 -716.1 Customer#000000110 7462.99 Customer#000000112 2953.35 Customer#000000115 7508.92 Customer#000000116 8403.99 Customer#000000118 3582.37 Customer#000000121 6428.32 Customer#000000122 7865.46 Customer#000000127 9280.71 Customer#000000128 -986.96 Customer#000000130 5073.58 Customer#000000131 8595.53 Customer#000000133 2314.67 Customer#000000134 4608.9 Customer#000000136 -842.39 Customer#000000137 7838.3 Customer#000000139 7897.78 Customer#000000140 9963.15 Customer#000000142 2209.81 Customer#000000143 2186.5 Customer#000000145 9748.93 Customer#000000148 2135.6 Customer#000000149 8959.65 call p(3); select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name c_acctbal Customer#000000001 714.56 Customer#000000002 124.65 Customer#000000005 797.47 Customer#000000007 9564.95 Customer#000000008 6822.74 Customer#000000010 2756.54 Customer#000000011 -269.6 Customer#000000013 3860.34 Customer#000000014 5269.3 Customer#000000016 4684.03 Customer#000000017 9.34 Customer#000000019 8917.71 Customer#000000022 594.98 Customer#000000023 3335.02 Customer#000000025 7136.7 Customer#000000028 1010.1799999999998 Customer#000000029 7621.27 Customer#000000031 5239.89 Customer#000000032 3474.53 Customer#000000034 8592.7 Customer#000000035 1231.24 Customer#000000037 -914.75 Customer#000000038 6348.11 Customer#000000040 1338.3 Customer#000000041 273.95 Customer#000000043 9907.28 Customer#000000044 7318.94 Customer#000000046 5747.59 Customer#000000047 277.58 Customer#000000049 4576.94 Customer#000000052 5633.28 Customer#000000053 4116.64 Customer#000000055 4575.11 Customer#000000056 6533.86 Customer#000000058 6481.46 Customer#000000059 3461.6 Customer#000000061 1539.24 Customer#000000062 598.61 Customer#000000064 -643.64 Customer#000000065 8798.16 Customer#000000067 8169.59 Customer#000000070 4870.52 Customer#000000071 -608.19 Customer#000000073 4291.5 Customer#000000074 2767.43 Customer#000000076 5748.33 Customer#000000079 5124.28 Customer#000000080 7386.53 Customer#000000082 9471.34 Customer#000000083 6466.51 Customer#000000085 3389.64 Customer#000000086 3309.32 Customer#000000088 8034.44 Customer#000000089 1533.76 Customer#000000091 4646.14 Customer#000000092 1185.91 Customer#000000094 5503.11 Customer#000000095 5330.38 Customer#000000097 2167.48 Customer#000000098 -548.37 Customer#000000100 9892.89 Customer#000000101 7473.96 Customer#000000103 2760.45 Customer#000000104 -585.38 Customer#000000106 3291.42 Customer#000000107 2517.15 Customer#000000109 -713.1 Customer#000000110 7465.99 Customer#000000112 2956.35 Customer#000000115 7511.92 Customer#000000116 8406.99 Customer#000000118 3585.37 Customer#000000121 6431.32 Customer#000000122 7868.46 Customer#000000127 9283.71 Customer#000000128 -983.96 Customer#000000130 5076.58 Customer#000000131 8598.53 Customer#000000133 2317.67 Customer#000000134 4611.9 Customer#000000136 -839.39 Customer#000000137 7841.3 Customer#000000139 7900.78 Customer#000000140 9966.15 Customer#000000142 2212.81 Customer#000000143 2189.5 Customer#000000145 9751.93 Customer#000000148 2138.6 Customer#000000149 8962.65 call p(7); select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name c_acctbal Customer#000000001 721.56 Customer#000000002 131.65 Customer#000000005 804.47 Customer#000000007 9571.95 Customer#000000008 6829.74 Customer#000000010 2763.54 Customer#000000011 -262.6 Customer#000000013 3867.34 Customer#000000014 5276.3 Customer#000000016 4691.03 Customer#000000017 16.34 Customer#000000019 8924.71 Customer#000000022 601.98 Customer#000000023 3342.02 Customer#000000025 7143.7 Customer#000000028 1017.1799999999998 Customer#000000029 7628.27 Customer#000000031 5246.89 Customer#000000032 3481.53 Customer#000000034 8599.7 Customer#000000035 1238.24 Customer#000000037 -907.75 Customer#000000038 6355.11 Customer#000000040 1345.3 Customer#000000041 280.95 Customer#000000043 9914.28 Customer#000000044 7325.94 Customer#000000046 5754.59 Customer#000000047 284.58 Customer#000000049 4583.94 Customer#000000052 5640.28 Customer#000000053 4123.64 Customer#000000055 4582.11 Customer#000000056 6540.86 Customer#000000058 6488.46 Customer#000000059 3468.6 Customer#000000061 1546.24 Customer#000000062 605.61 Customer#000000064 -636.64 Customer#000000065 8805.16 Customer#000000067 8176.59 Customer#000000070 4877.52 Customer#000000071 -601.19 Customer#000000073 4298.5 Customer#000000074 2774.43 Customer#000000076 5755.33 Customer#000000079 5131.28 Customer#000000080 7393.53 Customer#000000082 9478.34 Customer#000000083 6473.51 Customer#000000085 3396.64 Customer#000000086 3316.32 Customer#000000088 8041.44 Customer#000000089 1540.76 Customer#000000091 4653.14 Customer#000000092 1192.91 Customer#000000094 5510.11 Customer#000000095 5337.38 Customer#000000097 2174.48 Customer#000000098 -541.37 Customer#000000100 9899.89 Customer#000000101 7480.96 Customer#000000103 2767.45 Customer#000000104 -578.38 Customer#000000106 3298.42 Customer#000000107 2524.15 Customer#000000109 -706.1 Customer#000000110 7472.99 Customer#000000112 2963.35 Customer#000000115 7518.92 Customer#000000116 8413.99 Customer#000000118 3592.37 Customer#000000121 6438.32 Customer#000000122 7875.46 Customer#000000127 9290.71 Customer#000000128 -976.96 Customer#000000130 5083.58 Customer#000000131 8605.53 Customer#000000133 2324.67 Customer#000000134 4618.9 Customer#000000136 -832.39 Customer#000000137 7848.3 Customer#000000139 7907.78 Customer#000000140 9973.15 Customer#000000142 2219.81 Customer#000000143 2196.5 Customer#000000145 9758.93 Customer#000000148 2145.6 Customer#000000149 8969.65 call p(-(3+7)); select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name c_acctbal Customer#000000001 711.56 Customer#000000002 121.65 Customer#000000005 794.47 Customer#000000007 9561.95 Customer#000000008 6819.74 Customer#000000010 2753.54 Customer#000000011 -272.6 Customer#000000013 3857.34 Customer#000000014 5266.3 Customer#000000016 4681.03 Customer#000000017 6.34 Customer#000000019 8914.71 Customer#000000022 591.98 Customer#000000023 3332.02 Customer#000000025 7133.7 Customer#000000028 1007.1799999999998 Customer#000000029 7618.27 Customer#000000031 5236.89 Customer#000000032 3471.53 Customer#000000034 8589.7 Customer#000000035 1228.24 Customer#000000037 -917.75 Customer#000000038 6345.11 Customer#000000040 1335.3 Customer#000000041 270.95 Customer#000000043 9904.28 Customer#000000044 7315.94 Customer#000000046 5744.59 Customer#000000047 274.58 Customer#000000049 4573.94 Customer#000000052 5630.28 Customer#000000053 4113.64 Customer#000000055 4572.11 Customer#000000056 6530.86 Customer#000000058 6478.46 Customer#000000059 3458.6 Customer#000000061 1536.24 Customer#000000062 595.61 Customer#000000064 -646.64 Customer#000000065 8795.16 Customer#000000067 8166.59 Customer#000000070 4867.52 Customer#000000071 -611.19 Customer#000000073 4288.5 Customer#000000074 2764.43 Customer#000000076 5745.33 Customer#000000079 5121.28 Customer#000000080 7383.53 Customer#000000082 9468.34 Customer#000000083 6463.51 Customer#000000085 3386.64 Customer#000000086 3306.32 Customer#000000088 8031.44 Customer#000000089 1530.76 Customer#000000091 4643.14 Customer#000000092 1182.91 Customer#000000094 5500.11 Customer#000000095 5327.38 Customer#000000097 2164.48 Customer#000000098 -551.37 Customer#000000100 9889.89 Customer#000000101 7470.96 Customer#000000103 2757.45 Customer#000000104 -588.38 Customer#000000106 3288.42 Customer#000000107 2514.15 Customer#000000109 -716.1 Customer#000000110 7462.99 Customer#000000112 2953.35 Customer#000000115 7508.92 Customer#000000116 8403.99 Customer#000000118 3582.37 Customer#000000121 6428.32 Customer#000000122 7865.46 Customer#000000127 9280.71 Customer#000000128 -986.96 Customer#000000130 5073.58 Customer#000000131 8595.53 Customer#000000133 2314.67 Customer#000000134 4608.9 Customer#000000136 -842.39 Customer#000000137 7838.3 Customer#000000139 7897.78 Customer#000000140 9963.15 Customer#000000142 2209.81 Customer#000000143 2186.5 Customer#000000145 9748.93 Customer#000000148 2135.6 Customer#000000149 8959.65 drop procedure p; # Materialization SJM SP # ====================== create procedure p(d int) update customer set c_acctbal = c_acctbal+d where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08' group by o_custkey having count(o_custkey) > 1); select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08' group by o_custkey having count(o_custkey) > 1); c_name c_acctbal Customer#000000013 3857.34 Customer#000000032 3471.53 Customer#000000037 -917.75 Customer#000000118 3582.37 Customer#000000056 6530.86 call p(-1); select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08' group by o_custkey having count(o_custkey) > 1); c_name c_acctbal Customer#000000013 3856.34 Customer#000000032 3470.53 Customer#000000037 -918.75 Customer#000000118 3581.37 Customer#000000056 6529.86 call p(-2); select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08' group by o_custkey having count(o_custkey) > 1); c_name c_acctbal Customer#000000013 3854.34 Customer#000000032 3468.53 Customer#000000037 -920.75 Customer#000000118 3579.37 Customer#000000056 6527.86 call p(1+2); select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08' group by o_custkey having count(o_custkey) > 1); c_name c_acctbal Customer#000000013 3857.34 Customer#000000032 3471.53 Customer#000000037 -917.75 Customer#000000118 3582.37 Customer#000000056 6530.86 drop procedure p; # Checking limitations # ==================== select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (1,2)); o_orderkey o_totalprice 1221 117397.16 324 26868.85 1856 189361.42 1344 43809.37 1925 146382.71 3139 40975.96 4903 34363.63 5607 24660.06 # Should not use semi-join conversion because has ORDER BY ... LIMIT explain update orders set o_totalprice = o_totalprice-50 where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (1,2)) order by o_totalprice limit 500; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY orders range i_o_orderdate i_o_orderdate 4 NULL 108 Using where; Using filesort 2 DEPENDENT SUBQUERY customer unique_subquery PRIMARY,i_c_nationkey PRIMARY 4 func 1 Using where update orders set o_totalprice = o_totalprice-50 where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (1,2)) order by o_totalprice limit 500; select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (1,2)); o_orderkey o_totalprice 1221 117347.16 324 26818.85 1856 189311.42 1344 43759.37 1925 146332.71 3139 40925.96 4903 34313.63 5607 24610.06 update orders set o_totalprice = o_totalprice+50 where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (1,2)) order by o_totalprice limit 500; select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (1,2)); o_orderkey o_totalprice 1221 117397.16 324 26868.85 1856 189361.42 1344 43809.37 1925 146382.71 3139 40975.96 4903 34363.63 5607 24660.06 # Should use semi-join converion explain update orders set o_totalprice = o_totalprice-50 where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (1,2)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY customer range PRIMARY,i_c_nationkey i_c_nationkey 5 NULL 15 Using index condition 1 PRIMARY orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (7%) Using where; Using rowid filter update orders set o_totalprice = o_totalprice-50 where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (1,2)); select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (1,2)); o_orderkey o_totalprice 1221 117347.16 324 26818.85 1856 189311.42 1344 43759.37 1925 146332.71 3139 40925.96 4903 34313.63 5607 24610.06 update orders set o_totalprice = o_totalprice+50 where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (1,2)); select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (1,2)); o_orderkey o_totalprice 1221 117397.16 324 26868.85 1856 189361.42 1344 43809.37 1925 146382.71 3139 40975.96 4903 34363.63 5607 24660.06 CREATE TABLE partsupp_small ( ps_partkey int(11) NOT NULL DEFAULT '0', ps_suppkey int(11) NOT NULL DEFAULT '0', ps_availqty int(11) DEFAULT NULL, ps_supplycost double DEFAULT NULL, ps_comment varchar(199) DEFAULT NULL, PRIMARY KEY (ps_partkey,ps_suppkey), KEY i_ps_partkey (ps_partkey), KEY i_ps_suppkey (ps_suppkey) ); create index i_ps_sup_part on partsupp_small(ps_suppkey, ps_partkey); insert into partsupp_small select * from partsupp where ps_partkey <50; analyze table partsupp_small persistent for all; Table Op Msg_type Msg_text dbt3_s001.partsupp_small analyze status Engine-independent statistics collected dbt3_s001.partsupp_small analyze status OK # LooseScan # ========= explain select count(*) from lineitem where l_suppkey in (select ps_suppkey from partsupp_small where ps_partkey in (1,2,3)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY partsupp_small index PRIMARY,i_ps_partkey,i_ps_suppkey,i_ps_sup_part i_ps_sup_part 8 NULL 176 Using where; Using index; LooseScan 1 PRIMARY lineitem ref i_l_suppkey i_l_suppkey 5 dbt3_s001.partsupp_small.ps_suppkey 600 Using index explain format=json select count(*) from lineitem where l_suppkey in (select ps_suppkey from partsupp_small where ps_partkey in (1,2,3)); EXPLAIN { "query_block": { "select_id": 1, "cost": 0.776623555, "nested_loop": [ { "table": { "table_name": "partsupp_small", "access_type": "index", "possible_keys": [ "PRIMARY", "i_ps_partkey", "i_ps_suppkey", "i_ps_sup_part" ], "key": "i_ps_sup_part", "key_length": "8", "used_key_parts": ["ps_suppkey", "ps_partkey"], "loops": 1, "rows": 176, "cost": 0.019403477, "filtered": 5.113636494, "attached_condition": "partsupp_small.ps_partkey in (1,2,3)", "using_index": true, "loose_scan": true } }, { "table": { "table_name": "lineitem", "access_type": "ref", "possible_keys": ["i_l_suppkey"], "key": "i_l_suppkey", "key_length": "5", "used_key_parts": ["l_suppkey"], "ref": ["dbt3_s001.partsupp_small.ps_suppkey"], "loops": 9, "rows": 600, "cost": 0.757220078, "filtered": 11.11111069, "using_index": true } } ] } } select count(*) from lineitem where l_suppkey in (select ps_suppkey from partsupp_small where ps_partkey in (1,2,3)); count(*) 5373 select 5373 as count, 136458704.22 as old_sum; count old_sum 5373 136458704.22 explain update lineitem set l_extendedprice=l_extendedprice+10 where l_suppkey in (select ps_suppkey from partsupp_small where ps_partkey in (1,2,3)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY lineitem ALL i_l_suppkey NULL NULL NULL 6005 1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED partsupp_small range PRIMARY,i_ps_partkey,i_ps_suppkey,i_ps_sup_part PRIMARY 4 NULL 9 Using where; Using index explain format=json update lineitem set l_extendedprice=l_extendedprice+10 where l_suppkey in (select ps_suppkey from partsupp_small where ps_partkey in (1,2,3)); EXPLAIN { "query_block": { "select_id": 1, "cost": 2.191459679, "nested_loop": [ { "table": { "table_name": "lineitem", "access_type": "ALL", "possible_keys": ["i_l_suppkey"], "loops": 1, "rows": 6005, "cost": 0.988432825, "filtered": 100 } }, { "table": { "table_name": "", "access_type": "eq_ref", "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "4", "used_key_parts": ["ps_suppkey"], "ref": ["func"], "rows": 1, "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, "nested_loop": [ { "table": { "table_name": "partsupp_small", "access_type": "range", "possible_keys": [ "PRIMARY", "i_ps_partkey", "i_ps_suppkey", "i_ps_sup_part" ], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["ps_partkey"], "loops": 1, "rows": 9, "cost": 0.003756598, "filtered": 100, "attached_condition": "partsupp_small.ps_partkey in (1,2,3)", "using_index": true } } ] } } } } ] } } update lineitem set l_extendedprice=l_extendedprice+10 where l_suppkey in (select ps_suppkey from partsupp_small where ps_partkey in (1,2,3)); select ROUND(sum(l_extendedprice),2), 5373 as count, (136458704.22+10*5373) as 'old_sum+10*count' from lineitem where l_suppkey in (select ps_suppkey from partsupp_small where ps_partkey in (1,2,3)); ROUND(sum(l_extendedprice),2) count old_sum+10*count 136512434.22 5373 136512434.22 update lineitem set l_extendedprice=l_extendedprice-10 where l_suppkey in (select ps_suppkey from partsupp_small where ps_partkey in (1,2,3)); select ROUND(sum(l_extendedprice),2), 5373 as count, 136458704.22 as old_sum from lineitem where l_suppkey in (select ps_suppkey from partsupp_small where ps_partkey in (1,2,3)); ROUND(sum(l_extendedprice),2) count old_sum 136458704.22 5373 136458704.22 # LooseScan PS # ============ prepare stmt from " update lineitem set l_extendedprice=l_extendedprice+? where l_suppkey in (select ps_suppkey from partsupp_small where ps_partkey in (1,2,3)); "; select 5373 as count, 136458704.22 as old_sum; count old_sum 5373 136458704.22 set @a1=20; execute stmt using @a1; select ROUND(sum(l_extendedprice),2), 5373 as count, (136458704.22+20*5373) as 'old_sum+20*count' from lineitem where l_suppkey in (select ps_suppkey from partsupp_small where ps_partkey in (1,2,3)); ROUND(sum(l_extendedprice),2) count old_sum+20*count 136566164.22 5373 136566164.22 set @a2=10; execute stmt using @a2; select ROUND(sum(l_extendedprice),2), 5373 as count, (136458704.22+30*5373) as 'old_sum+30*count' from lineitem where l_suppkey in (select ps_suppkey from partsupp_small where ps_partkey in (1,2,3)); ROUND(sum(l_extendedprice),2) count old_sum+30*count 136619894.22 5373 136619894.22 execute stmt using -(@a1+@a2); select ROUND(sum(l_extendedprice),2), 5373 as count, 136458704.22 as old_sum from lineitem where l_suppkey in (select ps_suppkey from partsupp_small where ps_partkey in (1,2,3)); ROUND(sum(l_extendedprice),2) count old_sum 136458704.22 5373 136458704.22 deallocate prepare stmt; # LooseScan SP # ============ create procedure p(d int) update lineitem set l_extendedprice=l_extendedprice+d where l_suppkey in (select ps_suppkey from partsupp_small where ps_partkey in (1,2,3)); select 5373 as count, 136458704.22 as old_sum; count old_sum 5373 136458704.22 call p(10); select ROUND(sum(l_extendedprice),2), 5373 as count, (136458704.22+10*5373) as 'old_sum+10*count' from lineitem where l_suppkey in (select ps_suppkey from partsupp_small where ps_partkey in (1,2,3)); ROUND(sum(l_extendedprice),2) count old_sum+10*count 136512434.22 5373 136512434.22 call p(20); select ROUND(sum(l_extendedprice),2), 5373 as count, (136458704.22+30*5373) as 'old_sum+30*count' from lineitem where l_suppkey in (select ps_suppkey from partsupp_small where ps_partkey in (1,2,3)); ROUND(sum(l_extendedprice),2) count old_sum+30*count 136619894.22 5373 136619894.22 call p(-(10+20)); select ROUND(sum(l_extendedprice),2), 5373 as count, 136458704.22 as old_sum from lineitem where l_suppkey in (select ps_suppkey from partsupp_small where ps_partkey in (1,2,3)); ROUND(sum(l_extendedprice),2) count old_sum 136458704.22 5373 136458704.22 drop procedure p; # DuplicateWeedout # ================ set @tmp_optimizer_switch= @@optimizer_switch; set optimizer_switch='materialization=off'; analyze table lineitem; Table Op Msg_type Msg_text dbt3_s001.lineitem analyze status Engine-independent statistics collected dbt3_s001.lineitem analyze status OK analyze table orders; Table Op Msg_type Msg_text dbt3_s001.orders analyze status Engine-independent statistics collected dbt3_s001.orders analyze status OK explain select count(*) from lineitem where l_partkey in ( select ps_partkey from partsupp_small join lineitem on ps_partkey=l_partkey where ps_suppkey in ( select s_suppkey from supplier where s_suppkey < 2 ) ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY supplier range PRIMARY PRIMARY 4 NULL 1 Using where; Using index; Start temporary 1 PRIMARY partsupp_small ref PRIMARY,i_ps_partkey,i_ps_suppkey,i_ps_sup_part i_ps_sup_part 4 dbt3_s001.supplier.s_suppkey 17 Using where; Using index 1 PRIMARY lineitem ref i_l_suppkey_partkey,i_l_partkey i_l_suppkey_partkey 5 dbt3_s001.partsupp_small.ps_partkey 30 Using index 1 PRIMARY lineitem ref i_l_suppkey_partkey,i_l_partkey i_l_partkey 5 dbt3_s001.partsupp_small.ps_partkey 30 Using index; End temporary explain format=json select count(*) from lineitem where l_partkey in ( select ps_partkey from partsupp_small join lineitem on ps_partkey=l_partkey where ps_suppkey in ( select s_suppkey from supplier where s_suppkey < 2 ) ); EXPLAIN { "query_block": { "select_id": 1, "cost": 4.129940741, "nested_loop": [ { "duplicates_removal": [ { "table": { "table_name": "supplier", "access_type": "range", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["s_suppkey"], "loops": 1, "rows": 1, "cost": 0.001478954, "filtered": 100, "attached_condition": "supplier.s_suppkey < 2", "using_index": true } }, { "table": { "table_name": "partsupp_small", "access_type": "ref", "possible_keys": [ "PRIMARY", "i_ps_partkey", "i_ps_suppkey", "i_ps_sup_part" ], "key": "i_ps_sup_part", "key_length": "4", "used_key_parts": ["ps_suppkey"], "ref": ["dbt3_s001.supplier.s_suppkey"], "loops": 1, "rows": 17, "cost": 0.003160332, "filtered": 79.54545593, "attached_condition": "partsupp_small.ps_partkey is not null", "using_index": true } }, { "table": { "table_name": "lineitem", "access_type": "ref", "possible_keys": ["i_l_suppkey_partkey", "i_l_partkey"], "key": "i_l_suppkey_partkey", "key_length": "5", "used_key_parts": ["l_partkey"], "ref": ["dbt3_s001.partsupp_small.ps_partkey"], "loops": 14, "rows": 30, "cost": 0.069152188, "filtered": 100, "using_index": true } }, { "table": { "table_name": "lineitem", "access_type": "ref", "possible_keys": ["i_l_suppkey_partkey", "i_l_partkey"], "key": "i_l_partkey", "key_length": "5", "used_key_parts": ["l_partkey"], "ref": ["dbt3_s001.partsupp_small.ps_partkey"], "loops": 420.35, "rows": 30, "cost": 1.994712365, "filtered": 0.237896994, "using_index": true } } ] } ] } } select count(*) from lineitem where l_partkey in ( select ps_partkey from partsupp_small join lineitem on ps_partkey=l_partkey where ps_suppkey in ( select s_suppkey from supplier where s_suppkey < 2 ) ); count(*) 471 select 471 as count, 10751458.66 as old_sum; count old_sum 471 10751458.66 explain update lineitem set l_extendedprice=l_extendedprice+10 where l_partkey in ( select ps_partkey from partsupp_small join lineitem on ps_partkey=l_partkey where ps_suppkey in ( select s_suppkey from supplier where s_suppkey < 2 ) ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY supplier range PRIMARY PRIMARY 4 NULL 1 Using where; Using index; Start temporary 1 PRIMARY partsupp_small ref PRIMARY,i_ps_partkey,i_ps_suppkey,i_ps_sup_part i_ps_sup_part 4 dbt3_s001.supplier.s_suppkey 17 Using where; Using index 1 PRIMARY lineitem ref i_l_suppkey_partkey,i_l_partkey i_l_suppkey_partkey 5 dbt3_s001.partsupp_small.ps_partkey 30 1 PRIMARY lineitem ref i_l_suppkey_partkey,i_l_partkey i_l_partkey 5 dbt3_s001.partsupp_small.ps_partkey 30 Using index; End temporary explain format=json update lineitem set l_extendedprice=l_extendedprice+10 where l_partkey in ( select ps_partkey from partsupp_small join lineitem on ps_partkey=l_partkey where ps_suppkey in ( select s_suppkey from supplier where s_suppkey < 2 ) ); EXPLAIN { "query_block": { "select_id": 1, "cost": 4.590092321, "nested_loop": [ { "duplicates_removal": [ { "table": { "table_name": "supplier", "access_type": "range", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["s_suppkey"], "loops": 1, "rows": 1, "cost": 0.001478954, "filtered": 100, "attached_condition": "supplier.s_suppkey < 2", "using_index": true } }, { "table": { "table_name": "partsupp_small", "access_type": "ref", "possible_keys": [ "PRIMARY", "i_ps_partkey", "i_ps_suppkey", "i_ps_sup_part" ], "key": "i_ps_sup_part", "key_length": "4", "used_key_parts": ["ps_suppkey"], "ref": ["dbt3_s001.supplier.s_suppkey"], "loops": 1, "rows": 17, "cost": 0.003160332, "filtered": 79.54545593, "attached_condition": "partsupp_small.ps_partkey is not null", "using_index": true } }, { "table": { "table_name": "lineitem", "access_type": "ref", "possible_keys": ["i_l_suppkey_partkey", "i_l_partkey"], "key": "i_l_suppkey_partkey", "key_length": "5", "used_key_parts": ["l_partkey"], "ref": ["dbt3_s001.partsupp_small.ps_partkey"], "loops": 14, "rows": 30, "cost": 0.529303768, "filtered": 100 } }, { "table": { "table_name": "lineitem", "access_type": "ref", "possible_keys": ["i_l_suppkey_partkey", "i_l_partkey"], "key": "i_l_partkey", "key_length": "5", "used_key_parts": ["l_partkey"], "ref": ["dbt3_s001.partsupp_small.ps_partkey"], "loops": 420.35, "rows": 30, "cost": 1.994712365, "filtered": 0.237896994, "using_index": true } } ] } ] } } update lineitem set l_extendedprice=l_extendedprice+10 where l_partkey in ( select ps_partkey from partsupp_small join lineitem on ps_partkey=l_partkey where ps_suppkey in ( select s_suppkey from supplier where s_suppkey < 2 ) ); select ROUND(sum(l_extendedprice),2), 471 as count, (10751458.66+10*471) as 'old_sum+10*count' from lineitem where l_partkey in ( select ps_partkey from partsupp_small join lineitem on ps_partkey=l_partkey where ps_suppkey in ( select s_suppkey from supplier where s_suppkey < 2 ) ); ROUND(sum(l_extendedprice),2) count old_sum+10*count 10756168.66 471 10756168.66 update lineitem set l_extendedprice=l_extendedprice-10 where l_partkey in ( select ps_partkey from partsupp_small join lineitem on ps_partkey=l_partkey where ps_suppkey in ( select s_suppkey from supplier where s_suppkey < 2 ) ); select ROUND(sum(l_extendedprice),2), 471 as count, 10751458.66 as old_sum from lineitem where l_partkey in ( select ps_partkey from partsupp_small join lineitem on ps_partkey=l_partkey where ps_suppkey in ( select s_suppkey from supplier where s_suppkey < 2 ) ); ROUND(sum(l_extendedprice),2) count old_sum 10751458.66 471 10751458.66 # DuplicateWeedout PS # =================== prepare stmt from " update lineitem set l_extendedprice=l_extendedprice+? where l_partkey in ( select ps_partkey from partsupp_small join lineitem on ps_partkey=l_partkey where ps_suppkey in ( select s_suppkey from supplier where s_suppkey < 2 ) ); "; select 471 as count, 10751458.66 as old_sum; count old_sum 471 10751458.66 set @a1=20; execute stmt using @a1; select ROUND(sum(l_extendedprice),2), 471 as count, (10751458.66+20*471) as 'old_sum+20*count' from lineitem where l_partkey in ( select ps_partkey from partsupp_small join lineitem on ps_partkey=l_partkey where ps_suppkey in ( select s_suppkey from supplier where s_suppkey < 2 ) ); ROUND(sum(l_extendedprice),2) count old_sum+20*count 10760878.66 471 10760878.66 set @a2=10; execute stmt using @a2; select ROUND(sum(l_extendedprice),2), 471 as count, (10751458.66+30*471) as 'old_sum+30*count' from lineitem where l_partkey in ( select ps_partkey from partsupp_small join lineitem on ps_partkey=l_partkey where ps_suppkey in ( select s_suppkey from supplier where s_suppkey < 2 ) ); ROUND(sum(l_extendedprice),2) count old_sum+30*count 10765588.66 471 10765588.66 execute stmt using -(@a1+@a2); select ROUND(sum(l_extendedprice),2), 471 as count, 10751458.66 as old_sum from lineitem where l_partkey in ( select ps_partkey from partsupp_small join lineitem on ps_partkey=l_partkey where ps_suppkey in ( select s_suppkey from supplier where s_suppkey < 2 ) ); ROUND(sum(l_extendedprice),2) count old_sum 10751458.66 471 10751458.66 deallocate prepare stmt; # DuplicateWeedout SP # =================== create procedure p(d int) update lineitem set l_extendedprice=l_extendedprice+d where l_partkey in ( select ps_partkey from partsupp_small join lineitem on ps_partkey=l_partkey where ps_suppkey in ( select s_suppkey from supplier where s_suppkey < 2 ) ); select 471 as count, 10751458.66 as old_sum; count old_sum 471 10751458.66 call p(10); select ROUND(sum(l_extendedprice),2), 471 as count, (10751458.66+10*471) as 'old_sum+10*count' from lineitem where l_partkey in ( select ps_partkey from partsupp_small join lineitem on ps_partkey=l_partkey where ps_suppkey in ( select s_suppkey from supplier where s_suppkey < 2 ) ); ROUND(sum(l_extendedprice),2) count old_sum+10*count 10756168.66 471 10756168.66 call p(20); select ROUND(sum(l_extendedprice),2), 471 as count, (10751458.66+30*471) as 'old_sum+30*count' from lineitem where l_partkey in ( select ps_partkey from partsupp_small join lineitem on ps_partkey=l_partkey where ps_suppkey in ( select s_suppkey from supplier where s_suppkey < 2 ) ); ROUND(sum(l_extendedprice),2) count old_sum+30*count 10765588.66 471 10765588.66 call p(-(10+20)); select ROUND(sum(l_extendedprice),2), 471 as count, 10751458.66 as old_sum from lineitem where l_partkey in ( select ps_partkey from partsupp_small join lineitem on ps_partkey=l_partkey where ps_suppkey in ( select s_suppkey from supplier where s_suppkey < 2 ) ); ROUND(sum(l_extendedprice),2) count old_sum 10751458.66 471 10751458.66 drop procedure p; set @@optimizer_switch=@tmp_optimizer_switch; drop table partsupp_small; DROP DATABASE dbt3_s001;