I have a table with 3.5Billion rows. I have created a projection and I need to manually refresh. Will this lead to a table lock? Is there a way to manage the resources of the refresh?
During the refresh of a new projection an (X) Exclusive lock will be aquired on the projection, but no exclusive lock will be put on the anchor table, you can still query the table.
To get a better view of the lock process use the v_monitor.locks and v_monitor.lock_usage tables to monitor them.
Also you can monitor the active refresh operations
select node_name, projection_name, refresh_status, refresh_start, refresh_duration_sec, runtime_priority from projection_refreshes where is_executing = 't';
See refresh operations done on a anchor table
select node_name, projection_name, refresh_status, refresh_start from projection_refreshes where anchor_table_name = 'table_name';
Is there a way to limit the resources of the projection refresh??? Put it in a pool or so?
I assume that I can also insert on the table
Thank you so much
You can alter the refresh pool MAXMEMORYSIZE(how much memory can the pool borrow from the GENERAL POLL - default value is unlimited).
You can give it fixed MEMORYSIZE value if you want some prealocated memory for this pool(not recomanded as it will be unused resources while the refresh is not in use).
But normally when you do refreshes on initial loads they must be scheduled on off hours when only mainatainance sessions are up.
The thing is that at the moment I do not have the option to have a down time. I will limit the resources on the pool and the change them back to the original