wisconsin.sql

derby database source code.good for you.

-- Join on ten, 25% of rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.ten = TENKTUP2.ten
	and TENKTUP2.unique1 < 2500';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on ten, 10% of rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.ten = TENKTUP2.ten
	and TENKTUP2.unique1 < 1000';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on ten, 5% of rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.ten = TENKTUP2.ten
	and TENKTUP2.unique1 < 500';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on ten, 1% of rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.ten = TENKTUP2.ten
	and TENKTUP2.unique1 < 100';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on ten, 1 row in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.ten = TENKTUP2.ten
	and TENKTUP2.unique1 = 0';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Joins on twenty

-- Join on twenty, all rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.twenty = TENKTUP2.twenty';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on twenty, 60% of rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.twenty = TENKTUP2.twenty
	and TENKTUP2.unique1 < 6000';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on twenty, 25% of rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.twenty = TENKTUP2.twenty
	and TENKTUP2.unique1 < 2500';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on twenty, 10% of rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.twenty = TENKTUP2.twenty
	and TENKTUP2.unique1 < 1000';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on twenty, 5% of rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.twenty = TENKTUP2.twenty
	and TENKTUP2.unique1 < 500';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on twenty, 1% of rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.twenty = TENKTUP2.twenty
	and TENKTUP2.unique1 < 100';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on twenty, 1 row in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.twenty = TENKTUP2.twenty
	and TENKTUP2.unique1 = 0';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Joins on onePercent

-- Join on onePercent, all rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.onePercent = TENKTUP2.onePercent';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on onePercent, 60% of rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.onePercent = TENKTUP2.onePercent
	and TENKTUP2.unique1 < 6000';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on onePercent, 25% of rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.onePercent = TENKTUP2.onePercent
	and TENKTUP2.unique1 < 2500';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on onePercent, 10% of rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.onePercent = TENKTUP2.onePercent
	and TENKTUP2.unique1 < 1000';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on onePercent, 5% of rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.onePercent = TENKTUP2.onePercent
	and TENKTUP2.unique1 < 500';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on onePercent, 1% of rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.onePercent = TENKTUP2.onePercent
	and TENKTUP2.unique1 < 100';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on onePercent, 1 row in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.onePercent = TENKTUP2.onePercent
	and TENKTUP2.unique1 = 0';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Joins on twentyPercent

-- Join on twentyPercent, all rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.twentyPercent = TENKTUP2.twentyPercent';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on twentyPercent, 60% of rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.twentyPercent = TENKTUP2.twentyPercent
	and TENKTUP2.unique1 < 6000';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on twentyPercent, 25% of rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.twentyPercent = TENKTUP2.twentyPercent
	and TENKTUP2.unique1 < 2500';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on twentyPercent, 10% of rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.twentyPercent = TENKTUP2.twentyPercent
	and TENKTUP2.unique1 < 1000';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on twentyPercent, 5% of rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.twentyPercent = TENKTUP2.twentyPercent
	and TENKTUP2.unique1 < 500';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on twentyPercent, 1% of rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.twentyPercent = TENKTUP2.twentyPercent
	and TENKTUP2.unique1 < 100';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on twentyPercent, 1 row in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.twentyPercent = TENKTUP2.twentyPercent
	and TENKTUP2.unique1 = 0';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Joins on stringu1

-- Join on stringu1, all rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.stringu1 = TENKTUP2.stringu1';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on stringu1, 60% of rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.stringu1 = TENKTUP2.stringu1
	and TENKTUP2.unique1 < 6000';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on stringu1, 25% of rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.stringu1 = TENKTUP2.stringu1
	and TENKTUP2.unique1 < 2500';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on stringu1, 10% of rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.stringu1 = TENKTUP2.stringu1
	and TENKTUP2.unique1 < 1000';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on stringu1, 5% of rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.stringu1 = TENKTUP2.stringu1
	and TENKTUP2.unique1 < 500';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on stringu1, 1% of rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.stringu1 = TENKTUP2.stringu1
	and TENKTUP2.unique1 < 100';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on stringu1, 1 row in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.stringu1 = TENKTUP2.stringu1
	and TENKTUP2.unique1 = 0';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Joins on string4

