oceanbase

1
create table t1 (c1 int, c2 int, c3 int, c4 int, primary key(c1));
2
create index i1 on t1 (c2);
3
create index i2 on t1 (c3, c4);
4
create index i3 on t1 (c3) storing(c4);
5
replace into t1 values (1,1,2,1),(2,2,1,null),(3,3,null,null),(4,null,null,null);
6
select * from t1;
7
c1	c2	c3	c4
8
1	1	2	1
9
2	2	1	NULL
10
3	3	NULL	NULL
11
4	NULL	NULL	NULL
12
select  /*+ index(t1 i2) */ * from t1 where c3=1;
13
c1	c2	c3	c4
14
2	2	1	NULL
15
select * from t1 where c3=1;
16
c1	c2	c3	c4
17
2	2	1	NULL
18
select  /*+ index(a1 i2) */ * from t1 as a1 where c3 in(1,2);
19
c1	c2	c3	c4
20
2	2	1	NULL
21
1	1	2	1
22
select * from t1 as a1 where a1.c3 in (1,2);
23
c1	c2	c3	c4
24
2	2	1	NULL
25
1	1	2	1
26
select  /*+ index(t1 primary) */ * from t1 where c3 in(1,2);
27
c1	c2	c3	c4
28
1	1	2	1
29
2	2	1	NULL
30
select * from t1 where c3 is null;
31
c1	c2	c3	c4
32
3	3	NULL	NULL
33
4	NULL	NULL	NULL
34
select  /*+ index(t1 i2) */ * from t1 where c3 is null;
35
c1	c2	c3	c4
36
3	3	NULL	NULL
37
4	NULL	NULL	NULL
38
select  /*+ index(t1 primary) */ * from t1 where c3 is null;
39
c1	c2	c3	c4
40
3	3	NULL	NULL
41
4	NULL	NULL	NULL
42
select /*+ index(t1 i4) */ * from t1 where c3 is null;
43
c1	c2	c3	c4
44
3	3	NULL	NULL
45
4	NULL	NULL	NULL
46
drop table t1;
47