Greetings again,

Late last week I posted messages discussing how I had a problem with a child
table failing to return the first result of a select * from it, with the
problem potentially linked to indexes, and a cascade delete.

I have since modified the table to be self sufficient (it has it's own pk,
not a composite pk as before), and it doesn't have any foreign indexes on
it.

For every 1 record in the parent, there may be 0-x corresponding records in
the child. When a parent record is deleted, a before delete trigger of the
parent deletes the appropriate records from the child.

The child looks like this :

CREATE TABLE ST_CONTAINS(
ST_ID INTEGER NOT NULL,
SEQ INTEGER NOT NULL,
DICT_ID INTEGER NOT NULL,
constraint pk_st_contains primary key (ST_ID));

create index Ist_contains_SEQ on st_contains(seq);
create index Ist_contains_DICT_ID on st_contains(dict_id);


Now, the situation is still the same. I can sometimes read in the first
record of child, but on certain systems after a certain number of records it
fails to return. Even worst is the following :

My application is constantly adding records to the end, and deleting records
from the front (to keep the database from overflowing available disk space).
Unfortunately, even though the parents are being deleted, the childs are not
releasing the space they contain to be reused. Thus the database continues
to grow until it wanders off the end of the drive and corrupts.

Any assistance is greatly appreciated.


Regards,


Bill Morrison