Маленькие сюрпризы большого Oracle

Oracle умеет много (и с каждой версией все больше и больше). Зачастую, многие разработчики не используют некоторые нововведения очень долго. Иногда это обусловлено требованиями обратной совместимости, но чаще, банальным незнанием. Вот так-же и я, работая уже с 11 и 12 версиями Oracle, до вчерашнего дня, ни разу не использовал конструкцию insert all. Но вчера я подумал «на дворе уже 21-ый век, зачем пользоваться временными таблицами, если можно выполнять одновременную вставку в несколько таблиц?». Тут-то и начались приключения.

Суть задачи, сводится к тому, что в разрабатываемой нами системе, объекты раскладываются по нескольким таблицам (сам объект в одной таблице, его свойства — в нескольких других). Несколько упростив, схему хранения данных можно представить следующим образом:

Вопрос о том, почему мы храним данные именно так, а не иначе, не является предметом обсуждения данной статьи. Важно то, что мне потребовалось создавать наборы однотипных объектов (по 10-100 штук), по возможности минимизируя накладные расходы на их создание. Запрос, формирующий исходные данные, для создания этих объектов, достаточно ресурсоемкий и выполнять его несколько раз не хотелось. Естественным образом, были использованы временные таблицы, для промежуточного сохранения результата «тяжелого» запроса и последующего формирования объектов и их свойств. Именно от них я и планировал отказаться, раскладывая результаты первоначального запроса, непосредственно в несколько таблиц, используя insert all. Здесь меня подстерегал первый сюрприз.

SQL> create sequence test_object_seq;

Sequence created.

SQL> create table test_object (
  2      id number not null,
  3      name varchar2(30)
  4  );

Table created.

SQL> create unique index test_object_ipk on test_object(id);

Index created.

SQL> alter table test_object add
  2    constraint pk_test_object primary key(id);

Table altered.

SQL> create sequence test_prop_type_seq;

Sequence created.

SQL> create table test_prop_type (
  2      id number not null,
  3      name varchar2(30)
  4  );

Table created.

SQL> create unique index test_prop_type_ipk on test_prop_type(id);

Index created.

SQL> alter table test_prop_type add
  2    constraint pk_test_prop_type primary key(id);

Table altered.

SQL> create sequence test_property_seq;

Sequence created.

SQL> create table test_property (
  2      id number not null,
  3      obj_id number not null,
  4      prop_id number not null,
  5      value varchar2(30)
  6  );

Table created.

SQL> create unique index test_property_ipk on test_property(id);

Index created.

SQL> create index test_property_obj_ifk on test_property(obj_id);

Index created.

SQL> create index test_property_prop_ifk on test_property(prop_id);

Index created.

SQL> alter table test_property add
  2    constraint pk_test_property primary key(id);

Table altered.

SQL> alter table test_property add
  2    constraint fk_test_property_obj foreign key (obj_id)
  3      references test_object(id);

Table altered.

SQL> alter table test_property add
  2    constraint fk_test_property_prop foreign key (prop_id)
  3      references test_prop_type(id);

Table altered.

Insert into TEST_PROP_TYPE
   (ID, NAME)
 Values
   (1, 'Property 1');
Insert into TEST_PROP_TYPE
   (ID, NAME)
 Values
   (2, 'Property 2');
COMMIT;

Теперь, попробуем вставить данные:

SQL> insert all
  2    into test_object(id, name)
  3    values (obj_id, obj_name)
  4    into test_property(id, obj_id, prop_id, value)
  5    values (test_property_seq.nextval, obj_id, 1, value_1)
  6    into test_property(id, obj_id, prop_id, value)
  7    values (test_property_seq.nextval, obj_id, 2, value_2)
  8  select test_object_seq.nextval obj_id, 'some object' obj_name,
  9         'value 1' value_1, 'value 2' value_2
 10  from   dual;

select test_object_seq.nextval obj_id, 'some object' obj_name,
                       *
ERROR at line 8:
ORA-02287: sequence number not allowed here

Довольно неприятно. Oracle считает, что использовать здесь sequence нельзя. Но это не самое печальное. Допустим, мы смогли обойти эту проблему (например создается всего один объект, id которого мы можем сгенерировать заранее). В этом случае, мы получаем гораздо более забавный результат:

SQL> insert all
  2    into test_object(id, name)
  3    values (obj_id, obj_name)
  4    into test_property(id, obj_id, prop_id, value)
  5    values (test_property_seq.nextval, obj_id, 1, value_1)
  6    into test_property(id, obj_id, prop_id, value)
  7    values (test_property_seq.nextval, obj_id, 2, value_2)
  8  select 1 obj_id, 'some object' obj_name,
  9         'value 1' value_1, 'value 2' value_2
 10  from   dual;
insert all
*
ERROR at line 1:
ORA-00001: unique constraint (EM_MON.PK_TEST_PROPERTY) violated

После некоторого размышления, становится понятно, что происходит. На самом деле, id генерируются для строк выборки, после чего, эти строки раскладываются по таблицам, указанным в insert all. Но мы хотим разместить одну и ту же строку (различные ее поля) в одной таблице несколько раз! Тут-то нас и встречает ограничение уникальности.

В общем, временные таблицы пришлось вернуть на место.

Автор: GlukKazan

Источник