Google Git
Sign in
apache / derby / 08cc0f89fe077234078430bf9b93ebc9dfd70ac5 / . / java / testing / org / apache / derbyTesting / functionTests / master / desc_index.out
blob: 388affdc98cd5e8080ddb1e26728424df0cf24e2 [file] [log] [blame]
ij> --
-- 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;
ij> create table tab1 (c1 int, c2 smallint, c3 double precision, c4 varchar(30),
c5 varchar(1024));
0 rows inserted/updated/deleted
ij> insert into tab1 values (8, 12, 5.6, 'dfg', 'ghji');
1 row inserted/updated/deleted
ij> insert into tab1 values (76, 2, -9.86, 'yudf', '45gd');
1 row inserted/updated/deleted
ij> insert into tab1 values (-78, 45, -5.6, 'jakdsfh', 'df89g');
1 row inserted/updated/deleted
ij> insert into tab1 values (56, -3, 6.7, 'dfgs', 'fds');
1 row inserted/updated/deleted
ij> create index i1 on tab1 (c1, c3, c4);
0 rows inserted/updated/deleted
ij> create index i2 on tab1 (c1 desc, c3 desc, c4 desc);
0 rows inserted/updated/deleted
ij> create index i3 on tab1 (c1 desc, c3 asc, c4 desc);
0 rows inserted/updated/deleted
ij> create index i4 on tab1 (c2 desc, c3, c1);
0 rows inserted/updated/deleted
ij> create index i5 on tab1 (c1, c2 desc);
0 rows inserted/updated/deleted
ij> insert into tab1 values (34, 67, 5.3, 'rtgd', 'hds');
1 row inserted/updated/deleted
ij> insert into tab1 values (100, 11, 9.0, '34sfg', 'ayupo');
1 row inserted/updated/deleted
ij> insert into tab1 values (-100, 93, 9.1, 'egfh', 's6j');
1 row inserted/updated/deleted
ij> insert into tab1 values (55, 44, -9.85, 'yudd', 'df89f');
1 row inserted/updated/deleted
ij> insert into tab1 values (34, 68, 2.7, 'srg', 'iur');
1 row inserted/updated/deleted
ij> insert into tab1 values (34, 66, 1.2, 'yty', 'wer');
1 row inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
0 rows inserted/updated/deleted
ij> maximumdisplaywidth 20000;
ij> -- should use index i4
select c1, c3 from tab1 where c2 > 40 and c3 <= 5.3;
C1 |C3
------------------------------------
34 |2.7
34 |5.3
34 |1.2
-78 |-5.6
55 |-9.85
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
-- should use index i4
select c1, c3 from tab1 where c2 > 40 and c3 <= 5.3
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Project-Restrict ResultSet (3):
Number of opens = 1
Rows seen = 5
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (2):
Number of opens = 1
Rows seen = 5
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Index Scan ResultSet for TAB1 using index I4 at read committed isolation level using instantaneous share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 5
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 1, 2}
Number of columns fetched=3
Number of deleted rows visited=0
Number of pages visited=1
Number of rows qualified=5
Number of rows visited=7
Scan type=btree
Tree height=1
start position:
None
stop position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
Column[0][0] Id: 1
Operator: <=
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
ij> -- should use index i5
select c2, c1 from tab1 where c2 <= 44 and c1 > 55;
C2 |C1
------------------
-3 |56
2 |76
11 |100
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
-- should use index i5
select c2, c1 from tab1 where c2 <= 44 and c1 > 55
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Project-Restrict ResultSet (2):
Number of opens = 1
Rows seen = 3
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Index Scan ResultSet for TAB1 using index I5 at read committed isolation level using instantaneous share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 3
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 1}
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=1
Number of rows qualified=3
Number of rows visited=3
Scan type=btree
Tree height=1
start position:
> on first 1 column(s).
Ordered null semantics on the following columns:
stop position:
None
qualifiers:
Column[0][0] Id: 1
Operator: <=
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
ij> -- should use i1
select c1, c3, c4 from tab1 order by c1, c3;
C1 |C3 |C4
-------------------------------------------------------------------
-100 |9.1 |egfh
-78 |-5.6 |jakdsfh
8 |5.6 |dfg
34 |1.2 |yty
34 |2.7 |srg
34 |5.3 |rtgd
55 |-9.85 |yudd
56 |6.7 |dfgs
76 |-9.86 |yudf
100 |9.0 |34sfg
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
-- should use i1
select c1, c3, c4 from tab1 order by c1, c3
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Index Scan ResultSet for TAB1 using index I1 at read committed isolation level using instantaneous share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 10
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 1, 2}
Number of columns fetched=3
Number of deleted rows visited=0
Number of pages visited=1
Number of rows qualified=10
Number of rows visited=10
Scan type=btree
Tree height=1
start position:
None
stop position:
None
qualifiers:
None
ij> -- should use i2
select c1, c3, c4 from tab1 order by c1 desc, c3 desc, c4 desc;
C1 |C3 |C4
-------------------------------------------------------------------
100 |9.0 |34sfg
76 |-9.86 |yudf
56 |6.7 |dfgs
55 |-9.85 |yudd
34 |5.3 |rtgd
34 |2.7 |srg
34 |1.2 |yty
8 |5.6 |dfg
-78 |-5.6 |jakdsfh
-100 |9.1 |egfh
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
-- should use i2
select c1, c3, c4 from tab1 order by c1 desc, c3 desc, c4 desc
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Index Scan ResultSet for TAB1 using index I2 at read committed isolation level using instantaneous share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 10
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 1, 2}
Number of columns fetched=3
Number of deleted rows visited=0
Number of pages visited=1
Number of rows qualified=10
Number of rows visited=10
Scan type=btree
Tree height=1
start position:
None
stop position:
None
qualifiers:
None
ij> -- should use i3
select c1, c3, c4 from tab1 order by c1 desc, c3 asc, c4 desc;
C1 |C3 |C4
-------------------------------------------------------------------
100 |9.0 |34sfg
76 |-9.86 |yudf
56 |6.7 |dfgs
55 |-9.85 |yudd
34 |1.2 |yty
34 |2.7 |srg
34 |5.3 |rtgd
8 |5.6 |dfg
-78 |-5.6 |jakdsfh
-100 |9.1 |egfh
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
-- should use i3
select c1, c3, c4 from tab1 order by c1 desc, c3 asc, c4 desc
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Index Scan ResultSet for TAB1 using index I3 at read committed isolation level using instantaneous share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 10
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 1, 2}
Number of columns fetched=3
Number of deleted rows visited=0
Number of pages visited=1
Number of rows qualified=10
Number of rows visited=10
Scan type=btree
Tree height=1
start position:
None
stop position:
None
qualifiers:
None
ij> -- should use i4
select c1, c2, c3 from tab1 order by c2 desc, c3 asc;
C1 |C2 |C3
-------------------------------------------
-100 |93 |9.1
34 |68 |2.7
34 |67 |5.3
34 |66 |1.2
-78 |45 |-5.6
55 |44 |-9.85
8 |12 |5.6
100 |11 |9.0
76 |2 |-9.86
56 |-3 |6.7
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
-- should use i4
select c1, c2, c3 from tab1 order by c2 desc, c3 asc
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Project-Restrict ResultSet (2):
Number of opens = 1
Rows seen = 10
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Index Scan ResultSet for TAB1 using index I4 at read committed isolation level using instantaneous share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 10
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 1, 2}
Number of columns fetched=3
Number of deleted rows visited=0
Number of pages visited=1
Number of rows qualified=10
Number of rows visited=10
Scan type=btree
Tree height=1
start position:
None
stop position:
None
qualifiers:
None
ij> -- should use i5
select c1, c2 from tab1 order by c1, c2 desc;
C1 |C2
------------------
-100 |93
-78 |45
8 |12
34 |68
34 |67
34 |66
55 |44
56 |-3
76 |2
100 |11
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
-- should use i5
select c1, c2 from tab1 order by c1, c2 desc
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Index Scan ResultSet for TAB1 using index I5 at read committed isolation level using instantaneous share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 10
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 1}
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=1
Number of rows qualified=10
Number of rows visited=10
Scan type=btree
Tree height=1
start position:
None
stop position:
None
qualifiers:
None
ij> -- should use i4
select max(c2) from tab1;
1
------
93
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
-- should use i4
select max(c2) from tab1
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Project-Restrict ResultSet (4):
Number of opens = 1
Rows seen = 1
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Scalar Aggregate ResultSet:
Number of opens = 1
Rows input = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Index Key Optimization = true
Source result set:
Project-Restrict ResultSet (3):
Number of opens = 1
Rows seen = 1
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Index Scan ResultSet for TAB1 using index I4 at read committed isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 1
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0}
Number of columns fetched=1
Number of deleted rows visited=0
Number of pages visited=1
Number of rows qualified=1
Number of rows visited=1
Scan type=btree
Tree height=1
start position:
None
stop position:
None
qualifiers:
None
ij> -- should use i4
select min(c2) from tab1;
1
------
-3
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
-- should use i4
select min(c2) from tab1
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Project-Restrict ResultSet (4):
Number of opens = 1
Rows seen = 1
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Scalar Aggregate ResultSet:
Number of opens = 1
Rows input = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Index Key Optimization = true
Source result set:
Project-Restrict ResultSet (3):
Number of opens = 1
Rows seen = 1
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Last Key Index Scan ResultSet for TAB1 using index I4at read committed isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
ij> -- should use i5
select min(c2) from tab1 where c1 = 34;
1
------
66
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
-- should use i5
select min(c2) from tab1 where c1 = 34
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Project-Restrict ResultSet (4):
Number of opens = 1
Rows seen = 1
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Scalar Aggregate ResultSet:
Number of opens = 1
Rows input = 3
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Index Key Optimization = false
Source result set:
Project-Restrict ResultSet (3):
Number of opens = 1
Rows seen = 3
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Index Scan ResultSet for TAB1 using index I5 at read committed isolation level using instantaneous share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 3
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 1}
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=1
Number of rows qualified=3
Number of rows visited=4
Scan type=btree
Tree height=1
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
ij> -- should use i5
select max(c2) from tab1 where c1 = 34;
1
------
68
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
-- should use i5
select max(c2) from tab1 where c1 = 34
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Project-Restrict ResultSet (4):
Number of opens = 1
Rows seen = 1
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Scalar Aggregate ResultSet:
Number of opens = 1
Rows input = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Index Key Optimization = true
Source result set:
Project-Restrict ResultSet (3):
Number of opens = 1
Rows seen = 1
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Index Scan ResultSet for TAB1 using index I5 at read committed isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 1
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 1}
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=1
Number of rows qualified=1
Number of rows visited=1
Scan type=btree
Tree height=1
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
ij> -- test if bulk insert rebuilds desc index right
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'TAB1', 0);
0 rows inserted/updated/deleted
ij> select * from tab1 order by c1 desc;
C1 |C2 |C3 |C4 |C5
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
100 |11 |9.0 |34sfg |ayupo
76 |2 |-9.86 |yudf |45gd
56 |-3 |6.7 |dfgs |fds
55 |44 |-9.85 |yudd |df89f
34 |66 |1.2 |yty |wer
34 |68 |2.7 |srg |iur
34 |67 |5.3 |rtgd |hds
8 |12 |5.6 |dfg |ghji
-78 |45 |-5.6 |jakdsfh |df89g
-100 |93 |9.1 |egfh |s6j
ij> -- 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);
0 rows inserted/updated/deleted
ij> -- not unique index, shouldn't share with primary key's index
create index i21 on tab2(c1);
0 rows inserted/updated/deleted
WARNING 01504: 'I21' index not created because it is a duplicate of an existing index: 'xxxxGENERATED-IDxxxx'.
ij> -- desc index, shouldn't share with primary key's index
create index i22 on tab2(c1 desc);
0 rows inserted/updated/deleted
ij> -- this should share with primary key's index, and give a warning
create unique index i23 on tab2(c1);
0 rows inserted/updated/deleted
WARNING 01504: 'I23' index not created because it is a duplicate of an existing index: 'xxxxGENERATED-IDxxxx'.
ij> create index i24 on tab2(c1, c3 desc);
0 rows inserted/updated/deleted
ij> -- this should share with i24's conglomerate
create index i25 on tab2(c1, c3 desc);
0 rows inserted/updated/deleted
WARNING 01504: 'I25' index not created because it is a duplicate of an existing index: 'I24'.
ij> -- no share
create index i26 on tab2(c1, c3);
0 rows inserted/updated/deleted
ij> insert into tab2 values (6, 2, 8), (2, 8, 5), (28, 5, 9), (3, 12, 543);
4 rows inserted/updated/deleted
ij> create index i27 on tab2 (c1, c2 desc, c3);
0 rows inserted/updated/deleted
ij> -- no share
create index i28 on tab2 (c1, c2 desc, c3 desc);
0 rows inserted/updated/deleted
ij> -- share with i27
create index i29 on tab2 (c1, c2 desc, c3);
0 rows inserted/updated/deleted
WARNING 01504: 'I29' index not created because it is a duplicate of an existing index: 'I27'.
ij> create index i20 on tab2 (c1, c2 desc, c3);
0 rows inserted/updated/deleted
WARNING 01504: 'I20' index not created because it is a duplicate of an existing index: 'I27'.
ij> insert into tab2 values (56, 2, 7), (31, 5, 7), (-12, 5, 2);
3 rows inserted/updated/deleted
ij> select count(distinct conglomeratenumber) from sys.sysconglomerates
where tableid = (select tableid from sys.systables
where tablename = 'TAB2');
1
-----------
7
ij> select * from tab2;
C1 |C2 |C3
-----------------------------------
-12 |5 |2
2 |8 |5
3 |12 |543
6 |2 |8
28 |5 |9
31 |5 |7
56 |2 |7
ij> values SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'TAB2');
1
-----------
1
ij> -- see if rebuild indexes correctly
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'TAB2', 0);
0 rows inserted/updated/deleted
ij> select count(distinct conglomeratenumber) from sys.sysconglomerates
where tableid = (select tableid from sys.systables
where tablename = 'TAB2');
1
-----------
7
ij> select * from tab2;
C1 |C2 |C3
-----------------------------------
-12 |5 |2
2 |8 |5
3 |12 |543
6 |2 |8
28 |5 |9
31 |5 |7
56 |2 |7
ij> update tab2 set c2 = 11 where c3 = 7;
2 rows inserted/updated/deleted
ij> select * from tab2;
C1 |C2 |C3
-----------------------------------
-12 |5 |2
2 |8 |5
3 |12 |543
6 |2 |8
28 |5 |9
31 |11 |7
56 |11 |7
ij> delete from tab2 where c2 > 10 and c2 < 12;
2 rows inserted/updated/deleted
ij> select * from tab2;
C1 |C2 |C3
-----------------------------------
-12 |5 |2
2 |8 |5
3 |12 |543
6 |2 |8
28 |5 |9
ij> values SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'TAB2');
1
-----------
1
ij> -- drop indexes
drop index i22;
0 rows inserted/updated/deleted
ij> drop index i24;
0 rows inserted/updated/deleted
ij> drop index i26;
0 rows inserted/updated/deleted
ij> drop index i28;
0 rows inserted/updated/deleted
ij> drop index i20;
ERROR 42X65: Index 'I20' does not exist.
ij> select count(distinct conglomeratenumber) from sys.sysconglomerates
where tableid = (select tableid from sys.systables
where tablename = 'TAB2');
1
-----------
3
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'TAB2', 0);
0 rows inserted/updated/deleted
ij> select * from tab2;
C1 |C2 |C3
-----------------------------------
-12 |5 |2
2 |8 |5
3 |12 |543
6 |2 |8
28 |5 |9
ij> values SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'TAB2');
1
-----------
1
ij> drop index i21;
ERROR 42X65: Index 'I21' does not exist.
ij> drop index i23;
ERROR 42X65: Index 'I23' does not exist.
ij> drop index i25;
ERROR 42X65: Index 'I25' does not exist.
ij> drop index i27;
0 rows inserted/updated/deleted
ij> drop index i29;
ERROR 42X65: Index 'I29' does not exist.
ij> select count(distinct conglomeratenumber) from sys.sysconglomerates
where tableid = (select tableid from sys.systables
where tablename = 'TAB2');
1
-----------
2
ij> values SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'TAB2');
1
-----------
1
ij> -- beetle 4974
create table b4974 (a BIGINT, b BIGINT, c INT, d CHAR(16), e BIGINT);
0 rows inserted/updated/deleted
ij> create index i4974 on b4974(a, d, c, e);
0 rows inserted/updated/deleted
ij> 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);
B
--------------------
ij> rollback;
ij>