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

[Kathey Marsden <km...@sbcglobal.net>]

 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

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
>
>