Lines Matching refs:t2
19 # Assume there are two tables being joined - t1 and t2. Each has a cost
24 # t2(outer) - cost of scanning t2 as the outer loop.
25 # t2(inner) - cost of scanning t2 as the inner loop.
30 # t1(outer) * t2(inner)
31 # t2(outer) * t1(inner)
36 # + (t1(outer) * t2(inner)) > (t1(inner) * t2(outer)
37 # + t1(outer) < t2(outer)
39 # In other words, when the best overall query plan has t2 as the outer loop,
54 CREATE TABLE t2(d, e, f);
56 CREATE UNIQUE INDEX i2 ON t2(d);
60 1 "SELECT * FROM t1, t2 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10"
61 2 "SELECT * FROM t2, t1 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10"
62 3 "SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10"
66 } {/.*SCAN t2\y.*SEARCH t1\y.*/}
71 DROP TABLE t2;
73 CREATE TABLE t2(d, e, f);
77 CREATE UNIQUE INDEX i3 ON t2(d);
81 1 "SELECT * FROM t1, t2 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e"
82 2 "SELECT * FROM t2, t1 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e"
83 3 "SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e"
87 } {/.*SCAN t2\y.*SEARCH t1\y.*/}
92 DROP TABLE t2;
94 CREATE TABLE t2(d, e, f);
97 CREATE INDEX i2 ON t2(d);
101 1 {SELECT t1.a, t1.b, t2.d, t2.e FROM t1, t2
102 WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)}
104 2 {SELECT t1.a, t1.b, t2.d, t2.e FROM t2, t1
105 WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)}
107 3 {SELECT t1.a, t1.b, t2.d, t2.e FROM t2 CROSS JOIN t1
108 WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)}
112 } {/.*SCAN t2\y.*SEARCH t1\y.*/}
125 # ... FROM t1, t2 WHERE (
126 # t2.rowid = +t1.rowid OR (t2.f2 = t1.f1 AND t1.f1!=-1)
129 # where there is an index on t2(f2). The planner should use "t1" as the
130 # outer loop. The inner loop, on "t2", is an OR optimization. One pass
133 # t2.rowid = $1
137 # t2.f2=$1 AND $1!=-1
141 # the loop through the f2=$1 range of the t2(f2) index.
146 CREATE TABLE t2(f2);
147 CREATE INDEX t2f ON t2(f2);
157 INSERT INTO t2 SELECT -1 FROM w;
161 SELECT count(*) FROM t1, t2 WHERE t2.rowid = +t1.rowid
166 SELECT count(*) FROM t1, t2 WHERE (
167 t2.rowid = +t1.rowid OR t2.f2 = t1.f1
173 SELECT count(*) FROM t1, t2 WHERE (
174 t2.rowid = +t1.rowid OR (t2.f2 = t1.f1 AND t1.f1!=-1)
269 DROP TABLE IF EXISTS t2;
272 CREATE TABLE t2(aa INTEGER PRIMARY KEY, bb);
273 INSERT INTO t2(aa,bb) VALUES(1,1),(2,NULL),(3,NULL);
275 SELECT COUNT(*) FROM t2
276 WHERE ( t1.b IS NOT NULL AND t2.bb IS NULL )
277 OR ( t2.bb < t1.b )
278 OR ( t1.b IS t2.bb AND t2.aa > t1.a )
297 DROP TABLE IF EXISTS t2;
300 CREATE TABLE t2(aa INTEGER PRIMARY KEY, bb TEXT);
301 INSERT INTO t2(aa,bb) VALUES(1,'abc'),(2,'wxyz'),(3,'xyz');
302 CREATE INDEX t2bb ON t2(bb);
304 SELECT COUNT(*) FROM t2
305 WHERE ( t1.b GLOB 'a*z' AND t2.bb='xyz' )
306 OR ( t2.bb = t1.b )
307 OR ( t2.aa = t1.a )