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
in (0,2,4);
27 <ScanType
> TableScan
</ScanType
>
35 <FieldName1
> f1
</FieldName1
>
36 <CompOp
> Equals
</CompOp
>
37 <OperandPtr
> VALUE
</OperandPtr
>
42 <FieldName1
> f1
</FieldName1
>
43 <CompOp
> Equals
</CompOp
>
44 <OperandPtr
> VALUE
</OperandPtr
>
51 <FieldName1
> f1
</FieldName1
>
52 <CompOp
> Equals
</CompOp
>
53 <OperandPtr
> VALUE
</OperandPtr
>
58 echo explain plan
select * from t1 where f1 between
2 and
4;
61 <ScanType
> TableScan
</ScanType
>
63 <FieldName1
> f1
</FieldName1
>
64 <CompOp
> GreaterThanEquals
</CompOp
>
65 <OperandPtr
> VALUE
</OperandPtr
>
66 <Comp2Op
> LessThanEquals
</Comp2Op
>
67 <Operand2Ptr
> VALUE
</Operand2Ptr
>
70 echo explain plan
select * from t1 where f2
in (0,2,4);
73 <ScanType
> TableScan
</ScanType
>
81 <FieldName1
> f2
</FieldName1
>
82 <CompOp
> Equals
</CompOp
>
83 <OperandPtr
> VALUE
</OperandPtr
>
88 <FieldName1
> f2
</FieldName1
>
89 <CompOp
> Equals
</CompOp
>
90 <OperandPtr
> VALUE
</OperandPtr
>
97 <FieldName1
> f2
</FieldName1
>
98 <CompOp
> Equals
</CompOp
>
99 <OperandPtr
> VALUE
</OperandPtr
>
104 echo explain plan
select * from t1 where f2 between
2 and
4;
107 <ScanType
> TableScan
</ScanType
>
109 <FieldName1
> f2
</FieldName1
>
110 <CompOp
> GreaterThanEquals
</CompOp
>
111 <OperandPtr
> VALUE
</OperandPtr
>
112 <Comp2Op
> LessThanEquals
</Comp2Op
>
113 <Operand2Ptr
> VALUE
</Operand2Ptr
>
116 echo explain plan
select * from t1 where f3
in (1,2);
119 <ScanType
> TableScan
</ScanType
>
124 <FieldName1
> f3
</FieldName1
>
125 <CompOp
> Equals
</CompOp
>
126 <OperandPtr
> VALUE
</OperandPtr
>
131 <FieldName1
> f3
</FieldName1
>
132 <CompOp
> Equals
</CompOp
>
133 <OperandPtr
> VALUE
</OperandPtr
>
138 echo explain plan
select * from t1 where f3 between
1 and
4;
141 <ScanType
> TableScan
</ScanType
>
143 <FieldName1
> f3
</FieldName1
>
144 <CompOp
> GreaterThanEquals
</CompOp
>
145 <OperandPtr
> VALUE
</OperandPtr
>
146 <Comp2Op
> LessThanEquals
</Comp2Op
>
147 <Operand2Ptr
> VALUE
</Operand2Ptr
>
150 echo explain plan
select * from t1 where f4
in (1,2);
153 <ScanType
> TableScan
</ScanType
>
158 <FieldName1
> f4
</FieldName1
>
159 <CompOp
> Equals
</CompOp
>
160 <OperandPtr
> VALUE
</OperandPtr
>
165 <FieldName1
> f4
</FieldName1
>
166 <CompOp
> Equals
</CompOp
>
167 <OperandPtr
> VALUE
</OperandPtr
>
172 echo explain plan
select * from t1 where f4 between
1 and
4;
175 <ScanType
> TableScan
</ScanType
>
177 <FieldName1
> f4
</FieldName1
>
178 <CompOp
> GreaterThanEquals
</CompOp
>
179 <OperandPtr
> VALUE
</OperandPtr
>
180 <Comp2Op
> LessThanEquals
</Comp2Op
>
181 <Operand2Ptr
> VALUE
</Operand2Ptr
>
184 echo explain plan
select * from t1 where f5
in ('1','2');
187 <ScanType
> TableScan
</ScanType
>
192 <FieldName1
> f5
</FieldName1
>
193 <CompOp
> Equals
</CompOp
>
194 <OperandPtr
> VALUE
</OperandPtr
>
199 <FieldName1
> f5
</FieldName1
>
200 <CompOp
> Equals
</CompOp
>
201 <OperandPtr
> VALUE
</OperandPtr
>
206 echo explain plan
select * from t1 where f5 like
'N%';
209 <ScanType
> TableScan
</ScanType
>
211 <FieldName1
> f5
</FieldName1
>
212 <CompOp
> Like
</CompOp
>
213 <OperandPtr
> VALUE
</OperandPtr
>
216 echo explain plan
select * from t1 where f5 like
'N_h';
219 <ScanType
> TableScan
</ScanType
>
221 <FieldName1
> f5
</FieldName1
>
222 <CompOp
> Like
</CompOp
>
223 <OperandPtr
> VALUE
</OperandPtr
>
226 echo explain plan
select * from t1 where f6
in ('1','2');
229 <ScanType
> TableScan
</ScanType
>
234 <FieldName1
> f6
</FieldName1
>
235 <CompOp
> Equals
</CompOp
>
236 <OperandPtr
> VALUE
</OperandPtr
>
241 <FieldName1
> f6
</FieldName1
>
242 <CompOp
> Equals
</CompOp
>
243 <OperandPtr
> VALUE
</OperandPtr
>
248 echo explain plan
select * from t1 where f6 like
'N%';
251 <ScanType
> TableScan
</ScanType
>
253 <FieldName1
> f6
</FieldName1
>
254 <CompOp
> Like
</CompOp
>
255 <OperandPtr
> VALUE
</OperandPtr
>
258 echo explain plan
select * from t1 where f6 like
'N_h';
261 <ScanType
> TableScan
</ScanType
>
263 <FieldName1
> f6
</FieldName1
>
264 <CompOp
> Like
</CompOp
>
265 <OperandPtr
> VALUE
</OperandPtr
>
268 echo explain plan
select * from t1 where f7
in ('2003-11-12','2003-11-15');
271 <ScanType
> TableScan
</ScanType
>
276 <FieldName1
> f7
</FieldName1
>
277 <CompOp
> Equals
</CompOp
>
278 <OperandPtr
> VALUE
</OperandPtr
>
283 <FieldName1
> f7
</FieldName1
>
284 <CompOp
> Equals
</CompOp
>
285 <OperandPtr
> VALUE
</OperandPtr
>
290 echo explain plan
select * from t1 where f7 between
'2003-11-12' and
'2003-11-15';
293 <ScanType
> TableScan
</ScanType
>
295 <FieldName1
> f7
</FieldName1
>
296 <CompOp
> GreaterThanEquals
</CompOp
>
297 <OperandPtr
> VALUE
</OperandPtr
>
298 <Comp2Op
> LessThanEquals
</Comp2Op
>
299 <Operand2Ptr
> VALUE
</Operand2Ptr
>
302 echo explain plan
select * from t1 where f8
in ('03:21:00','03:21:08');
305 <ScanType
> TableScan
</ScanType
>
310 <FieldName1
> f8
</FieldName1
>
311 <CompOp
> Equals
</CompOp
>
312 <OperandPtr
> VALUE
</OperandPtr
>
317 <FieldName1
> f8
</FieldName1
>
318 <CompOp
> Equals
</CompOp
>
319 <OperandPtr
> VALUE
</OperandPtr
>
324 echo explain plan
select * from t1 where f8 between
'03:21:00' and
'03:21:08';
327 <ScanType
> TableScan
</ScanType
>
329 <FieldName1
> f8
</FieldName1
>
330 <CompOp
> GreaterThanEquals
</CompOp
>
331 <OperandPtr
> VALUE
</OperandPtr
>
332 <Comp2Op
> LessThanEquals
</Comp2Op
>
333 <Operand2Ptr
> VALUE
</Operand2Ptr
>