mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 19:06:14 +01:00
5e07d1abd4
Reintroduces delete_while_scanning optimization for multi_delete. Reverse some test changes from the initial feature devlopment now that we delete-on-the-fly once again.
4256 lines
134 KiB
Text
4256 lines
134 KiB
Text
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": "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'"
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
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
|
|
1729 12137.76
|
|
2880 145761.99
|
|
3142 16030.15
|
|
5095 184583.99
|
|
5121 150334.57
|
|
5382 138423.03
|
|
644 201268.06
|
|
737 12984.85
|
|
create table t as
|
|
select * 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
|
|
delete 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
|
|
delete 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": "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'"
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
delete 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'));
|
|
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
|
|
insert into orders select * from t;
|
|
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
|
|
1729 12137.76
|
|
2880 145761.99
|
|
3142 16030.15
|
|
5095 184583.99
|
|
5121 150334.57
|
|
5382 138423.03
|
|
644 201268.06
|
|
737 12984.85
|
|
drop table t;
|
|
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
|
|
create table t as
|
|
select * 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'));
|
|
explain
|
|
delete 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
|
|
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
|
|
delete 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'));
|
|
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
|
|
insert into partsupp select * from t;
|
|
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
|
|
drop table t;
|
|
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
|
|
create table t as
|
|
select * 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'));
|
|
explain
|
|
delete 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
|
|
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
|
|
delete 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'));
|
|
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
|
|
insert into partsupp select * from t;
|
|
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
|
|
drop table t;
|
|
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
|
|
2500 2 0.02
|
|
2500 4 0.02
|
|
4996 1 0.01
|
|
933 1 0.04
|
|
create table t as
|
|
select * 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'));
|
|
explain
|
|
delete 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
|
|
delete 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'));
|
|
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
|
|
insert into lineitem select * from t;
|
|
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
|
|
2500 2 0.02
|
|
2500 4 0.02
|
|
4996 1 0.01
|
|
933 1 0.04
|
|
drop table t;
|
|
# 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#000000007 9561.95
|
|
Customer#000000008 6819.74
|
|
Customer#000000017 6.34
|
|
Customer#000000019 8914.71
|
|
Customer#000000022 591.98
|
|
Customer#000000025 7133.7
|
|
Customer#000000028 1007.18
|
|
Customer#000000037 -917.75
|
|
Customer#000000040 1335.3
|
|
Customer#000000047 274.58
|
|
Customer#000000059 3458.6
|
|
Customer#000000061 1536.24
|
|
Customer#000000064 -646.64
|
|
Customer#000000067 8166.59
|
|
Customer#000000077 1738.87
|
|
Customer#000000082 9468.34
|
|
Customer#000000091 4643.14
|
|
Customer#000000092 1182.91
|
|
Customer#000000094 5500.11
|
|
Customer#000000097 2164.48
|
|
Customer#000000101 7470.96
|
|
Customer#000000103 2757.45
|
|
Customer#000000106 3288.42
|
|
Customer#000000115 7508.92
|
|
Customer#000000121 6428.32
|
|
Customer#000000122 7865.46
|
|
Customer#000000124 1842.49
|
|
Customer#000000127 9280.71
|
|
Customer#000000130 5073.58
|
|
Customer#000000133 2314.67
|
|
Customer#000000139 7897.78
|
|
Customer#000000142 2209.81
|
|
create table t as
|
|
select * 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
|
|
delete 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
|
|
delete 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"
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
delete 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');
|
|
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
|
|
insert into customer select * from t;
|
|
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#000000007 9561.95
|
|
Customer#000000008 6819.74
|
|
Customer#000000017 6.34
|
|
Customer#000000019 8914.71
|
|
Customer#000000022 591.98
|
|
Customer#000000025 7133.7
|
|
Customer#000000028 1007.18
|
|
Customer#000000037 -917.75
|
|
Customer#000000040 1335.3
|
|
Customer#000000047 274.58
|
|
Customer#000000059 3458.6
|
|
Customer#000000061 1536.24
|
|
Customer#000000064 -646.64
|
|
Customer#000000067 8166.59
|
|
Customer#000000077 1738.87
|
|
Customer#000000082 9468.34
|
|
Customer#000000091 4643.14
|
|
Customer#000000092 1182.91
|
|
Customer#000000094 5500.11
|
|
Customer#000000097 2164.48
|
|
Customer#000000101 7470.96
|
|
Customer#000000103 2757.45
|
|
Customer#000000106 3288.42
|
|
Customer#000000115 7508.92
|
|
Customer#000000121 6428.32
|
|
Customer#000000122 7865.46
|
|
Customer#000000124 1842.49
|
|
Customer#000000127 9280.71
|
|
Customer#000000130 5073.58
|
|
Customer#000000133 2314.67
|
|
Customer#000000139 7897.78
|
|
Customer#000000142 2209.81
|
|
drop table t;
|
|
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
|
|
create table t as
|
|
select * 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");
|
|
explain
|
|
delete 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
|
|
delete 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");
|
|
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
|
|
insert into customer select * from t;
|
|
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
|
|
drop table t;
|
|
# 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#000000008 6819.74
|
|
Customer#000000014 5266.3
|
|
Customer#000000025 7133.7
|
|
Customer#000000035 1228.24
|
|
Customer#000000038 6345.11
|
|
Customer#000000059 3458.6
|
|
Customer#000000061 1536.24
|
|
Customer#000000067 8166.59
|
|
Customer#000000077 1738.87
|
|
Customer#000000094 5500.11
|
|
Customer#000000097 2164.48
|
|
Customer#000000098 -551.37
|
|
Customer#000000103 2757.45
|
|
Customer#000000106 3288.42
|
|
Customer#000000113 2912
|
|
Customer#000000121 6428.32
|
|
Customer#000000130 5073.58
|
|
Customer#000000133 2314.67
|
|
Customer#000000139 7897.78
|
|
Customer#000000142 2209.81
|
|
create table t as
|
|
select * 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");
|
|
explain
|
|
delete 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
|
|
delete 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");
|
|
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
|
|
insert into customer select * from t;
|
|
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#000000008 6819.74
|
|
Customer#000000014 5266.3
|
|
Customer#000000025 7133.7
|
|
Customer#000000035 1228.24
|
|
Customer#000000038 6345.11
|
|
Customer#000000059 3458.6
|
|
Customer#000000061 1536.24
|
|
Customer#000000067 8166.59
|
|
Customer#000000077 1738.87
|
|
Customer#000000094 5500.11
|
|
Customer#000000097 2164.48
|
|
Customer#000000098 -551.37
|
|
Customer#000000103 2757.45
|
|
Customer#000000106 3288.42
|
|
Customer#000000113 2912
|
|
Customer#000000121 6428.32
|
|
Customer#000000130 5073.58
|
|
Customer#000000133 2314.67
|
|
Customer#000000139 7897.78
|
|
Customer#000000142 2209.81
|
|
drop table t;
|
|
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": "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'"
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
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
|
|
create table t as
|
|
select * from customer where c_custkey in (select o_custkey from orders
|
|
where o_orderDATE between '1992-01-09' and '1993-03-08');
|
|
explain
|
|
delete 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
|
|
delete 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": "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'"
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
delete from customer 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
|
|
insert into customer select * from t;
|
|
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
|
|
drop table t;
|
|
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
|
|
create table t as
|
|
select * from customer where c_custkey in (select o_custkey from orders
|
|
where o_orderDATE between '1992-06-09' and '1993-01-08');
|
|
explain
|
|
delete 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
|
|
delete from customer 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
|
|
insert into customer select * from t;
|
|
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
|
|
drop table t;
|
|
# 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#000000056 6530.86
|
|
Customer#000000118 3582.37
|
|
create table t as
|
|
select * 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
|
|
delete 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
|
|
delete 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
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
delete 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);
|
|
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
|
|
insert into customer select * from t;
|
|
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#000000056 6530.86
|
|
Customer#000000118 3582.37
|
|
drop table t;
|
|
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
|
|
create table t as
|
|
select * 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);
|
|
explain
|
|
delete 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
|
|
delete 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);
|
|
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
|
|
insert into customer select * from t;
|
|
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
|
|
drop table t;
|
|
# Pullout PS
|
|
# ==========
|
|
prepare stmt from "
|
|
delete 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'));
|
|
";
|
|
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
|
|
1729 12137.76
|
|
2880 145761.99
|
|
3142 16030.15
|
|
5095 184583.99
|
|
5121 150334.57
|
|
5382 138423.03
|
|
644 201268.06
|
|
737 12984.85
|
|
create table t as
|
|
select * 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'));
|
|
execute stmt;
|
|
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
|
|
insert into orders select * from t;
|
|
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
|
|
1729 12137.76
|
|
2880 145761.99
|
|
3142 16030.15
|
|
5095 184583.99
|
|
5121 150334.57
|
|
5382 138423.03
|
|
644 201268.06
|
|
737 12984.85
|
|
create table r as
|
|
select * 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'));
|
|
execute stmt;
|
|
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
|
|
insert into orders select * from r;
|
|
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
|
|
1729 12137.76
|
|
2880 145761.99
|
|
3142 16030.15
|
|
5095 184583.99
|
|
5121 150334.57
|
|
5382 138423.03
|
|
644 201268.06
|
|
737 12984.85
|
|
drop table t,r;
|
|
deallocate prepare stmt;
|
|
# FirstMatch PS
|
|
# =============
|
|
set optimizer_switch='materialization=off';
|
|
prepare stmt from "
|
|
delete 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');
|
|
";
|
|
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#000000007 9561.95
|
|
Customer#000000008 6819.74
|
|
Customer#000000017 6.34
|
|
Customer#000000019 8914.71
|
|
Customer#000000022 591.98
|
|
Customer#000000025 7133.7
|
|
Customer#000000028 1007.18
|
|
Customer#000000037 -917.75
|
|
Customer#000000040 1335.3
|
|
Customer#000000047 274.58
|
|
Customer#000000059 3458.6
|
|
Customer#000000061 1536.24
|
|
Customer#000000064 -646.64
|
|
Customer#000000067 8166.59
|
|
Customer#000000077 1738.87
|
|
Customer#000000082 9468.34
|
|
Customer#000000091 4643.14
|
|
Customer#000000092 1182.91
|
|
Customer#000000094 5500.11
|
|
Customer#000000097 2164.48
|
|
Customer#000000101 7470.96
|
|
Customer#000000103 2757.45
|
|
Customer#000000106 3288.42
|
|
Customer#000000115 7508.92
|
|
Customer#000000121 6428.32
|
|
Customer#000000122 7865.46
|
|
Customer#000000124 1842.49
|
|
Customer#000000127 9280.71
|
|
Customer#000000130 5073.58
|
|
Customer#000000133 2314.67
|
|
Customer#000000139 7897.78
|
|
Customer#000000142 2209.81
|
|
create table t as
|
|
select * 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');
|
|
execute stmt;
|
|
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
|
|
insert into customer select * from t;
|
|
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#000000007 9561.95
|
|
Customer#000000008 6819.74
|
|
Customer#000000017 6.34
|
|
Customer#000000019 8914.71
|
|
Customer#000000022 591.98
|
|
Customer#000000025 7133.7
|
|
Customer#000000028 1007.18
|
|
Customer#000000037 -917.75
|
|
Customer#000000040 1335.3
|
|
Customer#000000047 274.58
|
|
Customer#000000059 3458.6
|
|
Customer#000000061 1536.24
|
|
Customer#000000064 -646.64
|
|
Customer#000000067 8166.59
|
|
Customer#000000077 1738.87
|
|
Customer#000000082 9468.34
|
|
Customer#000000091 4643.14
|
|
Customer#000000092 1182.91
|
|
Customer#000000094 5500.11
|
|
Customer#000000097 2164.48
|
|
Customer#000000101 7470.96
|
|
Customer#000000103 2757.45
|
|
Customer#000000106 3288.42
|
|
Customer#000000115 7508.92
|
|
Customer#000000121 6428.32
|
|
Customer#000000122 7865.46
|
|
Customer#000000124 1842.49
|
|
Customer#000000127 9280.71
|
|
Customer#000000130 5073.58
|
|
Customer#000000133 2314.67
|
|
Customer#000000139 7897.78
|
|
Customer#000000142 2209.81
|
|
create table r as
|
|
select * 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');
|
|
execute stmt;
|
|
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
|
|
insert into customer select * from r;
|
|
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#000000007 9561.95
|
|
Customer#000000008 6819.74
|
|
Customer#000000017 6.34
|
|
Customer#000000019 8914.71
|
|
Customer#000000022 591.98
|
|
Customer#000000025 7133.7
|
|
Customer#000000028 1007.18
|
|
Customer#000000037 -917.75
|
|
Customer#000000040 1335.3
|
|
Customer#000000047 274.58
|
|
Customer#000000059 3458.6
|
|
Customer#000000061 1536.24
|
|
Customer#000000064 -646.64
|
|
Customer#000000067 8166.59
|
|
Customer#000000077 1738.87
|
|
Customer#000000082 9468.34
|
|
Customer#000000091 4643.14
|
|
Customer#000000092 1182.91
|
|
Customer#000000094 5500.11
|
|
Customer#000000097 2164.48
|
|
Customer#000000101 7470.96
|
|
Customer#000000103 2757.45
|
|
Customer#000000106 3288.42
|
|
Customer#000000115 7508.92
|
|
Customer#000000121 6428.32
|
|
Customer#000000122 7865.46
|
|
Customer#000000124 1842.49
|
|
Customer#000000127 9280.71
|
|
Customer#000000130 5073.58
|
|
Customer#000000133 2314.67
|
|
Customer#000000139 7897.78
|
|
Customer#000000142 2209.81
|
|
drop table t,r;
|
|
deallocate prepare stmt;
|
|
set optimizer_switch='materialization=default';
|
|
# Materialization PS
|
|
# ==================
|
|
prepare stmt from "
|
|
delete from customer where c_custkey in (select o_custkey from orders
|
|
where o_orderDATE between '1992-01-09' and '1993-03-08') and c_name like ?;
|
|
";
|
|
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
|
|
set @a1='Customer#%1_';
|
|
create table t as
|
|
select * from customer where c_custkey in (select o_custkey from orders
|
|
where o_orderDATE between '1992-01-09' and '1993-03-08') and c_name like @a1;
|
|
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 711.56
|
|
Customer#000000002 121.65
|
|
Customer#000000005 794.47
|
|
Customer#000000007 9561.95
|
|
Customer#000000008 6819.74
|
|
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#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
|
|
insert into customer select * from t;
|
|
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
|
|
set @a2='Customer#%3_';
|
|
create table r as
|
|
select * from customer where c_custkey in (select o_custkey from orders
|
|
where o_orderDATE between '1992-01-09' and '1993-03-08') and c_name like @a2;
|
|
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 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#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#000000140 9963.15
|
|
Customer#000000142 2209.81
|
|
Customer#000000143 2186.5
|
|
Customer#000000145 9748.93
|
|
Customer#000000148 2135.6
|
|
Customer#000000149 8959.65
|
|
insert into customer select * from r;
|
|
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
|
|
drop table t,r;
|
|
deallocate prepare stmt;
|
|
# Materialization SJM PS
|
|
# ======================
|
|
prepare stmt from "
|
|
delete 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) and c_acctbal between ? and ?;
|
|
";
|
|
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#000000056 6530.86
|
|
Customer#000000118 3582.37
|
|
set @a1=3500;
|
|
set @a2=4000;
|
|
create table t as
|
|
select * 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) and c_acctbal between @a1 and @a2;
|
|
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#000000032 3471.53
|
|
Customer#000000037 -917.75
|
|
Customer#000000056 6530.86
|
|
insert into customer select * from t;
|
|
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#000000056 6530.86
|
|
Customer#000000118 3582.37
|
|
set @a3=-1000;
|
|
set @a4=3500;
|
|
create table r as
|
|
select * 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) and c_acctbal between @a3 and @a4;
|
|
execute stmt using @a3, @a4;
|
|
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#000000056 6530.86
|
|
Customer#000000118 3582.37
|
|
insert into customer select * from r;
|
|
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#000000056 6530.86
|
|
Customer#000000118 3582.37
|
|
drop table t,r;
|
|
deallocate prepare stmt;
|
|
# Pullout SP
|
|
# ==========
|
|
create procedure p(a1 int, a2 int)
|
|
delete 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')) and o_totalprice between a1 and 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
|
|
1729 12137.76
|
|
2880 145761.99
|
|
3142 16030.15
|
|
5095 184583.99
|
|
5121 150334.57
|
|
5382 138423.03
|
|
644 201268.06
|
|
737 12984.85
|
|
create table t as
|
|
select * 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')) and o_totalprice between 150000 and 200000;
|
|
call p(150000, 200000);
|
|
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
|
|
1729 12137.76
|
|
2880 145761.99
|
|
3142 16030.15
|
|
5382 138423.03
|
|
644 201268.06
|
|
737 12984.85
|
|
insert into orders select * from t;
|
|
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
|
|
1729 12137.76
|
|
2880 145761.99
|
|
3142 16030.15
|
|
5095 184583.99
|
|
5121 150334.57
|
|
5382 138423.03
|
|
644 201268.06
|
|
737 12984.85
|
|
create table r as
|
|
select * 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')) and o_totalprice between 180000 and 210000;
|
|
call p(180000, 210000);
|
|
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
|
|
1729 12137.76
|
|
2880 145761.99
|
|
3142 16030.15
|
|
5121 150334.57
|
|
5382 138423.03
|
|
737 12984.85
|
|
insert into orders select * from r;
|
|
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
|
|
1729 12137.76
|
|
2880 145761.99
|
|
3142 16030.15
|
|
5095 184583.99
|
|
5121 150334.57
|
|
5382 138423.03
|
|
644 201268.06
|
|
737 12984.85
|
|
drop table t,r;
|
|
drop procedure p;
|
|
# FirstMatch SP
|
|
# =============
|
|
set optimizer_switch='materialization=off';
|
|
create procedure p(a int)
|
|
delete 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') and c_acctbal > a;
|
|
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#000000007 9561.95
|
|
Customer#000000008 6819.74
|
|
Customer#000000017 6.34
|
|
Customer#000000019 8914.71
|
|
Customer#000000022 591.98
|
|
Customer#000000025 7133.7
|
|
Customer#000000028 1007.18
|
|
Customer#000000037 -917.75
|
|
Customer#000000040 1335.3
|
|
Customer#000000047 274.58
|
|
Customer#000000059 3458.6
|
|
Customer#000000061 1536.24
|
|
Customer#000000064 -646.64
|
|
Customer#000000067 8166.59
|
|
Customer#000000077 1738.87
|
|
Customer#000000082 9468.34
|
|
Customer#000000091 4643.14
|
|
Customer#000000092 1182.91
|
|
Customer#000000094 5500.11
|
|
Customer#000000097 2164.48
|
|
Customer#000000101 7470.96
|
|
Customer#000000103 2757.45
|
|
Customer#000000106 3288.42
|
|
Customer#000000115 7508.92
|
|
Customer#000000121 6428.32
|
|
Customer#000000122 7865.46
|
|
Customer#000000124 1842.49
|
|
Customer#000000127 9280.71
|
|
Customer#000000130 5073.58
|
|
Customer#000000133 2314.67
|
|
Customer#000000139 7897.78
|
|
Customer#000000142 2209.81
|
|
create table t as
|
|
select * 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') and c_acctbal > 4000;
|
|
call p(4000);
|
|
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#000000017 6.34
|
|
Customer#000000022 591.98
|
|
Customer#000000028 1007.18
|
|
Customer#000000037 -917.75
|
|
Customer#000000040 1335.3
|
|
Customer#000000047 274.58
|
|
Customer#000000059 3458.6
|
|
Customer#000000061 1536.24
|
|
Customer#000000064 -646.64
|
|
Customer#000000077 1738.87
|
|
Customer#000000092 1182.91
|
|
Customer#000000097 2164.48
|
|
Customer#000000103 2757.45
|
|
Customer#000000106 3288.42
|
|
Customer#000000124 1842.49
|
|
Customer#000000133 2314.67
|
|
Customer#000000142 2209.81
|
|
insert into customer select * from t;
|
|
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#000000007 9561.95
|
|
Customer#000000008 6819.74
|
|
Customer#000000017 6.34
|
|
Customer#000000019 8914.71
|
|
Customer#000000022 591.98
|
|
Customer#000000025 7133.7
|
|
Customer#000000028 1007.18
|
|
Customer#000000037 -917.75
|
|
Customer#000000040 1335.3
|
|
Customer#000000047 274.58
|
|
Customer#000000059 3458.6
|
|
Customer#000000061 1536.24
|
|
Customer#000000064 -646.64
|
|
Customer#000000067 8166.59
|
|
Customer#000000077 1738.87
|
|
Customer#000000082 9468.34
|
|
Customer#000000091 4643.14
|
|
Customer#000000092 1182.91
|
|
Customer#000000094 5500.11
|
|
Customer#000000097 2164.48
|
|
Customer#000000101 7470.96
|
|
Customer#000000103 2757.45
|
|
Customer#000000106 3288.42
|
|
Customer#000000115 7508.92
|
|
Customer#000000121 6428.32
|
|
Customer#000000122 7865.46
|
|
Customer#000000124 1842.49
|
|
Customer#000000127 9280.71
|
|
Customer#000000130 5073.58
|
|
Customer#000000133 2314.67
|
|
Customer#000000139 7897.78
|
|
Customer#000000142 2209.81
|
|
create table r as
|
|
select * 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') and c_acctbal > 2000;
|
|
call p(2000);
|
|
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#000000017 6.34
|
|
Customer#000000022 591.98
|
|
Customer#000000028 1007.18
|
|
Customer#000000037 -917.75
|
|
Customer#000000040 1335.3
|
|
Customer#000000047 274.58
|
|
Customer#000000061 1536.24
|
|
Customer#000000064 -646.64
|
|
Customer#000000077 1738.87
|
|
Customer#000000092 1182.91
|
|
Customer#000000124 1842.49
|
|
insert into customer select * from r;
|
|
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#000000007 9561.95
|
|
Customer#000000008 6819.74
|
|
Customer#000000017 6.34
|
|
Customer#000000019 8914.71
|
|
Customer#000000022 591.98
|
|
Customer#000000025 7133.7
|
|
Customer#000000028 1007.18
|
|
Customer#000000037 -917.75
|
|
Customer#000000040 1335.3
|
|
Customer#000000047 274.58
|
|
Customer#000000059 3458.6
|
|
Customer#000000061 1536.24
|
|
Customer#000000064 -646.64
|
|
Customer#000000067 8166.59
|
|
Customer#000000077 1738.87
|
|
Customer#000000082 9468.34
|
|
Customer#000000091 4643.14
|
|
Customer#000000092 1182.91
|
|
Customer#000000094 5500.11
|
|
Customer#000000097 2164.48
|
|
Customer#000000101 7470.96
|
|
Customer#000000103 2757.45
|
|
Customer#000000106 3288.42
|
|
Customer#000000115 7508.92
|
|
Customer#000000121 6428.32
|
|
Customer#000000122 7865.46
|
|
Customer#000000124 1842.49
|
|
Customer#000000127 9280.71
|
|
Customer#000000130 5073.58
|
|
Customer#000000133 2314.67
|
|
Customer#000000139 7897.78
|
|
Customer#000000142 2209.81
|
|
drop table t,r;
|
|
drop procedure p;
|
|
set optimizer_switch='materialization=default';
|
|
# Materialization SP
|
|
# ==================
|
|
create procedure p()
|
|
delete from customer 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
|
|
create table t as
|
|
select * from customer where c_custkey in (select o_custkey from orders
|
|
where o_orderDATE between '1992-01-09' and '1993-03-08');
|
|
call p();
|
|
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
|
|
insert into customer select * from t;
|
|
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
|
|
create table r as
|
|
select * from customer where c_custkey in (select o_custkey from orders
|
|
where o_orderDATE between '1992-01-09' and '1993-03-08');
|
|
call p();
|
|
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
|
|
insert into customer select * from r;
|
|
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
|
|
drop table t,r;
|
|
drop procedure p;
|
|
# Materialization SJM SP
|
|
# ======================
|
|
create procedure p()
|
|
delete 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);
|
|
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#000000056 6530.86
|
|
Customer#000000118 3582.37
|
|
create table t as
|
|
select * 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);
|
|
call p();
|
|
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
|
|
insert into customer select * from t;
|
|
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#000000056 6530.86
|
|
Customer#000000118 3582.37
|
|
create table r as
|
|
select * 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);
|
|
call p();
|
|
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
|
|
insert into customer select * from r;
|
|
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#000000056 6530.86
|
|
Customer#000000118 3582.37
|
|
drop table t,r;
|
|
drop procedure p;
|
|
# Checking limitations
|
|
# ====================
|
|
# Check for DELETE ... RETURNING with SJ subquery in WHERE
|
|
select c_name from customer where c_custkey in (select o_custkey from orders
|
|
where o_orderDATE between '1992-01-09' and '1993-03-08');
|
|
c_name
|
|
Customer#000000001
|
|
Customer#000000002
|
|
Customer#000000005
|
|
Customer#000000007
|
|
Customer#000000008
|
|
Customer#000000010
|
|
Customer#000000011
|
|
Customer#000000013
|
|
Customer#000000014
|
|
Customer#000000016
|
|
Customer#000000017
|
|
Customer#000000019
|
|
Customer#000000022
|
|
Customer#000000023
|
|
Customer#000000025
|
|
Customer#000000028
|
|
Customer#000000029
|
|
Customer#000000031
|
|
Customer#000000032
|
|
Customer#000000034
|
|
Customer#000000035
|
|
Customer#000000037
|
|
Customer#000000038
|
|
Customer#000000040
|
|
Customer#000000041
|
|
Customer#000000043
|
|
Customer#000000044
|
|
Customer#000000046
|
|
Customer#000000047
|
|
Customer#000000049
|
|
Customer#000000052
|
|
Customer#000000053
|
|
Customer#000000055
|
|
Customer#000000056
|
|
Customer#000000058
|
|
Customer#000000059
|
|
Customer#000000061
|
|
Customer#000000062
|
|
Customer#000000064
|
|
Customer#000000065
|
|
Customer#000000067
|
|
Customer#000000070
|
|
Customer#000000071
|
|
Customer#000000073
|
|
Customer#000000074
|
|
Customer#000000076
|
|
Customer#000000079
|
|
Customer#000000080
|
|
Customer#000000082
|
|
Customer#000000083
|
|
Customer#000000085
|
|
Customer#000000086
|
|
Customer#000000088
|
|
Customer#000000089
|
|
Customer#000000091
|
|
Customer#000000092
|
|
Customer#000000094
|
|
Customer#000000095
|
|
Customer#000000097
|
|
Customer#000000098
|
|
Customer#000000100
|
|
Customer#000000101
|
|
Customer#000000103
|
|
Customer#000000104
|
|
Customer#000000106
|
|
Customer#000000107
|
|
Customer#000000109
|
|
Customer#000000110
|
|
Customer#000000112
|
|
Customer#000000115
|
|
Customer#000000116
|
|
Customer#000000118
|
|
Customer#000000121
|
|
Customer#000000122
|
|
Customer#000000127
|
|
Customer#000000128
|
|
Customer#000000130
|
|
Customer#000000131
|
|
Customer#000000133
|
|
Customer#000000134
|
|
Customer#000000136
|
|
Customer#000000137
|
|
Customer#000000139
|
|
Customer#000000140
|
|
Customer#000000142
|
|
Customer#000000143
|
|
Customer#000000145
|
|
Customer#000000148
|
|
Customer#000000149
|
|
create table t as
|
|
select * from customer where c_custkey in (select o_custkey from orders
|
|
where o_orderDATE between '1992-01-09' and '1993-03-08');
|
|
explain
|
|
delete from customer where c_custkey in (select o_custkey from orders
|
|
where o_orderDATE between '1992-01-09' and '1993-03-08') returning c_name;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY customer ALL NULL NULL NULL NULL 150 Using where
|
|
2 DEPENDENT SUBQUERY orders index_subquery i_o_orderdate,i_o_custkey i_o_custkey 5 func 15 Using where
|
|
delete from customer where c_custkey in (select o_custkey from orders
|
|
where o_orderDATE between '1992-01-09' and '1993-03-08') returning c_name;
|
|
c_name
|
|
Customer#000000001
|
|
Customer#000000002
|
|
Customer#000000005
|
|
Customer#000000007
|
|
Customer#000000008
|
|
Customer#000000010
|
|
Customer#000000011
|
|
Customer#000000013
|
|
Customer#000000014
|
|
Customer#000000016
|
|
Customer#000000017
|
|
Customer#000000019
|
|
Customer#000000022
|
|
Customer#000000023
|
|
Customer#000000025
|
|
Customer#000000028
|
|
Customer#000000029
|
|
Customer#000000031
|
|
Customer#000000032
|
|
Customer#000000034
|
|
Customer#000000035
|
|
Customer#000000037
|
|
Customer#000000038
|
|
Customer#000000040
|
|
Customer#000000041
|
|
Customer#000000043
|
|
Customer#000000044
|
|
Customer#000000046
|
|
Customer#000000047
|
|
Customer#000000049
|
|
Customer#000000052
|
|
Customer#000000053
|
|
Customer#000000055
|
|
Customer#000000056
|
|
Customer#000000058
|
|
Customer#000000059
|
|
Customer#000000061
|
|
Customer#000000062
|
|
Customer#000000064
|
|
Customer#000000065
|
|
Customer#000000067
|
|
Customer#000000070
|
|
Customer#000000071
|
|
Customer#000000073
|
|
Customer#000000074
|
|
Customer#000000076
|
|
Customer#000000079
|
|
Customer#000000080
|
|
Customer#000000082
|
|
Customer#000000083
|
|
Customer#000000085
|
|
Customer#000000086
|
|
Customer#000000088
|
|
Customer#000000089
|
|
Customer#000000091
|
|
Customer#000000092
|
|
Customer#000000094
|
|
Customer#000000095
|
|
Customer#000000097
|
|
Customer#000000098
|
|
Customer#000000100
|
|
Customer#000000101
|
|
Customer#000000103
|
|
Customer#000000104
|
|
Customer#000000106
|
|
Customer#000000107
|
|
Customer#000000109
|
|
Customer#000000110
|
|
Customer#000000112
|
|
Customer#000000115
|
|
Customer#000000116
|
|
Customer#000000118
|
|
Customer#000000121
|
|
Customer#000000122
|
|
Customer#000000127
|
|
Customer#000000128
|
|
Customer#000000130
|
|
Customer#000000131
|
|
Customer#000000133
|
|
Customer#000000134
|
|
Customer#000000136
|
|
Customer#000000137
|
|
Customer#000000139
|
|
Customer#000000140
|
|
Customer#000000142
|
|
Customer#000000143
|
|
Customer#000000145
|
|
Customer#000000148
|
|
Customer#000000149
|
|
select c_name from customer where c_custkey in (select o_custkey from orders
|
|
where o_orderDATE between '1992-01-09' and '1993-03-08');
|
|
c_name
|
|
insert into customer select * from t;
|
|
select c_name from customer where c_custkey in (select o_custkey from orders
|
|
where o_orderDATE between '1992-01-09' and '1993-03-08');
|
|
c_name
|
|
Customer#000000001
|
|
Customer#000000002
|
|
Customer#000000005
|
|
Customer#000000007
|
|
Customer#000000008
|
|
Customer#000000010
|
|
Customer#000000011
|
|
Customer#000000013
|
|
Customer#000000014
|
|
Customer#000000016
|
|
Customer#000000017
|
|
Customer#000000019
|
|
Customer#000000022
|
|
Customer#000000023
|
|
Customer#000000025
|
|
Customer#000000028
|
|
Customer#000000029
|
|
Customer#000000031
|
|
Customer#000000032
|
|
Customer#000000034
|
|
Customer#000000035
|
|
Customer#000000037
|
|
Customer#000000038
|
|
Customer#000000040
|
|
Customer#000000041
|
|
Customer#000000043
|
|
Customer#000000044
|
|
Customer#000000046
|
|
Customer#000000047
|
|
Customer#000000049
|
|
Customer#000000052
|
|
Customer#000000053
|
|
Customer#000000055
|
|
Customer#000000056
|
|
Customer#000000058
|
|
Customer#000000059
|
|
Customer#000000061
|
|
Customer#000000062
|
|
Customer#000000064
|
|
Customer#000000065
|
|
Customer#000000067
|
|
Customer#000000070
|
|
Customer#000000071
|
|
Customer#000000073
|
|
Customer#000000074
|
|
Customer#000000076
|
|
Customer#000000079
|
|
Customer#000000080
|
|
Customer#000000082
|
|
Customer#000000083
|
|
Customer#000000085
|
|
Customer#000000086
|
|
Customer#000000088
|
|
Customer#000000089
|
|
Customer#000000091
|
|
Customer#000000092
|
|
Customer#000000094
|
|
Customer#000000095
|
|
Customer#000000097
|
|
Customer#000000098
|
|
Customer#000000100
|
|
Customer#000000101
|
|
Customer#000000103
|
|
Customer#000000104
|
|
Customer#000000106
|
|
Customer#000000107
|
|
Customer#000000109
|
|
Customer#000000110
|
|
Customer#000000112
|
|
Customer#000000115
|
|
Customer#000000116
|
|
Customer#000000118
|
|
Customer#000000121
|
|
Customer#000000122
|
|
Customer#000000127
|
|
Customer#000000128
|
|
Customer#000000130
|
|
Customer#000000131
|
|
Customer#000000133
|
|
Customer#000000134
|
|
Customer#000000136
|
|
Customer#000000137
|
|
Customer#000000139
|
|
Customer#000000140
|
|
Customer#000000142
|
|
Customer#000000143
|
|
Customer#000000145
|
|
Customer#000000148
|
|
Customer#000000149
|
|
drop table t;
|
|
select c_name 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
|
|
Customer#000000013
|
|
Customer#000000032
|
|
Customer#000000037
|
|
Customer#000000056
|
|
Customer#000000118
|
|
create table t as
|
|
select * 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
|
|
delete 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) returning c_name;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY customer ALL NULL NULL NULL NULL 150 Using where
|
|
2 DEPENDENT SUBQUERY orders range i_o_orderdate i_o_orderdate 4 NULL 28 Using index condition; Using temporary
|
|
delete 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) returning c_name;
|
|
c_name
|
|
Customer#000000013
|
|
Customer#000000032
|
|
Customer#000000037
|
|
Customer#000000056
|
|
Customer#000000118
|
|
select c_name 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
|
|
insert into customer select * from t;
|
|
select c_name 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
|
|
Customer#000000013
|
|
Customer#000000032
|
|
Customer#000000037
|
|
Customer#000000056
|
|
Customer#000000118
|
|
drop table t;
|
|
# Check for DELETE ... ORDER BY ...LIMIT with SJ subquery in WHERE
|
|
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
|
|
1344 43809.37
|
|
1856 189361.42
|
|
1925 146382.71
|
|
3139 40975.96
|
|
324 26868.85
|
|
4903 34363.63
|
|
5607 24660.06
|
|
# Should not use semi-join conversion because has ORDER BY ... LIMIT
|
|
explain
|
|
delete 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))
|
|
order by o_totalprice limit 500;
|
|
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 13 Using index condition; Using temporary; Using filesort
|
|
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
|
|
create table t as
|
|
select * 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));
|
|
select o_orderkey, o_totalprice from t;
|
|
o_orderkey o_totalprice
|
|
324 26868.85
|
|
1856 189361.42
|
|
1221 117397.16
|
|
1344 43809.37
|
|
1925 146382.71
|
|
3139 40975.96
|
|
4903 34363.63
|
|
5607 24660.06
|
|
delete 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))
|
|
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
|
|
insert into orders select * from t;
|
|
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
|
|
1344 43809.37
|
|
1856 189361.42
|
|
1925 146382.71
|
|
3139 40975.96
|
|
324 26868.85
|
|
4903 34363.63
|
|
5607 24660.06
|
|
drop table t;
|
|
# Should use semi-join converion
|
|
explain
|
|
delete 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));
|
|
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 13 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
|
|
create table t as
|
|
select * 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));
|
|
select o_orderkey, o_totalprice from t;
|
|
o_orderkey o_totalprice
|
|
324 26868.85
|
|
1856 189361.42
|
|
1221 117397.16
|
|
3139 40975.96
|
|
1344 43809.37
|
|
1925 146382.71
|
|
4903 34363.63
|
|
5607 24660.06
|
|
delete 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));
|
|
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
|
|
insert into orders select * from t;
|
|
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
|
|
1344 43809.37
|
|
1856 189361.42
|
|
1925 146382.71
|
|
3139 40975.96
|
|
324 26868.85
|
|
4903 34363.63
|
|
5607 24660.06
|
|
drop table t;
|
|
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_partkey in
|
|
(select ps_partkey from partsupp_small
|
|
where ps_suppkey 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 PRIMARY 8 NULL 176 Using where; Using index; LooseScan
|
|
1 PRIMARY lineitem ref i_l_suppkey_partkey,i_l_partkey i_l_partkey 5 dbt3_s001.partsupp_small.ps_partkey 30 Using index
|
|
explain format=json
|
|
select count(*) from lineitem where l_partkey in
|
|
(select ps_partkey from partsupp_small
|
|
where ps_suppkey 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": "PRIMARY",
|
|
"key_length": "8",
|
|
"used_key_parts": ["ps_partkey", "ps_suppkey"],
|
|
"loops": 1,
|
|
"rows": 176,
|
|
"cost": "COST_REPLACED",
|
|
"filtered": 23.86363602,
|
|
"attached_condition": "partsupp_small.ps_suppkey in (1,2,3)",
|
|
"using_index": true,
|
|
"loose_scan": 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": 42,
|
|
"rows": 30,
|
|
"cost": "COST_REPLACED",
|
|
"filtered": 2.380952358,
|
|
"using_index": true
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
explain
|
|
select l_partkey from lineitem where l_partkey in
|
|
(select ps_partkey from partsupp_small
|
|
where ps_suppkey 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 PRIMARY 8 NULL 176 Using where; Using index; LooseScan
|
|
1 PRIMARY lineitem ref i_l_suppkey_partkey,i_l_partkey i_l_partkey 5 dbt3_s001.partsupp_small.ps_partkey 30 Using index
|
|
explain format=json
|
|
select l_partkey from lineitem where l_partkey in
|
|
(select ps_partkey from partsupp_small
|
|
where ps_suppkey 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": "PRIMARY",
|
|
"key_length": "8",
|
|
"used_key_parts": ["ps_partkey", "ps_suppkey"],
|
|
"loops": 1,
|
|
"rows": 176,
|
|
"cost": "COST_REPLACED",
|
|
"filtered": 23.86363602,
|
|
"attached_condition": "partsupp_small.ps_suppkey in (1,2,3)",
|
|
"using_index": true,
|
|
"loose_scan": 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": 42,
|
|
"rows": 30,
|
|
"cost": "COST_REPLACED",
|
|
"filtered": 2.380952358,
|
|
"using_index": true
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
select count(*) from lineitem where l_partkey in
|
|
(select ps_partkey from partsupp_small
|
|
where ps_suppkey in (1,2,3));
|
|
count(*)
|
|
1175
|
|
create table t as
|
|
select * from lineitem where l_partkey in
|
|
(select ps_partkey from partsupp_small
|
|
where ps_suppkey in (1,2,3));
|
|
explain
|
|
delete from lineitem where l_partkey in
|
|
(select ps_partkey from partsupp_small
|
|
where ps_suppkey 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 PRIMARY 8 NULL 176 Using where; Using index; LooseScan
|
|
1 PRIMARY lineitem ref i_l_suppkey_partkey,i_l_partkey i_l_partkey 5 dbt3_s001.partsupp_small.ps_partkey 30
|
|
explain format=json
|
|
delete from lineitem where l_partkey in
|
|
(select ps_partkey from partsupp_small
|
|
where ps_suppkey 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": "PRIMARY",
|
|
"key_length": "8",
|
|
"used_key_parts": ["ps_partkey", "ps_suppkey"],
|
|
"loops": 1,
|
|
"rows": 176,
|
|
"cost": "COST_REPLACED",
|
|
"filtered": 23.86363602,
|
|
"attached_condition": "partsupp_small.ps_suppkey in (1,2,3)",
|
|
"using_index": true,
|
|
"loose_scan": 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": 42,
|
|
"rows": 30,
|
|
"cost": "COST_REPLACED",
|
|
"filtered": 2.380952358
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
delete from lineitem where l_partkey in
|
|
(select ps_partkey from partsupp_small
|
|
where ps_suppkey in (1,2,3));
|
|
select count(*) from lineitem where l_partkey in
|
|
(select ps_partkey from partsupp_small
|
|
where ps_suppkey in (1,2,3));
|
|
count(*)
|
|
0
|
|
insert into lineitem select * from t;
|
|
select count(*) from lineitem where l_partkey in
|
|
(select ps_partkey from partsupp_small
|
|
where ps_suppkey in (1,2,3));
|
|
count(*)
|
|
1175
|
|
drop table t;
|
|
# LooseScan PS
|
|
# ============
|
|
prepare stmt from "
|
|
delete from lineitem where l_partkey in
|
|
(select ps_partkey from partsupp_small
|
|
where ps_suppkey in (1,2,3));
|
|
";
|
|
select count(*) from lineitem where l_partkey in
|
|
(select ps_partkey from partsupp_small
|
|
where ps_suppkey in (1,2,3));
|
|
count(*)
|
|
1175
|
|
create table t as
|
|
select * from lineitem where l_partkey in
|
|
(select ps_partkey from partsupp_small
|
|
where ps_suppkey in (1,2,3));
|
|
execute stmt;
|
|
select count(*) from lineitem where l_partkey in
|
|
(select ps_partkey from partsupp_small
|
|
where ps_suppkey in (1,2,3));
|
|
count(*)
|
|
0
|
|
insert into lineitem select * from t;
|
|
select count(*) from lineitem where l_partkey in
|
|
(select ps_partkey from partsupp_small
|
|
where ps_suppkey in (1,2,3));
|
|
count(*)
|
|
1175
|
|
create table r as
|
|
select * from lineitem where l_partkey in
|
|
(select ps_partkey from partsupp_small
|
|
where ps_suppkey in (1,2,3));
|
|
execute stmt;
|
|
select count(*) from lineitem where l_partkey in
|
|
(select ps_partkey from partsupp_small
|
|
where ps_suppkey in (1,2,3));
|
|
count(*)
|
|
0
|
|
insert into lineitem select * from r;
|
|
select count(*) from lineitem where l_partkey in
|
|
(select ps_partkey from partsupp_small
|
|
where ps_suppkey in (1,2,3));
|
|
count(*)
|
|
1175
|
|
drop tables r, t;
|
|
deallocate prepare stmt;
|
|
# LooseScan SP
|
|
# ============
|
|
create procedure p()
|
|
delete from lineitem where l_partkey in
|
|
(select ps_partkey from partsupp_small
|
|
where ps_suppkey in (1,2,3));
|
|
select count(*) from lineitem where l_partkey in
|
|
(select ps_partkey from partsupp_small
|
|
where ps_suppkey in (1,2,3));
|
|
count(*)
|
|
1175
|
|
create table t as
|
|
select * from lineitem where l_partkey in
|
|
(select ps_partkey from partsupp_small
|
|
where ps_suppkey in (1,2,3));
|
|
call p();
|
|
select count(*) from lineitem where l_partkey in
|
|
(select ps_partkey from partsupp_small
|
|
where ps_suppkey in (1,2,3));
|
|
count(*)
|
|
0
|
|
insert into lineitem select * from t;
|
|
select count(*) from lineitem where l_partkey in
|
|
(select ps_partkey from partsupp_small
|
|
where ps_suppkey in (1,2,3));
|
|
count(*)
|
|
1175
|
|
create table r as
|
|
select * from lineitem where l_partkey in
|
|
(select ps_partkey from partsupp_small
|
|
where ps_suppkey in (1,2,3));
|
|
call p();
|
|
select count(*) from lineitem where l_partkey in
|
|
(select ps_partkey from partsupp_small
|
|
where ps_suppkey in (1,2,3));
|
|
count(*)
|
|
0
|
|
insert into lineitem select * from r;
|
|
select count(*) from lineitem where l_partkey in
|
|
(select ps_partkey from partsupp_small
|
|
where ps_suppkey in (1,2,3));
|
|
count(*)
|
|
1175
|
|
drop tables r, t;
|
|
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
|
|
}
|
|
}
|
|
]
|
|
}
|
|
]
|
|
}
|
|
}
|
|
explain
|
|
select l_partkey 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 l_partkey 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
|
|
create table t as
|
|
select * 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
|
|
delete 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
|
|
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
|
|
delete 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
|
|
}
|
|
},
|
|
{
|
|
"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
|
|
}
|
|
}
|
|
]
|
|
}
|
|
]
|
|
}
|
|
}
|
|
delete 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
|
|
)
|
|
);
|
|
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(*)
|
|
0
|
|
insert into lineitem select * from t;
|
|
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
|
|
drop table t;
|
|
# DuplicateWeedout PS
|
|
# ===================
|
|
prepare stmt from "
|
|
delete 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
|
|
)
|
|
);
|
|
";
|
|
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
|
|
create table t as
|
|
select * 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
|
|
)
|
|
);
|
|
execute stmt;
|
|
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(*)
|
|
0
|
|
insert into lineitem select * from t;
|
|
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
|
|
create table r as
|
|
select * 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
|
|
)
|
|
);
|
|
execute stmt;
|
|
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(*)
|
|
0
|
|
insert into lineitem select * from r;
|
|
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
|
|
drop tables r, t;
|
|
deallocate prepare stmt;
|
|
# DuplicateWeedout SP
|
|
# ===================
|
|
create procedure p()
|
|
delete 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
|
|
)
|
|
);
|
|
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
|
|
create table t as
|
|
select * 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
|
|
)
|
|
);
|
|
call p();
|
|
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(*)
|
|
0
|
|
insert into lineitem select * from t;
|
|
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
|
|
create table r as
|
|
select * 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
|
|
)
|
|
);
|
|
call p();
|
|
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(*)
|
|
0
|
|
insert into lineitem select * from r;
|
|
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
|
|
drop tables r, t;
|
|
drop procedure p;
|
|
set @@optimizer_switch=@tmp_optimizer_switch;
|
|
drop table partsupp_small;
|
|
DROP DATABASE dbt3_s001;
|