selectivity.sql

derby database source code.good for you.

get cursor c as 'select t1.id from t1, t2 where t1.two=t2.two and t1.twenty=t2.twenty and t1.hundred=t2.hundred';
close c;
values runtimestatistics()->getEstimatedRowCount();

-- now muck around with the order of the predicates.
--2,100,20
get cursor c as 'select t1.id from t1, t2 where t1.two=t2.two and t1.hundred=t2.hundred and t1.twenty=t2.twenty ';
close c;
values runtimestatistics()->getEstimatedRowCount();

--100,20,2
get cursor c as 'select t1.id from t1, t2 where t1.hundred=t2.hundred and t1.twenty=t2.twenty and t1.two=t2.two';
close c;
values runtimestatistics()->getEstimatedRowCount();

--100,2,20
get cursor c as 'select t1.id from t1, t2 where t1.hundred=t2.hundred and t1.two=t2.two and t1.twenty=t2.twenty ';
close c;
values runtimestatistics()->getEstimatedRowCount();

get cursor c as 'select t1.id from t1, t2 where t1.twenty=t2.twenty  and t1.hundred=t2.hundred and t1.two=t2.two ';
close c;
values runtimestatistics()->getEstimatedRowCount();

get cursor c as 'select t1.id from t1, t2 where t1.twenty=t2.twenty  and t1.two=t2.two and t1.hundred=t2.hundred';
close c;
values runtimestatistics()->getEstimatedRowCount();

--Beetle 4321
-- check what happens when we need to remove non-continguous predicates
create table complex (id int generated always as identity,
					   two int, twenty int, hundred int, a int, b int);
insert into complex (two, twenty, hundred, a, b)
	   select two.x, twenty.x, hundred.x, two.x, twenty.x from two, twenty, hundred;
create index complexind on complex(two, twenty, hundred, a, b);

-- since the statistics (rowEstimates) are not precise
-- we force them to be updated for some queries.
select count(*) from complex;
select count(*) from template;

get cursor c as 'select t1.two from complex t1, template t2 where t1.two = 1 and t1.hundred = 2 and t1.a = 2 and t1.b = 2';
close c;
values runtimestatistics()->getEstimatedRowCount();

drop table two;
drop table ten;
drop table twenty;
drop table hundred;
drop table template;
drop table test;
drop table rts_table;
drop table t1;
drop table t2;
drop table t3;
drop table scratch_table;
drop table complex;
drop view showstats;
-- basic tests for update statistics; make sure that 
-- statistics with correct values are created and dropped
-- and such.

create view showstats as
select cast (conglomeratename as varchar(20)) indexname, 
	   cast (statistics->toString() as varchar(40)) stats,
	   creationtimestamp createtime, 
	   colcount ncols
from sys.sysstatistics, sys.sysconglomerates 
where conglomerateid = referenceid;

-- first on int, multi-column
create table t1 (c1 int generated always as identity, c2 int, c3 int);

insert into t1 values (default, 1, 1);
insert into t1 values (default, 1, 1);
insert into t1 values (default, 1, 2);
insert into t1 values (default, 1, 2);

insert into t1 values (default, 2, 1);
insert into t1 values (default, 2, 1);
insert into t1 values (default, 2, 2);
insert into t1 values (default, 2, 2);

insert into t1 values (default, 3, 1);
insert into t1 values (default, 3, 1);
insert into t1 values (default, 3, 2);
insert into t1 values (default, 3, 2);

insert into t1 values (default, 4, 1);
insert into t1 values (default, 4, 1);
insert into t1 values (default, 4, 2);
insert into t1 values (default, 4, 2);

-- create index should automatically create stats.
create index t1_c1c2 on t1 (c1, c2);
select * from showstats order by indexname, stats, createtime, ncols;

-- index dropped stats should be dropped.
drop index t1_c1c2;
select * from showstats order by indexname, stats, createtime, ncols;


-- second part of the test.
-- check a few extra types.

create table t2
(
	i int not null,
	vc varchar(32) not null, 
	dt date, 
	ch char(20), 
	constraint pk primary key (i, vc)
);

