Delete and insert into table and query real time

Sreedhar1

I have requriement of doing full refresh of data a lookup table which always needs to be queryiable real time by customer. Currently i am doing inserting data using staging table and then

 

delete from temp1;insert into temp1 select * from temp1_stage;commit; However when query is done while the delete /insert operation is going on the query response is slow. Is there any alternate mechanism to do the operation without user query  getting slow response?

marcothesane

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