Dropping and Recreating the staging table

Joseph

We have the following situation and i would like to know what is the best approach to take.

to enhance our backend process we are removing any kind of delete statement that is required to reload data for previous dates.
we decided to use the swap_partitions_between_tables and our approach is creating a staging table for each table to load the data into it and swap with the original table.

the process will require dropping and recreating the staging table on each load and not truncating the staging table.
the reason why we are doing that, in order to always have an updated staging table incase the main table structure was changed,...

anyway, I need to know if there are any concerns for always dropping and recreating a table to load data into it and swap with the main table.
we might have 20 to 1000 tables...

Bryan_H

Hi, there should be no issue with dropping and re-creating tables. I am aware of several customers that create staging tables and swap partitions as you describe. However, it is strongly recommended to upgrade to version 9.3.1 or later to improve catalog memory usage when performing many daily drop and create operations.

sherrijackson

l can't think of any concerns with dropping and re-creating tables in the described case. There are multiple cases on importing a somewhat larger datasets from a web service, for example at domyhomework4me.onl.

mosheg

Long long ago we preferred using TRUNCATE TABLE because high frequent DROP/CREATE increased the catalog size at the time.
In current versions, many DROP/CREATE activities do not increase it any more.