Refresh Projection locking escalation


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?




  • Options


     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,
    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';
  • Options

    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

  • Options

      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. 





  • Options

    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

Leave a Comment

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