-- Join on string4, all rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.string4 = TENKTUP2.string4';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on string4, 60% of rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.string4 = TENKTUP2.string4
	and TENKTUP2.unique1 < 6000';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on string4, 25% of rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.string4 = TENKTUP2.string4
	and TENKTUP2.unique1 < 2500';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on string4, 10% of rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.string4 = TENKTUP2.string4
	and TENKTUP2.unique1 < 1000';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on string4, 5% of rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.string4 = TENKTUP2.string4
	and TENKTUP2.unique1 < 500';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on string4, 1% of rows in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.string4 = TENKTUP2.string4
	and TENKTUP2.unique1 < 100';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on string4, 1 row in TENKTUP2
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	where TENKTUP1.string4 = TENKTUP2.string4
	and TENKTUP2.unique1 = 0';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Now do the same equijoin tests, but select only one column from TENKTUP1.
-- This way, it can choose hash join where appropriate (it avoids it where
-- it thinks the hash table will take too much memory).

-- Joins on unique1

-- Join on unique1, all rows in TENKTUP2
get cursor c as
	'select TENKTUP1.unique2, TENKTUP2.* from TENKTUP1, TENKTUP2
	where TENKTUP1.unique1 = TENKTUP2.unique1';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on unique1, 60% of rows in TENKTUP2
get cursor c as
	'select TENKTUP1.unique2, TENKTUP2.* from TENKTUP1, TENKTUP2
	where TENKTUP1.unique1 = TENKTUP2.unique1
	and TENKTUP2.unique1 < 6000';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on unique1, 25% of rows in TENKTUP2
get cursor c as
	'select TENKTUP1.unique2, TENKTUP2.* from TENKTUP1, TENKTUP2
	where TENKTUP1.unique1 = TENKTUP2.unique1
	and TENKTUP2.unique1 < 2500';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on unique1, 10% of rows in TENKTUP2
get cursor c as
	'select TENKTUP1.unique2, TENKTUP2.* from TENKTUP1, TENKTUP2
	where TENKTUP1.unique1 = TENKTUP2.unique1
	and TENKTUP2.unique1 < 1000';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on unique1, 5% of rows in TENKTUP2
get cursor c as
	'select TENKTUP1.unique2, TENKTUP2.* from TENKTUP1, TENKTUP2
	where TENKTUP1.unique1 = TENKTUP2.unique1
	and TENKTUP2.unique1 < 500';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on unique1, 1% of rows in TENKTUP2
get cursor c as
	'select TENKTUP1.unique2, TENKTUP2.* from TENKTUP1, TENKTUP2
	where TENKTUP1.unique1 = TENKTUP2.unique1
	and TENKTUP2.unique1 < 100';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on unique1, 1 row in TENKTUP2
get cursor c as
	'select TENKTUP1.unique2, TENKTUP2.* from TENKTUP1, TENKTUP2
	where TENKTUP1.unique1 = TENKTUP2.unique1
	and TENKTUP2.unique1 = 0';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Joins on two

-- Join on two, all rows in TENKTUP2
get cursor c as
	'select TENKTUP1.unique2, TENKTUP2.* from TENKTUP1, TENKTUP2
	where TENKTUP1.two = TENKTUP2.two';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on two, 60% of rows in TENKTUP2
get cursor c as
	'select TENKTUP1.unique2, TENKTUP2.* from TENKTUP1, TENKTUP2
	where TENKTUP1.two = TENKTUP2.two
	and TENKTUP2.unique1 < 6000';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on two, 25% of rows in TENKTUP2
get cursor c as
	'select TENKTUP1.unique2, TENKTUP2.* from TENKTUP1, TENKTUP2
	where TENKTUP1.two = TENKTUP2.two
	and TENKTUP2.unique1 < 2500';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on two, 10% of rows in TENKTUP2
get cursor c as
	'select TENKTUP1.unique2, TENKTUP2.* from TENKTUP1, TENKTUP2
	where TENKTUP1.two = TENKTUP2.two
	and TENKTUP2.unique1 < 1000';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on two, 5% of rows in TENKTUP2
get cursor c as
	'select TENKTUP1.unique2, TENKTUP2.* from TENKTUP1, TENKTUP2
	where TENKTUP1.two = TENKTUP2.two
	and TENKTUP2.unique1 < 500';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on two, 1% of rows in TENKTUP2
get cursor c as
	'select TENKTUP1.unique2, TENKTUP2.* from TENKTUP1, TENKTUP2
	where TENKTUP1.two = TENKTUP2.two
	and TENKTUP2.unique1 < 100';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on two, 1 row in TENKTUP2