Delete and insert into table and query real time

Hi Sridhar1

Instead of delete/insert - I would explore two ways:

1. Keep two private tables of equal structure - and a public view on top of it that projects the rows of the one of the two tables that is currently not under maintenance. This can be done by searching the highest last_updated timestamp in each table (empty or full), then taking the lower (or only existing) value from the two tables, and then using a UNION SELECT of the two tables, filtered by that timestamp you got before. Then, all you have to do is maintain the two tables in alteration, and use TRUNCATE TABLE instead of DELETE FROM. This approach is used in the Pipeline Project in HP IT.

2. Consider using the optmised MERGE for the tables: Make sure you have a primary key or unique constraint on the target table; create a staging table of equal structure. The MERGE command must have the unique or primary key column in the ON clause - and (even if it goes against your instincts) *all* columns of the table, including the column from the ON clause,  in the SET clause of the WHEN MATCHED THEN UPDATE branch - and, obviously, also in the VALUES() clause of the WHEN NOT MATCHED THEN INSERT branch.

And run the SELECT PURGE_TABLE() command just after the MERGE command.

Good luck -

Marco