1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
|
--source include/have_stat_tables.inc
select @@global.use_stat_tables;
select @@session.use_stat_tables;
set @save_use_stat_tables=@@use_stat_tables;
set use_stat_tables='preferably';
--disable_warnings
DROP DATABASE IF EXISTS dbt3_s001;
--enable_warnings
CREATE DATABASE dbt3_s001;
use dbt3_s001;
set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity;
set @save_histogram_size=@@histogram_size;
set @save_histogram_type=@@histogram_type;
--disable_query_log
--disable_result_log
--disable_warnings
--source include/dbt3_s001.inc
ANALYZE TABLE
customer, lineitem, nation, orders, part, partsupp, region, supplier;
FLUSH TABLE
customer, lineitem, nation, orders, part, partsupp, region, supplier;
--enable_warnings
--enable_result_log
--enable_query_log
--echo === Q15 ===
create view revenue0 (supplier_no, total_revenue) as
select l_suppkey, sum(l_extendedprice * (1 - l_discount))
from lineitem
where
l_shipdate >= '1995-08-01'
and l_shipdate < date_add('1995-08-01', interval 90 day)
group by l_suppkey;
let $Q15=
select s_suppkey, s_name, s_address, s_phone, total_revenue
from supplier, revenue0
where s_suppkey = supplier_no
and total_revenue = (select max(total_revenue) from revenue0)
order by s_suppkey;
set @save_optimizer_switch=@@optimizer_switch;
set optimizer_switch='index_condition_pushdown=off';
set optimizer_use_condition_selectivity=1;
eval EXPLAIN EXTENDED $Q15;
eval $Q15;
set optimizer_use_condition_selectivity=3;
eval EXPLAIN EXTENDED $Q15;
eval $Q15;
set optimizer_switch=@save_optimizer_switch;
drop view revenue0;
--echo === Q16 ===
let $Q16=
select p_brand, p_type, p_size, count(distinct ps_suppkey) as supplier_cnt
from partsupp, part
where p_partkey = ps_partkey
and p_brand <> 'Brand#11'
and p_type not like 'SMALL POLISHED%'
and p_size in (49, 37, 27, 5, 40, 6, 22, 8)
and ps_suppkey not in (select s_suppkey from supplier
where s_comment like '%Customer%Complaints%')
group by p_brand, p_type, p_size
order by supplier_cnt desc, p_brand, p_type, p_size;
set optimizer_use_condition_selectivity=1;
eval EXPLAIN EXTENDED $Q16;
eval $Q16;
set optimizer_use_condition_selectivity=3;
eval EXPLAIN EXTENDED $Q16;
eval $Q16;
set optimizer_use_condition_selectivity=4;
eval EXPLAIN EXTENDED $Q16;
eval $Q16;
--echo === Q18 ===
let $Q18=
select
c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity)
from customer, orders, lineitem
where
o_orderkey in (select l_orderkey from lineitem
group by l_orderkey having sum(l_quantity) > 250)
and c_custkey = o_custkey
and o_orderkey = l_orderkey
group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice
order by o_totalprice desc, o_orderdate;
set optimizer_use_condition_selectivity=1;
eval EXPLAIN EXTENDED $Q18;
eval $Q18;
set optimizer_use_condition_selectivity=3;
eval EXPLAIN EXTENDED $Q18;
eval $Q18;
--echo === Q22 ===
let $Q22=
select cntrycode, count(*) as numcust, sum(c_acctbal) as totacctbal
from (
select substr(c_phone, 1, 2) as cntrycode, c_acctbal
from customer
where
substr(c_phone, 1, 2) in ('10', '20', '14', '19', '11', '28', '25')
and c_acctbal > (select avg(c_acctbal) from customer
where c_acctbal > 0.00
and substr(c_phone, 1, 2) in
('10', '20', '14', '19', '11', '28', '25'))
and not exists (select * from orders where o_custkey = c_custkey)
) as vip
group by cntrycode
order by cntrycode;
set optimizer_use_condition_selectivity=1;
eval EXPLAIN EXTENDED $Q22;
eval $Q22;
set optimizer_use_condition_selectivity=3;
eval EXPLAIN EXTENDED $Q22;
eval $Q22;
--echo === Q20 ===
let $Q20=
select sql_calc_found_rows
s_name, s_address
from supplier, nation
where s_suppkey in (select ps_suppkey from partsupp
where ps_partkey in (select p_partkey from part
where p_name like 'g%')
and ps_availqty >
(select 0.5 * sum(l_quantity)
from lineitem
where l_partkey = ps_partkey
and l_suppkey = ps_suppkey
and l_shipdate >= date('1993-01-01')
and l_shipdate < date('1993-01-01') +
interval '1' year ))
and s_nationkey = n_nationkey
and n_name = 'UNITED STATES'
order by s_name
limit 10;
set optimizer_use_condition_selectivity=1;
eval EXPLAIN EXTENDED $Q20;
eval $Q20;
SELECT ((SELECT COUNT(*) FROM part WHERE p_name LIKE 'g%') /
(SELECT COUNT(*) FROM part)) AS sel;
set optimizer_use_condition_selectivity=3;
eval EXPLAIN EXTENDED $Q20;
eval $Q20;
set histogram_size=15;
ANALYZE TABLE part PERSISTENT FOR COLUMNS(p_name) INDEXES();
flush table part;
set optimizer_use_condition_selectivity=4;
eval EXPLAIN EXTENDED $Q20;
eval $Q20;
set histogram_type='DOUBLE_PREC_HB';
set histogram_size=30;
ANALYZE TABLE part PERSISTENT FOR COLUMNS(p_name) INDEXES();
flush table part;
eval EXPLAIN EXTENDED $Q20;
eval $Q20;
set histogram_type='SINGLE_PREC_HB';
set histogram_size=24;
ANALYZE TABLE nation PERSISTENT FOR COLUMNS(n_name) INDEXES();
flush table nation;
eval EXPLAIN EXTENDED $Q20;
eval $Q20;
DROP DATABASE dbt3_s001;
set histogram_type=@save_histogram_type;
set histogram_size=@save_histogram_size;
set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
set use_stat_tables=@save_use_stat_tables;
|