create index t2_ch_dt on t2(ch, dt);
create index t2_dt_vc on t2(dt,vc);

-- do normal inserts. 
insert into t2 values (1, 'one', '2001-01-01', 'one');
insert into t2 values (2, 'two', '2001-01-02', 'two');
insert into t2 values (3, 'three', '2001-01-03', 'three');

insert into t2 values (1, 'two', '2001-01-02', 'one');
insert into t2 values (1, 'three', '2001-01-03', 'one');
insert into t2 values (2, 'one', '2001-01-01', 'two');

select * from showstats order by indexname, stats, createtime, ncols;

-- do another insert then just updstat for whole table.
insert into t2 values (2, 'three', '2001-01-03', 'two');

update statistics for table t2;

-- make sure that stats are correct.

select * from showstats where indexname = 'T2_CH_DT' order by indexname, stats, createtime, ncols;
select count(*) from (select distinct ch from t2) t;
select count(*) from (select distinct ch,dt from t2) t;

select * from showstats where indexname = 'T2_DT_VC' order by indexname, stats, createtime, ncols;
select count(*) from (select distinct dt from t2) t;
select count(*) from (select distinct dt,vc from t2) t;

select stats, ncols from showstats where indexname not like 'T2%' order by stats, ncols;

-- delete everything from t2, do bulkinsert see what happens.
delete from t2;
-- no material impact on stats.

-- bulk insert; all indexes should have stats.
insert into t2 properties insertMode=bulkInsert values (2, 'one', '2001-01-01', 'two');

select * from showstats where indexname like 'T2%' order by indexname, stats, createtime, ncols;

-- now try bulk insert replace.
insert into t2 properties insertMode=replace values (2, 'one', '2001-01-01', 'two'), (1, 'one', '2001-01-01', 'two');

select * from showstats where indexname like 'T2%' order by indexname, stats, createtime, ncols;

drop table t2;


-- various alter table operations to ensure correctness.
-- 1. add and drop constraint.
create table t3 (x int not null generated always as identity, y int not null, z int);
insert into t3 (y,z) values (1,1),(1,2),(1,3),(1,null),(2,1),(2,2),(2,3),(2,null);

select * from showstats order by indexname, stats, createtime, ncols;
-- first alter table to add primary key;
alter table t3 add constraint pk_t3 primary key (x,y);

select stats, ncols from showstats order by stats, ncols;

-- now drop the constraint
alter table t3 drop constraint pk_t3;

select * from showstats order by indexname, stats, createtime, ncols;

-- try compress with tons of rows. you can never tell
-- what a few extra pages can do :)
insert into t3 (y,z) select y,z from t3;
insert into t3 (y,z) select y,z from t3;
insert into t3 (y,z) select y,z from t3;
insert into t3 (y,z) select y,z from t3;
insert into t3 (y,z) select y,z from t3;
insert into t3 (y,z) select y,z from t3;
insert into t3 (y,z) select y,z from t3;
insert into t3 (y,z) select y,z from t3;
insert into t3 (y,z) select y,z from t3;

select count(*) from t3;

create index t3_xy on t3(x,y);
select * from showstats order by indexname, stats, createtime, ncols;

delete from t3 where z is null;
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'T3', 0);

select * from showstats order by indexname, stats, createtime, ncols; -- all should be hunky dory.

drop table t3;

create table t4 (x int, y int, z int);
insert into t4 values (1,1,1);
insert into t4 values (1,2,1);
insert into t4 values (1,1,2);

create index t4_x on t4(x);
create index t4_xy on t4(x,y);
create index t4_yz on t4(y,z);

select * from showstats order by indexname, stats, createtime, ncols;

-- if I drop column x, then stats for t4_x should get dropped
-- index t4_xy should get rebuilt to only be on y. so one of the
-- stats should be recreated. and t4_yz shouldn remain in its
-- entirety.

alter table t4 drop column x;
select * from showstats order by indexname, stats, createtime, ncols;
drop table t4;

-- SPS tests make sure drop/update statistics statements
-- get written to disk correctly.
create table t5 (x int, y int);
insert into t5 values (1,1), (1,2);
create index t5_y on t5(y);

