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. -- set isolation to RR; 0 rows inserted/updated/deleted ij> run resource 'createTestProcedures.subsql'; 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. -- CREATE FUNCTION PADSTRING (DATA VARCHAR(32000), LENGTH INTEGER) RETURNS VARCHAR(32000) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.Formatters.padString' LANGUAGE JAVA PARAMETER STYLE JAVA; 0 rows inserted/updated/deleted ij> CREATE PROCEDURE WAIT_FOR_POST_COMMIT() DYNAMIC RESULT SETS 0 LANGUAGE JAVA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.T_Access.waitForPostCommitToFinish' PARAMETER STYLE JAVA; 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> -------------------------------------------------------------------------- -- test qualifier skip code on fields with length having the 8th bit set in low -- order length byte. -------------------------------------------------------------------------- drop table a; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'A' because it does not exist. ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '32768'); 0 rows inserted/updated/deleted ij> create table a ( i1 int, col00 varchar(384), col01 varchar(390), i2 int ); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL); 0 rows inserted/updated/deleted ij> insert into a values (1, PADSTRING('10',384), PADSTRING('100',390), 1000); 1 row inserted/updated/deleted ij> insert into a values (2, PADSTRING('20',384), PADSTRING('200',390), 2000); 1 row inserted/updated/deleted ij> insert into a values (3, PADSTRING('30',384), PADSTRING('300',390), 3000); 1 row inserted/updated/deleted ij> select i1, i2 from a where i2 = 3000; I1 |I2 ----------------------- 3 |3000 ij> drop table a; 0 rows inserted/updated/deleted ij> commit; ij> -- test case for track 2590 -- The problem was that the btree split would self deadlock while trying -- to reclaim rows during the split. Fixed by just giving up if btree -- can't get the locks during the reclaim try. create table foo (a int, b varchar(900), c int); 0 rows inserted/updated/deleted ij> insert into foo values (1, PADSTRING('1',900), 1); 1 row inserted/updated/deleted ij> insert into foo values (2, PADSTRING('2',900), 1); 1 row inserted/updated/deleted ij> insert into foo values (3, PADSTRING('3',900), 1); 1 row inserted/updated/deleted ij> insert into foo values (4, PADSTRING('4',900), 1); 1 row inserted/updated/deleted ij> insert into foo values (5, PADSTRING('5',900), 1); 1 row inserted/updated/deleted ij> insert into foo values (6, PADSTRING('6',900), 1); 1 row inserted/updated/deleted ij> insert into foo values (7, PADSTRING('7',900), 1); 1 row inserted/updated/deleted ij> insert into foo values (8, PADSTRING('8',900), 1); 1 row inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096'); 0 rows inserted/updated/deleted ij> create index foox on foo (a, b); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL); 0 rows inserted/updated/deleted ij> commit; ij> delete from foo where foo.a <> 2; 7 rows inserted/updated/deleted ij> -- Test full cursor for update scan over all the rows in the heap, -- with default group fetch. Group fetch should be disabled. -------------------------------------------------------------------------------- -- force index until optimizer fixes problem where it does not pick index. get cursor scan_cursor as 'select a, b, c from foo for update of c'; ij> next scan_cursor; A |B |C -------------------------------------------------------------------------------------------------------------------------------------------------------- 2 |2 &|1 ij> -- these inserts would cause a lock wait timeout before the bug fix. insert into foo values (1, PADSTRING('11',900), 1); 1 row inserted/updated/deleted ij> insert into foo values (1, PADSTRING('12',900), 1); 1 row inserted/updated/deleted ij> insert into foo values (1, PADSTRING('13',900), 1); 1 row inserted/updated/deleted ij> insert into foo values (1, PADSTRING('14',900), 1); 1 row inserted/updated/deleted ij> insert into foo values (1, PADSTRING('15',900), 1); 1 row inserted/updated/deleted ij> commit; ij> drop table foo; 0 rows inserted/updated/deleted ij> commit; ij> -- test case for track 735 -- The problem was that when the level of btree grew, raw store would -- incorrectly report that there was not enough space to move all the -- rows from the root page to a newly allocated leaf page, so the create -- index operation would fail with a message saying that a row was too -- big. -- create and load a table with values from 1024 down to 1, the reverse order -- is important to reproduce the bug. create table foo (a int); 0 rows inserted/updated/deleted ij> insert into foo values (1024); 1 row inserted/updated/deleted ij> insert into foo (select foo.a - 1 from foo); 1 row inserted/updated/deleted ij> insert into foo (select foo.a - 2 from foo); 2 rows inserted/updated/deleted ij> insert into foo (select foo.a - 4 from foo); 4 rows inserted/updated/deleted ij> insert into foo (select foo.a - 8 from foo); 8 rows inserted/updated/deleted ij> insert into foo (select foo.a - 16 from foo); 16 rows inserted/updated/deleted ij> insert into foo (select foo.a - 32 from foo); 32 rows inserted/updated/deleted ij> insert into foo (select foo.a - 64 from foo); 64 rows inserted/updated/deleted ij> insert into foo (select foo.a - 128 from foo); 128 rows inserted/updated/deleted ij> insert into foo (select foo.a - 256 from foo); 256 rows inserted/updated/deleted ij> insert into foo (select foo.a - 512 from foo); 512 rows inserted/updated/deleted ij> -- this create index use to fail. create index a on foo (a); 0 rows inserted/updated/deleted ij> -- Check the consistency of the indexes VALUES SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'FOO'); 1 ----------- 1 ij> -- a delete of the whole table also exercises the index well. delete from foo; 1024 rows inserted/updated/deleted ij> drop table foo; 0 rows inserted/updated/deleted ij> -- ---------------------------------------------------------------------------- -- stress the conglomerate directory. abort of an alter table will clear -- the cache. -- ---------------------------------------------------------------------------- autocommit off; ij> create table a (a int); 0 rows inserted/updated/deleted ij> commit; ij> alter table a add column c1 int; 0 rows inserted/updated/deleted ij> rollback; ij> select * from a; A ----------- ij> drop table a; 0 rows inserted/updated/deleted ij> commit; ij> -- ---------------------------------------------------------------------------- -- test case for partial row runtime statistics. -- ---------------------------------------------------------------------------- create table foo (a int, b int, c int, d int, e int); 0 rows inserted/updated/deleted ij> insert into foo values (1, 2, 3, 4, 5); 1 row inserted/updated/deleted ij> insert into foo values (10, 20, 30, 40, 50); 1 row inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); 0 rows inserted/updated/deleted ij> maximumdisplaywidth 2500; ij> -- all the columns select * from foo; A |B |C |D |E ----------------------------------------------------------- 1 |2 |3 |4 |5 10 |20 |30 |40 |50 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- all the columns select * from foo 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: Table Scan ResultSet for FOO at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 2 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=5 Number of pages visited=1 Number of rows qualified=2 Number of rows visited=2 Scan type=heap start position: null stop position: null qualifiers: None ij> -- just last column - should be 5 and 50 select e from foo; E ----------- 5 50 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- just last column - should be 5 and 50 select e from foo 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: Table Scan ResultSet for FOO at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 2 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={4} Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=2 Number of rows visited=2 Scan type=heap start position: null stop position: null qualifiers: None ij> -- as subset of columns - should be 5,3,1 and 50,30,10 select e, c, a from foo; E |C |A ----------------------------------- 5 |3 |1 50 |30 |10 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- as subset of columns - should be 5,3,1 and 50,30,10 select e, c, a from foo 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 = 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: Table Scan ResultSet for FOO at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 2 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, 2, 4} Number of columns fetched=3 Number of pages visited=1 Number of rows qualified=2 Number of rows visited=2 Scan type=heap start position: null stop position: null qualifiers: None ij> -- as subset of columns, with qualifier in list - should be 5,3,1 and 50,30,10 select e, c, a from foo where foo.e = 5; E |C |A ----------------------------------- 5 |3 |1 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- as subset of columns, with qualifier in list - should be 5,3,1 and 50,30,10 select e, c, a from foo where foo.e = 5 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 = 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: Table Scan ResultSet for FOO 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, 2, 4} Number of columns fetched=3 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=2 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 4 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> -- as subset of columns, with qualifier not in list -- - should be 5,3,1 and 50,30,10 select e, c, a from foo where foo.b = 20; E |C |A ----------------------------------- 50 |30 |10 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- as subset of columns, with qualifier not in list -- - should be 5,3,1 and 50,30,10 select e, c, a from foo where foo.b = 20 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 = 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: Table Scan ResultSet for FOO 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, 2, 4} Number of columns fetched=4 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=2 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 1 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> -- as subset of columns - should be 1,2 and 10,20 select a, b from foo; A |B ----------------------- 1 |2 10 |20 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- as subset of columns - should be 1,2 and 10,20 select a, b from foo 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: Table Scan ResultSet for FOO at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 2 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 pages visited=1 Number of rows qualified=2 Number of rows visited=2 Scan type=heap start position: null stop position: null qualifiers: None ij> -- now check index scans - force the index just to -- make sure it does an index scan. create index foo_cover on foo (e, d, c, b, a); 0 rows inserted/updated/deleted ij> -- all the columns select * from foo; A |B |C |D |E ----------------------------------------------------------- 1 |2 |3 |4 |5 10 |20 |30 |40 |50 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- all the columns select * from foo 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 = 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: Index Scan ResultSet for FOO using index FOO_COVER at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 2 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, 3, 4} Number of columns fetched=5 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=2 Number of rows visited=2 Scan type=btree Tree height=1 start position: None stop position: None qualifiers: None ij> -- just last column - should be 5 and 50 select e from foo; E ----------- 5 50 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- just last column - should be 5 and 50 select e from foo 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 FOO using index FOO_COVER at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 2 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=2 Number of rows visited=2 Scan type=btree Tree height=1 start position: None stop position: None qualifiers: None ij> -- as subset of columns - should be 5,3,1 and 50,30,10 select e, c, a from foo; E |C |A ----------------------------------- 5 |3 |1 50 |30 |10 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- as subset of columns - should be 5,3,1 and 50,30,10 select e, c, a from foo 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 = 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: Project-Restrict ResultSet (2): 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: Index Scan ResultSet for FOO using index FOO_COVER at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 2 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, 2, 4} Number of columns fetched=3 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=2 Number of rows visited=2 Scan type=btree Tree height=1 start position: None stop position: None qualifiers: None ij> -- as subset of columns, with qualifier in list - should be 5,3,1 and 50, 30, 10 select e, c, a from foo where foo.e = 5; E |C |A ----------------------------------- 5 |3 |1 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- as subset of columns, with qualifier in list - should be 5,3,1 and 50, 30, 10 select e, c, a from foo where foo.e = 5 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 = 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 (2): 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 FOO using index FOO_COVER 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={0, 2, 4} Number of columns fetched=3 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> -- as subset of columns, with qualifier not in list - should be 5,3,1 -- and 50, 30, 10 select e, c, a from foo where foo.b = 20; E |C |A ----------------------------------- 50 |30 |10 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- as subset of columns, with qualifier not in list - should be 5,3,1 -- and 50, 30, 10 select e, c, a from foo where foo.b = 20 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 = 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 (2): 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 FOO using index FOO_COVER 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, 2, 3, 4} Number of columns fetched=4 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: None stop position: None qualifiers: Column[0][0] Id: 3 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> -- as subset of columns - should be 1,2 and 10, 20 select a, b from foo; A |B ----------------------- 1 |2 10 |20 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- as subset of columns - should be 1,2 and 10, 20 select a, b from foo 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 = 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: Index Scan ResultSet for FOO using index FOO_COVER at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 2 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={3, 4} Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=2 Number of rows visited=2 Scan type=btree Tree height=1 start position: None stop position: None qualifiers: None ij> -- check deleted row feature insert into foo values (100, 2, 3, 4, 5); 1 row inserted/updated/deleted ij> insert into foo values (1000, 2, 3, 4, 5); 1 row inserted/updated/deleted ij> delete from foo where foo.a = 100; 1 row inserted/updated/deleted ij> delete from foo where foo.a = 1000; 1 row inserted/updated/deleted ij> -- now check with deleted rows involved. -- all the columns select * from foo; A |B |C |D |E ----------------------------------------------------------- 1 |2 |3 |4 |5 10 |20 |30 |40 |50 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- now check with deleted rows involved. -- all the columns select * from foo 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 = 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: Index Scan ResultSet for FOO using index FOO_COVER at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 2 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, 3, 4} Number of columns fetched=5 Number of deleted rows visited=2 Number of pages visited=1 Number of rows qualified=2 Number of rows visited=4 Scan type=btree Tree height=1 start position: None stop position: None qualifiers: None ij> -- just last column - should be 5 and 50 select e from foo; E ----------- 5 50 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- just last column - should be 5 and 50 select e from foo 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 FOO using index FOO_COVER at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 2 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=2 Number of pages visited=1 Number of rows qualified=2 Number of rows visited=4 Scan type=btree Tree height=1 start position: None stop position: None qualifiers: None ij> -- as subset of columns - should be 5,3,1 and 50,30,10 select e, c, a from foo; E |C |A ----------------------------------- 5 |3 |1 50 |30 |10 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- as subset of columns - should be 5,3,1 and 50,30,10 select e, c, a from foo 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 = 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: Project-Restrict ResultSet (2): 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: Index Scan ResultSet for FOO using index FOO_COVER at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 2 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, 2, 4} Number of columns fetched=3 Number of deleted rows visited=2 Number of pages visited=1 Number of rows qualified=2 Number of rows visited=4 Scan type=btree Tree height=1 start position: None stop position: None qualifiers: None ij> -- as subset of columns, with qualifier in list - should be 5,3,1 and 50,30,10 select e, c, a from foo where foo.e = 5; E |C |A ----------------------------------- 5 |3 |1 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- as subset of columns, with qualifier in list - should be 5,3,1 and 50,30,10 select e, c, a from foo where foo.e = 5 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 = 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 (2): 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 FOO using index FOO_COVER 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={0, 2, 4} Number of columns fetched=3 Number of deleted rows visited=2 Number of pages visited=1 Number of rows qualified=1 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> -- as subset of columns, with qualifier not in list -- - should be 5,3,1 and 50,30,10 select e, c, a from foo where foo.b = 20; E |C |A ----------------------------------- 50 |30 |10 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- as subset of columns, with qualifier not in list -- - should be 5,3,1 and 50,30,10 select e, c, a from foo where foo.b = 20 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 = 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 (2): 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 FOO using index FOO_COVER 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, 2, 3, 4} Number of columns fetched=4 Number of deleted rows visited=2 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=4 Scan type=btree Tree height=1 start position: None stop position: None qualifiers: Column[0][0] Id: 3 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> -- as subset of columns - should be 1,2 and 10,20 select a, b from foo; A |B ----------------------- 1 |2 10 |20 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- as subset of columns - should be 1,2 and 10,20 select a, b from foo 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 = 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: Index Scan ResultSet for FOO using index FOO_COVER at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 2 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={3, 4} Number of columns fetched=2 Number of deleted rows visited=2 Number of pages visited=1 Number of rows qualified=2 Number of rows visited=4 Scan type=btree Tree height=1 start position: None stop position: None qualifiers: None ij> -- now check index scans - force the index just to -- make sure it does an index scan. create index foo_cover on foo (e, d, c, b, a); 0 rows inserted/updated/deleted WARNING 01504: The new index is a duplicate of an existing index: FOO_COVER. ij> -- all the columns select * from foo; A |B |C |D |E ----------------------------------------------------------- 1 |2 |3 |4 |5 10 |20 |30 |40 |50 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- all the columns select * from foo 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 = 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: Index Scan ResultSet for FOO using index FOO_COVER at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 2 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, 3, 4} Number of columns fetched=5 Number of deleted rows visited=2 Number of pages visited=1 Number of rows qualified=2 Number of rows visited=4 Scan type=btree Tree height=1 start position: None stop position: None qualifiers: None ij> -- just last column - should be 5 and 50 select e from foo; E ----------- 5 50 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- just last column - should be 5 and 50 select e from foo 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 FOO using index FOO_COVER at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 2 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=2 Number of pages visited=1 Number of rows qualified=2 Number of rows visited=4 Scan type=btree Tree height=1 start position: None stop position: None qualifiers: None ij> -- as subset of columns - should be 5,3,1 and 50,30,10 select e, c, a from foo; E |C |A ----------------------------------- 5 |3 |1 50 |30 |10 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- as subset of columns - should be 5,3,1 and 50,30,10 select e, c, a from foo 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 = 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: Project-Restrict ResultSet (2): 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: Index Scan ResultSet for FOO using index FOO_COVER at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 2 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, 2, 4} Number of columns fetched=3 Number of deleted rows visited=2 Number of pages visited=1 Number of rows qualified=2 Number of rows visited=4 Scan type=btree Tree height=1 start position: None stop position: None qualifiers: None ij> -- as subset of columns, with qualifier in list - should be 5,3,1 and 50, 30, 10 select e, c, a from foo where foo.e = 5; E |C |A ----------------------------------- 5 |3 |1 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- as subset of columns, with qualifier in list - should be 5,3,1 and 50, 30, 10 select e, c, a from foo where foo.e = 5 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 = 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 (2): 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 FOO using index FOO_COVER 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={0, 2, 4} Number of columns fetched=3 Number of deleted rows visited=2 Number of pages visited=1 Number of rows qualified=1 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> -- as subset of columns, with qualifier not in list - should be 5,3,1 -- and 50, 30, 10 select e, c, a from foo where foo.b = 20; E |C |A ----------------------------------- 50 |30 |10 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- as subset of columns, with qualifier not in list - should be 5,3,1 -- and 50, 30, 10 select e, c, a from foo where foo.b = 20 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 = 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 (2): 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 FOO using index FOO_COVER 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, 2, 3, 4} Number of columns fetched=4 Number of deleted rows visited=2 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=4 Scan type=btree Tree height=1 start position: None stop position: None qualifiers: Column[0][0] Id: 3 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> -- as subset of columns - should be 1,2 and 10, 20 select a, b from foo; A |B ----------------------- 1 |2 10 |20 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- as subset of columns - should be 1,2 and 10, 20 select a, b from foo 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 = 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: Index Scan ResultSet for FOO using index FOO_COVER at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 2 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={3, 4} Number of columns fetched=2 Number of deleted rows visited=2 Number of pages visited=1 Number of rows qualified=2 Number of rows visited=4 Scan type=btree Tree height=1 start position: None stop position: None qualifiers: None ij> -- ---------------------------------------------------------------------------- -- test case for costing - make sure optimizer picks obvious covered query. -- ---------------------------------------------------------------------------- call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); 0 rows inserted/updated/deleted ij> maximumdisplaywidth 2000; ij> create table base_table (a int, b varchar(1000)); 0 rows inserted/updated/deleted ij> insert into base_table values (1, PADSTRING('1',1000)); 1 row inserted/updated/deleted ij> insert into base_table values (2, PADSTRING('2',1000)); 1 row inserted/updated/deleted ij> insert into base_table values (3, PADSTRING('3',1000)); 1 row inserted/updated/deleted ij> insert into base_table values (4, PADSTRING('4',1000)); 1 row inserted/updated/deleted ij> insert into base_table values (5, PADSTRING('5',1000)); 1 row inserted/updated/deleted ij> insert into base_table values (6, PADSTRING('6',1000)); 1 row inserted/updated/deleted ij> insert into base_table values (7, PADSTRING('7',1000)); 1 row inserted/updated/deleted ij> insert into base_table values (8, PADSTRING('8',1000)); 1 row inserted/updated/deleted ij> insert into base_table values (9, PADSTRING('9',1000)); 1 row inserted/updated/deleted ij> insert into base_table values (10, PADSTRING('10',1000)); 1 row inserted/updated/deleted ij> create index cover_idx on base_table(a); 0 rows inserted/updated/deleted ij> -- make sure covered index is chosen select a from base_table; A ----------- 1 2 3 4 5 6 7 8 9 10 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- make sure covered index is chosen select a from base_table 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 BASE_TABLE using index COVER_IDX at serializable isolation level using share table 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} 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> -- ---------------------------------------------------------------------------- -- test for key too big error message. -- ---------------------------------------------------------------------------- create table d (id int not null, t_bigvarchar varchar(400), unique (id)); 0 rows inserted/updated/deleted ij> create index t_bigvarchar_ind on d ( t_bigvarchar); 0 rows inserted/updated/deleted ij> alter table d alter t_bigvarchar set data type varchar(4096); 0 rows inserted/updated/deleted ij> insert into d (id, t_bigvarchar) values (1, '1111111123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567 89012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012 34567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567 89012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012 34567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567 89012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012 34567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567 89012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012 34567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567 89012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012 34567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567 89012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012 34567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567 89012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012 34567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567 89012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012 34567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567 89012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012 34567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567 89012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012 34567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567 89012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012 34567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567 89012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012 34567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567 89012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012 34567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567 890123456789012345678901234567890123456789012345678901234567890123456'); ERROR XSCB6: Limitation: Record of a btree secondary index cannot be updated or inserted due to lack of space on the page. Use the parameters derby.storage.pageSize and/or derby.storage.pageReservedSpace to work around this limitation. ij> -- ---------------------------------------------------------------------------- -- test space for update -- ---------------------------------------------------------------------------- call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '1024'); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.minimumRecordSize', '1'); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageReservedSpace', '0'); 0 rows inserted/updated/deleted ij> create table testing (a varchar(100)); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.minimumRecordSize', NULL); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.minimumRecordSize', NULL); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageReservedSpace', NULL); 0 rows inserted/updated/deleted ij> insert into testing values ('a'); 1 row inserted/updated/deleted ij> insert into testing (select testing.a from testing); 1 row inserted/updated/deleted ij> insert into testing (select testing.a from testing); 2 rows inserted/updated/deleted ij> insert into testing (select testing.a from testing); 4 rows inserted/updated/deleted ij> insert into testing (select testing.a from testing); 8 rows inserted/updated/deleted ij> insert into testing (select testing.a from testing); 16 rows inserted/updated/deleted ij> insert into testing (select testing.a from testing); 32 rows inserted/updated/deleted ij> insert into testing (select testing.a from testing); 64 rows inserted/updated/deleted ij> update testing set a = 'abcd' where a = 'a'; 128 rows inserted/updated/deleted ij> create index zz on testing (a); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '1024'); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.minimumRecordSize', '1'); 0 rows inserted/updated/deleted ij> create table t1 (a varchar(100)); 0 rows inserted/updated/deleted ij> insert into t1 values ('a'); 1 row inserted/updated/deleted ij> insert into t1 (select t1.a from t1); 1 row inserted/updated/deleted ij> insert into t1 (select t1.a from t1); 2 rows inserted/updated/deleted ij> insert into t1 (select t1.a from t1); 4 rows inserted/updated/deleted ij> insert into t1 (select t1.a from t1); 8 rows inserted/updated/deleted ij> insert into t1 (select t1.a from t1); 16 rows inserted/updated/deleted ij> insert into t1 (select t1.a from t1); 32 rows inserted/updated/deleted ij> insert into t1 (select t1.a from t1); 64 rows inserted/updated/deleted ij> update t1 set a = 'abcd' where a = 'a'; 128 rows inserted/updated/deleted ij> create index zz1 on t1 (a); 0 rows inserted/updated/deleted ij> -- ---------------------------------------------------------------------------- -- test load with long columns with index creation -- ---------------------------------------------------------------------------- call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '1024'); 0 rows inserted/updated/deleted ij> create table long1 (a varchar(32000), b int, c int); 0 rows inserted/updated/deleted ij> insert into long1 values ('this is a long row which will get even longer and longer to force a stream', 1, 2); 1 row inserted/updated/deleted ij> insert into long1 values ('this is another long row which will get even longer and longer to force a stream', 2, 3); 1 row inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL); 0 rows inserted/updated/deleted ij> update long1 set a = a||a||a||a||a||a; 2 rows inserted/updated/deleted ij> update long1 set a = a||a||a||a||a||a; 2 rows inserted/updated/deleted ij> update long1 set a = a||a; 2 rows inserted/updated/deleted ij> select LENGTH(a) from long1; 1 ----------- 5328 5760 ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '1024'); 0 rows inserted/updated/deleted ij> create table long2 (a varchar(16384), b int, c int); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '16384'); 0 rows inserted/updated/deleted ij> create index long2i1 on long2 (a); 0 rows inserted/updated/deleted ij> create index long2i2 on long2 (a,b); 0 rows inserted/updated/deleted ij> create index long2i3 on long2 (a,b,c); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL); 0 rows inserted/updated/deleted ij> insert into long2 select * from long1; 2 rows inserted/updated/deleted ij> select LENGTH(a) from long2; 1 ----------- 5328 5760 ij> -- DefectId 1346 insert into long2 select * from long1; 2 rows inserted/updated/deleted ij> select LENGTH(a) from long2; 1 ----------- 5328 5328 5760 5760 ij> delete from long2; 4 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '1024'); 0 rows inserted/updated/deleted ij> create index long2small on long2 (a, c); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL); 0 rows inserted/updated/deleted ij> -- this small index should cause the insert to fail insert into long2 select * from long1; ERROR XSCB6: Limitation: Record of a btree secondary index cannot be updated or inserted due to lack of space on the page. Use the parameters derby.storage.pageSize and/or derby.storage.pageReservedSpace to work around this limitation. ij> -- DefectId 1346 -- the small index should cause this insert to also fail insert into long2 select * from long1; ERROR XSCB6: Limitation: Record of a btree secondary index cannot be updated or inserted due to lack of space on the page. Use the parameters derby.storage.pageSize and/or derby.storage.pageReservedSpace to work around this limitation. ij> select LENGTH(a) from long2; 1 ----------- ij> -- -- test case for track 1346 drop table long1; 0 rows inserted/updated/deleted ij> drop table long2; 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '1024'); 0 rows inserted/updated/deleted ij> create table long1 (a varchar(32000), b int, c int); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL); 0 rows inserted/updated/deleted ij> insert into long1 values ('this is a long row which will get even longer', 1, 2); 1 row inserted/updated/deleted ij> insert into long1 values ('a second row that will also grow very long', 2, 3); 1 row inserted/updated/deleted ij> update long1 set a = a||a||a||a||a||a; 2 rows inserted/updated/deleted ij> update long1 set a = a||a||a||a||a||a; 2 rows inserted/updated/deleted ij> update long1 set a = a||a; 2 rows inserted/updated/deleted ij> select LENGTH(a) from long1; 1 ----------- 3240 3024 ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '1024'); 0 rows inserted/updated/deleted ij> create table long2 (a varchar(30000), b int, c int); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '16384'); 0 rows inserted/updated/deleted ij> create index long2i1 on long2 (a); 0 rows inserted/updated/deleted ij> create index long2i2 on long2 (b, a); 0 rows inserted/updated/deleted ij> create index long2i3 on long2 (b, a, c); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL); 0 rows inserted/updated/deleted ij> insert into long2 select * from long1; 2 rows inserted/updated/deleted ij> insert into long2 select * from long1; 2 rows inserted/updated/deleted ij> select LENGTH(a) from long2; 1 ----------- 3240 3240 3024 3024 ij> drop table long1; 0 rows inserted/updated/deleted ij> drop table long2; 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '1024'); 0 rows inserted/updated/deleted ij> create table long1 (a varchar(32000), b int, c int); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL); 0 rows inserted/updated/deleted ij> insert into long1 values ('this is a long row which will get even longer', 1, 2); 1 row inserted/updated/deleted ij> insert into long1 values ('a second row that will also grow very long', 2, 3); 1 row inserted/updated/deleted ij> update long1 set a = a||a||a||a||a||a; 2 rows inserted/updated/deleted ij> update long1 set a = a||a||a||a||a||a; 2 rows inserted/updated/deleted ij> update long1 set a = a||a; 2 rows inserted/updated/deleted ij> select LENGTH(a) from long1; 1 ----------- 3240 3024 ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '1024'); 0 rows inserted/updated/deleted ij> create table long2 (a varchar(32000), b int, c int); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '16384'); 0 rows inserted/updated/deleted ij> create index long2i1 on long2 (a); 0 rows inserted/updated/deleted ij> create index long2i2 on long2 (b, a); 0 rows inserted/updated/deleted ij> create index long2i3 on long2 (b, a, c); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL); 0 rows inserted/updated/deleted ij> -- insert into the second table multiple times insert into long2 select * from long1; 2 rows inserted/updated/deleted ij> insert into long2 select * from long1; 2 rows inserted/updated/deleted ij> insert into long2 select * from long1; 2 rows inserted/updated/deleted ij> insert into long2 select * from long1; 2 rows inserted/updated/deleted ij> insert into long2 select * from long1; 2 rows inserted/updated/deleted ij> insert into long2 select * from long1; 2 rows inserted/updated/deleted ij> insert into long2 select * from long1; 2 rows inserted/updated/deleted ij> insert into long2 select * from long1; 2 rows inserted/updated/deleted ij> insert into long2 select * from long1; 2 rows inserted/updated/deleted ij> insert into long2 select * from long1; 2 rows inserted/updated/deleted ij> select LENGTH(a) from long2; 1 ----------- 3240 3240 3240 3240 3240 3240 3240 3240 3240 3240 3024 3024 3024 3024 3024 3024 3024 3024 3024 3024 ij> select count(*) from long2; 1 ----------- 20 ij> -- test case for track 1552 -- Make sure that a full scan which needs columns not in index does not -- use the index. Before the fix, access costing would make the optimizer -- pick the index because it incorrectly costed rows spanning pages. drop table a; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'A' because it does not exist. ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize','4096'); 0 rows inserted/updated/deleted ij> create table a (a int, b varchar(4000), c varchar(4000), d varchar(4000)); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL); 0 rows inserted/updated/deleted ij> create index a_idx on a (a); 0 rows inserted/updated/deleted ij> insert into a values (5, PADSTRING('a',4000), PADSTRING('a',4000), PADSTRING('a',4000)); 1 row inserted/updated/deleted ij> insert into a values (4, PADSTRING('a',4000), PADSTRING('a',4000), PADSTRING('a',4000)); 1 row inserted/updated/deleted ij> insert into a values (3, PADSTRING('a',4000), PADSTRING('a',4000), PADSTRING('a',4000)); 1 row inserted/updated/deleted ij> insert into a values (2, PADSTRING('a',4000), PADSTRING('a',4000), PADSTRING('a',4000)); 1 row inserted/updated/deleted ij> insert into a values (1, PADSTRING('a',4000), PADSTRING('a',4000), PADSTRING('a',4000)); 1 row inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); 0 rows inserted/updated/deleted ij> maximumdisplaywidth 2000; ij> select a, d from a; A |D ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 5 |a & 4 |a & 3 |a & 2 |a & 1 |a & ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select a, d from a 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: Table Scan ResultSet for A 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, 3} Number of columns fetched=2 Number of pages visited=6 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None ij> autocommit on; ij> -- test case for track 2241 -- The problem was that when the level of btree grew, sometimes a long -- row would be chosen as the branch delimiter, and the branch code did -- not throw the correct error noSpaceForKey error. call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.minimumRecordSize', NULL); 0 rows inserted/updated/deleted ij> create table b2241 (a int, b varchar(32000)); 0 rows inserted/updated/deleted ij> insert into b2241 values (1024, '01234567890123456789012345678901234567890123456789'); 1 row inserted/updated/deleted ij> insert into b2241 (select b2241.a + 1 , b from b2241); 1 row inserted/updated/deleted ij> insert into b2241 (select b2241.a + 2 , b from b2241); 2 rows inserted/updated/deleted ij> insert into b2241 (select b2241.a + 4 , b from b2241); 4 rows inserted/updated/deleted ij> insert into b2241 (select b2241.a + 8 , b from b2241); 8 rows inserted/updated/deleted ij> insert into b2241 (select b2241.a + 16 , b from b2241); 16 rows inserted/updated/deleted ij> insert into b2241 (select b2241.a + 32 , b from b2241); 32 rows inserted/updated/deleted ij> insert into b2241 (select b2241.a + 64 , b from b2241); 64 rows inserted/updated/deleted ij> update b2241 set b = b||b; 128 rows inserted/updated/deleted ij> update b2241 set b = b||b; 128 rows inserted/updated/deleted ij> update b2241 set b = b||b; 128 rows inserted/updated/deleted ij> update b2241 set b = b||b; 128 rows inserted/updated/deleted ij> update b2241 set b = b||b; 128 rows inserted/updated/deleted ij> select LENGTH(b) from b2241 where a = 1025; 1 ----------- 1600 ij> insert into b2241 (select 1, b||b||b||b||b||b||b||b from b2241 where a = 1024); 1 row inserted/updated/deleted ij> insert into b2241 (select 8000, b||b||b||b||b||b||b||b from b2241 where a = 1024); 1 row inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096'); 0 rows inserted/updated/deleted ij> -- this create index use to fail with an assert - -- should fail with key too big error. create index a on b2241 (b, a); ERROR XSCB6: Limitation: Record of a btree secondary index cannot be updated or inserted due to lack of space on the page. Use the parameters derby.storage.pageSize and/or derby.storage.pageReservedSpace to work around this limitation. ij> -- make sure table still accessable create index a on b2241 (b, a); ERROR XSCB6: Limitation: Record of a btree secondary index cannot be updated or inserted due to lack of space on the page. Use the parameters derby.storage.pageSize and/or derby.storage.pageReservedSpace to work around this limitation. ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL); 0 rows inserted/updated/deleted ij> -- delete 2 big records and then index should work. delete from b2241 where a = 1; 1 row inserted/updated/deleted ij> delete from b2241 where a = 8000; 1 row inserted/updated/deleted ij> create index a on b2241 (b, a); 0 rows inserted/updated/deleted ij> -- Check the consistency of the indexes VALUES SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'FOO'); 1 ----------- 1 ij> drop table b2241; 0 rows inserted/updated/deleted ij> -- test case for reclaiming deleted rows during split. -- o insert bunch of rows with sequential keys. -- o create non-unique index. -- o delete every other one - this will make normat post commit not fire. -- o commit -- o now reinsert rows into the "holes" which before the fix would cause -- splits, but now will force reclaim space and reuse existing space in -- btree. autocommit off; ij> -- set page size back to default. call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096'); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.minimumRecordSize', '1'); 0 rows inserted/updated/deleted ij> commit; ij> drop table foo; 0 rows inserted/updated/deleted ij> drop table foo2; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'FOO2' because it does not exist. ij> -- create and load a table with values from 1024 down to 1, create table foo (a int, b char(200), c int); 0 rows inserted/updated/deleted ij> insert into foo values (1024, 'even', 0); 1 row inserted/updated/deleted ij> insert into foo (select foo.a - 1, 'odd' , 1 from foo); 1 row inserted/updated/deleted ij> insert into foo (select foo.a - 2, foo.b, foo.c from foo); 2 rows inserted/updated/deleted ij> insert into foo (select foo.a - 4, foo.b, foo.c from foo); 4 rows inserted/updated/deleted ij> insert into foo (select foo.a - 8, foo.b, foo.c from foo); 8 rows inserted/updated/deleted ij> insert into foo (select foo.a - 16, foo.b, foo.c from foo); 16 rows inserted/updated/deleted ij> insert into foo (select foo.a - 32, foo.b, foo.c from foo); 32 rows inserted/updated/deleted ij> insert into foo (select foo.a - 64, foo.b, foo.c from foo); 64 rows inserted/updated/deleted ij> insert into foo (select foo.a - 128, foo.b, foo.c from foo); 128 rows inserted/updated/deleted ij> insert into foo (select foo.a - 256, foo.b, foo.c from foo); 256 rows inserted/updated/deleted ij> insert into foo (select foo.a - 512, foo.b, foo.c from foo); 512 rows inserted/updated/deleted ij> -- insert into the "holes", but different keys (even2 instead of even) create table foo2 (a int, b char(200), c int); 0 rows inserted/updated/deleted ij> insert into foo2 (select * from foo); 1024 rows inserted/updated/deleted ij> delete from foo2 where foo2.c = 1; 512 rows inserted/updated/deleted ij> -- create "packed" index. create index a on foo (a, b); 0 rows inserted/updated/deleted ij> -- delete ever other row delete from foo where foo.c = 0; 512 rows inserted/updated/deleted ij> -- turn all the deletes into "committed deletes" commit; ij> insert into foo (select foo2.a, 'even2', foo2.c from foo2); 512 rows inserted/updated/deleted ij> commit; ij> -- insert dups insert into foo (select foo2.a, 'even2', foo2.c from foo2); 512 rows inserted/updated/deleted ij> commit; ij> -- a delete of the whole table also exercises the btree well. delete from foo; 1536 rows inserted/updated/deleted ij> drop table foo; 0 rows inserted/updated/deleted ij> drop table foo2; 0 rows inserted/updated/deleted ij> -- try same test with unique index. -- create and load a table with values from 1024 down to 1, create table foo (a int, b char(200), c int); 0 rows inserted/updated/deleted ij> insert into foo values (1024, 'even', 0); 1 row inserted/updated/deleted ij> insert into foo (select foo.a - 1, 'odd' , 1 from foo); 1 row inserted/updated/deleted ij> insert into foo (select foo.a - 2, foo.b, foo.c from foo); 2 rows inserted/updated/deleted ij> insert into foo (select foo.a - 4, foo.b, foo.c from foo); 4 rows inserted/updated/deleted ij> insert into foo (select foo.a - 8, foo.b, foo.c from foo); 8 rows inserted/updated/deleted ij> insert into foo (select foo.a - 16, foo.b, foo.c from foo); 16 rows inserted/updated/deleted ij> insert into foo (select foo.a - 32, foo.b, foo.c from foo); 32 rows inserted/updated/deleted ij> insert into foo (select foo.a - 64, foo.b, foo.c from foo); 64 rows inserted/updated/deleted ij> insert into foo (select foo.a - 128, foo.b, foo.c from foo); 128 rows inserted/updated/deleted ij> insert into foo (select foo.a - 256, foo.b, foo.c from foo); 256 rows inserted/updated/deleted ij> insert into foo (select foo.a - 512, foo.b, foo.c from foo); 512 rows inserted/updated/deleted ij> -- insert into the "holes", but different keys (even2 instead of even) create table foo2 (a int, b char(200), c int); 0 rows inserted/updated/deleted ij> insert into foo2 (select * from foo); 1024 rows inserted/updated/deleted ij> delete from foo2 where foo2.c = 1; 512 rows inserted/updated/deleted ij> -- create "packed" unique index. create unique index a on foo (a, b); 0 rows inserted/updated/deleted ij> -- delete ever other row delete from foo where foo.c = 0; 512 rows inserted/updated/deleted ij> -- turn all the deletes into "committed deletes" commit; ij> insert into foo (select foo2.a, 'even2', foo2.c from foo2); 512 rows inserted/updated/deleted ij> commit; ij> -- insert dups will cause error insert into foo (select foo2.a, 'even2', foo2.c from foo2); ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'A' defined on 'FOO'. ij> commit; ij> -- a delete of the whole table also exercises the btree well. delete from foo; 1024 rows inserted/updated/deleted ij> drop table foo; 0 rows inserted/updated/deleted ij> drop table foo2; 0 rows inserted/updated/deleted ij> commit; ij> -- another simple test of reclaim deleted row code paths. -- this test should not reclaim rows as deletes are not committed. create table foo (a int, b varchar(1100), c int); 0 rows inserted/updated/deleted ij> create index a on foo (a, b); 0 rows inserted/updated/deleted ij> insert into foo values (1, PADSTRING('a',1100), 1); 1 row inserted/updated/deleted ij> insert into foo values (2, PADSTRING('a',1100), 1); 1 row inserted/updated/deleted ij> insert into foo values (3, PADSTRING('a',1100), 1); 1 row inserted/updated/deleted ij> commit; ij> delete from foo where foo.a = 1; 1 row inserted/updated/deleted ij> delete from foo where foo.a = 2; 1 row inserted/updated/deleted ij> insert into foo values (-1, PADSTRING('ab',1100), 1); 1 row inserted/updated/deleted ij> insert into foo values (-2, PADSTRING('ab',1100), 1); 1 row inserted/updated/deleted ij> rollback; ij> drop table foo; 0 rows inserted/updated/deleted ij> -- another simple test of reclaim deleted row code paths. -- this test should reclaim rows as deletes are not committed. create table foo (a int, b varchar(1100), c int); 0 rows inserted/updated/deleted ij> create index a on foo (a, b); 0 rows inserted/updated/deleted ij> insert into foo values (1, PADSTRING('a',1100), 1); 1 row inserted/updated/deleted ij> insert into foo values (2, PADSTRING('a',1100), 1); 1 row inserted/updated/deleted ij> insert into foo values (3, PADSTRING('a',1100), 1); 1 row inserted/updated/deleted ij> commit; ij> delete from foo where foo.a = 1; 1 row inserted/updated/deleted ij> delete from foo where foo.a = 2; 1 row inserted/updated/deleted ij> commit; ij> insert into foo values (-1, PADSTRING('ab',1100), 1); 1 row inserted/updated/deleted ij> insert into foo values (-2, PADSTRING('ab',1100), 1); 1 row inserted/updated/deleted ij> rollback; ij> drop table foo; 0 rows inserted/updated/deleted ij> -- this test will not reclaim rows because the parent xact has table level lock. create table foo (a int, b varchar(1100), c int); 0 rows inserted/updated/deleted ij> create index a on foo (a, b); 0 rows inserted/updated/deleted ij> insert into foo values (1, PADSTRING('a',1100), 1); 1 row inserted/updated/deleted ij> insert into foo values (2, PADSTRING('a',1100), 1); 1 row inserted/updated/deleted ij> insert into foo values (3, PADSTRING('a',1100), 1); 1 row inserted/updated/deleted ij> delete from foo where foo.a = 1; 1 row inserted/updated/deleted ij> insert into foo values (0, PADSTRING('a',1100), 1); 1 row inserted/updated/deleted ij> insert into foo values (1, PADSTRING('a',1100), 1); 1 row inserted/updated/deleted ij> rollback; ij> drop table foo; 0 rows inserted/updated/deleted ij> -- test case for track 2778 -- Make sure that an update which causes a row to go from a non long row -- to a long row can be aborted correctly. Prior to this fix the columns -- moving off the page would be corrupted. -- create a base table that contains 2 rows, 19 columns, that leaves just -- 1 byte free on the page. freeSpace: 1, spareSpace: 10, PageSize: 2048 drop table t2778; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'T2778' because it does not exist. ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '2048'); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageReservedSpace', '10'); 0 rows inserted/updated/deleted ij> create table t2778 ( col00 char(2), col01 char(1), col02 char(99), col03 char(11), col04 char(7), col05 char(11), col06 char(6), col07 char(6), col08 char(2), col09 char(6), col10 varchar(1000), col11 char(2), col12 char(1), col13 char(7), col14 char(24), col15 char(1), col16 char(166), col17 char(207), col18 char(2) ); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageReservedSpace', NULL); 0 rows inserted/updated/deleted ij> create unique index a_idx on t2778 (col00); ERROR X0Y32: Index 'A_IDX' already exists in Schema 'APP'. ij> commit; ij> insert into t2778 values ( '0_', '0', '0_col02', '0_col03', '0_col04', '0_col05', '0_06', '0_07', '0_', '0_09', '0_col10llllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll012340_col10lllllllllll', '0_', '0', '0_col13', '0_col14', '0', '0_col16', '0_col17', '0_' ); 1 row inserted/updated/deleted ij> insert into t2778 values ( '1_', '1', '1_col02', '1_col03', '1_col04', '1_col05', '1_06', '1_07', '1_', '1_09', '1_col10llllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll012340_col10lllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll012340_col10lllllllllllxxxxxxxxxxxxxxxxxxx', '1_', '1', '1_col13', '1_col14', '1', '1_col16', '1_col17', '1_' ); 1 row inserted/updated/deleted ij> commit; ij> select col16, col17, col18 from t2778; COL16 |COL17 |COL& ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0_col16 |0_col17 |0_ 1_col16 |1_col17 |1_ ij> commit; ij> update t2778 --derby-properties index = a_idx set col10 = '0_col10llllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll012340_col10lllllllllllxxxxxx' where col00 = '0_'; ERROR 42X01: Syntax error: DERBY-PROPERTIES. ij> rollback; ij> -- prior to the fix col17 and col18 would come back null. select col01, col02, col03, col04, col05, col06, col07, col08, col09, col10, col11, col12, col13, col14, col15, col16, col17, col18 from t2778; COL&|COL02 |COL03 |COL04 |COL05 |COL06 |COL07 |COL&|COL09 |COL10 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0 |0_col02 |0_col03 |0_col04|0_col05 |0_06 |0_07 |0_ |0_09 |0_col10llllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll012340_col10lllllllllll |0_ |0 |0_col13|0_col14 |0 |0_col16 |0_col17 |0_ 1 |1_col02 |1_col03 |1_col04|1_col05 |1_06 |1_07 |1_ |1_09 |1_col10llllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll012340_col10lllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll012340_col10lllllllllllxxxxxxxxxxxxxxxxxxx |1_ |1 |1_col13|1_col14 |1 |1_col16 |1_col17 |1_ ij> commit; ij> -- test case for track 3149, improving max on btree optimization autocommit off; ij> create table foo (a int, b varchar(500), c int); 0 rows inserted/updated/deleted ij> insert into foo values (1, PADSTRING('1',500), 1); 1 row inserted/updated/deleted ij> insert into foo values (11, PADSTRING('11',500), 1); 1 row inserted/updated/deleted ij> insert into foo values (12, PADSTRING('12',500), 1); 1 row inserted/updated/deleted ij> insert into foo values (13, PADSTRING('13',500), 1); 1 row inserted/updated/deleted ij> insert into foo values (14, PADSTRING('14',500), 1); 1 row inserted/updated/deleted ij> insert into foo values (15, PADSTRING('15',500), 1); 1 row inserted/updated/deleted ij> insert into foo values (16, PADSTRING('16',500), 1); 1 row inserted/updated/deleted ij> insert into foo values (17, PADSTRING('17',500), 1); 1 row inserted/updated/deleted ij> insert into foo values (18, PADSTRING('18',500), 1); 1 row inserted/updated/deleted ij> insert into foo values (2, PADSTRING('2',500), 1); 1 row inserted/updated/deleted ij> insert into foo values (3, PADSTRING('3',500), 1); 1 row inserted/updated/deleted ij> insert into foo values (4, PADSTRING('4',500), 1); 1 row inserted/updated/deleted ij> insert into foo values (5, PADSTRING('5',500), 1); 1 row inserted/updated/deleted ij> insert into foo values (6, PADSTRING('6',500), 1); 1 row inserted/updated/deleted ij> insert into foo values (7, PADSTRING('7',500), 1); 1 row inserted/updated/deleted ij> insert into foo values (8, PADSTRING('8',500), 1); 1 row inserted/updated/deleted ij> insert into foo values (9, PADSTRING('9',500), 1); 1 row inserted/updated/deleted ij> create index foox on foo (b); 0 rows inserted/updated/deleted ij> commit; ij> -- normal max optimization, last row in index is not deleted. select max(b) from foo; 1 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 9 ij> -- new max optimization, last row in index is deleted but others on page aren't. delete from foo where a = 9; 1 row inserted/updated/deleted ij> select max(b) from foo; 1 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 8 ij> -- new max optimization, last row in index is deleted but others on page aren't. delete from foo where a = 8; 1 row inserted/updated/deleted ij> select max(b) from foo; 1 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 7 ij> -- max optimization does not work - fail over to scan, all rows on last page are -- deleted. delete from foo where a > 2; 13 rows inserted/updated/deleted ij> select max(b) from foo; 1 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 ij> commit; ij> drop table foo; 0 rows inserted/updated/deleted ij> ------------------------------------------------------------------------ -- regression test for bugs 3368, 3370 -- the bugs arose for the edge case where pageReservedSpace = 100 -- before bug 3368 was fixed, a short row insert caused 2 pages to be -- allocated per short row insert. drop table a; 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageReservedSpace', '100'); 0 rows inserted/updated/deleted ij> create table a (a int); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageReservedSpace', NULL); 0 rows inserted/updated/deleted ij> insert into a values (1); 1 row inserted/updated/deleted ij> select numallocatedpages from new org.apache.derby.diag.SpaceTable('A') as a; NUMALLOCATEDPAGES -------------------- 2 ij> insert into a values (2); 1 row inserted/updated/deleted ij> select numallocatedpages from new org.apache.derby.diag.SpaceTable('A') as a; NUMALLOCATEDPAGES -------------------- 3 ij> insert into a values (1); 1 row inserted/updated/deleted ij> select numallocatedpages from new org.apache.derby.diag.SpaceTable('A') as a; NUMALLOCATEDPAGES -------------------- 4 ij> insert into a values (2); 1 row inserted/updated/deleted ij> select numallocatedpages from new org.apache.derby.diag.SpaceTable('A') as a; NUMALLOCATEDPAGES -------------------- 5 ij> ------------------------------------------------------------------------ -- regression test for bug 4595, make sure index used in unique key update -- even if table has zero rows. -- call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); 0 rows inserted/updated/deleted ij> maximumdisplaywidth 9000; ij> -- try delete/update statement compiled against table with 0 rows drop table foo; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'FOO' because it does not exist. ij> create table foo (a int, b int); 0 rows inserted/updated/deleted ij> create unique index foox on foo (a); 0 rows inserted/updated/deleted ij> -- delete against table with 0 rows. delete from foo where a = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- delete against table with 0 rows. delete from foo where a = 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: Delete ResultSet using row locking: deferred: false Rows deleted = 0 Indexes updated = 1 Execute Time = 0 Project-Restrict ResultSet (2): 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: Project-Restrict ResultSet (1): 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 FOO using index FOOX at serializable isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 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: >= 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> -- update against table with 0 rows. update foo set b = 1 where a = 2; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- update against table with 0 rows. update foo set b = 1 where a = 2 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: Update ResultSet using row locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (2): 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 Row to Base Row ResultSet for FOO: Number of opens = 1 Rows seen = 0 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 FOO using index FOOX at serializable isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 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=0 Number of rows visited=0 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 against table with 0 rows. select * from foo where a = 2; A |B ----------------------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- select against table with 0 rows. select * from foo where a = 2 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 Row to Base Row ResultSet for FOO: Number of opens = 1 Rows seen = 0 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 FOO using index FOOX at serializable isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 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=0 Number of rows visited=0 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 against table with 0 rows. select a from foo where a = 2; A ----------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- select against table with 0 rows. select a from foo where a = 2 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 FOO using index FOOX at serializable isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 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=0 Number of rows visited=0 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 against table with 0 rows. select a from foo where a = 2; A ----------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- select against table with 0 rows. select a from foo where a = 2 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 FOO using index FOOX at serializable isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 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=0 Number of rows visited=0 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> -- now insert one row and make sure still same plan. Previous to 4595 -- 0 row plan was a table scan and it would not change when 1 row was inserted. insert into foo values (1, 1); 1 row inserted/updated/deleted ij> -- update against table with 1 row. update foo set b = 2 where a = 1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- update against table with 1 row. update foo set b = 2 where a = 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: Update ResultSet using row locking: deferred: false Rows updated = 1 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (2): 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 FOO: 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 FOO using index FOOX at serializable isolation level using exclusive 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> -- delete against table with 1 row. delete from foo where a = 1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- delete against table with 1 row. delete from foo where a = 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: Delete ResultSet using row locking: deferred: false Rows deleted = 1 Indexes updated = 1 Execute Time = 0 Project-Restrict ResultSet (2): 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 (1): 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 FOO using index FOOX at serializable isolation level using exclusive 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> -- try delete/update statement compiled against table with 1 row. drop table foo; 0 rows inserted/updated/deleted ij> create table foo (a int, b int); 0 rows inserted/updated/deleted ij> insert into foo values (1, 1); 1 row inserted/updated/deleted ij> create unique index foox on foo (a); 0 rows inserted/updated/deleted ij> -- update against table with 1 row. update foo set b = 2 where a = 1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- update against table with 1 row. update foo set b = 2 where a = 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: Update ResultSet using row locking: deferred: false Rows updated = 1 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (2): 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 FOO: 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 FOO using index FOOX at serializable isolation level using exclusive 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> -- delete against table with 1 row. delete from foo where a = 1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- delete against table with 1 row. delete from foo where a = 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: Delete ResultSet using row locking: deferred: false Rows deleted = 1 Indexes updated = 1 Execute Time = 0 Project-Restrict ResultSet (2): 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 (1): 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 FOO using index FOOX at serializable isolation level using exclusive 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> -- repeat set of 4595 tests against table with primary key, vs. unique index - -- there should be no difference in plan shape. -- try delete/update statement compiled against table with 0 rows drop table foo; 0 rows inserted/updated/deleted ij> create table foo (a int not null primary key, b int); 0 rows inserted/updated/deleted ij> -- delete against table with 0 rows. delete from foo where a = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- delete against table with 0 rows. delete from foo where a = 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: Delete ResultSet using row locking: deferred: false Rows deleted = 0 Indexes updated = 1 Execute Time = 0 Project-Restrict ResultSet (2): 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: Project-Restrict ResultSet (1): 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 FOO using constraint xxxxGENERATED-IDxxxx at serializable isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 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: >= on first 1 column(s). Ordered null semantics on the following columns: 0 stop position: > on first 1 column(s). Ordered null semantics on the following columns: 0 qualifiers: None ij> -- update against table with 0 rows. update foo set b = 1 where a = 2; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- update against table with 0 rows. update foo set b = 1 where a = 2 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: Update ResultSet using row locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (2): 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 Row to Base Row ResultSet for FOO: Number of opens = 1 Rows seen = 0 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 FOO using constraint xxxxGENERATED-IDxxxx at serializable isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 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=0 Number of rows visited=0 Scan type=btree Tree height=1 start position: >= on first 1 column(s). Ordered null semantics on the following columns: 0 stop position: > on first 1 column(s). Ordered null semantics on the following columns: 0 qualifiers: None ij> -- select against table with 0 rows. select * from foo where a = 2; A |B ----------------------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- select against table with 0 rows. select * from foo where a = 2 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 Row to Base Row ResultSet for FOO: Number of opens = 1 Rows seen = 0 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 FOO using constraint xxxxGENERATED-IDxxxx at serializable isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 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=0 Number of rows visited=0 Scan type=btree Tree height=1 start position: >= on first 1 column(s). Ordered null semantics on the following columns: 0 stop position: > on first 1 column(s). Ordered null semantics on the following columns: 0 qualifiers: None ij> -- select against table with 0 rows. select a from foo where a = 2; A ----------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- select against table with 0 rows. select a from foo where a = 2 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 FOO using constraint xxxxGENERATED-IDxxxx at serializable isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 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=0 Number of rows visited=0 Scan type=btree Tree height=1 start position: >= on first 1 column(s). Ordered null semantics on the following columns: 0 stop position: > on first 1 column(s). Ordered null semantics on the following columns: 0 qualifiers: None ij> -- select against table with 0 rows. select a from foo where a = 2; A ----------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- select against table with 0 rows. select a from foo where a = 2 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 FOO using constraint xxxxGENERATED-IDxxxx at serializable isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 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=0 Number of rows visited=0 Scan type=btree Tree height=1 start position: >= on first 1 column(s). Ordered null semantics on the following columns: 0 stop position: > on first 1 column(s). Ordered null semantics on the following columns: 0 qualifiers: None ij> -- now insert one row and make sure still same plan. Previous to 4595 -- 0 row plan was a table scan and it would not change when 1 row was inserted. insert into foo values (1, 1); 1 row inserted/updated/deleted ij> -- update against table with 1 row. update foo set b = 2 where a = 1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- update against table with 1 row. update foo set b = 2 where a = 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: Update ResultSet using row locking: deferred: false Rows updated = 1 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (2): 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 FOO: 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 FOO using constraint xxxxGENERATED-IDxxxx at serializable isolation level using exclusive 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: 0 stop position: > on first 1 column(s). Ordered null semantics on the following columns: 0 qualifiers: None ij> -- delete against table with 1 row. delete from foo where a = 1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- delete against table with 1 row. delete from foo where a = 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: Delete ResultSet using row locking: deferred: false Rows deleted = 1 Indexes updated = 1 Execute Time = 0 Project-Restrict ResultSet (2): 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 (1): 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 FOO using constraint xxxxGENERATED-IDxxxx at serializable isolation level using exclusive 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: 0 stop position: > on first 1 column(s). Ordered null semantics on the following columns: 0 qualifiers: None ij> -- try delete/update statement compiled against table with 1 row. drop table foo; 0 rows inserted/updated/deleted ij> create table foo (a int not null primary key, b int); 0 rows inserted/updated/deleted ij> insert into foo values (1, 1); 1 row inserted/updated/deleted ij> -- update against table with 1 row. update foo set b = 2 where a = 1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- update against table with 1 row. update foo set b = 2 where a = 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: Update ResultSet using row locking: deferred: false Rows updated = 1 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (2): 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 FOO: 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 FOO using constraint xxxxGENERATED-IDxxxx at serializable isolation level using exclusive 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: 0 stop position: > on first 1 column(s). Ordered null semantics on the following columns: 0 qualifiers: None ij> -- delete against table with 1 row. delete from foo where a = 1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- delete against table with 1 row. delete from foo where a = 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: Delete ResultSet using row locking: deferred: false Rows deleted = 1 Indexes updated = 1 Execute Time = 0 Project-Restrict ResultSet (2): 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 (1): 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 FOO using constraint xxxxGENERATED-IDxxxx at serializable isolation level using exclusive 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: 0 stop position: > on first 1 column(s). Ordered null semantics on the following columns: 0 qualifiers: None ij> -- select against table with 1 row. select * from foo where a = 2; A |B ----------------------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- select against table with 1 row. select * from foo where a = 2 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 Row to Base Row ResultSet for FOO: Number of opens = 1 Rows seen = 0 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 FOO using constraint xxxxGENERATED-IDxxxx at serializable isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 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=0 Number of rows visited=0 Scan type=btree Tree height=1 start position: >= on first 1 column(s). Ordered null semantics on the following columns: 0 stop position: > on first 1 column(s). Ordered null semantics on the following columns: 0 qualifiers: None ij> -- select against table with 1 row. select a from foo where a = 2; A ----------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- select against table with 1 row. select a from foo where a = 2 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 FOO using constraint xxxxGENERATED-IDxxxx at serializable isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 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=0 Number of rows visited=0 Scan type=btree Tree height=1 start position: >= on first 1 column(s). Ordered null semantics on the following columns: 0 stop position: > on first 1 column(s). Ordered null semantics on the following columns: 0 qualifiers: None ij> -- select against table with 1 row. select a from foo where a = 2; A ----------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- select against table with 1 row. select a from foo where a = 2 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 FOO using constraint xxxxGENERATED-IDxxxx at serializable isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 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=0 Number of rows visited=0 Scan type=btree Tree height=1 start position: >= on first 1 column(s). Ordered null semantics on the following columns: 0 stop position: > on first 1 column(s). Ordered null semantics on the following columns: 0 qualifiers: None ij> -- repeat set of 4595 tests against table with non-unique index with no -- statistics. -- there should be no difference in plan shape. -- try delete/update statement compiled against table with 0 rows drop table foo; 0 rows inserted/updated/deleted ij> create table foo (a int, b int); 0 rows inserted/updated/deleted ij> create index foox on foo (a); 0 rows inserted/updated/deleted ij> -- delete against table with 0 rows. delete from foo where a = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- delete against table with 0 rows. delete from foo where a = 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: Delete ResultSet using row locking: deferred: false Rows deleted = 0 Indexes updated = 1 Execute Time = 0 Project-Restrict ResultSet (2): 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: Project-Restrict ResultSet (1): 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 FOO using index FOOX at serializable isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 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: >= 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> -- update against table with 0 rows. update foo set b = 1 where a = 2; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- update against table with 0 rows. update foo set b = 1 where a = 2 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: Update ResultSet using row locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (2): 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 Row to Base Row ResultSet for FOO: Number of opens = 1 Rows seen = 0 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 FOO using index FOOX at serializable isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 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=0 Number of rows visited=0 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 against table with 0 rows. select * from foo where a = 2; A |B ----------------------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- select against table with 0 rows. select * from foo where a = 2 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 Row to Base Row ResultSet for FOO: Number of opens = 1 Rows seen = 0 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 FOO using index FOOX at serializable isolation level using share row 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=All 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: >= 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 against table with 0 rows. select a from foo where a = 2; A ----------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- select against table with 0 rows. select a from foo where a = 2 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 FOO using index FOOX at serializable isolation level using share row 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} Number of columns fetched=1 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: >= 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 against table with 0 rows. select a from foo where a = 2; A ----------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- select against table with 0 rows. select a from foo where a = 2 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 FOO using index FOOX at serializable isolation level using share row 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} Number of columns fetched=1 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: >= 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> -- now insert one row and make sure still same plan. Previous to 4595 -- 0 row plan was a table scan and it would not change when 1 row was inserted. insert into foo values (1, 1); 1 row inserted/updated/deleted ij> -- update against table with 1 row. update foo set b = 2 where a = 1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- update against table with 1 row. update foo set b = 2 where a = 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: Update ResultSet using row locking: deferred: false Rows updated = 1 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (2): 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 FOO: 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 FOO using index FOOX at serializable isolation level using exclusive 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> -- delete against table with 1 row. delete from foo where a = 1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- delete against table with 1 row. delete from foo where a = 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: Delete ResultSet using row locking: deferred: false Rows deleted = 1 Indexes updated = 1 Execute Time = 0 Project-Restrict ResultSet (2): 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 (1): 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 FOO using index FOOX at serializable isolation level using exclusive 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> -- try delete/update statement compiled against table with 1 row. drop table foo; 0 rows inserted/updated/deleted ij> create table foo (a int, b int); 0 rows inserted/updated/deleted ij> create index foox on foo (a); 0 rows inserted/updated/deleted ij> insert into foo values (1, 1); 1 row inserted/updated/deleted ij> -- update against table with 1 row. update foo set b = 2 where a = 1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- update against table with 1 row. update foo set b = 2 where a = 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: Update ResultSet using row locking: deferred: false Rows updated = 1 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (2): 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 FOO: 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 FOO using index FOOX at serializable isolation level using exclusive 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> -- delete against table with 1 row. delete from foo where a = 1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- delete against table with 1 row. delete from foo where a = 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: Delete ResultSet using row locking: deferred: false Rows deleted = 1 Indexes updated = 1 Execute Time = 0 Project-Restrict ResultSet (2): 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 (1): 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 FOO using index FOOX at serializable isolation level using exclusive 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> -- select against table with 1 row. select * from foo where a = 2; A |B ----------------------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- select against table with 1 row. select * from foo where a = 2 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 Row to Base Row ResultSet for FOO: Number of opens = 1 Rows seen = 0 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 FOO using index FOOX at serializable isolation level using share row 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=All 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: >= 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 against table with 1 row. select a from foo where a = 2; A ----------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- select against table with 1 row. select a from foo where a = 2 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 FOO using index FOOX at serializable isolation level using share row 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} Number of columns fetched=1 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: >= 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 against table with 1 row. select a from foo where a = 2; A ----------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- select against table with 1 row. select a from foo where a = 2 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 FOO using index FOOX at serializable isolation level using share row 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} Number of columns fetched=1 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: >= 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> ------------------------------------------------------------------------ -- simple regression test for qualifier work. ------------------------------------------------------------------------ drop table foo; 0 rows inserted/updated/deleted ij> commit; ij> create table foo (a int, b int, c int); 0 rows inserted/updated/deleted ij> insert into foo values (1, 10, 100); 1 row inserted/updated/deleted ij> insert into foo values (2, 20, 200); 1 row inserted/updated/deleted ij> insert into foo values (3, 30, 300); 1 row inserted/updated/deleted ij> -- should return no rows select a, b, c from foo where a = 1 and b = 20; A |B |C ----------------------------------- ij> -- should return one row select a, b, c from foo where a = 3 and b = 30; A |B |C ----------------------------------- 3 |30 |300 ij> select a, b, c from foo where a = 3 or c = 40; A |B |C ----------------------------------- 3 |30 |300 ij> -- should return 2 rows select a, b, c from foo where a = 1 or b = 20; A |B |C ----------------------------------- 1 |10 |100 2 |20 |200 ij> select a, b, c from foo where a = 1 or a = 3; A |B |C ----------------------------------- 1 |10 |100 3 |30 |300 ij> DROP FUNCTION PADSTRING; 0 rows inserted/updated/deleted ij> DROP PROCEDURE WAIT_FOR_POST_COMMIT; 0 rows inserted/updated/deleted ij> exit;