wisconsin.sql

derby database source code.good for you.

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

set isolation serializable;
-- 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';

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

--insert 10000 rows into TENKTUP1 
call WISCINSERT( 10000, 'TENKTUP1'); 

create unique index TK1UNIQUE1 on TENKTUP1(unique1);
create unique index TK1UNIQUE2 on TENKTUP1(unique2);
create index TK1TWO on TENKTUP1(two);
create index TK1FOUR on TENKTUP1(four);
create index TK1TEN on TENKTUP1(ten);
create index TK1TWENTY on TENKTUP1(twenty);
create index TK1ONEPERCENT on TENKTUP1(onePercent);
create index TK1TWENTYPERCENT on TENKTUP1(twentyPercent);
create index TK1EVENONEPERCENT on TENKTUP1(evenOnePercent);
create index TK1ODDONEPERCENT on TENKTUP1(oddOnePercent);
create unique index TK1STRINGU1 on TENKTUP1(stringu1);
create unique index TK1STRINGU2 on TENKTUP1(stringu2);
create index TK1STRING4 on TENKTUP1(string4);

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

-- insert 10000 rows into TENKTUP2
call WISCInsert( 10000, 'TENKTUP2'); 

create unique index TK2UNIQUE1 on TENKTUP2(unique1);
create unique index TK2UNIQUE2 on TENKTUP2(unique2);

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

-- insert 1000 rows into ONEKTUP
call WISCInsert( 1000, 'ONEKTUP'); 

create unique index ONEKUNIQUE1 on ONEKTUP(unique1);
create unique index ONEKUNIQUE2 on ONEKTUP(unique2);

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

insert into BPRIME
select * from TENKTUP2
where TENKTUP2.unique2 < 1000;

commit;

call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
maximumdisplaywidth 8000;

-- Wisconsin Query 3

get cursor c as
	'select * from TENKTUP1
	where unique2 between 0 and 99';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Wisconsin Query 4

get cursor c as
	'select * from TENKTUP1
	where unique2 between 792 and 1791';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Wisconsin Query 5
get cursor c as
	'select * from TENKTUP1
	where unique1 between 0 and 99';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Wisconsin Query 6
get cursor c as
	'select * from TENKTUP1
	where unique1 between 792 and 1791';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Wisconsin Query 7
get cursor c as
	'select *
	from TENKTUP1
	where unique2 = 2001';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Wisconsin Query 12
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where (TENKTUP1.unique2 = TENKTUP2.unique2)
	and (TENKTUP2.unique2 < 1000)';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Wisconsin Query 13
get cursor c as
	'select * from TENKTUP1, BPRIME
	where (TENKTUP1.unique2 = BPRIME.UNIQUE2)';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 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)';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Wisconsin Query 15
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where (TENKTUP1.unique1 = TENKTUP2.unique1)
	and (TENKTUP1.unique1 < 1000)';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Wisconsin Query 16
get cursor c as
	'select * from TENKTUP1, BPRIME
	where (TENKTUP1.unique1 = BPRIME.unique1)';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 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)';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 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';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 50% selectivity index with 0 matching rows - should do index scan
get cursor c as
	'select * from TENKTUP1
	where two = 3';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- matches 100% of rows - should do table scan
get cursor c as
	'select * from TENKTUP1
	where two >= 0';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- matches 0 rows - should do index scan
get cursor c as
	'select * from TENKTUP1
	where two > 1';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 50% selectivity index that covers query - should do index scan
get cursor c as
	'select two from TENKTUP1
	where two = 1';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 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';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 25% selectivity index with 0 matching rows - should do index scan
get cursor c as
	'select * from TENKTUP1
	where four = 4';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- matches 75% of rows - should do table scan
get cursor c as
	'select * from TENKTUP1
	where four >= 1';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- matches 0 rows - should do index scan
get cursor c as
	'select * from TENKTUP1
	where four > 3';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 25% selectivity index that covers query - should do index scan
get cursor c as
	'select four from TENKTUP1
	where four = 2';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 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';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 20% selectivity index with 0 matching rows - should do index scan
get cursor c as
	'select * from TENKTUP1
	where twentyPercent = 5';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- matches 60% of rows - should do table scan
get cursor c as
	'select * from TENKTUP1
	where twentyPercent > 1';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- matches 0 rows - should do index scan
get cursor c as
	'select * from TENKTUP1
	where twentyPercent > 4';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 20% selectivity index that covers query - should do index scan
get cursor c as
	'select twentyPercent from TENKTUP1
	where twentyPercent = 3';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 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';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 10% selectivity index with 0 matching rows - should do index scan
get cursor c as
	'select * from TENKTUP1
	where ten = 10';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- matches 50% of rows - should do index scan
get cursor c as
	'select * from TENKTUP1
	where ten <= 4';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- matches 60% of rows - should do table scan
get cursor c as
	'select * from TENKTUP1
	where ten <= 5';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- matches 0 rows - should do index scan
get cursor c as
	'select * from TENKTUP1
	where ten > 100';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 10% selectivity index that covers query - should do index scan
get cursor c as
	'select ten from TENKTUP1
	where ten = 7';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Single-table queries using index on column 'twenty'

-- 5% selectivity index that doesn't cover query - should use index
get cursor c as
	'select * from TENKTUP1
	where twenty = 17';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 5% selectivity index with 0 matching rows - should do index scan
get cursor c as
	'select * from TENKTUP1
	where twenty = 20';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- matches 50% of rows - should do index scan
get cursor c as
	'select * from TENKTUP1
	where twenty <= 9';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- matches 55% of rows - should do table scan
get cursor c as
	'select * from TENKTUP1
	where twenty <= 10';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- matches 0 rows - should do index scan
get cursor c as
	'select * from TENKTUP1
	where twenty < 0';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 5% selectivity index that covers query - should do index scan
get cursor c as
	'select twenty from TENKTUP1
	where twenty = 19';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Single-table queries using index on column 'onePercent'

-- 1% selectivity index that doesn't cover query - should use index
get cursor c as
	'select * from TENKTUP1
	where onePercent = 63';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 1% selectivity index with 0 matching rows - should do index scan
get cursor c as
	'select * from TENKTUP1
	where onePercent = 100';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- matches 50% of rows - should do index scan
get cursor c as
	'select * from TENKTUP1
	where onePercent > 49';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;