1 echo create table t1
(f1 tinyint
, f2 smallint
, f3 int
, f4 bigint
, f5 char
(10), f6 varchar
(10), f7
date, f8
time, f9 float
, f10 double
, f11 timestamp
);
3 echo create index treetiny on t1
(f1
) tree
;
5 echo create index treesmall on t1
(f2
) tree
;
7 echo create index treeint on t1
(f3
) tree
;
9 echo create index treebig on t1
(f4
) tree
;
11 echo create index treechar on t1
(f5
) tree
;
13 echo create index treevarchar on t1
(f6
) tree
;
15 echo create index treedate on t1
(f7
) tree
;
17 echo create index treetime on t1
(f8
) tree
;
19 echo create index treefloat on t1
(f9
) tree
;
21 echo create index treedouble on t1
(f10
) tree
;
23 echo create index treetimestamp on t1
(f11
) tree
;
25 echo explain plan
select * from t1
;
28 <ScanType
> TableScan
</ScanType
>
30 echo explain plan
select * from t1 where f1
in (0,2,4);
33 <ScanType
> TableScan
</ScanType
>
41 <FieldName1
> f1
</FieldName1
>
42 <CompOp
> Equals
</CompOp
>
43 <OperandPtr
> VALUE
</OperandPtr
>
48 <FieldName1
> f1
</FieldName1
>
49 <CompOp
> Equals
</CompOp
>
50 <OperandPtr
> VALUE
</OperandPtr
>
57 <FieldName1
> f1
</FieldName1
>
58 <CompOp
> Equals
</CompOp
>
59 <OperandPtr
> VALUE
</OperandPtr
>
64 echo explain plan
select * from t1 where f1 between
2 and
4;
67 <ScanType
> TreeScan
</ScanType
>
69 <FieldName1
> f1
</FieldName1
>
70 <CompOp
> GreaterThanEquals
</CompOp
>
71 <OperandPtr
> VALUE
</OperandPtr
>
72 <Comp2Op
> LessThanEquals
</Comp2Op
>
73 <Operand2Ptr
> VALUE
</Operand2Ptr
>
76 echo explain plan
select * from t1 where f2
in (0,2,4);
79 <ScanType
> TableScan
</ScanType
>
87 <FieldName1
> f2
</FieldName1
>
88 <CompOp
> Equals
</CompOp
>
89 <OperandPtr
> VALUE
</OperandPtr
>
94 <FieldName1
> f2
</FieldName1
>
95 <CompOp
> Equals
</CompOp
>
96 <OperandPtr
> VALUE
</OperandPtr
>
103 <FieldName1
> f2
</FieldName1
>
104 <CompOp
> Equals
</CompOp
>
105 <OperandPtr
> VALUE
</OperandPtr
>
110 echo explain plan
select * from t1 where f2 between
2 and
4;
113 <ScanType
> TreeScan
</ScanType
>
115 <FieldName1
> f2
</FieldName1
>
116 <CompOp
> GreaterThanEquals
</CompOp
>
117 <OperandPtr
> VALUE
</OperandPtr
>
118 <Comp2Op
> LessThanEquals
</Comp2Op
>
119 <Operand2Ptr
> VALUE
</Operand2Ptr
>
122 echo explain plan
select * from t1 where f3
in (1,2);
125 <ScanType
> TableScan
</ScanType
>
130 <FieldName1
> f3
</FieldName1
>
131 <CompOp
> Equals
</CompOp
>
132 <OperandPtr
> VALUE
</OperandPtr
>
137 <FieldName1
> f3
</FieldName1
>
138 <CompOp
> Equals
</CompOp
>
139 <OperandPtr
> VALUE
</OperandPtr
>
144 echo explain plan
select * from t1 where f3 between
1 and
4;
147 <ScanType
> TreeScan
</ScanType
>
149 <FieldName1
> f3
</FieldName1
>
150 <CompOp
> GreaterThanEquals
</CompOp
>
151 <OperandPtr
> VALUE
</OperandPtr
>
152 <Comp2Op
> LessThanEquals
</Comp2Op
>
153 <Operand2Ptr
> VALUE
</Operand2Ptr
>
156 echo explain plan
select * from t1 where f4
in (1,2);
159 <ScanType
> TableScan
</ScanType
>
164 <FieldName1
> f4
</FieldName1
>
165 <CompOp
> Equals
</CompOp
>
166 <OperandPtr
> VALUE
</OperandPtr
>
171 <FieldName1
> f4
</FieldName1
>
172 <CompOp
> Equals
</CompOp
>
173 <OperandPtr
> VALUE
</OperandPtr
>
178 echo explain plan
select * from t1 where f4 between
1 and
4;
181 <ScanType
> TreeScan
</ScanType
>
183 <FieldName1
> f4
</FieldName1
>
184 <CompOp
> GreaterThanEquals
</CompOp
>
185 <OperandPtr
> VALUE
</OperandPtr
>
186 <Comp2Op
> LessThanEquals
</Comp2Op
>
187 <Operand2Ptr
> VALUE
</Operand2Ptr
>
190 echo explain plan
select * from t1 where f5
in ('1','2');
193 <ScanType
> TableScan
</ScanType
>
198 <FieldName1
> f5
</FieldName1
>
199 <CompOp
> Equals
</CompOp
>
200 <OperandPtr
> VALUE
</OperandPtr
>
205 <FieldName1
> f5
</FieldName1
>
206 <CompOp
> Equals
</CompOp
>
207 <OperandPtr
> VALUE
</OperandPtr
>
212 echo explain plan
select * from t1 where f5 like
'N%';
215 <ScanType
> TableScan
</ScanType
>
217 <FieldName1
> f5
</FieldName1
>
218 <CompOp
> Like
</CompOp
>
219 <OperandPtr
> VALUE
</OperandPtr
>
222 echo explain plan
select * from t1 where f5 like
'N_h';
225 <ScanType
> TableScan
</ScanType
>
227 <FieldName1
> f5
</FieldName1
>
228 <CompOp
> Like
</CompOp
>
229 <OperandPtr
> VALUE
</OperandPtr
>
232 echo explain plan
select * from t1 where f6
in ('1','2');
235 <ScanType
> TableScan
</ScanType
>
240 <FieldName1
> f6
</FieldName1
>
241 <CompOp
> Equals
</CompOp
>
242 <OperandPtr
> VALUE
</OperandPtr
>
247 <FieldName1
> f6
</FieldName1
>
248 <CompOp
> Equals
</CompOp
>
249 <OperandPtr
> VALUE
</OperandPtr
>
254 echo explain plan
select * from t1 where f6 like
'N%';
257 <ScanType
> TableScan
</ScanType
>
259 <FieldName1
> f6
</FieldName1
>
260 <CompOp
> Like
</CompOp
>
261 <OperandPtr
> VALUE
</OperandPtr
>
264 echo explain plan
select * from t1 where f6 like
'N_h';
267 <ScanType
> TableScan
</ScanType
>
269 <FieldName1
> f6
</FieldName1
>
270 <CompOp
> Like
</CompOp
>
271 <OperandPtr
> VALUE
</OperandPtr
>
274 echo explain plan
select * from t1 where f7
in ('2003-11-12','2003-11-15');
277 <ScanType
> TableScan
</ScanType
>
282 <FieldName1
> f7
</FieldName1
>
283 <CompOp
> Equals
</CompOp
>
284 <OperandPtr
> VALUE
</OperandPtr
>
289 <FieldName1
> f7
</FieldName1
>
290 <CompOp
> Equals
</CompOp
>
291 <OperandPtr
> VALUE
</OperandPtr
>
296 echo explain plan
select * from t1 where f7 between
'2003-11-12' and
'2003-11-15';
299 <ScanType
> TreeScan
</ScanType
>
301 <FieldName1
> f7
</FieldName1
>
302 <CompOp
> GreaterThanEquals
</CompOp
>
303 <OperandPtr
> VALUE
</OperandPtr
>
304 <Comp2Op
> LessThanEquals
</Comp2Op
>
305 <Operand2Ptr
> VALUE
</Operand2Ptr
>
308 echo explain plan
select * from t1 where f8
in ('03:21:00','03:21:08');
311 <ScanType
> TableScan
</ScanType
>
316 <FieldName1
> f8
</FieldName1
>
317 <CompOp
> Equals
</CompOp
>
318 <OperandPtr
> VALUE
</OperandPtr
>
323 <FieldName1
> f8
</FieldName1
>
324 <CompOp
> Equals
</CompOp
>
325 <OperandPtr
> VALUE
</OperandPtr
>
330 echo explain plan
select * from t1 where f8 between
'03:21:00' and
'03:21:08';
333 <ScanType
> TreeScan
</ScanType
>
335 <FieldName1
> f8
</FieldName1
>
336 <CompOp
> GreaterThanEquals
</CompOp
>
337 <OperandPtr
> VALUE
</OperandPtr
>
338 <Comp2Op
> LessThanEquals
</Comp2Op
>
339 <Operand2Ptr
> VALUE
</Operand2Ptr
>
342 echo explain plan
select * from t1 where f9
in ('2.7','3.7');
345 <ScanType
> TableScan
</ScanType
>
350 <FieldName1
> f9
</FieldName1
>
351 <CompOp
> Equals
</CompOp
>
352 <OperandPtr
> VALUE
</OperandPtr
>
357 <FieldName1
> f9
</FieldName1
>
358 <CompOp
> Equals
</CompOp
>
359 <OperandPtr
> VALUE
</OperandPtr
>
364 echo explain plan
select * from t1 where f9 between
'2.7' and
'3.7';
367 <ScanType
> TreeScan
</ScanType
>
369 <FieldName1
> f9
</FieldName1
>
370 <CompOp
> GreaterThanEquals
</CompOp
>
371 <OperandPtr
> VALUE
</OperandPtr
>
372 <Comp2Op
> LessThanEquals
</Comp2Op
>
373 <Operand2Ptr
> VALUE
</Operand2Ptr
>
376 echo explain plan
select * from t1 where f10
in ('2.7','3.7');
379 <ScanType
> TableScan
</ScanType
>
384 <FieldName1
> f10
</FieldName1
>
385 <CompOp
> Equals
</CompOp
>
386 <OperandPtr
> VALUE
</OperandPtr
>
391 <FieldName1
> f10
</FieldName1
>
392 <CompOp
> Equals
</CompOp
>
393 <OperandPtr
> VALUE
</OperandPtr
>
398 echo explain plan
select * from t1 where f10 between
'2.7' and
'3.7';
401 <ScanType
> TreeScan
</ScanType
>
403 <FieldName1
> f10
</FieldName1
>
404 <CompOp
> GreaterThanEquals
</CompOp
>
405 <OperandPtr
> VALUE
</OperandPtr
>
406 <Comp2Op
> LessThanEquals
</Comp2Op
>
407 <Operand2Ptr
> VALUE
</Operand2Ptr
>
410 echo explain plan
select * from t1 where f11
in ('2003-11-12 01:01:01','2003-11-18 01:01:01');
413 <ScanType
> TableScan
</ScanType
>
418 <FieldName1
> f11
</FieldName1
>
419 <CompOp
> Equals
</CompOp
>
420 <OperandPtr
> VALUE
</OperandPtr
>
425 <FieldName1
> f11
</FieldName1
>
426 <CompOp
> Equals
</CompOp
>
427 <OperandPtr
> VALUE
</OperandPtr
>
432 echo explain plan
select * from t1 where f11 between
'2003-11-12 01:01:01' and
'2003-11-18 01:01:01';
435 <ScanType
> TreeScan
</ScanType
>
437 <FieldName1
> f11
</FieldName1
>
438 <CompOp
> GreaterThanEquals
</CompOp
>
439 <OperandPtr
> VALUE
</OperandPtr
>
440 <Comp2Op
> LessThanEquals
</Comp2Op
>
441 <Operand2Ptr
> VALUE
</Operand2Ptr
>