wisconsin.sql

derby database source code.good for you.

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Now try the same thing with covering indexes

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

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

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Where clause on same column as order by, with different selectivities.

-- 60%
get cursor c as
	'select * from TENKTUP1 where unique1 < 6000 order by unique1';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 25%
get cursor c as
	'select * from TENKTUP1 where unique1 < 2500 order by unique1';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 10%
get cursor c as
	'select * from TENKTUP1 where unique1 < 1000 order by unique1';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 5%
get cursor c as
	'select * from TENKTUP1 where unique1 < 500 order by unique1';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 1%
get cursor c as
	'select * from TENKTUP1 where unique1 < 100 order by unique1';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- one row
get cursor c as
	'select * from TENKTUP1 where unique1 = 0 order by unique1';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Where clause and order by on different columns - non-covering

-- 60%
get cursor c as
	'select * from TENKTUP1 where unique1 < 6000 order by unique2';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 25%
get cursor c as
	'select * from TENKTUP1 where unique1 < 2500 order by unique2';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 10%
get cursor c as
	'select * from TENKTUP1 where unique1 < 1000 order by unique2';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 5%
get cursor c as
	'select * from TENKTUP1 where unique1 < 500 order by unique2';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 1%
get cursor c as
	'select * from TENKTUP1 where unique1 < 100 order by unique2';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- one row
get cursor c as
	'select * from TENKTUP1 where unique1 = 0 order by unique2';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Where clause and order by on different columns - covering

create index TK1UNIQUE1TWO on TENKTUP1(unique1, two);
create index TK1TWOUNIQUE1 on TENKTUP1(two, unique1);

-- 60%
get cursor c as
	'select two from TENKTUP1 where unique1 < 6000 order by two';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 25%
get cursor c as
	'select two from TENKTUP1 where unique1 < 2500 order by two';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 10%
get cursor c as
	'select two from TENKTUP1 where unique1 < 1000 order by two';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 5%
get cursor c as
	'select two from TENKTUP1 where unique1 < 500 order by two';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 1%
get cursor c as
	'select two from TENKTUP1 where unique1 < 100 order by two';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- one row
-- RESOLVE: For some reason, this avoids the sort by choosing the
-- index on column two, rather than by treating it as a one-row table.
-- It does not do this if you run the query by itself, outside of this
-- test.
get cursor c as
	'select two from TENKTUP1 where unique1 = 0 order by two';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

drop index TK1UNIQUE1TWO;

-- Constant search condition on first column of index, order on second
-- column.
get cursor c as
	'select two, unique1 from TENKTUP1 where two = 0 order by unique1';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Constant search condition on first column of index, order on first and second
-- columns.
get cursor c as
	'select two, unique1 from TENKTUP1 where two = 0 order by two, unique1';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

drop index TK1TWOUNIQUE1;

commit;

-- Now test sort avoidance with joins.
--
-- First try two-way joins where the order by column is in only one table

-- Order by column same as joining column
--
-- 100% of rows from joining table
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	 where TENKTUP1.unique1 = TENKTUP2.unique1
	 order by TENKTUP1.unique1';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 60% of rows from joining table
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	 where TENKTUP1.unique1 = TENKTUP2.unique1
	 and TENKTUP2.unique1 < 6000
	 order by TENKTUP1.unique1';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 25% of rows from joining table
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	 where TENKTUP1.unique1 = TENKTUP2.unique1
	 and TENKTUP2.unique1 < 2500
	 order by TENKTUP1.unique1';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 25% of rows from joining table
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	 where TENKTUP1.unique1 = TENKTUP2.unique1
	 and TENKTUP2.unique1 < 2500
	 order by TENKTUP1.unique1';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 10% of rows from joining table
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	 where TENKTUP1.unique1 = TENKTUP2.unique1
	 and TENKTUP2.unique1 < 1000
	 order by TENKTUP1.unique1';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 5% of rows from joining table
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	 where TENKTUP1.unique1 = TENKTUP2.unique1
	 and TENKTUP2.unique1 < 500
	 order by TENKTUP1.unique1';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 1% of rows from joining table
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	 where TENKTUP1.unique1 = TENKTUP2.unique1
	 and TENKTUP2.unique1 < 100
	 order by TENKTUP1.unique1';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- one row from joining table
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	 where TENKTUP1.unique1 = TENKTUP2.unique1
	 and TENKTUP2.unique1 = 0
	 order by TENKTUP1.unique1';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Order by column different from joining column
--
-- 100% of rows from joining table
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	 where TENKTUP1.unique2 = TENKTUP2.unique2
	 order by TENKTUP1.unique1';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 60% of rows from joining table
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	 where TENKTUP1.unique2 = TENKTUP2.unique2
	 and TENKTUP2.unique1 < 6000
	 order by TENKTUP1.unique1';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 25% of rows from joining table
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	 where TENKTUP1.unique2 = TENKTUP2.unique2
	 and TENKTUP2.unique1 < 2500
	 order by TENKTUP1.unique1';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 25% of rows from joining table
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	 where TENKTUP1.unique2 = TENKTUP2.unique2
	 and TENKTUP2.unique1 < 2500
	 order by TENKTUP1.unique1';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 10% of rows from joining table
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	 where TENKTUP1.unique2 = TENKTUP2.unique2
	 and TENKTUP2.unique1 < 1000
	 order by TENKTUP1.unique1';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 5% of rows from joining table
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	 where TENKTUP1.unique2 = TENKTUP2.unique2
	 and TENKTUP2.unique1 < 500
	 order by TENKTUP1.unique1';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- 1% of rows from joining table
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	 where TENKTUP1.unique2 = TENKTUP2.unique2
	 and TENKTUP2.unique1 < 100
	 order by TENKTUP1.unique1';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- one row from joining table
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	 where TENKTUP1.unique2 = TENKTUP2.unique2
	 and TENKTUP2.unique1 = 0
	 order by TENKTUP1.unique1';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- Sort avoidance with joins and order by on columns in different tables
--
-- order on joining columns
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	 where TENKTUP1.unique1 = TENKTUP2.unique1
	 order by TENKTUP1.unique1, TENKTUP2.unique1';
close c;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

commit;

-- order on joining columns with qualifications on non-