access.sql

derby database source code.good for you.

--    o check space usage for index using SpaceTable VTI
--    o delete every other one less than 1000 - this will make normal post 
--      commit not fire as no page should have all deleted rows (< 1000 avoids
--      possible case where last page only has 1 row).
--    o commit
--    o now reinsert rows into the "holes" which before the fix would cause
--      splits, but now will force reclaim space and reuse existing space in
--      btree.
--    o redo delete, commit and insert several times over
--    o check space usage for index using SpaceTable VTI is same as above

-- set page size to default.
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096');
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.minimumRecordSize', '1');

autocommit off;
commit;
drop table foo;
drop table foo2;

-- create and load a table with values from 1024 down to 1,
create table foo (a int, b char(200), c int);

insert into foo values (1024, 'even', 0);
insert into foo        (select foo.a - 1, 'odd' , 1 from foo);

insert into foo (select foo.a - 2,   foo.b, foo.c from foo);
insert into foo (select foo.a - 4,   foo.b, foo.c from foo);
insert into foo (select foo.a - 8,   foo.b, foo.c from foo);
insert into foo (select foo.a - 16,  foo.b, foo.c from foo);
insert into foo (select foo.a - 32,  foo.b, foo.c from foo);
insert into foo (select foo.a - 64,  foo.b, foo.c from foo);
insert into foo (select foo.a - 128, foo.b, foo.c from foo);
insert into foo (select foo.a - 256, foo.b, foo.c from foo);
insert into foo (select foo.a - 512, foo.b, foo.c from foo);

-- insert into the "holes", but different keys (even2 instead of even)
create table foo2 (a int, b char(200), c int);
insert into foo2 (select * from foo);
delete from foo2 where foo2.c = 1;
delete from foo2 where foo2.a >= 1000;

-- create "packed" index.
create index a on foo (a, b);
commit;

create table spaceused (a int);

-- check space usage on the index
insert into spaceused
select numallocatedpages
from new org.apache.derby.diag.SpaceTable('FOO') t
where conglomeratename = 'A';

-- delete every other row
delete from foo where foo.c = 0 and foo.a < 1000;
-- turn all the deletes into "committed deletes"
commit;
-- wait for post commit on heap deleted rows to finish, so that there is
-- no interference between post commit heap processing, and btree reclamation
-- during split.
CALL WAIT_FOR_POST_COMMIT();

-- reinsert
insert into foo (select * from foo2);
commit;
-- do the above several times
delete from foo where foo.c = 0 and foo.a < 1000;
commit;
-- wait for post commit on heap deleted rows to finish, so that there is
-- no interference between post commit heap processing, and btree reclamation
-- during split.
CALL WAIT_FOR_POST_COMMIT();

insert into foo (select * from foo2);
delete from foo where foo.c = 0 and foo.a < 1000;
commit;
-- wait for post commit on heap deleted rows to finish, so that there is
-- no interference between post commit heap processing, and btree reclamation
-- during split.
CALL WAIT_FOR_POST_COMMIT();

insert into foo (select * from foo2);
delete from foo where foo.c = 0 and foo.a < 1000;
commit;
-- wait for post commit on heap deleted rows to finish, so that there is
-- no interference between post commit heap processing, and btree reclamation
-- during split.
CALL WAIT_FOR_POST_COMMIT();

insert into foo (select * from foo2);
delete from foo where foo.c = 0 and foo.a < 1000;
commit;
-- wait for post commit on heap deleted rows to finish, so that there is
-- no interference between post commit heap processing, and btree reclamation
-- during split.
CALL WAIT_FOR_POST_COMMIT();

insert into foo (select * from foo2);
delete from foo where foo.c = 0 and foo.a < 1000;
commit;
-- wait for post commit on heap deleted rows to finish, so that there is
-- no interference between post commit heap processing, and btree reclamation
-- during split.
CALL WAIT_FOR_POST_COMMIT();


insert into foo (select * from foo2);

commit;

-- check space usage on the index
insert into spaceused
select numallocatedpages
from new org.apache.derby.diag.SpaceTable('FOO') t
where conglomeratename = 'A';

-- should only be 1 distinct row in this table since space used by the
-- index should be the same before and after
select count(distinct a)
from spaceused;

-- cleanup
drop table foo;
drop table foo2;
drop table spaceused;

-- test case for track 3149, improving max on btree optimization
autocommit off;

create table foo (a int, b varchar(500), c int);
insert into foo values (1, PADSTRING('1',500), 1); 
insert into foo values (11, PADSTRING('11',500), 1); 
insert into foo values (12, PADSTRING('12',500), 1); 
insert into foo values (13, PADSTRING('13',500), 1); 
insert into foo values (14, PADSTRING('14',500), 1); 
insert into foo values (15, PADSTRING('15',500), 1); 
insert into foo values (16, PADSTRING('16',500), 1); 
insert into foo values (17, PADSTRING('17',500), 1); 
insert into foo values (18, PADSTRING('18',500), 1); 
insert into foo values (2, PADSTRING('2',500), 1); 
insert into foo values (3, PADSTRING('3',500), 1); 
insert into foo values (4, PADSTRING('4',500), 1); 
insert into foo values (5, PADSTRING('5',500), 1); 
insert into foo values (6, PADSTRING('6',500), 1); 
insert into foo values (7, PADSTRING('7',500), 1); 
insert into foo values (8, PADSTRING('8',500), 1); 
insert into foo values (9, PADSTRING('9',500), 1); 
create index foox on foo (b);

commit;

-- normal max optimization, last row in index is not deleted.
select max(b) from foo;

