Periodic Data in SQL

Hello, I have a problem designing a database table with indexes, which should contain a short time history of periodic data (short time means 2-4 weeks). The enity table is designed as follows: create table my_object (id integer not null, -- ID of object descr varchar(255), -- Description of object -- ... primary key (id) ); This table is populated with 3000 objects. The corresponding history table looks like: create table my_history (time integer, -- unix time stamp id integer, -- ID of the object, referencing my_object(id) attr smallint -- Some kind of data __ ... ); In this history table the combination of time and id is unique. The history table is filled by an automatic process with measured or calculated data with 3000 data sets per minute (for each object there is one dataset per minute). Once a day another process is running to delete "old" datasets from the history table to prevent the database from growing too much. This process executes a SQL statement like: delete from my_history where time < XY;where XY is a unix time-stamp. Some application programs query the history table like select * from my_history where time = XY; or select * from my_history where id = Z and time > XY order by id,time desc; For this queries the following indexes would increase performance: create index my_history_1 on my_history (time, id); create index my_history_2 on my_history (id,time desc); One problem is: If I create just the history table without any index (or constraint) the insertion of data to the table is very fast (about 200000 data sets per minute on my system). If I create the index my_history_1 the insertion is also fast (about 150000 data sets per minute). If I create the second index "my_history_2" instead or in addition to "my_history_2" the insertion is fast at the beginning (when the table is small) and slows down dramatically to values like 500 data sets per minute, when the table becomes bigger (200000 data sets). I think the problem is, that the data sets, which are inserted, are in the order of index "my_history_1", but not in order of index "my_history_2". So there is a lot of rebalancing to be done for index "my_history_2". Another problem is, that even if I only create the first index "my_history_1" the insertion slows down dramatically, when the delete process starts working. It slows down in a way, that insertion of 3000 new data sets and deletetion of 3000 old data sets needs more than one minute of real time, also due to the rebalancing of the index, I suppose. Has anybody an idea to solve these problems in Informix-SQL (maybe by using table fragmentation, or by splitting the table my_history into more than one table, or ...) Thanks in advance, Please reply ONLY to this newsgroup AND to: werner.scholtes@heuboe.de