1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
|
--
-- Licensed to the Apache Software Foundation (ASF) under one or more
-- contributor license agreements. See the NOTICE file distributed with
-- this work for additional information regarding copyright ownership.
-- The ASF licenses this file to You under the Apache License, Version 2.0
-- (the "License"); you may not use this file except in compliance with
-- the License. You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--
-- this also tests multiple indexes share one conglomerate if they essentially
-- are the same
autocommit off;
create table tab1 (c1 int, c2 smallint, c3 double precision, c4 varchar(30),
c5 varchar(1024));
insert into tab1 values (8, 12, 5.6, 'dfg', 'ghji');
insert into tab1 values (76, 2, -9.86, 'yudf', '45gd');
insert into tab1 values (-78, 45, -5.6, 'jakdsfh', 'df89g');
insert into tab1 values (56, -3, 6.7, 'dfgs', 'fds');
create index i1 on tab1 (c1, c3, c4);
create index i2 on tab1 (c1 desc, c3 desc, c4 desc);
create index i3 on tab1 (c1 desc, c3 asc, c4 desc);
create index i4 on tab1 (c2 desc, c3, c1);
create index i5 on tab1 (c1, c2 desc);
insert into tab1 values (34, 67, 5.3, 'rtgd', 'hds');
insert into tab1 values (100, 11, 9.0, '34sfg', 'ayupo');
insert into tab1 values (-100, 93, 9.1, 'egfh', 's6j');
insert into tab1 values (55, 44, -9.85, 'yudd', 'df89f');
insert into tab1 values (34, 68, 2.7, 'srg', 'iur');
insert into tab1 values (34, 66, 1.2, 'yty', 'wer');
call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
maximumdisplaywidth 20000;
-- should use index i4
select c1, c3 from tab1 where c2 > 40 and c3 <= 5.3;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- should use index i5
select c2, c1 from tab1 where c2 <= 44 and c1 > 55;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- should use i1
select c1, c3, c4 from tab1 order by c1, c3;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- should use i2
select c1, c3, c4 from tab1 order by c1 desc, c3 desc, c4 desc;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- should use i3
select c1, c3, c4 from tab1 order by c1 desc, c3 asc, c4 desc;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- should use i4
select c1, c2, c3 from tab1 order by c2 desc, c3 asc;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- should use i5
select c1, c2 from tab1 order by c1, c2 desc;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- should use i4
select max(c2) from tab1;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- should use i4
select min(c2) from tab1;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- should use i5
select min(c2) from tab1 where c1 = 34;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- should use i5
select max(c2) from tab1 where c1 = 34;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- test if bulk insert rebuilds desc index right
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'TAB1', 0);
select * from tab1 order by c1 desc;
-- this tests multiple indexes share one conglomerate if they essentially
-- are the same
create table tab2 (c1 int not null primary key, c2 int, c3 int);
-- not unique index, shouldn't share with primary key's index
create index i21 on tab2(c1);
-- desc index, shouldn't share with primary key's index
create index i22 on tab2(c1 desc);
-- this should share with primary key's index, and give a warning
create unique index i23 on tab2(c1);
create index i24 on tab2(c1, c3 desc);
-- this should share with i24's conglomerate
create index i25 on tab2(c1, c3 desc);
-- no share
create index i26 on tab2(c1, c3);
insert into tab2 values (6, 2, 8), (2, 8, 5), (28, 5, 9), (3, 12, 543);
create index i27 on tab2 (c1, c2 desc, c3);
-- no share
create index i28 on tab2 (c1, c2 desc, c3 desc);
-- share with i27
create index i29 on tab2 (c1, c2 desc, c3);
create index i20 on tab2 (c1, c2 desc, c3);
insert into tab2 values (56, 2, 7), (31, 5, 7), (-12, 5, 2);
select count(distinct conglomeratenumber) from sys.sysconglomerates
where tableid = (select tableid from sys.systables
where tablename = 'TAB2');
select * from tab2;
values SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'TAB2');
-- see if rebuild indexes correctly
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'TAB2', 0);
select count(distinct conglomeratenumber) from sys.sysconglomerates
where tableid = (select tableid from sys.systables
where tablename = 'TAB2');
select * from tab2;
update tab2 set c2 = 11 where c3 = 7;
select * from tab2;
delete from tab2 where c2 > 10 and c2 < 12;
select * from tab2;
values SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'TAB2');
-- drop indexes
drop index i22;
drop index i24;
drop index i26;
drop index i28;
drop index i20;
select count(distinct conglomeratenumber) from sys.sysconglomerates
where tableid = (select tableid from sys.systables
where tablename = 'TAB2');
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'TAB2', 0);
select * from tab2;
values SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'TAB2');
drop index i21;
drop index i23;
drop index i25;
drop index i27;
drop index i29;
select count(distinct conglomeratenumber) from sys.sysconglomerates
where tableid = (select tableid from sys.systables
where tablename = 'TAB2');
values SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'TAB2');
-- beetle 4974
create table b4974 (a BIGINT, b BIGINT, c INT, d CHAR(16), e BIGINT);
create index i4974 on b4974(a, d, c, e);
SELECT b from b4974 t1
where (T1.a = 10127 or T1.a = 0)
and (T1.d = 'ProductBean' or T1.d = 'CatalogEntryBean')
and (T1.e =0 or T1.e = 0);
rollback;
|
