ij> -- 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> 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> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); 0 rows inserted/updated/deleted ij> maximumdisplaywidth 2000; 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 using index xxxxFILTERED-UUIDxxxx 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: None stop position: None scan qualifiers: None next qualifiers: None 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 using index xxxxFILTERED-UUIDxxxx 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: None stop position: None 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& 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 isol& 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 (millis& 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 tim& 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 opti& 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 tim& 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 optimizer es& 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 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 optimizer estimat& 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 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 & 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 & 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 comparis& 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 O& 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>