wisconsin.sql

derby database source code.good for you.

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

-- 1% selectivity index that doesn't cover query - should use index
get cursor c as
	'select * from TENKTUP1
	where evenOnePercent = 64';
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 evenOnePercent = 200';
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 evenOnePercent > 99';
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 evenOnePercent > 80';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

-- 1% selectivity index that doesn't cover query - should use index
get cursor c as
	'select * from TENKTUP1
	where oddOnePercent = 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 oddOnePercent = 100';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- matches 40% of rows - should do index scan
get cursor c as
	'select * from TENKTUP1
	where oddOnePercent > 120';
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 oddOnePercent > 80';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

-- unique index that doesn't cover query - should use index
get cursor c as
	'select * from TENKTUP1
	where stringu1 = ''AAAAJKLxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx''';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- unique index with 0 matching rows - should do index scan
get cursor c as
	'select * from TENKTUP1
	where stringu1 = ''AAAAZZZxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx''';
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 stringu1 > ''AAAAHKHxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx''';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- matches 51% of rows - should do table scan
get cursor c as
	'select * from TENKTUP1
	where stringu1 > ''AAAAHOCxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx''';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- unique index that covers query - should do index scan
get cursor c as
	'select stringu1 from TENKTUP1
	where stringu1 = ''AAAAAABxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx''';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

-- unique index that doesn't cover query - should use index
get cursor c as
	'select * from TENKTUP1
	where stringu2 = ''AAAAJKLxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx''';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- unique index with 0 matching rows - should do index scan
get cursor c as
	'select * from TENKTUP1
	where stringu2 = ''AAAAZZZxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx''';
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 stringu2 > ''AAAAHKHxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx''';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- matches 51% of rows - should do table scan
get cursor c as
	'select * from TENKTUP1
	where stringu2 > ''AAAAHOCxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx''';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- unique index that covers query - should do index scan
get cursor c as
	'select stringu2 from TENKTUP1
	where stringu2 = ''AAAAAABxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx''';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

-- 25% selectivity index that doesn't cover query - should do index scan
get cursor c as
	'select * from TENKTUP1
	where string4 = ''AAAAxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx''';
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 string4 = ''EEEExxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx''';
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 string4 > ''HHHHxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx''';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Now test equijoins with different selectivities and different numbers
-- of outer rows.  The approach taken is that different join columns are
-- used, and that TENKTUP1 has indexes on all the joining columns, while
-- TENKTUP2 does not.  We use the unique1 column of TENKTUP2 to select
-- different numbers of rows.  The two tables will always appear in the
-- FROM clause with TENKTUP1 first, and TENKTUP2 second - it is up to
-- the optimizer to figure out which should come first in the join order.

-- Joins on unique1

-- Join on unique1, all rows in TENKTUP2
get cursor c as
	'select * 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 * 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 * 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 * 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 * 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 * 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 * 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 * 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 * 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 * 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 * 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 * 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 * 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
get cursor c as
	'select * 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 * 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 * 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 * 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 * 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 * 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 * 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 * 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 * 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 * from TENKTUP1, TENKTUP2
	where TENKTUP1.ten = TENKTUP2.ten
	and TENKTUP2.unique1 < 6000';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;