Oracle / OWB：在运行时为 INSERT 指定分区

Oracle / OWB: Specifying the partition for INSERT at runtime

提问by Frank Schmitt

(Background: we're running a data warehouse built with Oracle Warehouse Builder. Recently, we started getting a lot of "ORA-02049: distributed transaction timeout while waiting for lock" errors. The reason for this is that we're running several ETL jobs in parallel, and each of these jobs does a INSERT /*+APPEND PARALLEL*/into our staging table. This staging table is partitioned by the source system ID. )

（背景：我们正在运行一个使用 Oracle Warehouse Builder 构建的数据仓库。最近，我们开始收到很多“ORA-02049：等待锁定时分布式事务超时”的错误。原因是我们正在运行几个并行的 ETL 作业，这些作业中的每一个都会对INSERT /*+APPEND PARALLEL*/我们的临时表执行一个操作。该临时表按源系统 ID 进行分区。）

I'd like to know if it is possible to specify the partition key for an INSERT at runtime. Suppose I have a table

我想知道是否可以在运行时为 INSERT 指定分区键。假设我有一张桌子

create table tmp_loading_table (
  etl_source_system_fk number not null enable,
  object_id number not null enable,
  object_name varchar2(30) not null enable
)
  PARTITION BY LIST ("ETL_SOURCE_SYSTEM_FK") 
 (PARTITION "ESS1"  VALUES (1), 
 PARTITION "ESS2"  VALUES (2)  
);

then I can insert into a specific partition using

然后我可以使用插入到特定的分区

insert  /*+ APPEND PARALLEL("TMP_LOADING_TABLE") */     
into tmp_loading_table partition(ESS1) (
  etl_source_system_fk, object_id, object_name)
(select 1 etl_source_system_fk, object_id, object_name from user_objects);

but this requires me to hard-code the partition name.

但这需要我对分区名称进行硬编码。

Since our OWB mappings are generic (they get the source system ID as a parameter), I'd like to provide the partition name at runtime, something like

由于我们的 OWB 映射是通用的（它们将源系统 ID 作为参数），我想在运行时提供分区名称，例如

insert  /*+ APPEND PARALLEL("TMP_LOADING_TABLE") */     
into tmp_loading_table partition(:partition_name) (
  etl_source_system_fk, object_id, object_name)
(select 1 etl_source_system_fk, object_id, object_name from user_objects);

Is this possible? If not, is there another method to achieve this with Oracle Warehouse Builder?

这可能吗？如果没有，是否有另一种方法可以使用 Oracle Warehouse Builder 实现此目的？

采纳答案by Jon Heller

The PARTITION FORsyntax and dynamic SQL can help.

在PARTITION FOR语法和动态SQL可以提供帮助。

Ideally it would be this simple:

理想情况下，它会很简单：

declare
    v_partition_value number := 1;
begin
    insert  /*+ APPEND PARALLEL("TMP_LOADING_TABLE") */     
    into tmp_loading_table partition for (v_partition_value) (
      etl_source_system_fk, object_id, object_name)
    (select 1 etl_source_system_fk, object_id, object_name from user_objects);
end;
/

Unfortunately the above code fails with ORA-14108: illegal partition-extended table name syntax. Which is strange since that seems like an obvious use for that syntax.

不幸的是，上面的代码失败了ORA-14108: illegal partition-extended table name syntax。这很奇怪，因为这似乎是该语法的明显用途。

Adding dynamic SQL removes the error.

添加动态 SQL 可消除错误。

declare
    v_partition_value number := 1;
begin
    execute immediate '
    insert  /*+ APPEND PARALLEL("TMP_LOADING_TABLE") */     
    into tmp_loading_table partition for ('||v_partition_value||') (
      etl_source_system_fk, object_id, object_name)
    (select 1 etl_source_system_fk, object_id, object_name from user_objects)';
end;
/

I'm not familiar with Oracle Warehouse Builder and do not know if this solution will work in that environment. And I assume that in a data warehouse SQL injection is not a concern.

我不熟悉 Oracle Warehouse Builder，不知道该解决方案是否适用于该环境。我假设在数据仓库中 SQL 注入不是问题。

---

---

Another way to specify the partition name at runtime is with system partitioningand DATAOBJ_TO_PARTITION .

在运行时指定分区名称的另一种方法是使用系统分区和DATAOBJ_TO_PARTITION。

create table tmp_loading_table (
  etl_source_system_fk number not null enable,
  object_id number not null enable,
  object_name varchar2(30) not null enable
)
PARTITION BY SYSTEM
(
  PARTITION ess1,
  PARTITION ess2
);

declare
    v_object_id number;
begin
    select object_id
    into v_object_id
    from dba_objects
    where object_name = 'TMP_LOADING_TABLE'
        and subobject_name = 'ESS1';

    insert into tmp_loading_table
    partition (dataobj_to_partition (tmp_loading_table, v_object_id))
    values (1, 2, 'A');
end;
/

The huge disadvantage of this method is that every DML must reference the partition:

这种方法的巨大缺点是每个 DML 都必须引用分区：

insert into tmp_loading_table
values (1, 2, 'A');

ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by the System method

I've never heard of anyone using this feature. And in my experience Oracle data cartridge is buggy. How's that dynamic SQL looking now? :)

我从未听说过有人使用此功能。根据我的经验，Oracle 数据盒式磁带有问题。动态 SQL 现在看起来如何？:)