This content has been marked as final. Show 4 replies
I recently inherited a db which is not in good health and code is written against views in views. I cannot make schema/table structure changes at this point, so after setting/tuning indexes, I am now created temp tables for a big process's intermediate step.
The process runs only few times a month but needs to be run by applications' end user by clicking button within application.
I wanted to have this temp table etc created/refreshed every time button is pressed by end user so that the the final query running against a particular view gets the latest data.
I will explore the possibility or materialized view little later as I need to have this process going.
Then what you need is a Global Temporary Table. The definiition of the table is fixed, that is, it is created in the database just like a regular table, but the data in it only exists for the life of the transaction (ON COMMIT DELETE ROWS) or the life of the database session (ON COMMIT PRESERVE ROWS).
You would create the GTT once, then use a stored procedure to populate the table and do whatever you need to do as part of "a big process's intermediate step"