Re: intermittent query plan diff for lang/wisconsin.sql with 10.1 jars

[Myrna van Lunteren <m....@gmail.com>]

well my 2 cents:
1c: I've seen it also in the past. I think I've even had some wiser heads
than me look at it, and I don't think they came back very worried. I'd have
remembered that/done something about it (like, log a bug).
2c: if it's happened before and it doesn't reproduce on rerun & it doesn't
show up in the nightly tests, then I think it's not critical enough to block
the release.
 Myrna

 On 11/3/05, Kathey Marsden <km...@sbcglobal.net> wrote:
>
> From time to time I get a diff with lang/wisconsin.sql with the 10.1
> insane jars. Running derbyall with this build
>
> http://people.apache.org/~kmarsden/derby10.1.2.1.330390
>
> I saw it in the derbynetmats suite with JCC. I have also seen it
> occasionally in embedded and client with derbyall runs in the past, but
> it does not reproduce on rerun. The diff is basically because instead
> of Nested Loop Exists Join ResultSet, I get a Hash Exists Join ResultSet.
> The query results are all ok. Has anyone else ever seen this? Anyone
> have any ideas on what might cause this and thoughts on whether it
> should hold up the release.
>
> Thanks
>
> Kathey
>
>
>
>
>
> ij version 10.1
> ij> -- This test is an adaptation of the Wisconsin benchmark, as
> documented in
> -- The Benchmark Handbook, Second Edition (edited by Jim Gray). The
> structure
> -- of the tables and the data in the tables are taken from there.
> --
> -- The original benchmark talks about clustered and non-clustered
> -- indexes - as far as I can tell, this really means indexes where the
> -- row ordering is or is not the same as in the base table. It does
> -- not mean special types of indexes. I am putting in queries that
> -- use both ordered and unordered indexes, despite the fact that
> -- our optimizer does not currently distinguish these cases.
> --
> -- Another difference is that the original Wisconsin benchmark is a
> performance
> -- test, while this test is only intended to ensure that the optimizer
> comes
> -- up with the right query plan. Therefore, this test doesn't include
> those
> -- parts of the Wisconsin benchmark where the optimizer has no choice of
> -- access path (e.g. single-table query with no indexes), nor does it
> include
> -- the projection and update queries.
> --
> -- This test only does the first variation of each query, since that is
> -- all that is documented in The Benchmark Handbook (it wouldn't be a true
> -- academic reference text if everything were spelled out).
> --
> -- After the original Wisconsin queries are a bunch of queries that use
> the
> -- Wisconsin schema but that were written at Cloudscape specifically for
> -- testing our optimizer.
>
> autocommit off;
> ij> set isolation serializable;
> 0 rows inserted/updated/deleted
> ij> -- the method refers to a method in performance suite that takes a
> Connection.
> --create function WISCInsert(rowcount int, tableName varchar(20)) returns
> int language java parameter style java external name '
> org.apache.derbyTesting.functionTests.tests.lang.WiscVTI';
> CREATE PROCEDURE WISCINSERT(rowcount int, tableName varchar(20)) LANGUAGE
> JAVA PARAMETER STYLE JAVA EXTERNAL NAME '
> org.apache.derbyTesting.functionTests.tests.lang.WiscVTI.WISCInsertWOConnection
> ';
> 0 rows inserted/updated/deleted
> ij> create table TENKTUP1 (
> unique1 int not null,
> unique2 int not null,
> two int,
> four int,
> ten int,
> twenty int,
> onePercent int,
> tenPercent int,
> twentyPercent int,
> fiftyPercent int,
> unique3 int,
> evenOnePercent int,
> oddOnePercent int,
> stringu1 char(52) not null,
> stringu2 char(52) not null,
> string4 char(52)
> );
> 0 rows inserted/updated/deleted
> ij> --insert 10000 rows into TENKTUP1
> call WISCINSERT( 10000, 'TENKTUP1');
> Statement executed.
> ij> create unique index TK1UNIQUE1 on TENKTUP1(unique1);
> 0 rows inserted/updated/deleted
> ij> create unique index TK1UNIQUE2 on TENKTUP1(unique2);
> 0 rows inserted/updated/deleted
> ij> create index TK1TWO on TENKTUP1(two);
> 0 rows inserted/updated/deleted
> ij> create index TK1FOUR on TENKTUP1(four);
> 0 rows inserted/updated/deleted
> ij> create index TK1TEN on TENKTUP1(ten);
> 0 rows inserted/updated/deleted
> ij> create index TK1TWENTY on TENKTUP1(twenty);
> 0 rows inserted/updated/deleted
> ij> create index TK1ONEPERCENT on TENKTUP1(onePercent);
> 0 rows inserted/updated/deleted
> ij> create index TK1TWENTYPERCENT on TENKTUP1(twentyPercent);
> 0 rows inserted/updated/deleted
> ij> create index TK1EVENONEPERCENT on TENKTUP1(evenOnePercent);
> 0 rows inserted/updated/deleted
> ij> create index TK1ODDONEPERCENT on TENKTUP1(oddOnePercent);
> 0 rows inserted/updated/deleted
> ij> create unique index TK1STRINGU1 on TENKTUP1(stringu1);
> 0 rows inserted/updated/deleted
> ij> create unique index TK1STRINGU2 on TENKTUP1(stringu2);
> 0 rows inserted/updated/deleted
> ij> create index TK1STRING4 on TENKTUP1(string4);
> 0 rows inserted/updated/deleted
> ij> create table TENKTUP2 (
> unique1 int not null,
> unique2 int not null,
> two int,
> four int,
> ten int,
> twenty int,
> onePercent int,
> tenPercent int,
> twentyPercent int,
> fiftyPercent int,
> unique3 int,
> evenOnePercent int,
> oddOnePercent int,
> stringu1 char(52),
> stringu2 char(52),
> string4 char(52)
> );
> 0 rows inserted/updated/deleted
> ij> -- insert 10000 rows into TENKTUP2
> call WISCInsert( 10000, 'TENKTUP2');
> Statement executed.
> ij> create unique index TK2UNIQUE1 on TENKTUP2(unique1);
> 0 rows inserted/updated/deleted
> ij> create unique index TK2UNIQUE2 on TENKTUP2(unique2);
> 0 rows inserted/updated/deleted
> ij> create table ONEKTUP (
> unique1 int not null,
> unique2 int not null,
> two int,
> four int,
> ten int,
> twenty int,
> onePercent int,
> tenPercent int,
> twentyPercent int,
> fiftyPercent int,
> unique3 int,
> evenOnePercent int,
> oddOnePercent int,
> stringu1 char(52),
> stringu2 char(52),
> string4 char(52)
> );
> 0 rows inserted/updated/deleted
> ij> -- insert 1000 rows into ONEKTUP
> call WISCInsert( 1000, 'ONEKTUP');
> Statement executed.
> ij> create unique index ONEKUNIQUE1 on ONEKTUP(unique1);
> 0 rows inserted/updated/deleted
> ij> create unique index ONEKUNIQUE2 on ONEKTUP(unique2);
> 0 rows inserted/updated/deleted
> ij> create table BPRIME (
> unique1 int,
> unique2 int,
> two int,
> four int,
> ten int,
> twenty int,
> onePercent int,
> tenPercent int,
> twentyPercent int,
> fiftyPercent int,
> unique3 int,
> evenOnePercent int,
> oddOnePercent int,
> stringu1 char(52),
> stringu2 char(52),
> string4 char(52)
> );
> 0 rows inserted/updated/deleted
> ij> insert into BPRIME
> select * from TENKTUP2
> where TENKTUP2.unique2 < 1000;
> 1000 rows inserted/updated/deleted
> ij> commit;
> ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
> Statement executed.
> ij> maximumdisplaywidth 8000;
> ij> -- Wisconsin Query 3
>
> get cursor c as
> 'select * from TENKTUP1
> where unique2 between 0 and 99';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
>
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1
> where unique2 between 0 and 99
> Parse Time: 0
> Bind Time: 0
> Optimize Time: 0
> Generate Time: 0
> Compile Time: 0
> Execute Time: 0
> Begin Compilation Timestamp : null
> End Compilation Timestamp : null
> Begin Execution Timestamp : null
> End Execution Timestamp : null
> Statement Execution Plan Text:
> Index Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> optimizer estimated row count: 99.00
> optimizer estimated cost: 291.33
>
> Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE2 at serializable
> isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
>
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=btree
> Tree height=2
> start position:
> >= on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> stop position:
> > on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> qualifiers:
> None
> optimizer estimated row count: 99.00
> optimizer estimated cost: 291.33
>
>
> ij> commit;
> ij> -- Wisconsin Query 4
>
> get cursor c as
> 'select * from TENKTUP1
> where unique2 between 792 and 1791';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
>
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1
> where unique2 between 792 and 1791
> Parse Time: 0
> Bind Time: 0
> Optimize Time: 0
> Generate Time: 0
> Compile Time: 0
> Execute Time: 0
> Begin Compilation Timestamp : null
> End Compilation Timestamp : null
> Begin Execution Timestamp : null
> End Execution Timestamp : null
> Statement Execution Plan Text:
> Index Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> optimizer estimated row count: 993.00
> optimizer estimated cost: 2951.93
>
> Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE2 at serializable
> isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
>
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=btree
> Tree height=2
> start position:
> >= on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> stop position:
> > on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> qualifiers:
> None
> optimizer estimated row count: 993.00
> optimizer estimated cost: 2951.93
>
>
> ij> commit;
> ij> -- Wisconsin Query 5
> get cursor c as
> 'select * from TENKTUP1
> where unique1 between 0 and 99';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
>
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1
> where unique1 between 0 and 99
> Parse Time: 0
> Bind Time: 0
> Optimize Time: 0
> Generate Time: 0
> Compile Time: 0
> Execute Time: 0
> Begin Compilation Timestamp : null
> End Compilation Timestamp : null
> Begin Execution Timestamp : null
> End Execution Timestamp : null
> Statement Execution Plan Text:
> Index Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> optimizer estimated row count: 99.00
> optimizer estimated cost: 291.33
>
> Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable
> isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
>
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=btree
> Tree height=2
> start position:
> >= on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> stop position:
> > on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> qualifiers:
> None
> optimizer estimated row count: 99.00
> optimizer estimated cost: 291.33
>
>
> ij> commit;
> ij> -- Wisconsin Query 6
> get cursor c as
> 'select * from TENKTUP1
> where unique1 between 792 and 1791';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
>
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1
> where unique1 between 792 and 1791
> Parse Time: 0
> Bind Time: 0
> Optimize Time: 0
> Generate Time: 0
> Compile Time: 0
> Execute Time: 0
> Begin Compilation Timestamp : null
> End Compilation Timestamp : null
> Begin Execution Timestamp : null
> End Execution Timestamp : null
> Statement Execution Plan Text:
> Index Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> optimizer estimated row count: 993.00
> optimizer estimated cost: 2951.93
>
> Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable
> isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
>
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=btree
> Tree height=2
> start position:
> >= on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> stop position:
> > on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> qualifiers:
> None
> optimizer estimated row count: 993.00
> optimizer estimated cost: 2951.93
>
>
> ij> commit;
> ij> -- Wisconsin Query 7
> get cursor c as
> 'select *
> from TENKTUP1
> where unique2 = 2001';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
>
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select *
> from TENKTUP1
> where unique2 = 2001
> Parse Time: 0
> Bind Time: 0
> Optimize Time: 0
> Generate Time: 0
> Compile Time: 0
> Execute Time: 0
> Begin Compilation Timestamp : null
> End Compilation Timestamp : null
> Begin Execution Timestamp : null
> End Execution Timestamp : null
> Statement Execution Plan Text:
> Index Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {0, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13,
> 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> optimizer estimated row count: 1.00
> optimizer estimated cost: 5.90
>
> Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE2 at serializable
> isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 1
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
>
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=btree
> Tree height=2
> start position:
> >= on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> stop position:
> > on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> qualifiers:
> None
> optimizer estimated row count: 1.00
> optimizer estimated cost: 5.90
>
>
> ij> commit;
> ij> -- Wisconsin Query 12
> get cursor c as
> 'select * from TENKTUP1, TENKTUP2
> where (TENKTUP1.unique2 = TENKTUP2.unique2)
> and (TENKTUP2.unique2 < 1000)';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
>
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1, TENKTUP2
> where (TENKTUP1.unique2 = TENKTUP2.unique2)
> and (TENKTUP2.unique2 < 1000)
> 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:
> Hash Exists Join ResultSet:
> Number of opens = 1
> Rows seen from the left = 1
> Rows seen from the right = 0
> Rows filtered = 0
> Rows returned = 0
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> optimizer estimated row count: 993.00
> optimizer estimated cost: 5922.65
>
> Left result set:
> Index Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 1
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> optimizer estimated row count: 993.00
> optimizer estimated cost: 2951.93
>
> Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE2 at serializable
> isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 1
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> next time in milliseconds/row = 0
>
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=2
> Number of rows qualified=16
> Number of rows visited=16
> Scan type=btree
> Tree height=2
> start position:
> None
> stop position:
> >= on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> qualifiers:
> None
> optimizer estimated row count: 993.00
> optimizer estimated cost: 2951.93
>
>
> Right result set:
> Index Row to Base Row ResultSet for TENKTUP2:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> optimizer estimated row count: 993.00
> optimizer estimated cost: 2970.71
>
> Hash Scan ResultSet for TENKTUP2 using index TK2UNIQUE2 at serializable
> isolation level using share row locking:
> Number of opens = 1
> Hash table size = 1000
> Hash key is column number 0
> Rows seen = 0
> Rows filtered = 0
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
>
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=7
> Number of rows qualified=1000
> Number of rows visited=1001
> Scan type=btree
> Tree height=2
> start position:
> None
> stop position:
> >= on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> scan qualifiers:
> None
> next qualifiers:
> Column[0][0] Id: 0
> Operator: =
> Ordered nulls: false
> Unknown return value: false
> Negate comparison result: false
>
> optimizer estimated row count: 993.00
> optimizer estimated cost: 2970.71
>
>
>
> ij> commit;
> ij> -- Wisconsin Query 13
> get cursor c as
> 'select * from TENKTUP1, BPRIME
> where (TENKTUP1.unique2 = BPRIME.UNIQUE2)';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
>
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1, BPRIME
> where (TENKTUP1.unique2 = BPRIME.UNIQUE2)
> 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 = 0
> Rows filtered = 0
> restriction = false
> projection = true
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> restriction time (milliseconds) = 0
> projection time (milliseconds) = 0
> optimizer estimated row count: 386.00
> optimizer estimated cost: 3708.03
>
> Source result set:
> Nested Loop Exists Join ResultSet:
> Number of opens = 1
> Rows seen from the left = 1
> Rows seen from the right = 0
> Rows filtered = 0
> Rows returned = 0
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> optimizer estimated row count: 386.00
> optimizer estimated cost: 3708.03
>
> Left result set:
> Table Scan ResultSet for BPRIME at serializable isolation level using
> share table locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 1
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> next time in milliseconds/row = 0
>
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=16
> Number of pages visited=2
> Number of rows qualified=16
> Number of rows visited=16
> Scan type=heap
> start position:
> null stop position:
> null qualifiers:
> None
> optimizer estimated row count: 386.00
> optimizer estimated cost: 1429.47
>
> Right result set:
> Index Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {0, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13,
> 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> optimizer estimated row count: 386.00
> optimizer estimated cost: 2278.56
>
> Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE2 at serializable
> isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 1
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
>
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=btree
> Tree height=2
> 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 estimated row count: 386.00
> optimizer estimated cost: 2278.56
>
>
>
> ij> commit;
> ij> -- Wisconsin query 14
> -- NOTE: This could benefit from transitive closure, which our optimizer
> -- doesn't do (yet).
> -- Note that after fix for optimizer bug 5868, in runtime statistics info,
> we will see 2 qualifiers for table TENKTUP2. This is because as fix for
> -- bug 5868, while getting rid of a redundant predicate which is a start
> and/or stop AND a qualifier, we mark the predicate we are going to keep
> -- as start and/or stop AND as a qualifier. Prior to fix of bug 5868, we
> were disregarding the qualifier flag on the redundant predicate if it
> -- was a start and/or stop predicate too.
> get cursor c as
> 'select * from ONEKTUP, TENKTUP1, TENKTUP2
> where (ONEKTUP.unique2 = TENKTUP1.unique2)
> and (TENKTUP1.unique2 = TENKTUP2.unique2)
> and (TENKTUP1.unique2 < 1000)';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
>
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from ONEKTUP, TENKTUP1, TENKTUP2
> where (ONEKTUP.unique2 = TENKTUP1.unique2)
> and (TENKTUP1.unique2 = TENKTUP2.unique2)
> and (TENKTUP1.unique2 < 1000)
> 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:
> Hash Exists Join ResultSet:
> Number of opens = 1
> Rows seen from the left = 1
> Rows seen from the right = 0
> Rows filtered = 0
> Rows returned = 0
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> optimizer estimated row count: 100.70
> optimizer estimated cost: 7536.51
>
> Left result set:
> Hash 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
> optimizer estimated row count: 626.67
> optimizer estimated cost: 4565.79
>
> Left result set:
> Table Scan ResultSet for ONEKTUP at serializable isolation level using
> share table locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 1
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> next time in milliseconds/row = 0
>
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=16
> Number of pages visited=2
> Number of rows qualified=16
> Number of rows visited=16
> 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
>
> optimizer estimated row count: 626.67
> optimizer estimated cost: 1595.08
>
> Right result set:
> Index Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 1
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> optimizer estimated row count: 626.67
> optimizer estimated cost: 2970.71
>
> Hash Scan ResultSet for TENKTUP1 using index TK1UNIQUE2 at serializable
> isolation level using share row locking:
> Number of opens = 1
> Hash table size = 1000
> Hash key is column number 0
> 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=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=7
> Number of rows qualified=1000
> Number of rows visited=1001
> Scan type=btree
> Tree height=2
> start position:
> None
> stop position:
> >= on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> scan qualifiers:
> None
> next qualifiers:
> Column[0][0] Id: 0
> Operator: =
> Ordered nulls: false
> Unknown return value: false
> Negate comparison result: false
>
> optimizer estimated row count: 626.67
> optimizer estimated cost: 2970.71
>
>
>
> Right result set:
> Index Row to Base Row ResultSet for TENKTUP2:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> optimizer estimated row count: 100.70
> optimizer estimated cost: 2970.71
>
> Hash Scan ResultSet for TENKTUP2 using index TK2UNIQUE2 at serializable
> isolation level using share row locking:
> Number of opens = 1
> Hash table size = 1000
> Hash key is column number 0
> Rows seen = 0
> Rows filtered = 0
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
>
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=7
> Number of rows qualified=1000
> Number of rows visited=1001
> Scan type=btree
> Tree height=2
> start position:
> None
> stop position:
> >= on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> scan qualifiers:
> None
> next qualifiers:
> Column[0][0] Id: 0
> Operator: =
> Ordered nulls: false
> Unknown return value: false
> Negate comparison result: false
>
> optimizer estimated row count: 100.70
> optimizer estimated cost: 2970.71
>
>
>
> ij> commit;
> ij> -- Wisconsin Query 15
> get cursor c as
> 'select * from TENKTUP1, TENKTUP2
> where (TENKTUP1.unique1 = TENKTUP2.unique1)
> and (TENKTUP1.unique1 < 1000)';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
>
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1, TENKTUP2
> where (TENKTUP1.unique1 = TENKTUP2.unique1)
> and (TENKTUP1.unique1 < 1000)
> 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:
> Hash Exists Join ResultSet:
> Number of opens = 1
> Rows seen from the left = 1
> Rows seen from the right = 0
> Rows filtered = 0
> Rows returned = 0
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> optimizer estimated row count: 993.00
> optimizer estimated cost: 5922.65
>
> Left result set:
> Index Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 1
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> optimizer estimated row count: 993.00
> optimizer estimated cost: 2951.93
>
> Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable
> isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 1
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> next time in milliseconds/row = 0
>
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=2
> Number of rows qualified=16
> Number of rows visited=16
> Scan type=btree
> Tree height=2
> start position:
> None
> stop position:
> >= on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> qualifiers:
> None
> optimizer estimated row count: 993.00
> optimizer estimated cost: 2951.93
>
>
> Right result set:
> Index Row to Base Row ResultSet for TENKTUP2:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> optimizer estimated row count: 993.00
> optimizer estimated cost: 2970.71
>
> Hash Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable
> isolation level using share row locking:
> Number of opens = 1
> Hash table size = 1000
> Hash key is column number 0
> Rows seen = 0
> Rows filtered = 0
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
>
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=7
> Number of rows qualified=1000
> Number of rows visited=1001
> Scan type=btree
> Tree height=2
> start position:
> None
> stop position:
> >= on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> scan qualifiers:
> None
> next qualifiers:
> Column[0][0] Id: 0
> Operator: =
> Ordered nulls: false
> Unknown return value: false
> Negate comparison result: false
>
> optimizer estimated row count: 993.00
> optimizer estimated cost: 2970.71
>
>
>
> ij> commit;
> ij> -- Wisconsin Query 16
> get cursor c as
> 'select * from TENKTUP1, BPRIME
> where (TENKTUP1.unique1 = BPRIME.unique1)';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
>
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1, BPRIME
> where (TENKTUP1.unique1 = BPRIME.unique1)
> 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 = 0
> Rows filtered = 0
> restriction = false
> projection = true
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> restriction time (milliseconds) = 0
> projection time (milliseconds) = 0
> optimizer estimat
> ij> -- This test is an adaptation of the Wisconsin benchmark, as
> documented in
> ----- The Benchmark Handbook, Second Edition (edited by Jim Gray). The
> structure
> ----- of the tables and the data in the tables are taken from there.
> -----
> ----- The original benchmark talks about clustered and non-clustered
> ----- indexes - as far as I can tell, this really means indexes where the
> ----- row ordering is or is not the same as in the base table. It does
> ----- not mean special types of indexes. I am putting in queries that
> ----- use both ordered and unordered indexes, despite the fact that
> ----- our optimizer does not currently distinguish these cases.
> -----
> ----- Another difference is that the original Wisconsin benchmark is a
> performance
> ----- test, while this test is only intended to ensure that the optimizer
> comes
> ----- up with the right query plan. Therefore, this test doesn't include
> those
> ----- parts of the Wisconsin benchmark where the optimizer has no choice
> of
> ----- access path (e.g. single-table query with no indexes), nor does it
> include
> ----- the projection and update queries.
> -----
> ----- This test only does the first variation of each query, since that is
> ----- all that is documented in The Benchmark Handbook (it wouldn't be a
> true
> ----- academic reference text if everything were spelled out).
> -----
> ----- After the original Wisconsin queries are a bunch of queries that use
> the
> ----- Wisconsin schema but that were written at Cloudscape specifically
> for
> ----- testing our optimizer.
> autocommit off;
> ij> set isolation serializable;
> 0 rows inserted/updated/deleted
> ij> -- the method refers to a method in performance suite that takes a
> Connection.
> -----create function WISCInsert(rowcount int, tableName varchar(20))
> returns int language java parameter style java external name '
> org.apache.derbyTesting.functionTests.tests.lang.WiscVTI';
> CREATE PROCEDURE WISCINSERT(rowcount int, tableName varchar(20)) LANGUAGE
> JAVA PARAMETER STYLE JAVA EXTERNAL NAME '
> org.apache.derbyTesting.functionTests.tests.lang.WiscVTI.WISCInsertWOConnection
> ';
> 0 rows inserted/updated/deleted
> ij> create table TENKTUP1 (
> unique1 int not null,
> unique2 int not null,
> two int,
> four int,
> ten int,
> twenty int,
> onePercent int,
> tenPercent int,
> twentyPercent int,
> fiftyPercent int,
> unique3 int,
> evenOnePercent int,
> oddOnePercent int,
> stringu1 char(52) not null,
> stringu2 char(52) not null,
> string4 char(52)
> );
> 0 rows inserted/updated/deleted
> ij> --insert 10000 rows into TENKTUP1
> call WISCINSERT( 10000, 'TENKTUP1');
> Statement executed.
> ij> create unique index TK1UNIQUE1 on TENKTUP1(unique1);
> 0 rows inserted/updated/deleted
> ij> create unique index TK1UNIQUE2 on TENKTUP1(unique2);
> 0 rows inserted/updated/deleted
> ij> create index TK1TWO on TENKTUP1(two);
> 0 rows inserted/updated/deleted
> ij> create index TK1FOUR on TENKTUP1(four);
> 0 rows inserted/updated/deleted
> ij> create index TK1TEN on TENKTUP1(ten);
> 0 rows inserted/updated/deleted
> ij> create index TK1TWENTY on TENKTUP1(twenty);
> 0 rows inserted/updated/deleted
> ij> create index TK1ONEPERCENT on TENKTUP1(onePercent);
> 0 rows inserted/updated/deleted
> ij> create index TK1TWENTYPERCENT on TENKTUP1(twentyPercent);
> 0 rows inserted/updated/deleted
> ij> create index TK1EVENONEPERCENT on TENKTUP1(evenOnePercent);
> 0 rows inserted/updated/deleted
> ij> create index TK1ODDONEPERCENT on TENKTUP1(oddOnePercent);
> 0 rows inserted/updated/deleted
> ij> create unique index TK1STRINGU1 on TENKTUP1(stringu1);
> 0 rows inserted/updated/deleted
> ij> create unique index TK1STRINGU2 on TENKTUP1(stringu2);
> 0 rows inserted/updated/deleted
> ij> create index TK1STRING4 on TENKTUP1(string4);
> 0 rows inserted/updated/deleted
> ij> create table TENKTUP2 (
> unique1 int not null,
> unique2 int not null,
> two int,
> four int,
> ten int,
> twenty int,
> onePercent int,
> tenPercent int,
> twentyPercent int,
> fiftyPercent int,
> unique3 int,
> evenOnePercent int,
> oddOnePercent int,
> stringu1 char(52),
> stringu2 char(52),
> string4 char(52)
> );
> 0 rows inserted/updated/deleted
> ij> -- insert 10000 rows into TENKTUP2
> call WISCInsert( 10000, 'TENKTUP2');
> Statement executed.
> ij> create unique index TK2UNIQUE1 on TENKTUP2(unique1);
> 0 rows inserted/updated/deleted
> ij> create unique index TK2UNIQUE2 on TENKTUP2(unique2);
> 0 rows inserted/updated/deleted
> ij> create table ONEKTUP (
> unique1 int not null,
> unique2 int not null,
> two int,
> four int,
> ten int,
> twenty int,
> onePercent int,
> tenPercent int,
> twentyPercent int,
> fiftyPercent int,
> unique3 int,
> evenOnePercent int,
> oddOnePercent int,
> stringu1 char(52),
> stringu2 char(52),
> string4 char(52)
> );
> 0 rows inserted/updated/deleted
> ij> -- insert 1000 rows into ONEKTUP
> call WISCInsert( 1000, 'ONEKTUP');
> Statement executed.
> ij> create unique index ONEKUNIQUE1 on ONEKTUP(unique1);
> 0 rows inserted/updated/deleted
> ij> create unique index ONEKUNIQUE2 on ONEKTUP(unique2);
> 0 rows inserted/updated/deleted
> ij> create table BPRIME (
> unique1 int,
> unique2 int,
> two int,
> four int,
> ten int,
> twenty int,
> onePercent int,
> tenPercent int,
> twentyPercent int,
> fiftyPercent int,
> unique3 int,
> evenOnePercent int,
> oddOnePercent int,
> stringu1 char(52),
> stringu2 char(52),
> string4 char(52)
> );
> 0 rows inserted/updated/deleted
> ij> insert into BPRIME
> select * from TENKTUP2
> where TENKTUP2.unique2 < 1000;
> 1000 rows inserted/updated/deleted
> ij> commit;
> ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
> Statement executed.
> ij> maximumdisplaywidth 8000;
> ij> -- Wisconsin Query 3
> get cursor c as
> 'select * from TENKTUP1
> where unique2 between 0 and 99';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
> -----
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1
> where unique2 between 0 and 99
> Parse Time: 0
> Bind Time: 0
> Optimize Time: 0
> Generate Time: 0
> Compile Time: 0
> Execute Time: 0
> Begin Compilation Timestamp : null
> End Compilation Timestamp : null
> Begin Execution Timestamp : null
> End Execution Timestamp : null
> Statement Execution Plan Text:
> Index Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE2 at serializable
> isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=btree
> Tree height=2
> start position:
> >= on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> stop position:
> > on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> qualifiers:
> None
> ij> commit;
> ij> -- Wisconsin Query 4
> get cursor c as
> 'select * from TENKTUP1
> where unique2 between 792 and 1791';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
> -----
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1
> where unique2 between 792 and 1791
> Parse Time: 0
> Bind Time: 0
> Optimize Time: 0
> Generate Time: 0
> Compile Time: 0
> Execute Time: 0
> Begin Compilation Timestamp : null
> End Compilation Timestamp : null
> Begin Execution Timestamp : null
> End Execution Timestamp : null
> Statement Execution Plan Text:
> Index Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE2 at serializable
> isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=btree
> Tree height=2
> start position:
> >= on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> stop position:
> > on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> qualifiers:
> None
> ij> commit;
> ij> -- Wisconsin Query 5
> get cursor c as
> 'select * from TENKTUP1
> where unique1 between 0 and 99';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
> -----
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1
> where unique1 between 0 and 99
> Parse Time: 0
> Bind Time: 0
> Optimize Time: 0
> Generate Time: 0
> Compile Time: 0
> Execute Time: 0
> Begin Compilation Timestamp : null
> End Compilation Timestamp : null
> Begin Execution Timestamp : null
> End Execution Timestamp : null
> Statement Execution Plan Text:
> Index Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable
> isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=btree
> Tree height=2
> start position:
> >= on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> stop position:
> > on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> qualifiers:
> None
> ij> commit;
> ij> -- Wisconsin Query 6
> get cursor c as
> 'select * from TENKTUP1
> where unique1 between 792 and 1791';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
> -----
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1
> where unique1 between 792 and 1791
> Parse Time: 0
> Bind Time: 0
> Optimize Time: 0
> Generate Time: 0
> Compile Time: 0
> Execute Time: 0
> Begin Compilation Timestamp : null
> End Compilation Timestamp : null
> Begin Execution Timestamp : null
> End Execution Timestamp : null
> Statement Execution Plan Text:
> Index Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable
> isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=btree
> Tree height=2
> start position:
> >= on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> stop position:
> > on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> qualifiers:
> None
> ij> commit;
> ij> -- Wisconsin Query 7
> get cursor c as
> 'select *
> from TENKTUP1
> where unique2 = 2001';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
> -----
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select *
> from TENKTUP1
> where unique2 = 2001
> Parse Time: 0
> Bind Time: 0
> Optimize Time: 0
> Generate Time: 0
> Compile Time: 0
> Execute Time: 0
> Begin Compilation Timestamp : null
> End Compilation Timestamp : null
> Begin Execution Timestamp : null
> End Execution Timestamp : null
> Statement Execution Plan Text:
> Index Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {0, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13,
> 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE2 at serializable
> isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 1
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=btree
> Tree height=2
> start position:
> >= on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> stop position:
> > on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> qualifiers:
> None
> ij> commit;
> ij> -- Wisconsin Query 12
> get cursor c as
> 'select * from TENKTUP1, TENKTUP2
> where (TENKTUP1.unique2 = TENKTUP2.unique2)
> and (TENKTUP2.unique2 < 1000)';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
> -----
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1, TENKTUP2
> where (TENKTUP1.unique2 = TENKTUP2.unique2)
> and (TENKTUP2.unique2 < 1000)
> 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:
> Hash Exists Join ResultSet:
> Number of opens = 1
> Rows seen from the left = 1
> Rows seen from the right = 0
> Rows filtered = 0
> Rows returned = 0
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Left result set:
> Index Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 1
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE2 at serializable
> isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 1
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> next time in milliseconds/row = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=2
> Number of rows qualified=16
> Number of rows visited=16
> Scan type=btree
> Tree height=2
> start position:
> None
> stop position:
> >= on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> qualifiers:
> None
> Right result set:
> Index Row to Base Row ResultSet for TENKTUP2:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Hash Scan ResultSet for TENKTUP2 using index TK2UNIQUE2 at serializable
> isolation level using share row locking:
> Number of opens = 1
> Hash table size = 1000
> Hash key is column number 0
> Rows seen = 0
> Rows filtered = 0
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=7
> Number of rows qualified=1000
> Number of rows visited=1001
> Scan type=btree
> Tree height=2
> start position:
> None
> stop position:
> >= on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> scan qualifiers:
> None
> next qualifiers:
> Column[0][0] Id: 0
> Operator: =
> Ordered nulls: false
> Unknown return value: false
> Negate comparison result: false
> ij> commit;
> ij> -- Wisconsin Query 13
> get cursor c as
> 'select * from TENKTUP1, BPRIME
> where (TENKTUP1.unique2 = BPRIME.UNIQUE2)';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
> -----
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1, BPRIME
> where (TENKTUP1.unique2 = BPRIME.UNIQUE2)
> 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 = 0
> Rows filtered = 0
> restriction = false
> projection = true
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> restriction time (milliseconds) = 0
> projection time (milliseconds) = 0
> Source result set:
> Nested Loop Exists Join ResultSet:
> Number of opens = 1
> Rows seen from the left = 1
> Rows seen from the right = 0
> Rows filtered = 0
> Rows returned = 0
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Left result set:
> Table Scan ResultSet for BPRIME at serializable isolation level using
> share table locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 1
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> next time in milliseconds/row = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=16
> Number of pages visited=2
> Number of rows qualified=16
> Number of rows visited=16
> Scan type=heap
> start position:
> null stop position:
> null qualifiers:
> None
> Right result set:
> Index Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {0, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13,
> 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE2 at serializable
> isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 1
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=btree
> Tree height=2
> 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> commit;
> ij> -- Wisconsin query 14
> ----- NOTE: This could benefit from transitive closure, which our
> optimizer
> ----- doesn't do (yet).
> ----- Note that after fix for optimizer bug 5868, in runtime statistics
> info, we will see 2 qualifiers for table TENKTUP2. This is because as fix
> for
> ----- bug 5868, while getting rid of a redundant predicate which is a
> start and/or stop AND a qualifier, we mark the predicate we are going to
> keep
> ----- as start and/or stop AND as a qualifier. Prior to fix of bug 5868,
> we were disregarding the qualifier flag on the redundant predicate if it
> ----- was a start and/or stop predicate too.
> get cursor c as
> 'select * from ONEKTUP, TENKTUP1, TENKTUP2
> where (ONEKTUP.unique2 = TENKTUP1.unique2)
> and (TENKTUP1.unique2 = TENKTUP2.unique2)
> and (TENKTUP1.unique2 < 1000)';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
> -----
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from ONEKTUP, TENKTUP1, TENKTUP2
> where (ONEKTUP.unique2 = TENKTUP1.unique2)
> and (TENKTUP1.unique2 = TENKTUP2.unique2)
> and (TENKTUP1.unique2 < 1000)
> 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:
> Hash Exists Join ResultSet:
> Number of opens = 1
> Rows seen from the left = 1
> Rows seen from the right = 0
> Rows filtered = 0
> Rows returned = 0
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Left result set:
> Hash 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:
> Table Scan ResultSet for ONEKTUP at serializable isolation level using
> share table locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 1
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> next time in milliseconds/row = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=16
> Number of pages visited=2
> Number of rows qualified=16
> Number of rows visited=16
> 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 Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 1
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Hash Scan ResultSet for TENKTUP1 using index TK1UNIQUE2 at serializable
> isolation level using share row locking:
> Number of opens = 1
> Hash table size = 1000
> Hash key is column number 0
> 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=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=7
> Number of rows qualified=1000
> Number of rows visited=1001
> Scan type=btree
> Tree height=2
> start position:
> None
> stop position:
> >= on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> scan qualifiers:
> None
> next qualifiers:
> Column[0][0] Id: 0
> Operator: =
> Ordered nulls: false
> Unknown return value: false
> Negate comparison result: false
> Right result set:
> Index Row to Base Row ResultSet for TENKTUP2:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Hash Scan ResultSet for TENKTUP2 using index TK2UNIQUE2 at serializable
> isolation level using share row locking:
> Number of opens = 1
> Hash table size = 1000
> Hash key is column number 0
> Rows seen = 0
> Rows filtered = 0
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=7
> Number of rows qualified=1000
> Number of rows visited=1001
> Scan type=btree
> Tree height=2
> start position:
> None
> stop position:
> >= on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> scan qualifiers:
> None
> next qualifiers:
> Column[0][0] Id: 0
> Operator: =
> Ordered nulls: false
> Unknown return value: false
> Negate comparison result: false
> ij> commit;
> ij> -- Wisconsin Query 15
> get cursor c as
> 'select * from TENKTUP1, TENKTUP2
> where (TENKTUP1.unique1 = TENKTUP2.unique1)
> and (TENKTUP1.unique1 < 1000)';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
> -----
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1, TENKTUP2
> where (TENKTUP1.unique1 = TENKTUP2.unique1)
> and (TENKTUP1.unique1 < 1000)
> 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:
> Hash Exists Join ResultSet:
> Number of opens = 1
> Rows seen from the left = 1
> Rows seen from the right = 0
> Rows filtered = 0
> Rows returned = 0
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Left result set:
> Index Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 1
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable
> isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 1
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> next time in milliseconds/row = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=2
> Number of rows qualified=16
> Number of rows visited=16
> Scan type=btree
> Tree height=2
> start position:
> None
> stop position:
> >= on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> qualifiers:
> None
> Right result set:
> Index Row to Base Row ResultSet for TENKTUP2:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Hash Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable
> isolation level using share row locking:
> Number of opens = 1
> Hash table size = 1000
> Hash key is column number 0
> Rows seen = 0
> Rows filtered = 0
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=7
> Number of rows qualified=1000
> Number of rows visited=1001
> Scan type=btree
> Tree height=2
> start position:
> None
> stop position:
> >= on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> scan qualifiers:
> None
> next qualifiers:
> Column[0][0] Id: 0
> Operator: =
> Ordered nulls: false
> Unknown return value: false
> Negate comparison result: false
> ij> commit;
> ij> -- Wisconsin Query 16
> get cursor c as
> 'select * from TENKTUP1, BPRIME
> where (TENKTUP1.unique1 = BPRIME.unique1)';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
> -----
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1, BPRIME
> where (TENKTUP1.unique1 = BPRIME.unique1)
> 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 = 0
> Rows filtered = 0
> restriction = false
> projection = true
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> restriction time (milliseconds) = 0
> projection time (milliseconds) = 0
> Source result set:
> Nested Loop Exists Join ResultSet:
> Number of opens = 1
> Rows seen from the left = 1
> Rows seen from the right = 0
> Rows filtered = 0
> Rows returned = 0
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Left result set:
> Table Scan ResultSet for BPRIME at serializable isolation level using
> share table locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 1
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> next time in milliseconds/row = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=16
> Number of pages visited=2
> Number of rows qualified=16
> Number of rows visited=16
> Scan type=heap
> start position:
> null stop position:
> null qualifiers:
> None
> Right result set:
> Index Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13,
> 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable
> isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 1
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=btree
> Tree height=2
> 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> commit;
> ij> -- Wisconsin Query 17
> ----- NOTE: This could benefit from transitive closure, which our
> optimizer
> ----- doesn't do (yet).
> ----- Note that after fix for optimizer bug 5868, in runtime statistics
> info, we will see 2 qualifiers for table TENKTUP2. This is because as fix
> for
> ----- bug 5868, while getting rid of a redundant predicate which is a
> start and/or stop AND a qualifier, we mark the predicate we are going to
> keep
> ----- as start and/or stop AND as a qualifier. Prior to fix of bug 5868,
> we were disregarding the qualifier flag on the redundant predicate if it
> ----- was a start and/or stop predicate too.
> get cursor c as
> 'select * from ONEKTUP, TENKTUP1, TENKTUP2
> where (ONEKTUP.unique1 = TENKTUP1.unique1)
> and (TENKTUP1.unique1 = TENKTUP2.unique1)
> and (TENKTUP1.unique1 < 1000)';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
> -----
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from ONEKTUP, TENKTUP1, TENKTUP2
> where (ONEKTUP.unique1 = TENKTUP1.unique1)
> and (TENKTUP1.unique1 = TENKTUP2.unique1)
> and (TENKTUP1.unique1 < 1000)
> 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:
> Hash Exists Join ResultSet:
> Number of opens = 1
> Rows seen from the left = 1
> Rows seen from the right = 0
> Rows filtered = 0
> Rows returned = 0
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Left result set:
> Hash 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:
> Table Scan ResultSet for ONEKTUP at serializable isolation level using
> share table locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 1
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> next time in milliseconds/row = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=16
> Number of pages visited=2
> Number of rows qualified=16
> Number of rows visited=16
> Scan type=heap
> start position:
> null stop position:
> null qualifiers:
> Column[0][0] Id: 0
> Operator: <
> Ordered nulls: false
> Unknown return value: false
> Negate comparison result: false
> Right result set:
> Index Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 1
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Hash Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable
> isolation level using share row locking:
> Number of opens = 1
> Hash table size = 1000
> Hash key is column number 0
> 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=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=7
> Number of rows qualified=1000
> Number of rows visited=1001
> Scan type=btree
> Tree height=2
> start position:
> None
> stop position:
> >= on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> scan qualifiers:
> None
> next qualifiers:
> Column[0][0] Id: 0
> Operator: =
> Ordered nulls: false
> Unknown return value: false
> Negate comparison result: false
> Right result set:
> Index Row to Base Row ResultSet for TENKTUP2:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Hash Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable
> isolation level using share row locking:
> Number of opens = 1
> Hash table size = 1000
> Hash key is column number 0
> Rows seen = 0
> Rows filtered = 0
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=7
> Number of rows qualified=1000
> Number of rows visited=1001
> Scan type=btree
> Tree height=2
> start position:
> None
> stop position:
> >= on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> scan qualifiers:
> None
> next qualifiers:
> Column[0][0] Id: 0
> Operator: =
> Ordered nulls: false
> Unknown return value: false
> Negate comparison result: false
> ij> commit;
> ij> -- This is the end of the Wisconsin queries. Now do some queries that
> are
> ----- not part of the original Wisconsin benchmark, using the Wisconsin
> schema.
> ----- Single-table queries using index on column 'two'
> ----- 50% selectivity index that doesn't cover query - should do index
> scan
> get cursor c as
> 'select * from TENKTUP1
> where two = 0';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
> -----
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1
> where two = 0
> Parse Time: 0
> Bind Time: 0
> Optimize Time: 0
> Generate Time: 0
> Compile Time: 0
> Execute Time: 0
> Begin Compilation Timestamp : null
> End Compilation Timestamp : null
> Begin Execution Timestamp : null
> End Execution Timestamp : null
> Statement Execution Plan Text:
> Index Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Index Scan ResultSet for TENKTUP1 using index TK1TWO at serializable
> isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=btree
> Tree height=2
> 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> commit;
> ij> -- 50% selectivity index with 0 matching rows - should do index scan
> get cursor c as
> 'select * from TENKTUP1
> where two = 3';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
> -----
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1
> where two = 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:
> Index Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Index Scan ResultSet for TENKTUP1 using index TK1TWO at serializable
> isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=btree
> Tree height=2
> 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> commit;
> ij> -- matches 100% of rows - should do table scan
> get cursor c as
> 'select * from TENKTUP1
> where two >= 0';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
> -----
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1
> where two >= 0
> Parse Time: 0
> Bind Time: 0
> Optimize Time: 0
> Generate Time: 0
> Compile Time: 0
> Execute Time: 0
> Begin Compilation Timestamp : null
> End Compilation Timestamp : null
> Begin Execution Timestamp : null
> End Execution Timestamp : null
> Statement Execution Plan Text:
> Table Scan ResultSet for TENKTUP1 at serializable isolation level using
> share table locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=16
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=heap
> start position:
> null stop position:
> null qualifiers:
> Column[0][0] Id: 2
> Operator: <
> Ordered nulls: false
> Unknown return value: true
> Negate comparison result: true
> ij> commit;
> ij> -- matches 0 rows - should do index scan
> get cursor c as
> 'select * from TENKTUP1
> where two > 1';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
> -----
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1
> where two > 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:
> Index Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Index Scan ResultSet for TENKTUP1 using index TK1TWO at serializable
> isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=btree
> Tree height=2
> start position:
> > on first 1 column(s).
> Ordered null semantics on the following columns:
> stop position:
> None
> qualifiers:
> None
> ij> commit;
> ij> -- 50% selectivity index that covers query - should do index scan
> get cursor c as
> 'select two from TENKTUP1
> where two = 1';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
> -----
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select two from TENKTUP1
> where two = 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:
> Index Scan ResultSet for TENKTUP1 using index TK1TWO at serializable
> isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> scan information:
> Bit set of columns fetched={0}
> Number of columns fetched=1
> Number of deleted rows visited=0
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=btree
> Tree height=2
> 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> commit;
> ij> -- Single-table queries using index on column 'four'
> ----- 25% selectivity index that doesn't cover query - should do index
> scan
> get cursor c as
> 'select * from TENKTUP1
> where four = 0';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
> -----
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1
> where four = 0
> Parse Time: 0
> Bind Time: 0
> Optimize Time: 0
> Generate Time: 0
> Compile Time: 0
> Execute Time: 0
> Begin Compilation Timestamp : null
> End Compilation Timestamp : null
> Begin Execution Timestamp : null
> End Execution Timestamp : null
> Statement Execution Plan Text:
> Index Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Index Scan ResultSet for TENKTUP1 using index TK1FOUR at serializable
> isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=btree
> Tree height=2
> 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> commit;
> ij> -- 25% selectivity index with 0 matching rows - should do index scan
> get cursor c as
> 'select * from TENKTUP1
> where four = 4';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
> -----
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1
> where four = 4
> Parse Time: 0
> Bind Time: 0
> Optimize Time: 0
> Generate Time: 0
> Compile Time: 0
> Execute Time: 0
> Begin Compilation Timestamp : null
> End Compilation Timestamp : null
> Begin Execution Timestamp : null
> End Execution Timestamp : null
> Statement Execution Plan Text:
> Index Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Index Scan ResultSet for TENKTUP1 using index TK1FOUR at serializable
> isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=btree
> Tree height=2
> 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> commit;
> ij> -- matches 75% of rows - should do table scan
> get cursor c as
> 'select * from TENKTUP1
> where four >= 1';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
> -----
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1
> where four >= 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:
> Table Scan ResultSet for TENKTUP1 at serializable isolation level using
> share table locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=16
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=heap
> start position:
> null stop position:
> null qualifiers:
> Column[0][0] Id: 3
> Operator: <
> Ordered nulls: false
> Unknown return value: true
> Negate comparison result: true
> ij> commit;
> ij> -- matches 0 rows - should do index scan
> get cursor c as
> 'select * from TENKTUP1
> where four > 3';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
> -----
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1
> where four > 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:
> Index Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Index Scan ResultSet for TENKTUP1 using index TK1FOUR at serializable
> isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=btree
> Tree height=2
> start position:
> > on first 1 column(s).
> Ordered null semantics on the following columns:
> stop position:
> None
> qualifiers:
> None
> ij> commit;
> ij> -- 25% selectivity index that covers query - should do index scan
> get cursor c as
> 'select four from TENKTUP1
> where four = 2';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
> -----
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select four from TENKTUP1
> where four = 2
> Parse Time: 0
> Bind Time: 0
> Optimize Time: 0
> Generate Time: 0
> Compile Time: 0
> Execute Time: 0
> Begin Compilation Timestamp : null
> End Compilation Timestamp : null
> Begin Execution Timestamp : null
> End Execution Timestamp : null
> Statement Execution Plan Text:
> Index Scan ResultSet for TENKTUP1 using index TK1FOUR at serializable
> isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> scan information:
> Bit set of columns fetched={0}
> Number of columns fetched=1
> Number of deleted rows visited=0
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=btree
> Tree height=2
> 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> commit;
> ij> -- Single-table queries using index on column 'twentyPercent'
> ----- 20% selectivity index that doesn't cover query - should use index
> get cursor c as
> 'select * from TENKTUP1
> where twentyPercent = 2';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
> -----
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1
> where twentyPercent = 2
> Parse Time: 0
> Bind Time: 0
> Optimize Time: 0
> Generate Time: 0
> Compile Time: 0
> Execute Time: 0
> Begin Compilation Timestamp : null
> End Compilation Timestamp : null
> Begin Execution Timestamp : null
> End Execution Timestamp : null
> Statement Execution Plan Text:
> Index Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Index Scan ResultSet for TENKTUP1 using index TK1TWENTYPERCENT at
> serializable isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=btree
> Tree height=2
> 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> commit;
> ij> -- 20% selectivity index with 0 matching rows - should do index scan
> get cursor c as
> 'select * from TENKTUP1
> where twentyPercent = 5';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
> -----
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1
> where twentyPercent = 5
> Parse Time: 0
> Bind Time: 0
> Optimize Time: 0
> Generate Time: 0
> Compile Time: 0
> Execute Time: 0
> Begin Compilation Timestamp : null
> End Compilation Timestamp : null
> Begin Execution Timestamp : null
> End Execution Timestamp : null
> Statement Execution Plan Text:
> Index Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Index Scan ResultSet for TENKTUP1 using index TK1TWENTYPERCENT at
> serializable isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=btree
> Tree height=2
> 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> commit;
> ij> -- matches 60% of rows - should do table scan
> get cursor c as
> 'select * from TENKTUP1
> where twentyPercent > 1';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
> -----
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1
> where twentyPercent > 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:
> Table Scan ResultSet for TENKTUP1 at serializable isolation level using
> share table locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=16
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=heap
> start position:
> null stop position:
> null qualifiers:
> Column[0][0] Id: 8
> Operator: <=
> Ordered nulls: false
> Unknown return value: true
> Negate comparison result: true
> ij> commit;
> ij> -- matches 0 rows - should do index scan
> get cursor c as
> 'select * from TENKTUP1
> where twentyPercent > 4';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
> -----
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1
> where twentyPercent > 4
> Parse Time: 0
> Bind Time: 0
> Optimize Time: 0
> Generate Time: 0
> Compile Time: 0
> Execute Time: 0
> Begin Compilation Timestamp : null
> End Compilation Timestamp : null
> Begin Execution Timestamp : null
> End Execution Timestamp : null
> Statement Execution Plan Text:
> Index Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Index Scan ResultSet for TENKTUP1 using index TK1TWENTYPERCENT at
> serializable isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=btree
> Tree height=2
> start position:
> > on first 1 column(s).
> Ordered null semantics on the following columns:
> stop position:
> None
> qualifiers:
> None
> ij> commit;
> ij> -- 20% selectivity index that covers query - should do index scan
> get cursor c as
> 'select twentyPercent from TENKTUP1
> where twentyPercent = 3';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
> -----
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select twentyPercent from TENKTUP1
> where twentyPercent = 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:
> Index Scan ResultSet for TENKTUP1 using index TK1TWENTYPERCENT at
> serializable isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> scan information:
> Bit set of columns fetched={0}
> Number of columns fetched=1
> Number of deleted rows visited=0
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=btree
> Tree height=2
> 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> commit;
> ij> -- Single-table queries using index on column 'ten'
> ----- 10% selectivity index that doesn't cover query - should use index
> get cursor c as
> 'select * from TENKTUP1
> where ten = 5';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
> -----
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1
> where ten = 5
> Parse Time: 0
> Bind Time: 0
> Optimize Time: 0
> Generate Time: 0
> Compile Time: 0
> Execute Time: 0
> Begin Compilation Timestamp : null
> End Compilation Timestamp : null
> Begin Execution Timestamp : null
> End Execution Timestamp : null
> Statement Execution Plan Text:
> Index Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Index Scan ResultSet for TENKTUP1 using index TK1TEN at serializable
> isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=btree
> Tree height=2
> 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> commit;
> ij> -- 10% selectivity index with 0 matching rows - should do index scan
> get cursor c as
> 'select * from TENKTUP1
> where ten = 10';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
> -----
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1
> where ten = 10
> Parse Time: 0
> Bind Time: 0
> Optimize Time: 0
> Generate Time: 0
> Compile Time: 0
> Execute Time: 0
> Begin Compilation Timestamp : null
> End Compilation Timestamp : null
> Begin Execution Timestamp : null
> End Execution Timestamp : null
> Statement Execution Plan Text:
> Index Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Index Scan ResultSet for TENKTUP1 using index TK1TEN at serializable
> isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=btree
> Tree height=2
> 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> commit;
> ij> -- matches 50% of rows - should do index scan
> get cursor c as
> 'select * from TENKTUP1
> where ten <= 4';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
> -----
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1
> where ten <= 4
> Parse Time: 0
> Bind Time: 0
> Optimize Time: 0
> Generate Time: 0
> Compile Time: 0
> Execute Time: 0
> Begin Compilation Timestamp : null
> End Compilation Timestamp : null
> Begin Execution Timestamp : null
> End Execution Timestamp : null
> Statement Execution Plan Text:
> Index Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Index Scan ResultSet for TENKTUP1 using index TK1TEN at serializable
> isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=btree
> Tree height=2
> start position:
> None
> stop position:
> > on first 1 column(s).
> Ordered null semantics on the following columns:
> qualifiers:
> None
> ij> commit;
> ij> -- matches 60% of rows - should do table scan
> get cursor c as
> 'select * from TENKTUP1
> where ten <= 5';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
> -----
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1
> where ten <= 5
> Parse Time: 0
> Bind Time: 0
> Optimize Time: 0
> Generate Time: 0
> Compile Time: 0
> Execute Time: 0
> Begin Compilation Timestamp : null
> End Compilation Timestamp : null
> Begin Execution Timestamp : null
> End Execution Timestamp : null
> Statement Execution Plan Text:
> Table Scan ResultSet for TENKTUP1 at serializable isolation level using
> share table locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=16
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=heap
> start position:
> null stop position:
> null qualifiers:
> Column[0][0] Id: 4
> Operator: <=
> Ordered nulls: false
> Unknown return value: false
> Negate comparison result: false
> ij> commit;
> ij> -- matches 0 rows - should do index scan
> get cursor c as
> 'select * from TENKTUP1
> where ten > 100';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
> -----
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select * from TENKTUP1
> where ten > 100
> Parse Time: 0
> Bind Time: 0
> Optimize Time: 0
> Generate Time: 0
> Compile Time: 0
> Execute Time: 0
> Begin Compilation Timestamp : null
> End Compilation Timestamp : null
> Begin Execution Timestamp : null
> End Execution Timestamp : null
> Statement Execution Plan Text:
> Index Row to Base Row ResultSet for TENKTUP1:
> Number of opens = 1
> Rows seen = 0
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> Index Scan ResultSet for TENKTUP1 using index TK1TEN at serializable
> isolation level using share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 0
> Rows filtered = 0
> Fetch Size = 16
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> scan information:
> Bit set of columns fetched=All
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=0
> Number of rows qualified=0
> Number of rows visited=0
> Scan type=btree
> Tree height=2
> start position:
> > on first 1 column(s).
> Ordered null semantics on the following columns:
> stop position:
> None
> qualifiers:
> None
> ij> commit;
> ij> -- 10% selectivity index that covers query - should do index scan
> get cursor c as
> 'select ten from TENKTUP1
> where ten = 7';
> ij> close c;
> ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> 1
> -----
> Statement Name:
> SQL_CURSH200C1
> Statement Text:
> select ten from TENKTUP1
> where ten = 7
> Parse Time: 0
> Bind Time: 0
> Optimize Time: 0
> Generate Time: 0
> Compile Time: 0
> Execute Time: 0
> Begin Compilation Timestamp : null
> End Compilation Timestamp : null
> Begin Execution Timestamp : null
> End Execution Timestamp : null
> Statement Execution Plan Text:
> Index Scan ResultSet for TENKTUP1 using index TK1TEN at serializa
>
>