1 echo create table t1
(f1 smallint
, f2 smallint
, f3 smallint
, f4 smallint
);
3 echo create index hashsmallf1f2f3 on t1
(f2
,f3
,f1
) hash;
5 echo explain plan
select * from t1
;
8 <ScanType
> TableScan
</ScanType
>
10 echo explain plan
select * from t1 where f1
= 2;
13 <ScanType
> TableScan
</ScanType
>
15 <FieldName1
> f1
</FieldName1
>
16 <CompOp
> Equals
</CompOp
>
17 <OperandPtr
> VALUE
</OperandPtr
>
20 echo explain plan
select * from t1 where f1
= 2 and f2
=2;
23 <ScanType
> TableScan
</ScanType
>
28 <FieldName1
> f1
</FieldName1
>
29 <CompOp
> Equals
</CompOp
>
30 <OperandPtr
> VALUE
</OperandPtr
>
35 <FieldName1
> f2
</FieldName1
>
36 <CompOp
> Equals
</CompOp
>
37 <OperandPtr
> VALUE
</OperandPtr
>
42 echo explain plan
select * from t1 where f1
= 2 and f2
=2 and f3
=2;
45 <ScanType
> TableScan
</ScanType
>
53 <FieldName1
> f1
</FieldName1
>
54 <CompOp
> Equals
</CompOp
>
55 <OperandPtr
> VALUE
</OperandPtr
>
60 <FieldName1
> f2
</FieldName1
>
61 <CompOp
> Equals
</CompOp
>
62 <OperandPtr
> VALUE
</OperandPtr
>
69 <FieldName1
> f3
</FieldName1
>
70 <CompOp
> Equals
</CompOp
>
71 <OperandPtr
> VALUE
</OperandPtr
>
76 echo explain plan
select * from t1 where f1
= 2 or f2
=2 or f3
=2;
79 <ScanType
> TableScan
</ScanType
>
87 <FieldName1
> f1
</FieldName1
>
88 <CompOp
> Equals
</CompOp
>
89 <OperandPtr
> VALUE
</OperandPtr
>
94 <FieldName1
> f2
</FieldName1
>
95 <CompOp
> Equals
</CompOp
>
96 <OperandPtr
> VALUE
</OperandPtr
>
103 <FieldName1
> f3
</FieldName1
>
104 <CompOp
> Equals
</CompOp
>
105 <OperandPtr
> VALUE
</OperandPtr
>
110 echo explain plan
select * from t1 where f1
= 2 and f2
=2 or f3
=2;
113 <ScanType
> TableScan
</ScanType
>
121 <FieldName1
> f1
</FieldName1
>
122 <CompOp
> Equals
</CompOp
>
123 <OperandPtr
> VALUE
</OperandPtr
>
128 <FieldName1
> f2
</FieldName1
>
129 <CompOp
> Equals
</CompOp
>
130 <OperandPtr
> VALUE
</OperandPtr
>
137 <FieldName1
> f3
</FieldName1
>
138 <CompOp
> Equals
</CompOp
>
139 <OperandPtr
> VALUE
</OperandPtr
>
144 echo explain plan
select * from t1 where f1
= 2 and f2
=2 and not
(f3
=2);
147 <ScanType
> TableScan
</ScanType
>
155 <FieldName1
> f1
</FieldName1
>
156 <CompOp
> Equals
</CompOp
>
157 <OperandPtr
> VALUE
</OperandPtr
>
162 <FieldName1
> f2
</FieldName1
>
163 <CompOp
> Equals
</CompOp
>
164 <OperandPtr
> VALUE
</OperandPtr
>
174 <FieldName1
> f3
</FieldName1
>
175 <CompOp
> Equals
</CompOp
>
176 <OperandPtr
> VALUE
</OperandPtr
>
183 echo explain plan
select * from t1 where f1
in(1,2,3) and f2
=2 and not
(f3
=2);
186 <ScanType
> TableScan
</ScanType
>
200 <FieldName1
> f1
</FieldName1
>
201 <CompOp
> Equals
</CompOp
>
202 <OperandPtr
> VALUE
</OperandPtr
>
207 <FieldName1
> f1
</FieldName1
>
208 <CompOp
> Equals
</CompOp
>
209 <OperandPtr
> VALUE
</OperandPtr
>
216 <FieldName1
> f1
</FieldName1
>
217 <CompOp
> Equals
</CompOp
>
218 <OperandPtr
> VALUE
</OperandPtr
>
225 <FieldName1
> f2
</FieldName1
>
226 <CompOp
> Equals
</CompOp
>
227 <OperandPtr
> VALUE
</OperandPtr
>
237 <FieldName1
> f3
</FieldName1
>
238 <CompOp
> Equals
</CompOp
>
239 <OperandPtr
> VALUE
</OperandPtr
>
246 echo explain plan
select * from t1 where f1
in(1,2,3) and
(f2 between
2 and
4) and not
(f3
=2);
249 <ScanType
> TableScan
</ScanType
>
263 <FieldName1
> f1
</FieldName1
>
264 <CompOp
> Equals
</CompOp
>
265 <OperandPtr
> VALUE
</OperandPtr
>
270 <FieldName1
> f1
</FieldName1
>
271 <CompOp
> Equals
</CompOp
>
272 <OperandPtr
> VALUE
</OperandPtr
>
279 <FieldName1
> f1
</FieldName1
>
280 <CompOp
> Equals
</CompOp
>
281 <OperandPtr
> VALUE
</OperandPtr
>
288 <FieldName1
> f2
</FieldName1
>
289 <CompOp
> GreaterThanEquals
</CompOp
>
290 <OperandPtr
> VALUE
</OperandPtr
>
291 <Comp2Op
> LessThanEquals
</Comp2Op
>
292 <Operand2Ptr
> VALUE
</Operand2Ptr
>
302 <FieldName1
> f3
</FieldName1
>
303 <CompOp
> Equals
</CompOp
>
304 <OperandPtr
> VALUE
</OperandPtr
>
311 echo explain plan
select * from t1 where f1
in(1,2,3) or
(f2 between
2 and
4) or not
(f3
=2);
314 <ScanType
> TableScan
</ScanType
>
328 <FieldName1
> f1
</FieldName1
>
329 <CompOp
> Equals
</CompOp
>
330 <OperandPtr
> VALUE
</OperandPtr
>
335 <FieldName1
> f1
</FieldName1
>
336 <CompOp
> Equals
</CompOp
>
337 <OperandPtr
> VALUE
</OperandPtr
>
344 <FieldName1
> f1
</FieldName1
>
345 <CompOp
> Equals
</CompOp
>
346 <OperandPtr
> VALUE
</OperandPtr
>
353 <FieldName1
> f2
</FieldName1
>
354 <CompOp
> GreaterThanEquals
</CompOp
>
355 <OperandPtr
> VALUE
</OperandPtr
>
356 <Comp2Op
> LessThanEquals
</Comp2Op
>
357 <Operand2Ptr
> VALUE
</Operand2Ptr
>
367 <FieldName1
> f3
</FieldName1
>
368 <CompOp
> Equals
</CompOp
>
369 <OperandPtr
> VALUE
</OperandPtr
>
376 echo explain plan
select * from t1 where f1
in(1,2,3) or
(f2 between
2 and
4) and not
(f3
=2);
379 <ScanType
> TableScan
</ScanType
>
390 <FieldName1
> f1
</FieldName1
>
391 <CompOp
> Equals
</CompOp
>
392 <OperandPtr
> VALUE
</OperandPtr
>
397 <FieldName1
> f1
</FieldName1
>
398 <CompOp
> Equals
</CompOp
>
399 <OperandPtr
> VALUE
</OperandPtr
>
406 <FieldName1
> f1
</FieldName1
>
407 <CompOp
> Equals
</CompOp
>
408 <OperandPtr
> VALUE
</OperandPtr
>
418 <FieldName1
> f2
</FieldName1
>
419 <CompOp
> GreaterThanEquals
</CompOp
>
420 <OperandPtr
> VALUE
</OperandPtr
>
421 <Comp2Op
> LessThanEquals
</Comp2Op
>
422 <Operand2Ptr
> VALUE
</Operand2Ptr
>
430 <FieldName1
> f3
</FieldName1
>
431 <CompOp
> Equals
</CompOp
>
432 <OperandPtr
> VALUE
</OperandPtr
>