2 SET pg_hint_plan.enable_hint TO on;
3 SET pg_hint_plan.debug_print TO on;
4 SET client_min_messages TO LOG;
5 SET search_path TO public;
7 \o results/R_sample.out.log
8 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
10 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_sample.out.log > results/R_sample.out
11 \! diff expected/R_sample.out results/R_sample.out
14 ---- No. R-1-1 specified pattern of the object name
18 \o results/R_1-1-1.out.log
20 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
22 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-1-1.out.log > results/R_1-1-1.out
23 \! diff expected/R_1-1-1.out results/R_1-1-1.out
26 \o results/R_1-1-2.out.log
28 EXPLAIN SELECT * FROM s1.t1 t_1, s1.t2 t_2 WHERE t_1.c1 = t_2.c1;
30 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-1-2.out.log > results/R_1-1-2.out
31 \! diff expected/R_1-1-2.out results/R_1-1-2.out
34 \o results/R_1-1-3.out.log
36 EXPLAIN SELECT * FROM s1.t1 t_1, s1.t2 t_2 WHERE t_1.c1 = t_2.c1;
38 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-1-3.out.log > results/R_1-1-3.out
39 \! diff expected/R_1-1-3.out results/R_1-1-3.out
43 ---- No. R-1-2 specified schema name in the hint option
47 \o results/R_1-2-1.out.log
49 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
51 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-2-1.out.log > results/R_1-2-1.out
52 \! diff expected/R_1-2-1.out results/R_1-2-1.out
55 \o results/R_1-2-2.out.log
56 /*+Rows(s1.t1 s1.t2 #1)*/
57 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
59 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-2-2.out.log > results/R_1-2-2.out
60 \! diff expected/R_1-2-2.out results/R_1-2-2.out
63 ---- No. R-1-3 table doesn't exist in the hint option
67 \o results/R_1-3-1.out.log
69 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
71 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-3-1.out.log > results/R_1-3-1.out
72 \! diff expected/R_1-3-1.out results/R_1-3-1.out
75 \o results/R_1-3-2.out.log
77 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
79 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-3-2.out.log > results/R_1-3-2.out
80 \! diff expected/R_1-3-2.out results/R_1-3-2.out
83 ---- No. R-1-4 conflict table name
87 \o results/R_1-4-1.out.log
89 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
91 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-4-1.out.log > results/R_1-4-1.out
92 \! diff expected/R_1-4-1.out results/R_1-4-1.out
95 \o results/R_1-4-2.out.log
96 EXPLAIN SELECT * FROM s1.t1, s2.t1 WHERE s1.t1.c1 = s2.t1.c1;
98 EXPLAIN SELECT * FROM s1.t1, s2.t1 WHERE s1.t1.c1 = s2.t1.c1;
99 /*+Rows(s1.t1 s2.t1 #1)*/
100 EXPLAIN SELECT * FROM s1.t1, s2.t1 WHERE s1.t1.c1 = s2.t1.c1;
102 EXPLAIN SELECT * FROM s1.t1, s2.t1 s2t1 WHERE s1.t1.c1 = s2t1.c1;
103 /*+Rows(t1 s2t1 #1)*/
104 EXPLAIN SELECT * FROM s1.t1, s2.t1 s2t1 WHERE s1.t1.c1 = s2t1.c1;
106 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-4-2.out.log > results/R_1-4-2.out
107 \! diff expected/R_1-4-2.out results/R_1-4-2.out
110 \o results/R_1-4-3.out.log
111 EXPLAIN SELECT *, (SELECT max(t1.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
113 EXPLAIN SELECT *, (SELECT max(t1.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
114 /*+Rows(st1 st2 #1)Rows(t1 t2 #1)*/
115 EXPLAIN SELECT *, (SELECT max(st1.c1) FROM s1.t1 st1, s1.t2 st2 WHERE st1.c1 = st2.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
117 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-4-3.out.log > results/R_1-4-3.out
118 \! diff expected/R_1-4-3.out results/R_1-4-3.out
121 ---- No. R-1-5 conflict table name
125 \o results/R_1-5-1.out.log
127 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
129 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-5-1.out.log > results/R_1-5-1.out
130 \! diff expected/R_1-5-1.out results/R_1-5-1.out
133 \o results/R_1-5-2.out.log
135 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
137 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-5-2.out.log > results/R_1-5-2.out
138 \! diff expected/R_1-5-2.out results/R_1-5-2.out
141 \o results/R_1-5-3.out.log
143 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
144 EXPLAIN SELECT * FROM s1.t1, s1.t2, s1.t3 WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1;
146 EXPLAIN SELECT * FROM s1.t1, s1.t2, s1.t3, s1.t4 WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1;
148 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-5-3.out.log > results/R_1-5-3.out
149 \! diff expected/R_1-5-3.out results/R_1-5-3.out
152 ---- No. R-1-6 object type for the hint
156 \o results/R_1-6-1.out.log
158 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
160 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-6-1.out.log > results/R_1-6-1.out
161 \! diff expected/R_1-6-1.out results/R_1-6-1.out
164 \o results/R_1-6-2.out.log
165 EXPLAIN SELECT * FROM s1.p1 t1, s1.p1 t2 WHERE t1.c1 = t2.c1;
167 EXPLAIN SELECT * FROM s1.p1 t1, s1.p1 t2 WHERE t1.c1 = t2.c1;
169 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-6-2.out.log > results/R_1-6-2.out
170 \! diff expected/R_1-6-2.out results/R_1-6-2.out
173 \o results/R_1-6-3.out.log
174 EXPLAIN SELECT * FROM s1.ul1 t1, s1.ul1 t2 WHERE t1.c1 = t2.c1;
176 EXPLAIN SELECT * FROM s1.ul1 t1, s1.ul1 t2 WHERE t1.c1 = t2.c1;
178 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-6-3.out.log > results/R_1-6-3.out
179 \! diff expected/R_1-6-3.out results/R_1-6-3.out
182 CREATE TEMP TABLE tm1 (LIKE s1.t1 INCLUDING ALL);
183 \o results/R_1-6-4.out.log
184 EXPLAIN SELECT * FROM tm1 t1, tm1 t2 WHERE t1.c1 = t2.c1;
186 EXPLAIN SELECT * FROM tm1 t1, tm1 t2 WHERE t1.c1 = t2.c1;
188 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-6-4.out.log > results/R_1-6-4.out
189 \! diff expected/R_1-6-4.out results/R_1-6-4.out
192 \o results/R_1-6-5.out.log
193 EXPLAIN SELECT * FROM pg_catalog.pg_class t1, pg_catalog.pg_class t2 WHERE t1.oid = t2.oid;
195 EXPLAIN SELECT * FROM pg_catalog.pg_class t1, pg_catalog.pg_class t2 WHERE t1.oid = t2.oid;
197 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-6-5.out.log > results/R_1-6-5.out
198 \! diff expected/R_1-6-5.out results/R_1-6-5.out
204 \o results/R_1-6-7.out.log
205 EXPLAIN SELECT * FROM s1.f1() t1, s1.f1() t2 WHERE t1.c1 = t2.c1;
207 EXPLAIN SELECT * FROM s1.f1() t1, s1.f1() t2 WHERE t1.c1 = t2.c1;
209 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-6-7.out.log > results/R_1-6-7.out
210 \! diff expected/R_1-6-7.out results/R_1-6-7.out
213 \o results/R_1-6-8.out.log
214 EXPLAIN SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4), s1.t2 WHERE t1.c1 = t2.c1;
216 EXPLAIN SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4), s1.t2 WHERE t1.c1 = t2.c1;
217 /*+Rows(*VALUES* t2 #1)*/
218 EXPLAIN SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4), s1.t2 WHERE t1.c1 = t2.c1;
220 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-6-8.out.log > results/R_1-6-8.out
221 \! diff expected/R_1-6-8.out results/R_1-6-8.out
224 \o results/R_1-6-9.out.log
225 EXPLAIN WITH c1(c1) AS (SELECT max(t1.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1) SELECT * FROM s1.t1, c1 WHERE t1.c1 = c1.c1;
226 /*+Rows(t1 t2 #1)Rows(t1 c1 +1)*/
227 EXPLAIN WITH c1(c1) AS (SELECT max(t1.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1) SELECT * FROM s1.t1, c1 WHERE t1.c1 = c1.c1;
229 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-6-9.out.log > results/R_1-6-9.out
230 \! diff expected/R_1-6-9.out results/R_1-6-9.out
233 \o results/R_1-6-10.out.log
234 EXPLAIN SELECT * FROM s1.v1 t1, s1.v1 t2 WHERE t1.c1 = t2.c1;
236 EXPLAIN SELECT * FROM s1.v1 t1, s1.v1 t2 WHERE t1.c1 = t2.c1;
237 /*+Rows(v1t1 v1t1_ #1)*/
238 EXPLAIN SELECT * FROM s1.v1 t1, s1.v1_ t2 WHERE t1.c1 = t2.c1;
240 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-6-10.out.log > results/R_1-6-10.out
241 \! diff expected/R_1-6-10.out results/R_1-6-10.out
244 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1 AND t1.c1 = (SELECT max(st1.c1) FROM s1.t1 st1, s1.t2 st2 WHERE st1.c1 = st2.c1);
245 /*+Rows(t1 t2 #1)Rows(st1 st2 #1)*/
246 EXPLAIN (COSTS true) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1 AND t1.c1 = (SELECT max(st1.c1) FROM s1.t1 st1, s1.t2 st2 WHERE st1.c1 = st2.c1);
248 -- There are cases where difference in the measured value and predicted value
249 -- depending upon the version of PostgreSQL
252 \o results/R_1-6-11.out.log
253 EXPLAIN SELECT * FROM s1.t1, (SELECT t2.c1 FROM s1.t2) st2 WHERE t1.c1 = st2.c1;
255 EXPLAIN SELECT * FROM s1.t1, (SELECT t2.c1 FROM s1.t2) st2 WHERE t1.c1 = st2.c1;
257 EXPLAIN SELECT * FROM s1.t1, (SELECT t2.c1 FROM s1.t2) st2 WHERE t1.c1 = st2.c1;
259 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-6-11.out.log > results/R_1-6-11.out
260 \! diff expected/R_1-6-11.out results/R_1-6-11.out
263 ---- No. R-1-7 specified number of conditions
267 \o results/R_1-7-1.out.log
269 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
271 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-7-1.out.log > results/R_1-7-1.out
272 \! diff expected/R_1-7-1.out results/R_1-7-1.out
275 \o results/R_1-7-2.out.log
277 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
279 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-7-2.out.log > results/R_1-7-2.out
280 \! diff expected/R_1-7-2.out results/R_1-7-2.out
283 \o results/R_1-7-3.out.log
284 /*+Rows(t1 t2 #notrows)*/
285 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
287 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-7-3.out.log > results/R_1-7-3.out
288 \! diff expected/R_1-7-3.out results/R_1-7-3.out
291 ---- No. R-2-1 some complexity query blocks
295 \o results/R_2-1-1.out.log
297 Leading(bmt1 bmt2 bmt3 bmt4)
298 Leading(b1t2 b1t3 b1t4 b1t1)
299 Leading(b2t3 b2t4 b2t1 b2t2)
300 MergeJoin(bmt1 bmt2)HashJoin(bmt1 bmt2 bmt3)NestLoop(bmt1 bmt2 bmt3 bmt4)
301 MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1)
302 MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2)
305 SELECT max(bmt1.c1), (
306 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
308 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
310 FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
313 Leading(bmt1 bmt2 bmt3 bmt4)
314 Leading(b1t2 b1t3 b1t4 b1t1)
315 Leading(b2t3 b2t4 b2t1 b2t2)
316 MergeJoin(bmt1 bmt2)HashJoin(bmt1 bmt2 bmt3)NestLoop(bmt1 bmt2 bmt3 bmt4)
317 MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1)
318 MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2)
319 Rows(bmt1 bmt2 #1)Rows(bmt1 bmt2 bmt3 #1)Rows(bmt1 bmt2 bmt3 bmt4 #1)
320 Rows(b1t2 b1t3 #1)Rows(b1t2 b1t3 b1t4 #1)Rows(b1t2 b1t3 b1t4 b1t1 #1)
321 Rows(b2t3 b2t4 #1)Rows(b2t3 b2t4 b2t1 #1)Rows(b2t3 b2t4 b2t1 b2t2 #1)
324 SELECT max(bmt1.c1), (
325 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
327 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1)
328 FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
331 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-1-1.out.log > results/R_2-1-1.out
332 \! diff expected/R_2-1-1.out results/R_2-1-1.out
335 \o results/R_2-1-2.out.log
337 Leading(bmt1 bmt2 bmt3 bmt4)
338 Leading(b1t2 b1t3 b1t4 b1t1)
339 Leading(b2t3 b2t4 b2t1 b2t2)
340 Leading(b3t4 b3t1 b3t2 b3t3)
341 MergeJoin(bmt1 bmt2)HashJoin(bmt1 bmt2 bmt3)NestLoop(bmt1 bmt2 bmt3 bmt4)
342 MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1)
343 MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2)
344 MergeJoin(b3t4 b3t1)HashJoin(b3t4 b3t1 b3t2)NestLoop(b3t1 b3t2 b3t3 b3t4)
347 SELECT max(bmt1.c1), (
348 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
350 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
352 SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.c1 = b3t2.c1 AND b3t1.c1 = b3t3.c1 AND b3t1.c1 = b3t4.c1
354 FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
357 Leading(bmt1 bmt2 bmt3 bmt4)
358 Leading(b1t2 b1t3 b1t4 b1t1)
359 Leading(b2t3 b2t4 b2t1 b2t2)
360 Leading(b3t4 b3t1 b3t2 b3t3)
361 MergeJoin(bmt1 bmt2)HashJoin(bmt1 bmt2 bmt3)NestLoop(bmt1 bmt2 bmt3 bmt4)
362 MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1)
363 MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2)
364 MergeJoin(b3t4 b3t1)HashJoin(b3t4 b3t1 b3t2)NestLoop(b3t1 b3t2 b3t3 b3t4)
365 Rows(bmt1 bmt2 #1)Rows(bmt1 bmt2 bmt3 #1)Rows(bmt1 bmt2 bmt3 bmt4 #1)
366 Rows(b1t2 b1t3 #1)Rows(b1t2 b1t3 b1t4 #1)Rows(b1t2 b1t3 b1t4 b1t1 #1)
367 Rows(b2t3 b2t4 #1)Rows(b2t3 b2t4 b2t1 #1)Rows(b2t3 b2t4 b2t1 b2t2 #1)
368 Rows(b3t4 b3t1 #1)Rows(b3t4 b3t1 b3t2 #1)Rows(b3t1 b3t2 b3t3 b3t4 #1)
371 SELECT max(bmt1.c1), (
372 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
374 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
376 SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.c1 = b3t2.c1 AND b3t1.c1 = b3t3.c1 AND b3t1.c1 = b3t4.c1
378 FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
381 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-1-2.out.log > results/R_2-1-2.out
382 \! diff expected/R_2-1-2.out results/R_2-1-2.out
385 \o results/R_2-1-3.out.log
387 Leading(bmt4 bmt3 bmt2 bmt1)
389 EXPLAIN SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, (SELECT ctid, * FROM s1.t3 bmt3) sbmt3, (SELECT ctid, * FROM s1.t4 bmt4) sbmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = sbmt3.c1 AND bmt1.c1 = sbmt4.c1;
391 Leading(bmt4 bmt3 bmt2 bmt1)
392 Rows(bmt4 bmt3 #1)Rows(bmt4 bmt3 bmt2 #1)Rows(bmt1 bmt2 bmt3 bmt4 #1)
394 EXPLAIN SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, (SELECT ctid, * FROM s1.t3 bmt3) sbmt3, (SELECT ctid, * FROM s1.t4 bmt4) sbmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = sbmt3.c1 AND bmt1.c1 = sbmt4.c1;
396 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-1-3.out.log > results/R_2-1-3.out
397 \! diff expected/R_2-1-3.out results/R_2-1-3.out
400 \o results/R_2-1-4.out.log
402 Leading(bmt4 bmt3 bmt2 bmt1)
404 EXPLAIN SELECT max(bmt1.c1) FROM s1.t1 bmt1, (SELECT ctid, * FROM s1.t2 bmt2) sbmt2, (SELECT ctid, * FROM s1.t3 bmt3) sbmt3, (SELECT ctid, * FROM s1.t4 bmt4) sbmt4 WHERE bmt1.c1 = sbmt2.c1 AND bmt1.c1 = sbmt3.c1 AND bmt1.c1 = sbmt4.c1;
406 Leading(bmt4 bmt3 bmt2 bmt1)
407 Rows(bmt4 bmt3 #1)Rows(bmt4 bmt3 bmt2 #1)Rows(bmt1 bmt2 bmt3 bmt4 #1)
409 EXPLAIN SELECT max(bmt1.c1) FROM s1.t1 bmt1, (SELECT ctid, * FROM s1.t2 bmt2) sbmt2, (SELECT ctid, * FROM s1.t3 bmt3) sbmt3, (SELECT ctid, * FROM s1.t4 bmt4) sbmt4 WHERE bmt1.c1 = sbmt2.c1 AND bmt1.c1 = sbmt3.c1 AND bmt1.c1 = sbmt4.c1;
411 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-1-4.out.log > results/R_2-1-4.out
412 \! diff expected/R_2-1-4.out results/R_2-1-4.out
415 \o results/R_2-1-5.out.log
417 Leading(bmt1 bmt2 bmt3 bmt4)
418 Leading(b1t2 b1t3 b1t4 b1t1)
419 Leading(b2t3 b2t4 b2t1 b2t2)
420 MergeJoin(bmt1 bmt2)HashJoin(bmt1 bmt2 bmt3)NestLoop(bmt1 bmt2 bmt3 bmt4)
421 MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1)
422 MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2)
425 SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
427 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
429 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
433 Leading(bmt1 bmt2 bmt3 bmt4)
434 Leading(b1t2 b1t3 b1t4 b1t1)
435 Leading(b2t3 b2t4 b2t1 b2t2)
436 MergeJoin(bmt1 bmt2)HashJoin(bmt1 bmt2 bmt3)NestLoop(bmt1 bmt2 bmt3 bmt4)
437 MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1)
438 MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2)
439 Rows(bmt1 bmt2 #1)Rows(bmt1 bmt2 bmt3 #1)Rows(bmt1 bmt2 bmt3 bmt4 #1)
440 Rows(b1t2 b1t3 #1)Rows(b1t2 b1t3 b1t4 #1)Rows(b1t2 b1t3 b1t4 b1t1 #1)
441 Rows(b2t3 b2t4 #1)Rows(b2t3 b2t4 b2t1 #1)Rows(b2t3 b2t4 b2t1 b2t2 #1)
444 SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
446 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
448 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
452 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-1-5.out.log > results/R_2-1-5.out
453 \! diff expected/R_2-1-5.out results/R_2-1-5.out
456 \o results/R_2-1-6.out.log
458 Leading(bmt1 bmt2 bmt3 bmt4)
459 Leading(b1t2 b1t3 b1t4 b1t1)
460 Leading(b2t3 b2t4 b2t1 b2t2)
461 Leading(b3t4 b3t1 b3t2 b3t3)
462 MergeJoin(bmt1 bmt2)HashJoin(bmt1 bmt2 bmt3)NestLoop(bmt1 bmt2 bmt3 bmt4)
463 MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1)
464 MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2)
465 MergeJoin(b3t4 b3t1)HashJoin(b3t4 b3t1 b3t2)NestLoop(b3t1 b3t2 b3t3 b3t4)
468 SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
470 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
472 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
474 SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.c1 = b3t2.c1 AND b3t1.c1 = b3t3.c1 AND b3t1.c1 = b3t4.c1
478 Leading(bmt1 bmt2 bmt3 bmt4)
479 Leading(b1t2 b1t3 b1t4 b1t1)
480 Leading(b2t3 b2t4 b2t1 b2t2)
481 Leading(b3t4 b3t1 b3t2 b3t3)
482 MergeJoin(bmt1 bmt2)HashJoin(bmt1 bmt2 bmt3)NestLoop(bmt1 bmt2 bmt3 bmt4)
483 MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1)
484 MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2)
485 MergeJoin(b3t4 b3t1)HashJoin(b3t4 b3t1 b3t2)NestLoop(b3t1 b3t2 b3t3 b3t4)
486 Rows(bmt1 bmt2 #1)Rows(bmt1 bmt2 bmt3 #1)Rows(bmt1 bmt2 bmt3 bmt4 #1)
487 Rows(b1t2 b1t3 #1)Rows(b1t2 b1t3 b1t4 #1)Rows(b1t2 b1t3 b1t4 b1t1 #1)
488 Rows(b2t3 b2t4 #1)Rows(b2t3 b2t4 b2t1 #1)Rows(b2t3 b2t4 b2t1 b2t2 #1)
489 Rows(b3t4 b3t1 #1)Rows(b3t4 b3t1 b3t2 #1)Rows(b3t1 b3t2 b3t3 b3t4 #1)
492 SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
494 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
496 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
498 SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.c1 = b3t2.c1 AND b3t1.c1 = b3t3.c1 AND b3t1.c1 = b3t4.c1
502 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-1-6.out.log > results/R_2-1-6.out
503 \! diff expected/R_2-1-6.out results/R_2-1-6.out
506 \o results/R_2-1-7.out.log
508 Leading(c2 c1 bmt1 bmt2 bmt3 bmt4)
509 Leading(b1t2 b1t3 b1t4 b1t1)
510 Leading(b2t3 b2t4 b2t1 b2t2)
511 MergeJoin(c2 c1)HashJoin(c2 c1 bmt1)NestLoop(c2 c1 bmt1 bmt2)MergeJoin(c2 c1 bmt1 bmt2 bmt3)HashJoin(c2 c1 bmt1 bmt2 bmt3 bmt4)
512 MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1)
513 MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2)
517 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
520 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
522 SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4
524 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
529 Leading(c2 c1 bmt1 bmt2 bmt3 bmt4)
530 Leading(b1t2 b1t3 b1t4 b1t1)
531 Leading(b2t3 b2t4 b2t1 b2t2)
532 MergeJoin(c2 c1)HashJoin(c2 c1 bmt1)NestLoop(c2 c1 bmt1 bmt2)MergeJoin(c2 c1 bmt1 bmt2 bmt3)HashJoin(c2 c1 bmt1 bmt2 bmt3 bmt4)
533 MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1)
534 MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2)
535 Rows(c2 c1 #1)Rows(c2 c1 bmt1 #1)Rows(c2 c1 bmt1 bmt2 #1)Rows(c2 c1 bmt1 bmt2 bmt3 #1)Rows(c2 c1 bmt1 bmt2 bmt3 bmt4 #1)
536 Rows(b1t2 b1t3 #1)Rows(b1t2 b1t3 b1t4 #1)Rows(b1t2 b1t3 b1t4 b1t1 #1)
537 Rows(b2t3 b2t4 #1)Rows(b2t3 b2t4 b2t1 #1)Rows(b2t3 b2t4 b2t1 b2t2 #1)
541 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
544 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
546 SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4
548 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
553 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-1-7.out.log > results/R_2-1-7.out
554 \! diff expected/R_2-1-7.out results/R_2-1-7.out
557 \o results/R_2-1-8.out.log
559 Leading(c3 c2 c1 bmt1 bmt2 bmt3 bmt4)
560 Leading(b1t2 b1t3 b1t4 b1t1)
561 Leading(b2t3 b2t4 b2t1 b2t2)
562 Leading(b3t4 b3t1 b3t2 b3t3)
563 MergeJoin(c3 c2)HashJoin(c3 c2 c1)NestLoop(c3 c2 c1 bmt1)MergeJoin(c3 c2 c1 bmt1 bmt2)HashJoin(c3 c2 c1 bmt1 bmt2 bmt3)NestLoop(c3 c2 c1 bmt1 bmt2 bmt3 bmt4)
564 MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1)
565 MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2)
566 MergeJoin(b3t4 b3t1)HashJoin(b3t4 b3t1 b3t2)NestLoop(b3t1 b3t2 b3t3 b3t4)
570 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
573 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
576 SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.c1 = b3t2.c1 AND b3t1.c1 = b3t3.c1 AND b3t1.c1 = b3t4.c1
578 SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4
580 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
586 Leading(c3 c2 c1 bmt1 bmt2 bmt3 bmt4)
587 Leading(b1t2 b1t3 b1t4 b1t1)
588 Leading(b2t3 b2t4 b2t1 b2t2)
589 Leading(b3t4 b3t1 b3t2 b3t3)
590 MergeJoin(c3 c2)HashJoin(c3 c2 c1)NestLoop(c3 c2 c1 bmt1)MergeJoin(c3 c2 c1 bmt1 bmt2)HashJoin(c3 c2 c1 bmt1 bmt2 bmt3)NestLoop(c3 c2 c1 bmt1 bmt2 bmt3 bmt4)
591 MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1)
592 MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2)
593 MergeJoin(b3t4 b3t1)HashJoin(b3t4 b3t1 b3t2)NestLoop(b3t1 b3t2 b3t3 b3t4)
594 Rows(c3 c2 #1)Rows(c3 c2 c1 #1)Rows(c3 c2 c1 bmt1 #1)Rows(c3 c2 c1 bmt1 bmt2 #1)Rows(c3 c2 c1 bmt1 bmt2 bmt3 #1)Rows(c3 c2 c1 bmt1 bmt2 bmt3 bmt4 #1)
595 Rows(b1t2 b1t3 #1)Rows(b1t2 b1t3 b1t4 #1)Rows(b1t2 b1t3 b1t4 b1t1 #1)
596 Rows(b2t3 b2t4 #1)Rows(b2t3 b2t4 b2t1 #1)Rows(b2t3 b2t4 b2t1 b2t2 #1)
597 Rows(b3t4 b3t1 #1)Rows(b3t4 b3t1 b3t2 #1)Rows(b3t1 b3t2 b3t3 b3t4 #1)
601 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
604 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
607 SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.c1 = b3t2.c1 AND b3t1.c1 = b3t3.c1 AND b3t1.c1 = b3t4.c1
609 SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4
611 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
617 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-1-8.out.log > results/R_2-1-8.out
618 \! diff expected/R_2-1-8.out results/R_2-1-8.out
621 ---- No. R-2-2 the number of the tables per quiry block
625 \o results/R_2-2-1.out.log
631 SELECT b1t1.c1 FROM s1.t1 b1t1 WHERE b1t1.c1 = 1
634 SELECT b2t1.c1 FROM s1.t1 b2t1 WHERE b2t1.c1 = 1
636 FROM s1.t1 bmt1, c1 WHERE bmt1.c1 = 1
639 SELECT b3t1.c1 FROM s1.t1 b3t1 WHERE b3t1.c1 = 1
651 SELECT b1t1.c1 FROM s1.t1 b1t1 WHERE b1t1.c1 = 1
654 SELECT b2t1.c1 FROM s1.t1 b2t1 WHERE b2t1.c1 = 1
656 FROM s1.t1 bmt1, c1 WHERE bmt1.c1 = 1
659 SELECT b3t1.c1 FROM s1.t1 b3t1 WHERE b3t1.c1 = 1
663 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-2-1.out.log > results/R_2-2-1.out
664 \! diff expected/R_2-2-1.out results/R_2-2-1.out
667 \o results/R_2-2-2.out.log
669 Leading(c1 bmt2 bmt1)
674 HashJoin(c1 bmt1 bmt2)
681 SELECT b1t1.c1 FROM s1.t1 b1t1, s1.t2 b1t2 WHERE b1t1.c1 = b1t2.c1
684 SELECT b2t1.c1 FROM s1.t1 b2t1, s1.t2 b2t2 WHERE b2t1.c1 = b2t2.c1
686 FROM s1.t1 bmt1, s1.t2 bmt2, c1 WHERE bmt1.c1 = bmt2.c1
689 SELECT b3t1.c1 FROM s1.t1 b3t1, s1.t2 b3t2 WHERE b3t1.c1 = b3t2.c1
693 Leading(c1 bmt2 bmt1)
698 HashJoin(c1 bmt1 bmt2)
703 Rows(c1 bmt1 bmt2 #1)
710 SELECT b1t1.c1 FROM s1.t1 b1t1, s1.t2 b1t2 WHERE b1t1.c1 = b1t2.c1
713 SELECT b2t1.c1 FROM s1.t1 b2t1, s1.t2 b2t2 WHERE b2t1.c1 = b2t2.c1
715 FROM s1.t1 bmt1, s1.t2 bmt2, c1 WHERE bmt1.c1 = bmt2.c1
718 SELECT b3t1.c1 FROM s1.t1 b3t1, s1.t2 b3t2 WHERE b3t1.c1 = b3t2.c1
722 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-2-2.out.log > results/R_2-2-2.out
723 \! diff expected/R_2-2-2.out results/R_2-2-2.out
726 \o results/R_2-2-3.out.log
728 Leading(c1 bmt4 bmt3 bmt2 bmt1)
729 Leading(b1t4 b1t3 b1t2 b1t1)
730 Leading(b2t4 b2t3 b2t2 b2t1)
731 Leading(b3t4 b3t3 b3t2 b3t1)
733 HashJoin(c1 bmt4 bmt3)
734 NestLoop(c1 bmt4 bmt3 bmt2)
735 MergeJoin(c1 bmt4 bmt3 bmt2 bmt1)
737 NestLoop(b1t4 b1t3 b1t2)
738 MergeJoin(b1t4 b1t3 b1t2 b1t1)
740 NestLoop(b2t4 b2t3 b2t2)
741 MergeJoin(b2t4 b2t3 b2t2 b2t1)
743 NestLoop(b3t4 b3t3 b3t2)
744 MergeJoin(b3t4 b3t3 b3t2 b3t1)
748 SELECT b1t1.c1 FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
751 SELECT b2t1.c1 FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
753 FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4, c1 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 AND bmt1.c1 = c1.c1
755 SELECT b3t1.c1 FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.c1 = b3t2.c1 AND b3t1.c1 = b3t3.c1 AND b3t1.c1 = b3t4.c1
759 Leading(c1 bmt4 bmt3 bmt2 bmt1)
760 Leading(b1t4 b1t3 b1t2 b1t1)
761 Leading(b2t4 b2t3 b2t2 b2t1)
762 Leading(b3t4 b3t3 b3t2 b3t1)
764 HashJoin(c1 bmt4 bmt3)
765 NestLoop(c1 bmt4 bmt3 bmt2)
766 MergeJoin(c1 bmt4 bmt3 bmt2 bmt1)
768 NestLoop(b1t4 b1t3 b1t2)
769 MergeJoin(b1t4 b1t3 b1t2 b1t1)
771 NestLoop(b2t4 b2t3 b2t2)
772 MergeJoin(b2t4 b2t3 b2t2 b2t1)
774 NestLoop(b3t4 b3t3 b3t2)
775 MergeJoin(b3t4 b3t3 b3t2 b3t1)
777 Rows(c1 bmt4 bmt3 #1)
778 Rows(c1 bmt4 bmt3 bmt2 #1)
779 Rows(c1 bmt4 bmt3 bmt2 bmt1 #1)
781 Rows(b1t4 b1t3 b1t2 #1)
782 Rows(b1t4 b1t3 b1t2 b1t1 #1)
784 Rows(b2t4 b2t3 b2t2 #1)
785 Rows(b2t4 b2t3 b2t2 b2t1 #1)
787 Rows(b3t4 b3t3 b3t2 #1)
788 Rows(b3t4 b3t3 b3t2 b3t1 #1)
792 SELECT b1t1.c1 FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
795 SELECT b2t1.c1 FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
797 FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4, c1 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 AND bmt1.c1 = c1.c1
799 SELECT b3t1.c1 FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.c1 = b3t2.c1 AND b3t1.c1 = b3t3.c1 AND b3t1.c1 = b3t4.c1
803 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-2-3.out.log > results/R_2-2-3.out
804 \! diff expected/R_2-2-3.out results/R_2-2-3.out
807 \o results/R_2-2-4.out.log
809 Leading(c1 bmt4 bmt3 bmt2 bmt1)
810 Leading(b1t4 b1t3 b1t2 b1t1)
812 HashJoin(c1 bmt4 bmt3)
813 NestLoop(c1 bmt4 bmt3 bmt2)
814 MergeJoin(c1 bmt4 bmt3 bmt2 bmt1)
816 HashJoin(b1t4 b1t3 b1t2)
817 NestLoop(b1t4 b1t3 b1t2 b1t1)
821 SELECT b1t1.c1 FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
824 SELECT b2t1.c1 FROM s1.t1 b2t1 WHERE b2t1.c1 = 1
826 FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4, c1 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 AND bmt1.c1 = c1.c1
828 SELECT b3t1.c1 FROM s1.t1 b3t1
832 Leading(c1 bmt4 bmt3 bmt2 bmt1)
833 Leading(b1t4 b1t3 b1t2 b1t1)
835 HashJoin(c1 bmt4 bmt3)
836 NestLoop(c1 bmt4 bmt3 bmt2)
837 MergeJoin(c1 bmt4 bmt3 bmt2 bmt1)
839 HashJoin(b1t4 b1t3 b1t2)
840 NestLoop(b1t4 b1t3 b1t2 b1t1)
842 Rows(c1 bmt4 bmt3 #1)
843 Rows(c1 bmt4 bmt3 bmt2 #1)
844 Rows(c1 bmt4 bmt3 bmt2 bmt1 #1)
846 Rows(b1t4 b1t3 b1t2 #1)
847 Rows(b1t4 b1t3 b1t2 b1t1 #1)
851 SELECT b1t1.c1 FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
854 SELECT b2t1.c1 FROM s1.t1 b2t1 WHERE b2t1.c1 = 1
856 FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4, c1 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 AND bmt1.c1 = c1.c1
858 SELECT b3t1.c1 FROM s1.t1 b3t1
862 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-2-4.out.log > results/R_2-2-4.out
863 \! diff expected/R_2-2-4.out results/R_2-2-4.out
866 ---- No. R-2-3 RULE or VIEW
870 \o results/R_2-3-1.out.log
872 Leading(r1 t1 t2 t3 t4)
874 EXPLAIN UPDATE s1.r1 SET c1 = c1 WHERE c1 = 1;
876 Leading(r1 t1 t2 t3 t4)
877 Rows(r1 t1 t2 t3 t4 #2)
882 EXPLAIN UPDATE s1.r1 SET c1 = c1 WHERE c1 = 1;
884 Leading(r1_ b1t1 b1t2 b1t3 b1t4)
886 EXPLAIN UPDATE s1.r1_ SET c1 = c1 WHERE c1 = 1;
888 Leading(r1_ b1t1 b1t2 b1t3 b1t4)
889 Rows(r1_ b1t1 b1t2 b1t3 b1t4 #2)
890 Rows(r1_ b1t1 b1t2 b1t3 #2)
891 Rows(r1_ b1t1 b1t2 #2)
894 EXPLAIN UPDATE s1.r1_ SET c1 = c1 WHERE c1 = 1;
896 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-1.out.log > results/R_2-3-1.out
897 \! diff expected/R_2-3-1.out results/R_2-3-1.out
900 \o results/R_2-3-2.out.log
902 Leading(r2 t1 t2 t3 t4)
904 EXPLAIN UPDATE s1.r2 SET c1 = c1 WHERE c1 = 1;
906 Leading(r2 t1 t2 t3 t4)
907 Rows(r2 t1 t2 t3 t4 #2)
912 EXPLAIN UPDATE s1.r2 SET c1 = c1 WHERE c1 = 1;
914 Leading(r2_ b1t1 b1t2 b1t3 b1t4)
915 Leading(r2_ b2t1 b2t2 b2t3 b2t4)
917 EXPLAIN UPDATE s1.r2_ SET c1 = c1 WHERE c1 = 1;
919 Leading(r2_ b1t1 b1t2 b1t3 b1t4)
920 Leading(r2_ b2t1 b2t2 b2t3 b2t4)
922 Rows(r2_ b1t1 b1t2 #2)
923 Rows(r2_ b1t1 b1t2 b1t3 #2)
924 Rows(r2_ b1t1 b1t2 b1t3 b1t4 #2)
926 Rows(r2_ b2t1 b2t2 #2)
927 Rows(r2_ b2t1 b2t2 b2t3 #2)
928 Rows(r2_ b2t1 b2t2 b2t3 b2t4 #2)
930 EXPLAIN UPDATE s1.r2_ SET c1 = c1 WHERE c1 = 1;
932 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-2.out.log > results/R_2-3-2.out
933 \! diff expected/R_2-3-2.out results/R_2-3-2.out
936 \o results/R_2-3-3.out.log
938 Leading(r3 t1 t2 t3 t4)
940 EXPLAIN UPDATE s1.r3 SET c1 = c1 WHERE c1 = 1;
942 Leading(r3 t1 t2 t3 t4)
943 Rows(r3 t1 t2 t3 t4 #2)
948 EXPLAIN UPDATE s1.r3 SET c1 = c1 WHERE c1 = 1;
950 Leading(r3_ b1t1 b1t2 b1t3 b1t4)
951 Leading(r3_ b2t1 b2t2 b2t3 b2t4)
952 Leading(r3_ b3t1 b3t2 b3t3 b3t4)
954 EXPLAIN UPDATE s1.r3_ SET c1 = c1 WHERE c1 = 1;
956 Leading(r3_ b1t1 b1t2 b1t3 b1t4)
957 Leading(r3_ b2t1 b2t2 b2t3 b2t4)
958 Leading(r3_ b3t1 b3t2 b3t3 b3t4)
960 Rows(r3_ b1t1 b1t2 #2)
961 Rows(r3_ b1t1 b1t2 b1t3 #2)
962 Rows(r3_ b1t1 b1t2 b1t3 b1t4 #2)
964 Rows(r3_ b2t1 b2t2 #2)
965 Rows(r3_ b2t1 b2t2 b2t3 #2)
966 Rows(r3_ b2t1 b2t2 b2t3 b2t4 #2)
968 Rows(r3_ b3t1 b3t2 #2)
969 Rows(r3_ b3t1 b3t2 b3t3 #2)
970 Rows(r3_ b3t1 b3t2 b3t3 b3t4 #2)
972 EXPLAIN UPDATE s1.r3_ SET c1 = c1 WHERE c1 = 1;
974 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-3.out.log > results/R_2-3-3.out
975 \! diff expected/R_2-3-3.out results/R_2-3-3.out
978 \o results/R_2-3-4.out.log
979 /*+HashJoin(v1t1 v1t1)*/
980 EXPLAIN SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
981 /*+HashJoin(v1t1 v1t1)Rows(v1t1 v1t1 #1)*/
982 EXPLAIN SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
984 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-4.out.log > results/R_2-3-4.out
985 \! diff expected/R_2-3-4.out results/R_2-3-4.out
988 \o results/R_2-3-5.out.log
989 /*+NestLoop(v1t1 v1t1_)*/
990 EXPLAIN SELECT * FROM s1.v1 v1, s1.v1_ v2 WHERE v1.c1 = v2.c1;
991 /*+NestLoop(v1t1 v1t1_)Rows(v1t1 v1t1_ #1)*/
992 EXPLAIN SELECT * FROM s1.v1 v1, s1.v1_ v2 WHERE v1.c1 = v2.c1;
994 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-5.out.log > results/R_2-3-5.out
995 \! diff expected/R_2-3-5.out results/R_2-3-5.out
998 \o results/R_2-3-6.out.log
999 /*+RowsHashJoin(r4t1 r4t1)*/
1000 EXPLAIN SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1;
1001 /*+RowsHashJoin(r4t1 r4t1)Rows(r4t1 r4t1 #1)*/
1002 EXPLAIN SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1;
1004 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-6.out.log > results/R_2-3-6.out
1005 \! diff expected/R_2-3-6.out results/R_2-3-6.out
1008 \o results/R_2-3-7.out.log
1009 /*+NestLoop(r4t1 r5t1)*/
1010 EXPLAIN SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1;
1011 /*+NestLoop(r4t1 r5t1)Rows(r4t1 r5t1 #1)*/
1012 EXPLAIN SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1;
1014 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-7.out.log > results/R_2-3-7.out
1015 \! diff expected/R_2-3-7.out results/R_2-3-7.out