Lock mode and concurrency (Table/Partition?) - How is possible to run Copy and Update concurrently o

Vivian_RossiVivian_Rossi Employee, Registered User
How can I run COPY and UPDATE concurrently on same table but locking different partitions ? I have a fact table with granular/detailed data, big data, partitioned by date. Every day I load new data creating a new partition. Simultaneously I need to run an UPDATE on same table but for a previous day partition, different than the one used by COPY. Lock mode for UPDATE is 'X' and COPY is 'I', so no concurrency is allowed. Is it possible to lock the specific partition instead of all table? Any workaround? Thanks in advance.

Comments

  • Sharon_CutterSharon_Cutter Registered User
    You could load the new partition into a different table, and use MOVE_PARTITIONS_TO_TABLE() to move it into the real table. That would give you at least some opportunity to execute both operations at the same time. --Sharon

Leave a Comment

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