insert into t5 values (2,1);
select * from showstats order by indexname, stats, createtime, ncols;

update statistics for table t5;
select * from showstats order by indexname, stats, createtime, ncols;

create index t5_x on t5(x);
select * from showstats order by indexname, stats, createtime, ncols;

drop statistics for index t5_x;

-- t5_y should be there.
select * from showstats order by indexname, stats, createtime, ncols;

drop table t5;

create table t6 (i int generated always as identity, j varchar(10));
create index t6_i on t6(i);
create index t6_j on t6(j);
create index t6_ji on t6(j,i);

insert into t6 values (default, 'a');
insert into t6 values (default, 'b');
insert into t6 values (default, 'c');
insert into t6 values (default, 'd');
insert into t6 values (default, 'e');
insert into t6 values (default, 'f');
insert into t6 values (default, 'g');
insert into t6 values (default, 'h');

insert into t6 values (default, 'a');
insert into t6 values (default, 'b');
insert into t6 values (default, 'c');
insert into t6 values (default, 'd');
insert into t6 values (default, 'e');
insert into t6 values (default, 'f');
insert into t6 values (default, 'g');
insert into t6 values (default, 'h');
insert into t6 values (default, 'i');

insert into t6 values (default, 'a');
insert into t6 values (default, 'b');
insert into t6 values (default, 'c');
insert into t6 values (default, 'd');
insert into t6 values (default, 'e');
insert into t6 values (default, 'f');
insert into t6 values (default, 'g');
insert into t6 values (default, 'h');

update statistics for index t6_j;
update statistics for table t6;

select * from showstats order by indexname, stats, createtime, ncols;
delete from t6;

insert into t6 values (default, 'a');
insert into t6 values (default, 'a');
insert into t6 values (default, 'a');
insert into t6 values (default, 'a');
insert into t6 values (default, 'a');
insert into t6 values (default, 'a');
insert into t6 values (default, 'a');
insert into t6 values (default, 'a');
insert into t6 values (default, 'a');
insert into t6 values (default, 'a');
insert into t6 values (default, 'a');
insert into t6 values (default, 'a');
insert into t6 values (default, 'a');
insert into t6 values (default, 'a');
insert into t6 values (default, 'a');
insert into t6 values (default, 'a');

-- make the 17th row the same as the 16th;
-- make sure we switch to the next group
-- fetch we handle the case correctly.

insert into t6 values (default, 'a');

update statistics for table t6;
select * from showstats order by indexname, stats, createtime, ncols;

-- will be table with no rows.
create table et (x int, y int);
create index etx on et(x);
create index ety on et(y);

update statistics for index etx;
update statistics for table et;

select * from showstats where indexname like 'ET%' order by indexname, stats, createtime, ncols;
drop table et;

-- tests for nulls.
create table null_table (x int, y varchar(2));
create index nt_x on null_table(x desc);

insert into null_table values (1,'a');
insert into null_table values (2,'b');
insert into null_table values (3,'c');

insert into null_table values (null,'a');
insert into null_table values (null,'b');
insert into null_table values (null,'c');

insert into null_table values (null,'a');
insert into null_table values (null,'b');
insert into null_table values (null,'c');

update statistics for table null_table;

select * from showstats where indexname = 'NT_X' order by indexname, stats, createtime, ncols;

-- try composite null keys (1,null) is unique from (1,null)
-- as is (null,1) from (null,1)
drop index nt_x;

create index nt_yx on null_table(y,x);

-- the first key y has 3 unique values.
-- the second key y,x has 9 unique values because of nulls.

select * from showstats where indexname = 'NT_YX' order by indexname, stats, createtime, ncols;

-- keyword autoincrement is no more supported in both Cloudscape and DB2 mode. Instead need to use generated always as identity
create table autoinKeywordNotAllowed (x int default autoincrement, dc int);
-- Negative test for create statement in cloudscape mode. CREATE STATEMENT is no more supported in both cloudscape and DB2 mode.
create statement s1 as values 1,2;