-- new max optimization, last row in index is deleted but others on page aren't.
delete from foo where a = 9;
select max(b) from foo;

-- new max optimization, last row in index is deleted but others on page aren't.
delete from foo where a = 8;
select max(b) from foo;

-- max optimization does not work - fail over to scan, all rows on last page are
-- deleted.
delete from foo where a > 2;
select max(b) from foo;
commit;

drop table foo;

------------------------------------------------------------------------
-- regression test for bugs 3368, 3370
-- the bugs arose for the edge case where pageReservedSpace = 100
-- before bug 3368 was fixed, a short row insert caused 2 pages to be
-- allocated per short row insert.

drop table a;

call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageReservedSpace', '100');
create table a (a int);
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageReservedSpace', NULL);

insert into a values (1);
select numallocatedpages from new org.apache.derby.diag.SpaceTable('A') as a;

insert into a values (2);
select numallocatedpages from new org.apache.derby.diag.SpaceTable('A') as a;

insert into a values (1);
select numallocatedpages from new org.apache.derby.diag.SpaceTable('A') as a;

insert into a values (2);
select numallocatedpages from new org.apache.derby.diag.SpaceTable('A') as a;

------------------------------------------------------------------------
-- regression test for bug 4595, make sure index used in unique key update
-- even if table has zero rows.
--

call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
maximumdisplaywidth 9000;

-- try delete/update statement compiled against table with 0 rows
drop table foo;
create table foo (a int, b int);
create unique index foox on foo (a);

-- delete against table with 0 rows.
delete from foo where a = 1;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- update against table with 0 rows.
update foo set b = 1 where a = 2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- select against table with 0 rows.
select * from foo where a = 2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- select against table with 0 rows.
select a from foo where a = 2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- select against table with 0 rows.
select a from foo where a = 2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- now insert one row and make sure still same plan.  Previous to 4595 
-- 0 row plan was a table scan and it would not change when 1 row was inserted.
insert into foo values (1, 1);


-- update against table with 1 row.
update foo set b = 2 where a = 1;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- delete against table with 1 row.
delete from foo where a = 1;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- try delete/update statement compiled against table with 1 row.
drop table foo;
create table foo (a int, b int);
insert into foo values (1, 1);
create unique index foox on foo (a);

-- update against table with 1 row.
update foo set b = 2 where a = 1;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- delete against table with 1 row.
delete from foo where a = 1;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- repeat set of 4595 tests against table with primary key, vs. unique index - 
-- there should be no difference in plan shape.

-- try delete/update statement compiled against table with 0 rows
drop table foo;
create table foo (a int not null primary key, b int);

-- delete against table with 0 rows.
delete from foo where a = 1;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- update against table with 0 rows.
update foo set b = 1 where a = 2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- select against table with 0 rows.
select * from foo where a = 2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- select against table with 0 rows.
select a from foo where a = 2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- select against table with 0 rows.
select a from foo where a = 2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- now insert one row and make sure still same plan.  Previous to 4595 
-- 0 row plan was a table scan and it would not change when 1 row was inserted.
insert into foo values (1, 1);


-- update against table with 1 row.
update foo set b = 2 where a = 1;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- delete against table with 1 row.
delete from foo where a = 1;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- try delete/update statement compiled against table with 1 row.
drop table foo;
create table foo (a int not null primary key, b int);
insert into foo values (1, 1);

-- update against table with 1 row.
update foo set b = 2 where a = 1;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- delete against table with 1 row.
delete from foo where a = 1;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- select against table with 1 row.
select * from foo where a = 2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- select against table with 1 row.
select a from foo where a = 2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- select against table with 1 row.
select a from foo where a = 2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- repeat set of 4595 tests against table with non-unique index with no
-- statistics.

-- there should be no difference in plan shape.

-- try delete/update statement compiled against table with 0 rows
drop table foo;
create table foo (a int, b int);
create index foox on foo (a);

-- delete against table with 0 rows.
delete from foo where a = 1;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- update against table with 0 rows.
update foo set b = 1 where a = 2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- select against table with 0 rows.
select * from foo where a = 2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- select against table with 0 rows.
select a from foo where a = 2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- select against table with 0 rows.
select a from foo where a = 2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- now insert one row and make sure still same plan.  Previous to 4595 
-- 0 row plan was a table scan and it would not change when 1 row was inserted.
insert into foo values (1, 1);

-- update against table with 1 row.
update foo set b = 2 where a = 1;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- delete against table with 1 row.
delete from foo where a = 1;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- try delete/update statement compiled against table with 1 row.
drop table foo;
create table foo (a int, b int);
create index foox on foo (a);
insert into foo values (1, 1);

-- update against table with 1 row.
update foo set b = 2 where a = 1;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- delete against table with 1 row.
delete from foo where a = 1;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- select against table with 1 row.
select * from foo where a = 2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- select against table with 1 row.
select a from foo where a = 2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- select against table with 1 row.
select a from foo where a = 2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

------------------------------------------------------------------------
-- simple regression test for qualifier work.
------------------------------------------------------------------------
drop table foo;
commit;

create table foo (a int, b int, c int);
insert into foo values (1, 10, 100);
insert into foo values (2, 20, 200);
insert into foo values (3, 30, 300);

-- should return no rows
select a, b, c from foo where a = 1 and b = 20;

-- should return one row
select a, b, c from foo where a = 3 and b = 30;
select a, b, c from foo where a = 3 or c = 40;

-- should return 2 rows
select a, b, c from foo where a = 1 or b = 20;
select a, b, c from foo where a = 1 or a = 3;

DROP FUNCTION PADSTRING;
DROP PROCEDURE WAIT_FOR_POST_COMMIT;
exit;