Oracle / OWB:在运行时指定INSERT的分区

Question

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

我想知道是否可以在运行时为 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)  
);

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

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);

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

由于我们的 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);

这可能吗？如果没有，是否有其他方法可以使用Oracle仓库生成器实现此目的？

Answer 1

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

理想情况下，它会是这么简单：

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;
/

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

添加动态 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;
/

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

---

在运行时指定分区名称的另一种方法是使用系统分区和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;
/

此方法的巨大缺点是每个 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

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