ij> -- -- Licensed to the Apache Software Foundation (ASF) under one or more -- contributor license agreements. See the NOTICE file distributed with -- this work for additional information regarding copyright ownership. -- The ASF licenses this file to You under the Apache License, Version 2.0 -- (the "License"); you may not use this file except in compliance with -- the License. You may obtain a copy of the License at -- -- http://www.apache.org/licenses/LICENSE-2.0 -- -- Unless required by applicable law or agreed to in writing, software -- distributed under the License is distributed on an "AS IS" BASIS, -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -- -- test distinct elimination set isolation to rr; 0 rows inserted/updated/deleted ij> -- eliminate distincts based on a uniqueness condition -- create tables create table one(c1 int, c2 int, c3 int, c4 int, c5 int); 0 rows inserted/updated/deleted ij> create unique index one_c1 on one(c1); 0 rows inserted/updated/deleted ij> create table two(c1 int, c2 int, c3 int, c4 int, c5 int); 0 rows inserted/updated/deleted ij> create unique index two_c1c3 on two(c1, c3); 0 rows inserted/updated/deleted ij> create table three(c1 int, c2 int, c3 int, c4 int, c5 int); 0 rows inserted/updated/deleted ij> create unique index three_c1 on three(c1); 0 rows inserted/updated/deleted ij> create table four(c1 int, c2 int, c3 int, c4 int, c5 int); 0 rows inserted/updated/deleted ij> create unique index four_c1c3 on four(c1, c3); 0 rows inserted/updated/deleted ij> CREATE TABLE "APP"."IDEPT" ("DISCRIM_DEPT" VARCHAR(32), "NO1" INTEGER NOT NULL, "NAME" VARCHAR(50), "AUDITOR_NO" INTEGER, "REPORTTO_NO" INTEGER, "HARDWAREASSET" VARCHAR(15), "SOFTWAREASSET" VARCHAR(15)); 0 rows inserted/updated/deleted ij> -- primary/unique ALTER TABLE "APP"."IDEPT" ADD CONSTRAINT "PK_IDEPT" PRIMARY KEY ("NO1"); 0 rows inserted/updated/deleted ij> insert into one values (1, 1, 1, 1, 1); 1 row inserted/updated/deleted ij> insert into one values (2, 1, 1, 1, 1); 1 row inserted/updated/deleted ij> insert into one values (3, 1, 1, 1, 1); 1 row inserted/updated/deleted ij> insert into one values (4, 1, 1, 1, 1); 1 row inserted/updated/deleted ij> insert into one values (5, 1, 1, 1, 1); 1 row inserted/updated/deleted ij> insert into one values (6, 1, 1, 1, 1); 1 row inserted/updated/deleted ij> insert into one values (7, 1, 1, 1, 1); 1 row inserted/updated/deleted ij> insert into one values (8, 1, 1, 1, 1); 1 row inserted/updated/deleted ij> insert into two values (1, 1, 1, 1, 1); 1 row inserted/updated/deleted ij> insert into two values (1, 1, 2, 1, 1); 1 row inserted/updated/deleted ij> insert into two values (1, 1, 3, 1, 1); 1 row inserted/updated/deleted ij> insert into two values (2, 1, 1, 1, 1); 1 row inserted/updated/deleted ij> insert into two values (2, 1, 2, 1, 1); 1 row inserted/updated/deleted ij> insert into two values (2, 1, 3, 1, 1); 1 row inserted/updated/deleted ij> insert into two values (3, 1, 1, 1, 1); 1 row inserted/updated/deleted ij> insert into two values (3, 1, 2, 1, 1); 1 row inserted/updated/deleted ij> insert into two values (3, 1, 3, 1, 1); 1 row inserted/updated/deleted ij> insert into three values (1, 1, 1, 1, 1); 1 row inserted/updated/deleted ij> insert into three values (2, 1, 1, 1, 1); 1 row inserted/updated/deleted ij> insert into three values (3, 1, 1, 1, 1); 1 row inserted/updated/deleted ij> insert into three values (4, 1, 1, 1, 1); 1 row inserted/updated/deleted ij> insert into three values (5, 1, 1, 1, 1); 1 row inserted/updated/deleted ij> insert into three values (6, 1, 1, 1, 1); 1 row inserted/updated/deleted ij> insert into three values (7, 1, 1, 1, 1); 1 row inserted/updated/deleted ij> insert into three values (8, 1, 1, 1, 1); 1 row inserted/updated/deleted ij> insert into four values (1, 1, 1, 1, 1); 1 row inserted/updated/deleted ij> insert into four values (1, 1, 2, 1, 1); 1 row inserted/updated/deleted ij> insert into four values (1, 1, 3, 1, 1); 1 row inserted/updated/deleted ij> insert into four values (2, 1, 1, 1, 1); 1 row inserted/updated/deleted ij> insert into four values (2, 1, 2, 1, 1); 1 row inserted/updated/deleted ij> insert into four values (2, 1, 3, 1, 1); 1 row inserted/updated/deleted ij> insert into four values (3, 1, 1, 1, 1); 1 row inserted/updated/deleted ij> insert into four values (3, 1, 2, 1, 1); 1 row inserted/updated/deleted ij> insert into four values (3, 1, 3, 1, 1); 1 row inserted/updated/deleted ij> insert into idept values ('Dept', 1, 'Department1', null, null, null, null); 1 row inserted/updated/deleted ij> insert into idept values ('HardwareDept', 2, 'Department2', 25, 1, 'hardwareaset2', null); 1 row inserted/updated/deleted ij> insert into idept values ('HardwareDept', 3, 'Department3', 25, 2, 'hardwareaset3', null); 1 row inserted/updated/deleted ij> insert into idept values ('SoftwareDept', 4, 'Department4', 25, 1, null, 'softwareasset4'); 1 row inserted/updated/deleted ij> insert into idept values ('SoftwareDept', 5, 'Department5', 30, 4, null, 'softwareasset5'); 1 row inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); 0 rows inserted/updated/deleted ij> maximumdisplaywidth 20000; ij> -- queries that cannot eliminate the distinct -- no unique index select distinct c2 from one; C2 ----------- 1 ij> -- Following runtime statistics output should have Distinct Scan in it values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- queries that cannot eliminate the distinct -- no unique index select distinct c2 from one 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: Distinct Scan ResultSet for ONE at serializable isolation level using share table locking: Number of opens = 1 Hash table size = 1 Distinct column is column number 1 Rows seen = 1 Rows filtered = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={1} Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=8 Number of rows visited=8 Scan type=heap start position: null stop position: null scan qualifiers: None next qualifiers: None ij> --Derby251 Distinct should not get eliminated for following query --because there is no equality condition on unique column of table --in the outside query select distinct q1."NO1", q1."NAME", q1."AUDITOR_NO", q1."REPORTTO_NO", q1."DISCRIM_DEPT", q1."SOFTWAREASSET" from IDEPT q1, IDEPT q2 where ( q2."DISCRIM_DEPT" = 'HardwareDept') and ( q1."DISCRIM_DEPT" = 'SoftwareDept') and ( q1."NO1" <> ALL ( select q3."NO1" from IDEPT q3 where ( ( q3."DISCRIM_DEPT" = 'Dept') or ( q3."DISCRIM_DEPT" = 'HardwareDept') or ( q3."DISCRIM_DEPT" = 'SoftwareDept') ) and ( q3."REPORTTO_NO" = q2."NO1") ) ) ; NO1 |NAME |AUDITOR_NO |REPORTTO_NO|DISCRIM_DEPT |SOFTWAREASSET --------------------------------------------------------------------------------------------------------------------------------------- 4 |Department4 |25 |1 |SoftwareDept |softwareasset4 5 |Department5 |30 |4 |SoftwareDept |softwareasset5 ij> -- --Another test case of Derby251 where the exists table column is embedded in an expression. select distinct q1."NO1" from IDEPT q1, IDEPT q2 where ( q2."DISCRIM_DEPT" = 'HardwareDept') and ( q1."DISCRIM_DEPT" = 'SoftwareDept') and ( q1."NO1" <> ALL (select q3."NO1" from IDEPT q3 where ( ABS(q3."REPORTTO_NO") = q2."NO1"))); NO1 ----------- 4 5 ij> -- result ordering is not guaranteed, but order by clause will change how -- distinct is executed. So test by retrieving data into a temp table and -- return results ordered after making sure the query was executed as expected. create table temp_result (c2 int, c3 int); 0 rows inserted/updated/deleted ij> insert into temp_result select distinct c2, c3 from two; 3 rows inserted/updated/deleted ij> -- Following runtime statistics output should have Distinct Scan in it values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: insert into temp_result select distinct c2, c3 from two 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: Insert ResultSet using row locking: deferred: false insert mode: normal Rows inserted = 3 Indexes updated = 0 Execute Time = 0 Distinct Scan ResultSet for TWO at serializable isolation level using share table locking: Number of opens = 1 Hash table size = 3 Distinct columns are column numbers (1,2) Rows seen = 3 Rows filtered = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={1, 2} Number of columns fetched=2 Number of pages visited=1 Number of rows qualified=9 Number of rows visited=9 Scan type=heap start position: null stop position: null scan qualifiers: None next qualifiers: None ij> select c2, c3 from temp_result order by c2, c3; C2 |C3 ----------------------- 1 |1 1 |2 1 |3 ij> drop table temp_result; 0 rows inserted/updated/deleted ij> -- Try same query, but with an order by at the end. This will use the sort for -- the "order by" to do the distinct and not do a "DISTINCT SCAN". select distinct c2, c3 from two order by c2, c3; C2 |C3 ----------------------- 1 |1 1 |2 1 |3 ij> -- Following runtime statistics output should not have Distinct Scan in it values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- Try same query, but with an order by at the end. This will use the sort for -- the "order by" to do the distinct and not do a "DISTINCT SCAN". select distinct c2, c3 from two order by c2, c3 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Sort ResultSet: Number of opens = 1 Rows input = 9 Rows returned = 3 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=9 Number of rows output=3 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Table Scan ResultSet for TWO at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 9 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={1, 2} Number of columns fetched=2 Number of pages visited=1 Number of rows qualified=9 Number of rows visited=9 Scan type=heap start position: null stop position: null qualifiers: None ij> -- more than one table in the select list select distinct a.c1, b.c1 from one a, two b where a.c1 = b.c1 and b.c2 =1; C1 |C1 ----------------------- 1 |1 2 |2 3 |3 ij> -- Following runtime statistics output should have Eliminate duplicates = true values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- more than one table in the select list select distinct a.c1, b.c1 from one a, two b where a.c1 = b.c1 and b.c2 =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: Sort ResultSet: Number of opens = 1 Rows input = 9 Rows returned = 3 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=9 Number of rows output=3 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Project-Restrict ResultSet (5): Number of opens = 1 Rows seen = 9 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Nested Loop Exists Join ResultSet: Number of opens = 1 Rows seen from the left = 9 Rows seen from the right = 9 Rows filtered = 0 Rows returned = 9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for TWO at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 9 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0, 1} Number of columns fetched=2 Number of pages visited=1 Number of rows qualified=9 Number of rows visited=9 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 1 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false Right result set: Index Scan ResultSet for ONE using index ONE_C1 at serializable isolation level using share row locking chosen by the optimizer Number of opens = 9 Rows seen = 9 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0} Number of columns fetched=1 Number of deleted rows visited=0 Number of pages visited=9 Number of rows qualified=9 Number of rows visited=9 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> -- cross product join select distinct a.c1 from one a, two b; C1 ----------- 1 2 3 4 5 6 7 8 ij> -- Following runtime statistics output should have Eliminate duplicates = true values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- cross product join select distinct a.c1 from one a, two b 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: Sort ResultSet: Number of opens = 1 Rows input = 72 Rows returned = 8 Eliminate duplicates = true In sorted order = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 8 Rows seen from the right = 72 Rows filtered = 0 Rows returned = 72 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Index Scan ResultSet for ONE using index ONE_C1 at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 8 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=8 Number of rows visited=8 Scan type=btree Tree height=-1 start position: None stop position: None qualifiers: None Right result set: Index Scan ResultSet for TWO using index TWO_C1C3 at serializable isolation level using share table locking chosen by the optimizer Number of opens = 8 Rows seen = 72 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={} Number of columns fetched=0 Number of deleted rows visited=0 Number of pages visited=8 Number of rows qualified=72 Number of rows visited=72 Scan type=btree Tree height=1 start position: None stop position: None qualifiers: None ij> -- no single table will yield at most 1 row select distinct a.c1, a.c3, a.c2 from two a, two b where a.c1 = b.c1; C1 |C3 |C2 ----------------------------------- 1 |1 |1 1 |2 |1 1 |3 |1 2 |1 |1 2 |2 |1 2 |3 |1 3 |1 |1 3 |2 |1 3 |3 |1 ij> -- Following runtime statistics output should have Eliminate duplicates = true values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- no single table will yield at most 1 row select distinct a.c1, a.c3, a.c2 from two a, two b where a.c1 = b.c1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Sort ResultSet: Number of opens = 1 Rows input = 27 Rows returned = 9 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=27 Number of rows output=9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Project-Restrict ResultSet (5): Number of opens = 1 Rows seen = 27 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: Hash Join ResultSet: Number of opens = 1 Rows seen from the left = 9 Rows seen from the right = 27 Rows filtered = 0 Rows returned = 27 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for TWO at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 9 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0, 1, 2} Number of columns fetched=3 Number of pages visited=1 Number of rows qualified=9 Number of rows visited=9 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Hash Scan ResultSet for TWO using index TWO_C1C3 at serializable isolation level using share table locking: Number of opens = 9 Hash table size = 3 Hash key is column number 0 Rows seen = 27 Rows filtered = 0 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=9 Number of rows visited=9 Scan type=btree Tree height=1 start position: None stop position: None scan qualifiers: None next qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> select distinct a.c1, a.c3, a.c2 from two a, two b where a.c1 = b.c1 and a.c2 = 1; C1 |C3 |C2 ----------------------------------- 1 |1 |1 1 |2 |1 1 |3 |1 2 |1 |1 2 |2 |1 2 |3 |1 3 |1 |1 3 |2 |1 3 |3 |1 ij> -- Following runtime statistics output should have Eliminate duplicates = true values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select distinct a.c1, a.c3, a.c2 from two a, two b where a.c1 = b.c1 and a.c2 = 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: Sort ResultSet: Number of opens = 1 Rows input = 27 Rows returned = 9 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=27 Number of rows output=9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Project-Restrict ResultSet (5): Number of opens = 1 Rows seen = 27 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: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 9 Rows seen from the right = 27 Rows filtered = 0 Rows returned = 27 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for TWO at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 9 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0, 1, 2} Number of columns fetched=3 Number of pages visited=1 Number of rows qualified=9 Number of rows visited=9 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 1 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false Right result set: Index Scan ResultSet for TWO using index TWO_C1C3 at serializable isolation level using share row locking chosen by the optimizer Number of opens = 9 Rows seen = 27 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=9 Number of rows qualified=27 Number of rows visited=33 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> -- both keys from unique index in where clause but joined to different tables select distinct a.c1 from one a, two b, three c where a.c1 = b.c1 and c.c1 = b.c3 and a.c1 = 1; C1 ----------- 1 ij> -- Following runtime statistics output should have Eliminate duplicates = true values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- both keys from unique index in where clause but joined to different tables select distinct a.c1 from one a, two b, three c where a.c1 = b.c1 and c.c1 = b.c3 and a.c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Sort ResultSet: Number of opens = 1 Rows input = 3 Rows returned = 1 Eliminate duplicates = true In sorted order = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Project-Restrict ResultSet (7): Number of opens = 1 Rows seen = 3 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Nested Loop Exists Join ResultSet: Number of opens = 1 Rows seen from the left = 3 Rows seen from the right = 3 Rows filtered = 0 Rows returned = 3 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 1 Rows seen from the right = 3 Rows filtered = 0 Rows returned = 3 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Index Scan ResultSet for ONE using index ONE_C1 at serializable isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0} Number of columns fetched=1 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=1 Scan type=btree Tree height=-1 start position: >= on first 1 column(s). Ordered null semantics on the following columns: stop position: > on first 1 column(s). Ordered null semantics on the following columns: qualifiers: None Right result set: Index Scan ResultSet for TWO using index TWO_C1C3 at serializable isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 3 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0, 1} Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=3 Number of rows visited=4 Scan type=btree Tree height=1 start position: >= on first 1 column(s). Ordered null semantics on the following columns: stop position: > on first 1 column(s). Ordered null semantics on the following columns: qualifiers: None Right result set: Index Scan ResultSet for THREE using index THREE_C1 at serializable isolation level using share row locking chosen by the optimizer Number of opens = 3 Rows seen = 3 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0} Number of columns fetched=1 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=3 Number of rows visited=3 Scan type=btree Tree height=1 start position: >= on first 1 column(s). Ordered null semantics on the following columns: stop position: > on first 1 column(s). Ordered null semantics on the following columns: qualifiers: None ij> -- join between two tables using one columns of unique key select distinct a.c1 from two a, four b where a.c1 = b.c1 and b.c3 = 1; C1 ----------- 1 2 3 ij> -- Following runtime statistics output should have Eliminate duplicates = true values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- join between two tables using one columns of unique key select distinct a.c1 from two a, four b where a.c1 = b.c1 and b.c3 = 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: Sort ResultSet: Number of opens = 1 Rows input = 9 Rows returned = 3 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=9 Number of rows output=3 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Project-Restrict ResultSet (5): Number of opens = 1 Rows seen = 9 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 3 Rows seen from the right = 9 Rows filtered = 0 Rows returned = 9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Index Scan ResultSet for FOUR using index FOUR_C1C3 at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 3 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0, 1} Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=3 Number of rows visited=9 Scan type=btree Tree height=1 start position: None stop position: None qualifiers: Column[0][0] Id: 1 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false Right result set: Index Scan ResultSet for TWO using index TWO_C1C3 at serializable isolation level using share row locking chosen by the optimizer Number of opens = 3 Rows seen = 9 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0} Number of columns fetched=1 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=9 Number of rows visited=11 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> -- join between two tables with no join predicate select distinct a.c1, a.c3 from two a, one b; C1 |C3 ----------------------- 1 |1 1 |2 1 |3 2 |1 2 |2 2 |3 3 |1 3 |2 3 |3 ij> -- Following runtime statistics output should have Eliminate duplicates = true values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- join between two tables with no join predicate select distinct a.c1, a.c3 from two a, one b 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: Sort ResultSet: Number of opens = 1 Rows input = 72 Rows returned = 9 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=72 Number of rows output=9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 8 Rows seen from the right = 72 Rows filtered = 0 Rows returned = 72 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Index Scan ResultSet for ONE using index ONE_C1 at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 8 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={} Number of columns fetched=0 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=8 Number of rows visited=8 Scan type=btree Tree height=1 start position: None stop position: None qualifiers: None Right result set: Index Scan ResultSet for TWO using index TWO_C1C3 at serializable isolation level using share table locking chosen by the optimizer Number of opens = 8 Rows seen = 72 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0, 1} Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=8 Number of rows qualified=72 Number of rows visited=72 Scan type=btree Tree height=1 start position: None stop position: None qualifiers: None ij> -- join between three tables with two tables joined uniquely select distinct a.c1 from one a, two b, three c where a.c1 = c.c1 and a.c1 = 1; C1 ----------- 1 ij> -- Following runtime statistics output should have Eliminate duplicates = true values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- join between three tables with two tables joined uniquely select distinct a.c1 from one a, two b, three c where a.c1 = c.c1 and a.c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Sort ResultSet: Number of opens = 1 Rows input = 9 Rows returned = 1 Eliminate duplicates = true In sorted order = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Project-Restrict ResultSet (7): Number of opens = 1 Rows seen = 9 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 1 Rows seen from the right = 9 Rows filtered = 0 Rows returned = 9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Nested Loop Exists Join ResultSet: Number of opens = 1 Rows seen from the left = 1 Rows seen from the right = 1 Rows filtered = 0 Rows returned = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Index Scan ResultSet for ONE using index ONE_C1 at serializable isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0} Number of columns fetched=1 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=1 Scan type=btree Tree height=-1 start position: >= on first 1 column(s). Ordered null semantics on the following columns: stop position: > on first 1 column(s). Ordered null semantics on the following columns: qualifiers: None Right result set: Index Scan ResultSet for THREE using index THREE_C1 at serializable isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0} Number of columns fetched=1 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=1 Scan type=btree Tree height=1 start position: >= on first 1 column(s). Ordered null semantics on the following columns: stop position: > on first 1 column(s). Ordered null semantics on the following columns: qualifiers: None Right result set: Index Scan ResultSet for TWO using index TWO_C1C3 at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 9 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={} Number of columns fetched=0 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=9 Number of rows visited=9 Scan type=btree Tree height=1 start position: None stop position: None qualifiers: None ij> -- -- queries that should eliminate the distinct -- single table queries -- unique columns in select list select distinct c1 from one; C1 ----------- 1 2 3 4 5 6 7 8 ij> -- Following runtime statistics output should not have Eliminate duplicates = true values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- -- queries that should eliminate the distinct -- single table queries -- unique columns in select list select distinct c1 from one 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 ONE using index ONE_C1 at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 8 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=8 Number of rows visited=8 Scan type=btree Tree height=-1 start position: None stop position: None qualifiers: None ij> select distinct c1, c2 + c3 from one; C1 |2 ----------------------- 1 |2 2 |2 3 |2 4 |2 5 |2 6 |2 7 |2 8 |2 ij> -- Following runtime statistics output should not have Eliminate duplicates = true values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select distinct c1, c2 + c3 from one 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 = 8 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 ONE at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 8 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0, 1, 2} Number of columns fetched=3 Number of pages visited=1 Number of rows qualified=8 Number of rows visited=8 Scan type=heap start position: null stop position: null qualifiers: None ij> select distinct c3, c1 from two; C3 |C1 ----------------------- 1 |1 2 |1 3 |1 1 |2 2 |2 3 |2 1 |3 2 |3 3 |3 ij> -- Following runtime statistics output should not have Eliminate duplicates = true values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select distinct c3, c1 from two 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 = 9 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Index Scan ResultSet for TWO using index TWO_C1C3 at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 9 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0, 1} Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=9 Number of rows visited=9 Scan type=btree Tree height=-1 start position: None stop position: None qualifiers: None ij> -- query returns single row select distinct c2 from one where c1 = 3; C2 ----------- 1 ij> -- Following runtime statistics output should not have Eliminate duplicates = true values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- query returns single row select distinct c2 from one where c1 = 3 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Project-Restrict ResultSet (3): Number of opens = 1 Rows seen = 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 ONE: 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 ONE using index ONE_C1 at serializable isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=1 Scan type=btree Tree height=-1 start position: >= on first 1 column(s). Ordered null semantics on the following columns: stop position: > on first 1 column(s). Ordered null semantics on the following columns: qualifiers: None ij> select distinct c3 from one where c1 = 3; C3 ----------- 1 ij> -- Following runtime statistics output should not have Eliminate duplicates = true values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select distinct c3 from one where c1 = 3 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Project-Restrict ResultSet (3): Number of opens = 1 Rows seen = 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 ONE: Number of opens = 1 Rows seen = 1 Columns accessed from heap = {2} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for ONE using index ONE_C1 at serializable isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=1 Scan type=btree Tree height=-1 start position: >= on first 1 column(s). Ordered null semantics on the following columns: stop position: > on first 1 column(s). Ordered null semantics on the following columns: qualifiers: None ij> -- superset in select list select distinct c2, c5, c1 from one; C2 |C5 |C1 ----------------------------------- 1 |1 |1 1 |1 |2 1 |1 |3 1 |1 |4 1 |1 |5 1 |1 |6 1 |1 |7 1 |1 |8 ij> -- Following runtime statistics output should not have Eliminate duplicates = true values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- superset in select list select distinct c2, c5, c1 from one 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 = 8 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 ONE at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 8 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, 4} Number of columns fetched=3 Number of pages visited=1 Number of rows qualified=8 Number of rows visited=8 Scan type=heap start position: null stop position: null qualifiers: None ij> select distinct c2, c3, c1 from two; C2 |C3 |C1 ----------------------------------- 1 |1 |1 1 |2 |1 1 |3 |1 1 |1 |2 1 |2 |2 1 |3 |2 1 |1 |3 1 |2 |3 1 |3 |3 ij> -- Following runtime statistics output should not have Eliminate duplicates = true values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select distinct c2, c3, c1 from two 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 = 9 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for TWO at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 9 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0, 1, 2} Number of columns fetched=3 Number of pages visited=1 Number of rows qualified=9 Number of rows visited=9 Scan type=heap start position: null stop position: null qualifiers: None ij> -- multi table queries -- 1 to 1 join, select list is superset select distinct a.c1 from one a, one b where a.c1 = b.c1; C1 ----------- 1 2 3 4 5 6 7 8 ij> -- Following runtime statistics output should not have Eliminate duplicates = true values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- multi table queries -- 1 to 1 join, select list is superset select distinct a.c1 from one a, one b where a.c1 = b.c1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Project-Restrict ResultSet (4): Number of opens = 1 Rows seen = 8 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: Nested Loop Exists Join ResultSet: Number of opens = 1 Rows seen from the left = 8 Rows seen from the right = 8 Rows filtered = 0 Rows returned = 8 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Index Scan ResultSet for ONE using index ONE_C1 at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 8 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=8 Number of rows visited=8 Scan type=btree Tree height=-1 start position: None stop position: None qualifiers: None Right result set: Index Scan ResultSet for ONE using index ONE_C1 at serializable isolation level using share row locking chosen by the optimizer Number of opens = 8 Rows seen = 8 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0} Number of columns fetched=1 Number of deleted rows visited=0 Number of pages visited=8 Number of rows qualified=8 Number of rows visited=8 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 distinct a.c1, 3 from one a, one b where a.c1 = b.c1; C1 |2 ----------------------- 1 |3 2 |3 3 |3 4 |3 5 |3 6 |3 7 |3 8 |3 ij> -- Following runtime statistics output should not have Eliminate duplicates = true values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select distinct a.c1, 3 from one a, one b where a.c1 = b.c1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Project-Restrict ResultSet (4): Number of opens = 1 Rows seen = 8 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: Nested Loop Exists Join ResultSet: Number of opens = 1 Rows seen from the left = 8 Rows seen from the right = 8 Rows filtered = 0 Rows returned = 8 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Index Scan ResultSet for ONE using index ONE_C1 at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 8 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=8 Number of rows visited=8 Scan type=btree Tree height=-1 start position: None stop position: None qualifiers: None Right result set: Index Scan ResultSet for ONE using index ONE_C1 at serializable isolation level using share row locking chosen by the optimizer Number of opens = 8 Rows seen = 8 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0} Number of columns fetched=1 Number of deleted rows visited=0 Number of pages visited=8 Number of rows qualified=8 Number of rows visited=8 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 distinct a.c1, a.c3, a.c2 from two a, one b where a.c1 = b.c1; C1 |C3 |C2 ----------------------------------- 1 |1 |1 1 |2 |1 1 |3 |1 2 |1 |1 2 |2 |1 2 |3 |1 3 |1 |1 3 |2 |1 3 |3 |1 ij> -- Following runtime statistics output should not have Eliminate duplicates = true values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select distinct a.c1, a.c3, a.c2 from two a, one b where a.c1 = b.c1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Project-Restrict ResultSet (4): Number of opens = 1 Rows seen = 9 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Hash Exists Join ResultSet: Number of opens = 1 Rows seen from the left = 9 Rows seen from the right = 9 Rows filtered = 0 Rows returned = 9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for TWO at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 9 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0, 1, 2} Number of columns fetched=3 Number of pages visited=1 Number of rows qualified=9 Number of rows visited=9 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Hash Scan ResultSet for ONE using index ONE_C1 at serializable isolation level using share table locking: Number of opens = 9 Hash table size = 8 Hash key is column number 0 Rows seen = 9 Rows filtered = 0 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=8 Number of rows visited=8 Scan type=btree Tree height=1 start position: None stop position: None scan qualifiers: None next qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> select distinct a.c1, a.c3, a.c2 from two a, two b where a.c1 = b.c1 and b.c3 = 1; C1 |C3 |C2 ----------------------------------- 1 |1 |1 1 |2 |1 1 |3 |1 2 |1 |1 2 |2 |1 2 |3 |1 3 |1 |1 3 |2 |1 3 |3 |1 ij> -- Following runtime statistics output should not have Eliminate duplicates = true values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select distinct a.c1, a.c3, a.c2 from two a, two b where a.c1 = b.c1 and b.c3 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Project-Restrict ResultSet (5): Number of opens = 1 Rows seen = 9 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 3 Rows seen from the right = 9 Rows filtered = 0 Rows returned = 9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Index Scan ResultSet for TWO using index TWO_C1C3 at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 3 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0, 1} Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=3 Number of rows visited=9 Scan type=btree Tree height=-1 start position: None stop position: None qualifiers: Column[0][0] Id: 1 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false Right result set: Index Row to Base Row ResultSet for TWO: Number of opens = 3 Rows seen = 9 Columns accessed from heap = {0, 1, 2} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for TWO using index TWO_C1C3 at serializable isolation level using share row locking chosen by the optimizer Number of opens = 3 Rows seen = 9 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0, 2} Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=9 Number of rows visited=11 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> -- join between two tables using both columns of unique key select distinct a.c1 from two a, four b where a.c1 = b.c1 and a.c3 = b.c3 and b.c3 = 1; C1 ----------- 1 2 3 ij> -- Following runtime statistics output should not have Eliminate duplicates = true values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- join between two tables using both columns of unique key select distinct a.c1 from two a, four b where a.c1 = b.c1 and a.c3 = b.c3 and b.c3 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Project-Restrict ResultSet (4): Number of opens = 1 Rows seen = 3 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Nested Loop Exists Join ResultSet: Number of opens = 1 Rows seen from the left = 3 Rows seen from the right = 3 Rows filtered = 0 Rows returned = 3 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Index Scan ResultSet for FOUR using index FOUR_C1C3 at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 3 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0, 1} Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=3 Number of rows visited=9 Scan type=btree Tree height=-1 start position: None stop position: None qualifiers: Column[0][0] Id: 1 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false Right result set: Index Scan ResultSet for TWO using index TWO_C1C3 at serializable isolation level using share row locking chosen by the optimizer Number of opens = 3 Rows seen = 3 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=3 Number of rows qualified=3 Number of rows visited=3 Scan type=btree Tree height=1 start position: >= on first 2 column(s). Ordered null semantics on the following columns: stop position: > on first 2 column(s). Ordered null semantics on the following columns: qualifiers: None ij> -- clean up drop table one; 0 rows inserted/updated/deleted ij> drop table two; 0 rows inserted/updated/deleted ij> drop table three; 0 rows inserted/updated/deleted ij> drop table four; 0 rows inserted/updated/deleted ij> drop table idept; 0 rows inserted/updated/deleted ij>