Impressum update_single_to_multi.result   Sprache: Lisp

DROP DATABASE IF EXISTS dbt3_s001;
CREATE DATABASE dbt3_s001 CHARACTER SET latin1;
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": "COST_REPLACED",
    "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": "COST_REPLACED",
          "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": "COST_REPLACED",
          "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": "COST_REPLACED",
          "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": "COST_REPLACED",
    "nested_loop": [
      {
        "table": {
          "table_name": "nation",
          "access_type": "ALL",
          "possible_keys": ["PRIMARY", "i_n_regionkey"],
          "loops": 1,
          "rows": 25,
          "cost": "COST_REPLACED",
          "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": "COST_REPLACED",
          "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": "COST_REPLACED",
          "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": "COST_REPLACED",
    "nested_loop": [
      {
        "table": {
          "table_name": "nation",
          "access_type": "ALL",
          "possible_keys": ["PRIMARY", "i_n_regionkey"],
          "loops": 1,
          "rows": 25,
          "cost": "COST_REPLACED",
          "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": "COST_REPLACED",
          "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": "COST_REPLACED",
          "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 <subquery3> 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 <subquery3> 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 <subquery2> 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": "<subquery2>",
          "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 <subquery2> 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": "COST_REPLACED",
    "nested_loop": [
      {
        "table": {
          "table_name": "customer",
          "access_type": "ALL",
          "possible_keys": ["PRIMARY"],
          "loops": 1,
          "rows": 150,
          "cost": "COST_REPLACED",
          "filtered": 100
        }
      },
      {
        "table": {
          "table_name": "<subquery2>",
          "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": "COST_REPLACED",
                    "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 <subquery2> 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 <subquery2> 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 <subquery2> ALL distinct_key NULL NULL NULL 28 
1 PRIMARY customer eq_ref PRIMARY PRIMARY 4 <subquery2>.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": "COST_REPLACED",
    "nested_loop": [
      {
        "table": {
          "table_name": "<subquery2>",
          "access_type": "ALL",
          "possible_keys": ["distinct_key"],
          "loops": 1,
          "rows": 28,
          "cost": "COST_REPLACED",
          "filtered": 100,
          "materialized": {
            "unique": 1,
            "materialization": {
              "query_block": {
                "select_id": 2,
                "cost": "COST_REPLACED",
                "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": "COST_REPLACED",
                        "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": ["<subquery2>.o_custkey"],
          "loops": 28,
          "rows": 1,
          "cost": "COST_REPLACED",
          "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 <subquery2> ALL distinct_key NULL NULL NULL 28 
1 PRIMARY customer eq_ref PRIMARY PRIMARY 4 <subquery2>.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": "COST_REPLACED",
    "nested_loop": [
      {
        "table": {
          "table_name": "<subquery2>",
          "access_type": "ALL",
          "possible_keys": ["distinct_key"],
          "loops": 1,
          "rows": 28,
          "cost": "COST_REPLACED",
          "filtered": 100,
          "materialized": {
            "unique": 1,
            "materialization": {
              "query_block": {
                "select_id": 2,
                "cost": "COST_REPLACED",
                "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": "COST_REPLACED",
                        "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": ["<subquery2>.o_custkey"],
          "loops": 28,
          "rows": 1,
          "cost": "COST_REPLACED",
          "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 <subquery2> 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 <subquery2> 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": "COST_REPLACED",
    "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": "COST_REPLACED",
          "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": "COST_REPLACED",
          "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 <subquery2> 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": "COST_REPLACED",
    "nested_loop": [
      {
        "table": {
          "table_name": "lineitem",
          "access_type": "ALL",
          "possible_keys": ["i_l_suppkey"],
          "loops": 1,
          "rows": 6005,
          "cost": "COST_REPLACED",
          "filtered": 100
        }
      },
      {
        "table": {
          "table_name": "<subquery2>",
          "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": "COST_REPLACED",
                    "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": "COST_REPLACED",
    "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": "COST_REPLACED",
              "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": "COST_REPLACED",
              "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": "COST_REPLACED",
              "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": "COST_REPLACED",
              "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": "COST_REPLACED",
    "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": "COST_REPLACED",
              "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": "COST_REPLACED",
              "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": "COST_REPLACED",
              "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": "COST_REPLACED",
              "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;