Drop staging table

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






  • Options


     Hi ,

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



  • Options

    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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file