更新分区表oracle [英] Updating partitioned table oracle

问题描述

我有一个分区表，当我尝试通过动态传递分区名称来循环更新几个选定的分区时，它不起作用.

Hi I have a partitioned table and when I am trying to update taking few selected partition in a loop with passing partition name dynamically, it s not working.

for i in 1..partition_tbl.count Loop

UPDATE cdr_data PARTITION(partition_tbl(i)) cdt
SET A='B'
WHERE
cdt.ab='c'

End Loop;

partition_tbl对象具有我要在其中执行此更新的所有分区.

The partition_tbl object has all the partition in which I want to perform this update.

请向我建议如何进行此操作.

Please suggest me how to proceed here.

预先感谢

推荐答案

您要解决的问题是什么?对循环中的每个分区运行单独的UPDATE语句是没有意义的.如果您真的想更新表中ab = 'c'的每一行，只需发出一条UPDATE语句

What is the problem that you are trying to solve? It doesn't make sense to run separate UPDATE statements against each partition in a loop. If you really want to update every row in the table where ab = 'c', just issue a single UPDATE statement

UPDATE cdr_data cdt
   SET a = 'B'
 WHERE ab = 'c'

可能带有PARALLEL提示，该提示将允许Oracle并行更新多个分区.

potentially with a PARALLEL hint that would allow Oracle to update multiple partitions in parallel.

如果您确实要独立更新每个分区，则根据分区键进行更新将更加有意义.例如，如果您的表具有基于日期的每日分区

If you really, really want to update each partition independently, it would make much more sense to do so based on the partition keys. For example, if your table has daily partitions based on a date

FOR i IN 1 .. <<number of daily partitions>>
LOOP
  UPDATE cdr_data cdt
     SET a = 'B'
   WHERE ab = 'c'
     AND partition_key = <<minimum date>> + i;
END LOOP;

使用partition( <<partition name>> )语法绝对是不得已的选择.如果您真的确定要走这条路，则需要使用动态SQL，在循环中构造SQL语句，然后使用EXECUTE IMMEDIATE或dbms_sql来执行它.

Using the partition( <<partition name>> ) syntax is an absolute last resort. If you're really determined to go down that path, you'd need to use dynamic SQL, constructing the SQL statement in the loop and using EXECUTE IMMEDIATE or dbms_sql to execute it.

这篇关于更新分区表oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！