| 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. |
| -- |
| -- DERBY-939 |
| -- Test union and intersect/except with runtime statistics enabled. |
| CREATE TABLE t1 (i int); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE t2 (j int); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE t3 (k int); |
| 0 rows inserted/updated/deleted |
| ij> INSERT INTO t1 VALUES 3,2,1; |
| 3 rows inserted/updated/deleted |
| ij> INSERT INTO t2 VALUES 1,2,3,4; |
| 4 rows inserted/updated/deleted |
| ij> INSERT INTO t3 VALUES 5,2,3,4,1; |
| 5 rows inserted/updated/deleted |
| ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); |
| 0 rows inserted/updated/deleted |
| ij> MaximumDisplayWidth 7000; |
| ij> SELECT i FROM t1 UNION SELECT j FROM t2 INTERSECT SELECT k FROM t3; |
| 1 |
| ----------- |
| 1 |
| 2 |
| 3 |
| 4 |
| ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| 1 |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Statement Name: |
| null |
| Statement Text: |
| SELECT i FROM t1 UNION SELECT j FROM t2 INTERSECT SELECT k FROM t3 |
| 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 = 7 |
| Rows returned = 4 |
| Eliminate duplicates = true |
| In sorted order = false |
| Sort information: |
| Number of rows input=7 |
| Number of rows output=4 |
| constructor time (milliseconds) = 0 |
| open time (milliseconds) = 0 |
| next time (milliseconds) = 0 |
| close time (milliseconds) = 0 |
| Source result set: |
| Union ResultSet: |
| Number of opens = 1 |
| Rows seen from the left = 3 |
| Rows seen from the right = 4 |
| Rows returned = 7 |
| constructor time (milliseconds) = 0 |
| open time (milliseconds) = 0 |
| next time (milliseconds) = 0 |
| close time (milliseconds) = 0 |
| Left result set: |
| Table Scan ResultSet for T1 at read committed isolation level using instantaneous share row locking chosen by the optimizer |
| Number of opens = 1 |
| Rows seen = 3 |
| Rows filtered = 0 |
| Fetch Size = 16 |
| constructor time (milliseconds) = 0 |
| open time (milliseconds) = 0 |
| next time (milliseconds) = 0 |
| close time (milliseconds) = 0 |
| next time in milliseconds/row = 0 |
| scan information: |
| Bit set of columns fetched=All |
| Number of columns fetched=1 |
| Number of pages visited=1 |
| Number of rows qualified=3 |
| Number of rows visited=3 |
| Scan type=heap |
| start position: |
| null |
| stop position: |
| null |
| qualifiers: |
| None |
| Right result set: |
| INTERSECT ResultSet: |
| Number of opens = 1 |
| Rows seen from the left = 4 |
| Rows seen from the right = 4 |
| Rows returned = 4 |
| constructor time (milliseconds) = 0 |
| open time (milliseconds) = 0 |
| next time (milliseconds) = 0 |
| close time (milliseconds) = 0 |
| Left result set: |
| Sort ResultSet: |
| Number of opens = 1 |
| Rows input = 4 |
| Rows returned = 4 |
| Eliminate duplicates = false |
| In sorted order = false |
| Sort information: |
| Number of rows input=4 |
| Number of rows output=4 |
| constructor time (milliseconds) = 0 |
| open time (milliseconds) = 0 |
| next time (milliseconds) = 0 |
| close time (milliseconds) = 0 |
| Source result set: |
| Table Scan ResultSet for T2 at read committed isolation level using instantaneous share row locking chosen by the optimizer |
| Number of opens = 1 |
| Rows seen = 4 |
| Rows filtered = 0 |
| Fetch Size = 16 |
| constructor time (milliseconds) = 0 |
| open time (milliseconds) = 0 |
| next time (milliseconds) = 0 |
| close time (milliseconds) = 0 |
| next time in milliseconds/row = 0 |
| scan information: |
| Bit set of columns fetched=All |
| Number of columns fetched=1 |
| Number of pages visited=1 |
| Number of rows qualified=4 |
| Number of rows visited=4 |
| Scan type=heap |
| start position: |
| null |
| stop position: |
| null |
| qualifiers: |
| None |
| Right result set: |
| Sort ResultSet: |
| Number of opens = 1 |
| Rows input = 5 |
| Rows returned = 4 |
| Eliminate duplicates = false |
| In sorted order = false |
| Sort information: |
| Number of rows input=5 |
| Number of rows output=5 |
| constructor time (milliseconds) = 0 |
| open time (milliseconds) = 0 |
| next time (milliseconds) = 0 |
| close time (milliseconds) = 0 |
| Source result set: |
| Table Scan ResultSet for T3 at read committed isolation level using instantaneous share row locking chosen by the optimizer |
| Number of opens = 1 |
| Rows seen = 5 |
| Rows filtered = 0 |
| Fetch Size = 16 |
| constructor time (milliseconds) = 0 |
| open time (milliseconds) = 0 |
| next time (milliseconds) = 0 |
| close time (milliseconds) = 0 |
| next time in milliseconds/row = 0 |
| scan information: |
| Bit set of columns fetched=All |
| Number of columns fetched=1 |
| Number of pages visited=1 |
| Number of rows qualified=5 |
| Number of rows visited=5 |
| Scan type=heap |
| start position: |
| null |
| stop position: |
| null |
| qualifiers: |
| None |
| ij> SELECT i FROM t1 UNION SELECT j FROM t2 EXCEPT SELECT k FROM t3; |
| 1 |
| ----------- |
| ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| 1 |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Statement Name: |
| null |
| Statement Text: |
| SELECT i FROM t1 UNION SELECT j FROM t2 EXCEPT SELECT k FROM t3 |
| 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: |
| EXCEPT ResultSet: |
| Number of opens = 1 |
| Rows seen from the left = 4 |
| Rows seen from the right = 4 |
| Rows returned = 0 |
| constructor time (milliseconds) = 0 |
| open time (milliseconds) = 0 |
| next time (milliseconds) = 0 |
| close time (milliseconds) = 0 |
| Left result set: |
| Sort ResultSet: |
| Number of opens = 1 |
| Rows input = 7 |
| Rows returned = 4 |
| Eliminate duplicates = true |
| In sorted order = false |
| Sort information: |
| Number of rows input=7 |
| Number of rows output=4 |
| constructor time (milliseconds) = 0 |
| open time (milliseconds) = 0 |
| next time (milliseconds) = 0 |
| close time (milliseconds) = 0 |
| Source result set: |
| Union ResultSet: |
| Number of opens = 1 |
| Rows seen from the left = 3 |
| Rows seen from the right = 4 |
| Rows returned = 7 |
| constructor time (milliseconds) = 0 |
| open time (milliseconds) = 0 |
| next time (milliseconds) = 0 |
| close time (milliseconds) = 0 |
| Left result set: |
| Table Scan ResultSet for T1 at read committed isolation level using instantaneous share row locking chosen by the optimizer |
| Number of opens = 1 |
| Rows seen = 3 |
| Rows filtered = 0 |
| Fetch Size = 16 |
| constructor time (milliseconds) = 0 |
| open time (milliseconds) = 0 |
| next time (milliseconds) = 0 |
| close time (milliseconds) = 0 |
| next time in milliseconds/row = 0 |
| scan information: |
| Bit set of columns fetched=All |
| Number of columns fetched=1 |
| Number of pages visited=1 |
| Number of rows qualified=3 |
| Number of rows visited=3 |
| Scan type=heap |
| start position: |
| null |
| stop position: |
| null |
| qualifiers: |
| None |
| Right result set: |
| Table Scan ResultSet for T2 at read committed isolation level using instantaneous share row locking chosen by the optimizer |
| Number of opens = 1 |
| Rows seen = 4 |
| Rows filtered = 0 |
| Fetch Size = 16 |
| constructor time (milliseconds) = 0 |
| open time (milliseconds) = 0 |
| next time (milliseconds) = 0 |
| close time (milliseconds) = 0 |
| next time in milliseconds/row = 0 |
| scan information: |
| Bit set of columns fetched=All |
| Number of columns fetched=1 |
| Number of pages visited=1 |
| Number of rows qualified=4 |
| Number of rows visited=4 |
| Scan type=heap |
| start position: |
| null |
| stop position: |
| null |
| qualifiers: |
| None |
| Right result set: |
| Sort ResultSet: |
| Number of opens = 1 |
| Rows input = 5 |
| Rows returned = 4 |
| Eliminate duplicates = false |
| In sorted order = false |
| Sort information: |
| Number of rows input=5 |
| Number of rows output=5 |
| constructor time (milliseconds) = 0 |
| open time (milliseconds) = 0 |
| next time (milliseconds) = 0 |
| close time (milliseconds) = 0 |
| Source result set: |
| Table Scan ResultSet for T3 at read committed isolation level using instantaneous share row locking chosen by the optimizer |
| Number of opens = 1 |
| Rows seen = 5 |
| Rows filtered = 0 |
| Fetch Size = 16 |
| constructor time (milliseconds) = 0 |
| open time (milliseconds) = 0 |
| next time (milliseconds) = 0 |
| close time (milliseconds) = 0 |
| next time in milliseconds/row = 0 |
| scan information: |
| Bit set of columns fetched=All |
| Number of columns fetched=1 |
| Number of pages visited=1 |
| Number of rows qualified=5 |
| Number of rows visited=5 |
| Scan type=heap |
| start position: |
| null |
| stop position: |
| null |
| qualifiers: |
| None |
| ij> SELECT i FROM t1 INTERSECT SELECT j FROM t2 EXCEPT SELECT k FROM t3; |
| 1 |
| ----------- |
| ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| 1 |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Statement Name: |
| null |
| Statement Text: |
| SELECT i FROM t1 INTERSECT SELECT j FROM t2 EXCEPT SELECT k FROM t3 |
| 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: |
| EXCEPT ResultSet: |
| Number of opens = 1 |
| Rows seen from the left = 3 |
| Rows seen from the right = 3 |
| Rows returned = 0 |
| constructor time (milliseconds) = 0 |
| open time (milliseconds) = 0 |
| next time (milliseconds) = 0 |
| close time (milliseconds) = 0 |
| Left result set: |
| INTERSECT ResultSet: |
| Number of opens = 1 |
| Rows seen from the left = 3 |
| Rows seen from the right = 3 |
| Rows returned = 3 |
| constructor time (milliseconds) = 0 |
| open time (milliseconds) = 0 |
| next time (milliseconds) = 0 |
| close time (milliseconds) = 0 |
| Left result set: |
| Sort ResultSet: |
| Number of opens = 1 |
| Rows input = 3 |
| Rows returned = 3 |
| Eliminate duplicates = false |
| In sorted order = false |
| Sort information: |
| Number of rows input=3 |
| 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 T1 at read committed isolation level using instantaneous share row locking chosen by the optimizer |
| Number of opens = 1 |
| Rows seen = 3 |
| Rows filtered = 0 |
| Fetch Size = 16 |
| constructor time (milliseconds) = 0 |
| open time (milliseconds) = 0 |
| next time (milliseconds) = 0 |
| close time (milliseconds) = 0 |
| next time in milliseconds/row = 0 |
| scan information: |
| Bit set of columns fetched=All |
| Number of columns fetched=1 |
| Number of pages visited=1 |
| Number of rows qualified=3 |
| Number of rows visited=3 |
| Scan type=heap |
| start position: |
| null |
| stop position: |
| null |
| qualifiers: |
| None |
| Right result set: |
| Sort ResultSet: |
| Number of opens = 1 |
| Rows input = 4 |
| Rows returned = 3 |
| Eliminate duplicates = false |
| In sorted order = false |
| Sort information: |
| Number of rows input=4 |
| Number of rows output=4 |
| constructor time (milliseconds) = 0 |
| open time (milliseconds) = 0 |
| next time (milliseconds) = 0 |
| close time (milliseconds) = 0 |
| Source result set: |
| Table Scan ResultSet for T2 at read committed isolation level using instantaneous share row locking chosen by the optimizer |
| Number of opens = 1 |
| Rows seen = 4 |
| Rows filtered = 0 |
| Fetch Size = 16 |
| constructor time (milliseconds) = 0 |
| open time (milliseconds) = 0 |
| next time (milliseconds) = 0 |
| close time (milliseconds) = 0 |
| next time in milliseconds/row = 0 |
| scan information: |
| Bit set of columns fetched=All |
| Number of columns fetched=1 |
| Number of pages visited=1 |
| Number of rows qualified=4 |
| Number of rows visited=4 |
| Scan type=heap |
| start position: |
| null |
| stop position: |
| null |
| qualifiers: |
| None |
| Right result set: |
| Sort ResultSet: |
| Number of opens = 1 |
| Rows input = 5 |
| Rows returned = 3 |
| Eliminate duplicates = false |
| In sorted order = false |
| Sort information: |
| Number of rows input=5 |
| Number of rows output=5 |
| constructor time (milliseconds) = 0 |
| open time (milliseconds) = 0 |
| next time (milliseconds) = 0 |
| close time (milliseconds) = 0 |
| Source result set: |
| Table Scan ResultSet for T3 at read committed isolation level using instantaneous share row locking chosen by the optimizer |
| Number of opens = 1 |
| Rows seen = 5 |
| Rows filtered = 0 |
| Fetch Size = 16 |
| constructor time (milliseconds) = 0 |
| open time (milliseconds) = 0 |
| next time (milliseconds) = 0 |
| close time (milliseconds) = 0 |
| next time in milliseconds/row = 0 |
| scan information: |
| Bit set of columns fetched=All |
| Number of columns fetched=1 |
| Number of pages visited=1 |
| Number of rows qualified=5 |
| Number of rows visited=5 |
| Scan type=heap |
| start position: |
| null |
| stop position: |
| null |
| qualifiers: |
| None |
| ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0); |
| 0 rows inserted/updated/deleted |
| ij> |