1 echo create table t1
(f1 int
, f2 int
, f3 varchar
(20),f4
date);
3 echo create index idx on t1
(f3
,f4
,f2
) hash;
5 echo explain plan
select * from t1 where f2 is null
;
8 <ScanType
> TableScan
</ScanType
>
10 <FieldName1
> f2
</FieldName1
>
11 <CompOp
> isNULL
</CompOp
>
14 echo explain plan
select * from t1 where f2 is not null
;
17 <ScanType
> TableScan
</ScanType
>
19 <FieldName1
> f2
</FieldName1
>
20 <CompOp
> isNULL
</CompOp
>
23 echo explain plan
select * from t1 where f2 is null and f3 is not null
;
26 <ScanType
> TableScan
</ScanType
>
31 <FieldName1
> f2
</FieldName1
>
32 <CompOp
> isNULL
</CompOp
>
37 <FieldName1
> f3
</FieldName1
>
38 <CompOp
> isNULL
</CompOp
>
43 echo explain plan
select * from t1 where f1 is null or f2 is not null or f3 is null or f4 is not null
;
46 <ScanType
> TableScan
</ScanType
>
57 <FieldName1
> f1
</FieldName1
>
58 <CompOp
> isNULL
</CompOp
>
63 <FieldName1
> f2
</FieldName1
>
64 <CompOp
> isNULL
</CompOp
>
71 <FieldName1
> f3
</FieldName1
>
72 <CompOp
> isNULL
</CompOp
>
79 <FieldName1
> f4
</FieldName1
>
80 <CompOp
> isNULL
</CompOp
>
85 echo explain plan
select * from t1 where f1 is null and f2 is not null and f3 is null and f4 is not null
;
88 <ScanType
> TableScan
</ScanType
>
99 <FieldName1
> f1
</FieldName1
>
100 <CompOp
> isNULL
</CompOp
>
105 <FieldName1
> f2
</FieldName1
>
106 <CompOp
> isNULL
</CompOp
>
113 <FieldName1
> f3
</FieldName1
>
114 <CompOp
> isNULL
</CompOp
>
121 <FieldName1
> f4
</FieldName1
>
122 <CompOp
> isNULL
</CompOp
>