Google Git
Sign in
apache / derby / 08cc0f89fe077234078430bf9b93ebc9dfd70ac5 / . / java / testing / org / apache / derbyTesting / functionTests / master / aggregateOptimization.out
blob: 609808cec18b481a1ab9727c5c64bb6b8aee8b50 [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.
--
-- test various aggregate optimizations
set isolation to rr;
0 rows inserted/updated/deleted
ij> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
NoHoldForConnection;
ij> -- create and populate tables
create table t1(c1 int, c2 char(200));
0 rows inserted/updated/deleted
ij> insert into t1 (c1) values 10, 9, 10, 9, 8, 7, 6, 1, 3;
9 rows inserted/updated/deleted
ij> update t1 set c2 = CHAR(c1);
9 rows inserted/updated/deleted
ij> -- distinct min -> min, distinct max -> max
call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
0 rows inserted/updated/deleted
ij> maximumdisplaywidth 7000;
ij> select min(distinct c1), max(distinct(c1)) from t1;
1 |2
-----------------------
1 |10
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select min(distinct c1), max(distinct(c1)) from t1
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 = 9
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 = 9
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:
Table Scan ResultSet for T1 at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
Rows seen = 9
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}
Number of columns fetched=1
Number of pages visited=1
Number of rows qualified=9
Number of rows visited=9
Scan type=heap
start position:
null
stop position:
null
qualifiers:
None
ij> select min(distinct c1), max(distinct(c1)) from t1 group by c1;
1 |2
-----------------------
1 |1
3 |3
6 |6
7 |7
8 |8
9 |9
10 |10
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select min(distinct c1), max(distinct(c1)) from t1 group by c1
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 = 7
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:
Grouped Aggregate ResultSet:
Number of opens = 1
Rows input = 9
Has distinct aggregate = false
In sorted order = false
Sort information:
Number of rows input=9
Number of rows output=7
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (3):
Number of opens = 1
Rows seen = 9
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:
Table Scan ResultSet for T1 at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
Rows seen = 9
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}
Number of columns fetched=1
Number of pages visited=1
Number of rows qualified=9
Number of rows visited=9
Scan type=heap
start position:
null
stop position:
null
qualifiers:
None
ij> -- min optimization
create index i1 on t1(c1);
0 rows inserted/updated/deleted
ij> -- min column is 1st column in index
select min(c1) from t1;
1
-----------
1
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
-- min column is 1st column in index
select min(c1) from t1
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 T1 using index I1 at serializable isolation level using share table 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> create index i2 on t1(c2, c1);
0 rows inserted/updated/deleted
ij> -- equality predicates on all key columns preceding min column
select min(c1) from t1 where c2 = '10';
1
-----------
10
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
-- equality predicates on all key columns preceding min column
select min(c1) from t1 where c2 = '10'
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 (5):
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 (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:
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 T1 using index I2 at serializable 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> -- equality predicates on all key columns preceding min column,
-- not a unique index
select min(c2) from t1 where c1 = 1;
1
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
-- equality predicates on all key columns preceding min column,
-- not a unique index
select min(c2) from t1 where c1 = 1
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 (5):
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 = false
Source result set:
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:
Index Row to Base Row ResultSet for T1:
Number of opens = 1
Rows seen = 1
Columns accessed from heap = {0, 1}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Index Scan ResultSet for T1 using index I1 at serializable isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 1
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=All
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=2
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> delete from t1;
9 rows inserted/updated/deleted
ij> drop index i1;
0 rows inserted/updated/deleted
ij> create unique index i1 on t1(c1);
0 rows inserted/updated/deleted
ij> insert into t1 values (1, '1'), (2, '2');
2 rows inserted/updated/deleted
ij> -- equality predicates on all key columns preceding min column,
-- a unique index
select min(c2) from t1 where c1 = 1;
1
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
-- equality predicates on all key columns preceding min column,
-- a unique index
select min(c2) from t1 where c1 = 1
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 (5):
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 (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:
Index Row to Base Row ResultSet for T1:
Number of opens = 1
Rows seen = 1
Columns accessed from heap = {1}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Index Scan ResultSet for T1 using index I1 at serializable 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=All
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> -- group by ordered on grouping columns
create table t2(c1 int, c2 int, c3 int, c4 int);
0 rows inserted/updated/deleted
ij> create index t2_i1 on t2(c1);
0 rows inserted/updated/deleted
ij> create index t2_i2 on t2(c1, c2);
0 rows inserted/updated/deleted
ij> -- empty table
select c1, sum(c2) from t2 group by c1;
C1 |2
-----------------------
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
-- empty table
select c1, sum(c2) from t2 group by c1
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 = 0
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:
Grouped Aggregate ResultSet:
Number of opens = 1
Rows input = 0
Has distinct aggregate = false
In sorted order = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (3):
Number of opens = 1
Rows seen = 0
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 T2 using index T2_I2 at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
Rows seen = 0
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 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=0
Number of rows visited=0
Scan type=btree
Tree height=1
start position:
None
stop position:
None
qualifiers:
None
ij> -- 1 row table
insert into t2 values (1, 1, 1, 1);
1 row inserted/updated/deleted
ij> select c1, sum(c2) from t2 group by c1;
C1 |2
-----------------------
1 |1
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select c1, sum(c2) from t2 group by c1
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:
Grouped Aggregate ResultSet:
Number of opens = 1
Rows input = 1
Has distinct aggregate = false
In sorted order = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
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 T2 using index T2_I2 at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
Rows seen = 1
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=1
Number of rows visited=1
Scan type=btree
Tree height=1
start position:
None
stop position:
None
qualifiers:
None
ij> -- multiple rows, 1 group
insert into t2 values (1, 2, 2, 2), (1, -1, -1, -1);
2 rows inserted/updated/deleted
ij> select c1, sum(c2) from t2 group by c1;
C1 |2
-----------------------
1 |2
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select c1, sum(c2) from t2 group by c1
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:
Grouped Aggregate ResultSet:
Number of opens = 1
Rows input = 3
Has distinct aggregate = false
In sorted order = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
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 T2 using index T2_I2 at serializable isolation level using share table 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:
None
stop position:
None
qualifiers:
None
ij> -- multiple rows, multiple groups
insert into t2 values (2, 3, 2, 2), (2, 3, -1, -1);
2 rows inserted/updated/deleted
ij> select c1, sum(c2) from t2 group by c1;
C1 |2
-----------------------
1 |2
2 |6
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select c1, sum(c2) from t2 group by c1
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 = 2
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:
Grouped Aggregate ResultSet:
Number of opens = 1
Rows input = 5
Has distinct aggregate = false
In sorted order = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Source result set:
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:
Index Scan ResultSet for T2 using index T2_I2 at serializable isolation level using share table 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}
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=1
Number of rows qualified=5
Number of rows visited=5
Scan type=btree
Tree height=1
start position:
None
stop position:
None
qualifiers:
None
ij> -- ordered, but in reverse order
select c2, c1, sum(c3) from t2 group by c2, c1;
C2 |C1 |3
-----------------------------------
-1 |1 |-1
1 |1 |1
2 |1 |2
3 |2 |1
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
-- ordered, but in reverse order
select c2, c1, sum(c3) from t2 group by c2, c1
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 = 4
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:
Grouped Aggregate ResultSet:
Number of opens = 1
Rows input = 5
Has distinct aggregate = false
In sorted order = false
Sort information:
Number of rows input=5
Number of rows output=4
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Source result set:
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:
Table Scan ResultSet for T2 at serializable isolation level using share table 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 pages visited=1
Number of rows qualified=5
Number of rows visited=5
Scan type=heap
start position:
null
stop position:
null
qualifiers:
None
ij> -- clean up
drop table t1;
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> --
-- max optimization: the optimization is to call the store
-- with a special request for the last row in an index. so
-- we cannot deal with any predicates
--
set isolation read committed;
0 rows inserted/updated/deleted
ij> create table x (x int, y int);
0 rows inserted/updated/deleted
ij> create index ix on x(x);
0 rows inserted/updated/deleted
ij> create index ixy on x(x,y);
0 rows inserted/updated/deleted
ij> insert into x values (3,3),(7,7),(2,2),(666,6),(1,1);
5 rows inserted/updated/deleted
ij> select max(x) from x;
1
-----------
666
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select max(x) from x
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 X using index IXat 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> select max(x) from x;
1
-----------
666
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select max(x) from x
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 X using index IXat 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> -- cannot use max opt
select max(x) from x;
1
-----------
666
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
-- cannot use max opt
select max(x) from x
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 X using index IXat 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> select max(x) from x where x < 99;
1
-----------
7
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select max(x) from x where x < 99
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 = 4
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 = 4
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 X using index IX at read committed isolation level using instantaneous share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 4
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}
Number of columns fetched=1
Number of deleted rows visited=0
Number of pages visited=1
Number of rows qualified=4
Number of rows visited=5
Scan type=btree
Tree height=1
start position:
None
stop position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
ij> select max(x) from x where x = 7;
1
-----------
7
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select max(x) from x where x = 7
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 = false
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 X using index IX at read committed isolation level using instantaneous share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 1
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}
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=2
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> select max(x) from x where y = 7;
1
-----------
7
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select max(x) from x where y = 7
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 = false
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 X using index IXY at read committed isolation level using instantaneous share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 1
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=1
Number of rows visited=5
Scan type=btree
Tree height=1
start position:
None
stop position:
None
qualifiers:
Column[0][0] Id: 1
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
ij> select max(x) from x where y = 7;
1
-----------
7
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select max(x) from x where y = 7
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 = false
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 X using index IXY at read committed isolation level using instantaneous share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 1
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=1
Number of rows visited=5
Scan type=btree
Tree height=1
start position:
None
stop position:
None
qualifiers:
Column[0][0] Id: 1
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
ij> select max(y) from x where y = 7;
1
-----------
7
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select max(y) from x where y = 7
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 = false
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 X using index IXY at read committed isolation level using instantaneous share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 1
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={1}
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=5
Scan type=btree
Tree height=1
start position:
None
stop position:
None
qualifiers:
Column[0][0] Id: 1
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
ij> select max(x) from x group by x;
1
-----------
1
2
3
7
666
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select max(x) from x group by x
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 = 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:
Grouped Aggregate ResultSet:
Number of opens = 1
Rows input = 5
Has distinct aggregate = false
In sorted order = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Source result set:
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:
Index Scan ResultSet for X using index IX 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}
Number of columns fetched=1
Number of deleted rows visited=0
Number of pages visited=1
Number of rows qualified=5
Number of rows visited=5
Scan type=btree
Tree height=1
start position:
None
stop position:
None
qualifiers:
None
ij> -- could do max optimization on this, but we don't
-- really know much about qualifications
select max(x) from x where x > 99;
1
-----------
666
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
-- could do max optimization on this, but we don't
-- really know much about qualifications
select max(x) from x where x > 99
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 = false
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 X using index IX at read committed isolation level using instantaneous share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 1
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}
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:
> on first 1 column(s).
Ordered null semantics on the following columns:
stop position:
None
qualifiers:
None
ij> autocommit off;
ij> prepare p as 'select max(x) from x';
ij> execute p;
1
-----------
666
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select max(x) from x
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 X using index IXat 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> insert into x values (99999,99999);
1 row inserted/updated/deleted
ij> execute p;
1
-----------
99999
ij> rollback;
ij> execute p;
1
-----------
666
ij> delete from x;
5 rows inserted/updated/deleted
ij> execute p;
1
-----------
NULL
ij> rollback;
ij> -- since max uses some funky store interface, lets
-- check locking
connect 'wombat' as conn2;
ij(CONN2)> set isolation to rr;
0 rows inserted/updated/deleted
ij(CONN2)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
NoHoldForConnection;
ij(CONN2)> autocommit off;
ij(CONN2)> insert into x values (99999,null);
1 row inserted/updated/deleted
ij(CONN2)> set connection connection0;
ij(CONNECTION0)> -- should deadlock
select max(x) from x;
ERROR 40XL1: A lock could not be obtained within the time requested
ij(CONNECTION0)> set connection conn2;
ij(CONN2)> commit;
ij(CONN2)> insert into x values (99980,null);
1 row inserted/updated/deleted
ij(CONN2)> set connection connection0;
ij(CONNECTION0)> -- ok - should not block on previous key (lock held by conn2 on 99980)
select max(x) from x;
1
-----------
99999
ij(CONNECTION0)> set connection conn2;
ij(CONN2)> delete from x where x = 99980;
1 row inserted/updated/deleted
ij(CONN2)> delete from x where x = 99999;
1 row inserted/updated/deleted
ij(CONN2)> commit;
ij(CONN2)> set connection connection0;
ij(CONNECTION0)> -- ok
select max(x) from x;
1
-----------
666
ij(CONNECTION0)> set connection conn2;
ij(CONN2)> insert into x values (-1,null);
1 row inserted/updated/deleted
ij(CONN2)> set connection connection0;
ij(CONNECTION0)> -- does not deadlock in current implementation, as it handles cases where
-- the last row is deleted, but the maximum values is somewhere on the last
-- page.
select max(x) from x;
1
-----------
666
ij(CONNECTION0)> set connection conn2;
ij(CONN2)> insert into x values (100000,null);
1 row inserted/updated/deleted
ij(CONN2)> commit;
ij(CONN2)> set connection connection0;
ij(CONNECTION0)> -- ok
select max(x) from x;
1
-----------
100000
ij(CONNECTION0)> set connection connection0;
ij(CONNECTION0)> rollback;
ij(CONNECTION0)> disconnect;
ij> set connection conn2;
ij> rollback;
ij> disconnect;
ij> -- check case where all rows are deleted off the last page of index, store
-- will fault over to doing full table scan, rather than max optimization.
connect 'wombat' as conn1;
ij> set isolation to rr;
0 rows inserted/updated/deleted
ij> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
NoHoldForConnection;
ij> autocommit off;
ij> drop table x;
0 rows inserted/updated/deleted
ij> create table x (a bigint, b int);
0 rows inserted/updated/deleted
ij> -- insert enough rows so that there are multiple pages in the index.
insert into x values (1, 1);
1 row inserted/updated/deleted
ij> insert into x (select a + 1, b from x);
1 row inserted/updated/deleted
ij> insert into x (select a + 2, b from x);
2 rows inserted/updated/deleted
ij> insert into x (select a + 4, b from x);
4 rows inserted/updated/deleted
ij> insert into x (select a + 8, b from x);
8 rows inserted/updated/deleted
ij> insert into x (select a + 16, b from x);
16 rows inserted/updated/deleted
ij> insert into x (select a + 32, b from x);
32 rows inserted/updated/deleted
ij> insert into x (select a + 64, b from x);
64 rows inserted/updated/deleted
ij> insert into x (select a + 128, b from x);
128 rows inserted/updated/deleted
ij> insert into x (select a + 256, b from x);
256 rows inserted/updated/deleted
ij> create index x_idx on x (a);
0 rows inserted/updated/deleted
ij> commit;
ij> connect 'wombat' as conn2;
ij(CONN2)> set isolation to rr;
0 rows inserted/updated/deleted
ij(CONN2)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
NoHoldForConnection;
ij(CONN2)> autocommit off;
ij(CONN2)> commit;
ij(CONN2)> set connection conn1;
ij(CONN1)> -- get lock on first row of table
insert into x values (0, 0);
1 row inserted/updated/deleted
ij(CONN1)> set connection conn2;
ij(CONN2)> -- delete all the rows from the last page in the index, but don't commit or
-- else post commit will remove the page from the index.
delete from x where a > 4;
ERROR 40XL1: A lock could not be obtained within the time requested
ij(CONN2)> -- lock timeout in current implementation - to be fixed when row level locked
-- backward scan exists.
--
-- this one deadlocks because we have not done a complete implementation
-- of backward scan for max on btree. If the last page in the table is
-- all deletes, then instead of doing a backward scan we fault over
-- to the un-optimized max code which does a forward scan from the
-- beginnning of the table.
select max(a) from x;
ERROR 40XL1: A lock could not be obtained within the time requested
ij(CONN2)> -- cleanup
set connection conn1;
ij(CONN1)> rollback;
ij(CONN1)> disconnect;
ij> set connection conn2;
ij> drop table x;
0 rows inserted/updated/deleted
ij> commit;
ij> -- test a table with null values to be sure we do the right thing on optimization
call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
0 rows inserted/updated/deleted
ij> create table t1(a int, b int);
0 rows inserted/updated/deleted
ij> insert into t1 values (null, null);
1 row inserted/updated/deleted
ij> insert into t1 values (10, 10), (9, 9), (10, 10), (9, 9), (8, 8), (7, 7), (6, 6),
(1,1), (3,3);
9 rows inserted/updated/deleted
ij> create index aindex on t1(a);
0 rows inserted/updated/deleted
ij> create index bindex on t1(b desc);
0 rows inserted/updated/deleted
ij> select min(a) from t1;
1
-----------
1
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select min(a) from t1
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 T1 using index AINDEX at serializable isolation level using share table 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> -- min of b should use max optimization whether b in nullable or not because NULLS are sorted high
select min(b) from t1;
1
-----------
1
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
-- min of b should use max optimization whether b in nullable or not because NULLS are sorted high
select min(b) from t1
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 T1 using index BINDEXat serializable isolation level using share table 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> select max(a) from t1;
1
-----------
10
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select max(a) from t1
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 T1 using index AINDEXat serializable isolation level using share table 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> select max(b) from t1;
1
-----------
10
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select max(b) from t1
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 = 10
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 = 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 T1 using index BINDEX at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
Rows seen = 10
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=10
Number of rows visited=10
Scan type=btree
Tree height=1
start position:
None
stop position:
None
qualifiers:
None
ij> create table t2 (a int not null, b int not null);
0 rows inserted/updated/deleted
ij> insert into t2 select a, b from t1 where a is not null and b is not null;
9 rows inserted/updated/deleted
ij> create index bindex2 on t2(b desc);
0 rows inserted/updated/deleted
ij> -- min of b should use max optimization since b is nullable or not because NULLS are sorted high
select min(b) from t2;
1
-----------
1
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
-- min of b should use max optimization since b is nullable or not because NULLS are sorted high
select min(b) from t2
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 T2 using index BINDEX2at serializable isolation level using share table 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> drop table t1;
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij>