Most efficient deletes for Online 5.0 ?
Posted in 1993
Hi, Q. How to improve the delete rate on Online 5.0 ? Using ESQL/C on HP/UX server Online 5.0 I am using cursor insertions to load tables with data from our input network, by buffering the data into input lists I get about 10X speed up over single insertions. The data is keyed upon DATETIME and is retained for a number of days. When deletions occur the earliest day is deleted, to avoid running out of locks a hold cursor is opened and the rowid is fetched and deleted (as per delete current of). Yet the deletion rate is very slow with respect to the insertion rate, I am under the impression that Online is shuffling the indexes, which is ironic as they are probably going to be deleted in the next moment. One method is to keep each days data in its own table, use synonyms to point to the current table and drop the old table. This sounds good but also makes the queries harder as they need to know about the 14 or so synonyms pointing to the separate days. Also the synonym shuffle is problematic as the today_table & yesterday_table need to be moved on at mid-night causing hiccups on queries that are still open on the 'old' today_table & yesterday_table. Online does not let me treat a set off tables through a view. I cannot allow any hiccups to the system due to the deletes as the system is being used 24hours a day. So can anyone help me improve my delete rate (ie get it to match the incoming - without adversely inpacting the incoming rate). Any help or comments would be appreciated. George Long