wisconsin.sql

来自「derby database source code.good for you.」· SQL 代码 · 共 2,973 行 · 第 1/5 页

get cursor c as
	'select TENKTUP1.unique2, TENKTUP2.* from TENKTUP1, TENKTUP2
	where TENKTUP1.two = TENKTUP2.two
	and TENKTUP2.unique1 = 0';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Joins on four

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Joins on ten

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Join on ten, 25% of rows in TENKTUP2
get cursor c as
	'select TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* 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 TENKTUP1.unique2, TENKTUP2.* from TENKTUP1, TENKTUP2
	where TENKTUP1.string4 = TENKTUP2.string4
	and TENKTUP2.unique1 = 0';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Test the effect of ORDER BY on access path.  The optimizer takes
-- the cost of sorting into account, and may choose an access path
-- in the same order as the ORDER BY, especially if the sort is
-- expensive.
--
-- First try single-table queries.

-- No where clause, try ordering on different indexed columns

get cursor c as
	'select * from TENKTUP1 order by unique1';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

get cursor c as
	'select * from TENKTUP1 order by unique2';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

get cursor c as
	'select * from TENKTUP1 order by two';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

get cursor c as
	'select * from TENKTUP1 order by four';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();