Oracle / OWB: указание раздела для INSERT во время выполнения

Question

(Справочная информация: мы работаем с хранилищем данных, созданным с помощью Oracle Warehouse Builder. Недавно мы начали получать много ошибок "ORA-02049: время ожидания распределенной транзакции при ожидании блокировки". Причина этого в том, что мы запускаем несколько Задания ETL параллельно, и каждое из этих заданий выполняет INSERT /*+APPEND PARALLEL*/ в наш промежуточный стол. Эта промежуточная таблица разделена по идентификатору исходной системы.)

Я хотел бы знать, возможно ли указать ключ раздела для 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-сопоставления являются общими (они получают идентификатор исходной системы в качестве параметра), я хотел бы предоставить имя раздела во время выполнения, что-то вроде

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 Warehouse Builder?

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

Я никогда не слышал, чтобы кто-нибудь использовал эту функцию. И по моему опыту Oracle данные картридж глючит. Как сейчас выглядит этот динамический SQL?:)