Drop staging table

nobita

Hi All,

 

In my legacy system, we use oracle and i need to support for operational reporting.

i am creating tables which will be dropped after used.

 

Note : i dont need to create permanent table as requiment always change in daily basis. (kind of ad-hoc reporting) but we are suppoting lots of report like these.

 

Is it best practice in vertica to create then drop table everyday ?

 

Please advise

 

 

 

 

Adrian_Oprea_1

 

 Hi ,

Do this table have to persist between sessions ? If not try using temp table. I use alot of Temptables, especialy local temp table. 

 

 

DerekW

I think the approach in Vertica will be similar to what you did in Oracle. This is how we do it:

 

We use local temporary tables (which will be dropped once your session is closed) to work our way to a final persistent table, which we use for operational reporting. In case of ad-hoc reporting, which don't need to be updated all the time, I prefer to store these tables in a separate schema.

 

I normally have script running that checks the creation time of tables in this ad-hoc schema, and if the table is older than X days it will drop the table, but we always make sure that we have the code on a separate place (so if needed the table can be recreated). A similar script cleans up the report in the reporting server.

 

Hope this helps.