1 echo create table t1
(f1 tinyint
, f2 smallint
, f3 int
, f4 bigint
, f5 char
(10), f6 varchar
(10), f7
date, f8
time);
3 echo create index hashtiny on t1
(f1
) hash;
5 echo create index hashsmall on t1
(f2
) hash;
7 echo create index hashint on t1
(f3
) hash;
9 echo create index hashbig on t1
(f4
) hash;
11 echo create index hashchar on t1
(f5
) hash;
13 echo create index hashvarchar on t1
(f6
) hash;
15 echo create index hashdate on t1
(f7
) hash;
17 echo create index hashtime on t1
(f8
) hash;
19 echo explain plan
select * from t1
;
22 <ScanType
> TableScan
</ScanType
>
24 echo explain plan
select * from t1 where f1
= 2 and f1
in (0,2,4) ;
27 <ScanType
> TableScan
</ScanType
>
32 <FieldName1
> f1
</FieldName1
>
33 <CompOp
> Equals
</CompOp
>
34 <OperandPtr
> VALUE
</OperandPtr
>
45 <FieldName1
> f1
</FieldName1
>
46 <CompOp
> Equals
</CompOp
>
47 <OperandPtr
> VALUE
</OperandPtr
>
52 <FieldName1
> f1
</FieldName1
>
53 <CompOp
> Equals
</CompOp
>
54 <OperandPtr
> VALUE
</OperandPtr
>
61 <FieldName1
> f1
</FieldName1
>
62 <CompOp
> Equals
</CompOp
>
63 <OperandPtr
> VALUE
</OperandPtr
>
70 echo explain plan
select * from t1 where f1
= 3 or
(f1 between
2 and
4);
73 <ScanType
> TableScan
</ScanType
>
78 <FieldName1
> f1
</FieldName1
>
79 <CompOp
> Equals
</CompOp
>
80 <OperandPtr
> VALUE
</OperandPtr
>
85 <FieldName1
> f1
</FieldName1
>
86 <CompOp
> GreaterThanEquals
</CompOp
>
87 <OperandPtr
> VALUE
</OperandPtr
>
88 <Comp2Op
> LessThanEquals
</Comp2Op
>
89 <Operand2Ptr
> VALUE
</Operand2Ptr
>
94 echo explain plan
select * from t1 where f2
in (0,2,4) and f2
= 2;
97 <ScanType
> TableScan
</ScanType
>
108 <FieldName1
> f2
</FieldName1
>
109 <CompOp
> Equals
</CompOp
>
110 <OperandPtr
> VALUE
</OperandPtr
>
115 <FieldName1
> f2
</FieldName1
>
116 <CompOp
> Equals
</CompOp
>
117 <OperandPtr
> VALUE
</OperandPtr
>
124 <FieldName1
> f2
</FieldName1
>
125 <CompOp
> Equals
</CompOp
>
126 <OperandPtr
> VALUE
</OperandPtr
>
133 <FieldName1
> f2
</FieldName1
>
134 <CompOp
> Equals
</CompOp
>
135 <OperandPtr
> VALUE
</OperandPtr
>
140 echo explain plan
select * from t1 where
(f2 between
2 and
4) or f2
= 2;
143 <ScanType
> TableScan
</ScanType
>
148 <FieldName1
> f2
</FieldName1
>
149 <CompOp
> GreaterThanEquals
</CompOp
>
150 <OperandPtr
> VALUE
</OperandPtr
>
151 <Comp2Op
> LessThanEquals
</Comp2Op
>
152 <Operand2Ptr
> VALUE
</Operand2Ptr
>
157 <FieldName1
> f2
</FieldName1
>
158 <CompOp
> Equals
</CompOp
>
159 <OperandPtr
> VALUE
</OperandPtr
>
164 echo explain plan
select * from t1 where f3
= 2 or f3
in (1,2) ;
167 <ScanType
> TableScan
</ScanType
>
172 <FieldName1
> f3
</FieldName1
>
173 <CompOp
> Equals
</CompOp
>
174 <OperandPtr
> VALUE
</OperandPtr
>
182 <FieldName1
> f3
</FieldName1
>
183 <CompOp
> Equals
</CompOp
>
184 <OperandPtr
> VALUE
</OperandPtr
>
189 <FieldName1
> f3
</FieldName1
>
190 <CompOp
> Equals
</CompOp
>
191 <OperandPtr
> VALUE
</OperandPtr
>
198 echo explain plan
select * from t1 where f3
= 2 and f3 between
1 and
4;
201 <ScanType
> HashScan
</ScanType
>
206 <FieldName1
> f3
</FieldName1
>
207 <CompOp
> Equals
</CompOp
>
208 <OperandPtr
> VALUE
</OperandPtr
>
213 <FieldName1
> f3
</FieldName1
>
214 <CompOp
> GreaterThanEquals
</CompOp
>
215 <OperandPtr
> VALUE
</OperandPtr
>
216 <Comp2Op
> LessThanEquals
</Comp2Op
>
217 <Operand2Ptr
> VALUE
</Operand2Ptr
>
222 echo explain plan
select * from t1 where f4
in (1,2) or f4
= 2;
225 <ScanType
> TableScan
</ScanType
>
233 <FieldName1
> f4
</FieldName1
>
234 <CompOp
> Equals
</CompOp
>
235 <OperandPtr
> VALUE
</OperandPtr
>
240 <FieldName1
> f4
</FieldName1
>
241 <CompOp
> Equals
</CompOp
>
242 <OperandPtr
> VALUE
</OperandPtr
>
249 <FieldName1
> f4
</FieldName1
>
250 <CompOp
> Equals
</CompOp
>
251 <OperandPtr
> VALUE
</OperandPtr
>
256 echo explain plan
select * from t1 where
(f4 between
1 and
4) and f4
= 2;
259 <ScanType
> HashScan
</ScanType
>
264 <FieldName1
> f4
</FieldName1
>
265 <CompOp
> GreaterThanEquals
</CompOp
>
266 <OperandPtr
> VALUE
</OperandPtr
>
267 <Comp2Op
> LessThanEquals
</Comp2Op
>
268 <Operand2Ptr
> VALUE
</Operand2Ptr
>
273 <FieldName1
> f4
</FieldName1
>
274 <CompOp
> Equals
</CompOp
>
275 <OperandPtr
> VALUE
</OperandPtr
>
280 echo explain plan
select * from t1 where f5
in ('1','2') and f5
= '1';
283 <ScanType
> TableScan
</ScanType
>
291 <FieldName1
> f5
</FieldName1
>
292 <CompOp
> Equals
</CompOp
>
293 <OperandPtr
> VALUE
</OperandPtr
>
298 <FieldName1
> f5
</FieldName1
>
299 <CompOp
> Equals
</CompOp
>
300 <OperandPtr
> VALUE
</OperandPtr
>
307 <FieldName1
> f5
</FieldName1
>
308 <CompOp
> Equals
</CompOp
>
309 <OperandPtr
> VALUE
</OperandPtr
>
314 echo explain plan
select * from t1 where f5 like
'N%' or f5
= 'NP';
317 <ScanType
> TableScan
</ScanType
>
322 <FieldName1
> f5
</FieldName1
>
323 <CompOp
> Like
</CompOp
>
324 <OperandPtr
> VALUE
</OperandPtr
>
329 <FieldName1
> f5
</FieldName1
>
330 <CompOp
> Equals
</CompOp
>
331 <OperandPtr
> VALUE
</OperandPtr
>
336 echo explain plan
select * from t1 where f5 like
'N_h' and f6
= 'Nihar';
339 <ScanType
> HashScan
</ScanType
>
344 <FieldName1
> f5
</FieldName1
>
345 <CompOp
> Like
</CompOp
>
346 <OperandPtr
> VALUE
</OperandPtr
>
351 <FieldName1
> f6
</FieldName1
>
352 <CompOp
> Equals
</CompOp
>
353 <OperandPtr
> VALUE
</OperandPtr
>
358 echo explain plan
select * from t1 where f6
= '1' or f6
in ('1','2');
361 <ScanType
> TableScan
</ScanType
>
366 <FieldName1
> f6
</FieldName1
>
367 <CompOp
> Equals
</CompOp
>
368 <OperandPtr
> VALUE
</OperandPtr
>
376 <FieldName1
> f6
</FieldName1
>
377 <CompOp
> Equals
</CompOp
>
378 <OperandPtr
> VALUE
</OperandPtr
>
383 <FieldName1
> f6
</FieldName1
>
384 <CompOp
> Equals
</CompOp
>
385 <OperandPtr
> VALUE
</OperandPtr
>
392 echo explain plan
select * from t1 where f6
= 'NP' or f6 like
'N%';
395 <ScanType
> TableScan
</ScanType
>
400 <FieldName1
> f6
</FieldName1
>
401 <CompOp
> Equals
</CompOp
>
402 <OperandPtr
> VALUE
</OperandPtr
>
407 <FieldName1
> f6
</FieldName1
>
408 <CompOp
> Like
</CompOp
>
409 <OperandPtr
> VALUE
</OperandPtr
>
414 echo explain plan
select * from t1 where f6 like
'N_h' and f6
= 'NIHAR';
417 <ScanType
> HashScan
</ScanType
>
422 <FieldName1
> f6
</FieldName1
>
423 <CompOp
> Like
</CompOp
>
424 <OperandPtr
> VALUE
</OperandPtr
>
429 <FieldName1
> f6
</FieldName1
>
430 <CompOp
> Equals
</CompOp
>
431 <OperandPtr
> VALUE
</OperandPtr
>
436 echo explain plan
select * from t1 where f7
= '2003-11-12' or f7
in ('2003-11-12','2003-11-15');
439 <ScanType
> TableScan
</ScanType
>
444 <FieldName1
> f7
</FieldName1
>
445 <CompOp
> Equals
</CompOp
>
446 <OperandPtr
> VALUE
</OperandPtr
>
454 <FieldName1
> f7
</FieldName1
>
455 <CompOp
> Equals
</CompOp
>
456 <OperandPtr
> VALUE
</OperandPtr
>
461 <FieldName1
> f7
</FieldName1
>
462 <CompOp
> Equals
</CompOp
>
463 <OperandPtr
> VALUE
</OperandPtr
>
470 echo explain plan
select * from t1 where f7
= '2003-11-15' and
(f7 between
'2003-11-12' and
'2003-11-15');
473 <ScanType
> HashScan
</ScanType
>
478 <FieldName1
> f7
</FieldName1
>
479 <CompOp
> Equals
</CompOp
>
480 <OperandPtr
> VALUE
</OperandPtr
>
485 <FieldName1
> f7
</FieldName1
>
486 <CompOp
> GreaterThanEquals
</CompOp
>
487 <OperandPtr
> VALUE
</OperandPtr
>
488 <Comp2Op
> LessThanEquals
</Comp2Op
>
489 <Operand2Ptr
> VALUE
</Operand2Ptr
>
494 echo explain plan
select * from t1 where f8
in ('03:21:00','03:21:08') and f8
= '03:21:08';
497 <ScanType
> TableScan
</ScanType
>
505 <FieldName1
> f8
</FieldName1
>
506 <CompOp
> Equals
</CompOp
>
507 <OperandPtr
> VALUE
</OperandPtr
>
512 <FieldName1
> f8
</FieldName1
>
513 <CompOp
> Equals
</CompOp
>
514 <OperandPtr
> VALUE
</OperandPtr
>
521 <FieldName1
> f8
</FieldName1
>
522 <CompOp
> Equals
</CompOp
>
523 <OperandPtr
> VALUE
</OperandPtr
>
528 echo explain plan
select * from t1 where
(f8 between
'03:21:00' and
'03:21:08') or f8
='03:21:00';
531 <ScanType
> TableScan
</ScanType
>
536 <FieldName1
> f8
</FieldName1
>
537 <CompOp
> GreaterThanEquals
</CompOp
>
538 <OperandPtr
> VALUE
</OperandPtr
>
539 <Comp2Op
> LessThanEquals
</Comp2Op
>
540 <Operand2Ptr
> VALUE
</Operand2Ptr
>
545 <FieldName1
> f8
</FieldName1
>
546 <CompOp
> Equals
</CompOp
>
547 <OperandPtr
> VALUE
</OperandPtr
>