Отправляет email-рассылки с помощью сервиса Sendsay

Открыто о СУБД Oracle на русском

Рассылка закрыта

При закрытии подписчики были переданы в рассылку "СУБД Oracle "с нуля"" на которую и рекомендуем вам подписаться.

Вы можете найти рассылки сходной тематики в Каталоге рассылок.

←   Сентябрь 2003   →
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Автор
Сергей

Статистика

10.773 подписчиков
-8 за неделю
←   Все выпуски   →

Открыто о СУБД Oracle на русском : эффективное изменение и вставка


Информационный Канал Subscribe.Ru

Выпуск 49

Уважаемые подписчики рассылки!

Этот выпуск посвящен сравнению различных способов одновременного изменения существующих и вставки недостающих данных (update + insert = upsert). По мотивам интересного обсуждения на сайте Тома Кайта, начатого в ноябре 2002 года.

Эффективное изменение и вставка (upsert)

Том,

Для реализации логики upsert (изменить данные, если они существуют/вставить, если их еще нет) при пакетной обработке я использую следующие подходы:

  1. попытаться выполнить вставку, а при нарушении ограничения первичного ключа обработать исключительную ситуацию и изменить соответствующие данные.
  2. попытаться изменить данные, проверить значение sql%rowcount и если оно равно 0 - вставить соответствующие данные.

Сейчас появился еще оператор merge, но его сложно использовать в хранимых процедурах, когда необходимо обрабатывать строки по одной, и формируются они не обязательно по результатам запроса.

Меня интересует следующее:

  1. Что эффективнее ((1) или (2)) с точки зрения объема данных повторного выполнения? Генерирует ли сервер данные повторного выполнения для изменений, затрагивающих 0 строк, и для вставок, не срабатывающих из-за нарушения ограничения?
  2. Достаточно ли эффективен оператор merge, чтобы можно было начинать его использовать, вставляя предварительно данные в глобальную временную таблицу? Можно ли использовать pl/sql-таблицу вместо временной таблицы? Можно ли применять в качестве источника данных для merge переменные PL/SQL?

Ответ Тома Кайта

Оптимизировать обработку можно, если знать особенности данных:

  • Если вы уверены, что БОЛЬШИНСТВО строк будет изменяться, сначала изменяейте, а если sql%rowcount=0 - вставляйте данные.
  • Если вы уверены, что БОЛЬШИНСТВО строк будет вставляться, вставляйте, а в случае ошибки - изменяйте существующие данные.

Если не знаете точно, придется выбирать -- объем данных повторного выполнения в обоих случаях будет одинаковым.

Оператор Merge - замечательный, но появился в версии 9i. В версии 8.1.7 его еще не было. В версии 9i есть потоковые (pipelined) функции (поищите по ключевому слову pipelined соответствующие материалы у меня на сайте), так что, да, можно использовать pl/sql в качесте источника данных, наряду с временными таблицами.

Теперь, вот вам пример использования оператора MERGE и "временной" таблицы:

 
ops$tkyte@ORA920.US.ORACLE.COM> create global temporary table ao 
  2  on commit PRESERVE ROWS 
  3  as 
  4  select * 
  5    from all_objects 
  6   where 1=0; 
 
Table created. 
 
ops$tkyte@ORA920.US.ORACLE.COM> alter table ao 
  2  add constraint 
  3  ao_pk primary key(object_id); 
 
Table altered. 
 
ops$tkyte@ORA920.US.ORACLE.COM> insert into ao select * from all_objects; 
 
29311 rows created. 
 
ops$tkyte@ORA920.US.ORACLE.COM> create table t1 
  2  as 
  3  select * 
  4    from all_objects 
  5    where rownum<= (select count(*)/2 from all_objects); 
 
Table created. 
 
ops$tkyte@ORA920.US.ORACLE.COM> alter table t1 add constraint t1_pk primary key(object_id); 
 
Table altered. 
 
ops$tkyte@ORA920.US.ORACLE.COM> analyze table t1 compute statistics 
  2  for table for all indexes for all indexed columns; 
 
Table analyzed. 
 
ops$tkyte@ORA920.US.ORACLE.COM> create table t2 
  2  as 
  3  select * 
  4    from all_objects 
  5   where rownum<= (select count(*)/2 from all_objects); 
 
Table created. 
 
ops$tkyte@ORA920.US.ORACLE.COM> alter table t2 add constraint t2_pk primary key(object_id); 
 
Table altered. 
 
ops$tkyte@ORA920.US.ORACLE.COM> analyze table t2 compute statistics 
  2  for table for all indexes for all indexed columns; 
 
Table analyzed.

Итак, во всех смыслах таблицы t1 и t2 одинаковы - мы обе их построили методом upsert на базе данных из AO...

 
ops$tkyte@ORA920.US.ORACLE.COM> declare 
  2      l_start number; 
  3      l_run1  number; 
  4      l_run2  number; 
  5 
  6      type rc is ref cursor; 
  7      l_cur rc; 
  8  begin 
  9      insert into run_stats select 'before', stats.* from stats; 
 10 
 11      l_start := dbms_utility.get_time; 
 12      merge into t1 
 13      using ao on ( t1.object_id = ao.object_id ) 
 14      when matched then 
 15          update set owner = ao.owner, 
 16            object_name = ao.object_name, 
 17            subobject_name = ao.subobject_name, 
 18            data_object_id = ao.data_object_id, 
 19            object_type = ao.object_type, 
 20            created = ao.created, 
 21            last_ddl_time = ao.last_ddl_time, 
 22            timestamp = ao.timestamp, 
 23            status = ao.status, temporary = ao.temporary, 
 24            generated = ao.generated, 
 25            secondary = ao.secondary 
 26      when not matched then 
 27          insert ( OWNER, OBJECT_NAME, 
 28                   SUBOBJECT_NAME, OBJECT_ID, 
 29                   DATA_OBJECT_ID, OBJECT_TYPE, 
 30                   CREATED, LAST_DDL_TIME, 
 31                   TIMESTAMP, STATUS, TEMPORARY, 
 32                   GENERATED, SECONDARY ) 
 33          values ( ao.OWNER, ao.OBJECT_NAME, 
 34                   ao.SUBOBJECT_NAME, ao.OBJECT_ID, 
 35                   ao.DATA_OBJECT_ID, ao.OBJECT_TYPE, 
 36                   ao.CREATED, ao.LAST_DDL_TIME, 
 37                   ao.TIMESTAMP, ao.STATUS, ao.TEMPORARY, 
 38                   ao.GENERATED, ao.SECONDARY); 
 39      commit; 
 40      l_run1 := (dbms_utility.get_time-l_start); 
 41      dbms_output.put_line( l_run1 || ' hsecs' ); 
 42 
 43      insert into run_stats select 'after 1', stats.* from stats; 
 44      l_start := dbms_utility.get_time; 
 45      for x in ( select * from ao ) 
 46      loop 
 47          update t2 set ROW = x where object_id = x.object_id; 
 48          if ( sql%rowcount = 0 ) 
 49          then 
 50              insert into t2 values X; 
 51          end if; 
 52      end loop; 
 53      commit; 
 54      l_run2 := (dbms_utility.get_time-l_start); 
 55      dbms_output.put_line( l_run2 || ' hsecs' ); 
 56      dbms_output.put_line 
 57      ( 'run 1 ran in ' || round(l_run1/l_run2*100,2) || '% of the time' ); 
 58 
 59      insert into run_stats select 'after 2', stats.* from stats; 
 60  end; 
 61  / 
 
424 hsecs 
2116 hsecs 
 
run 1 ran in 20.04% of the time 
 
PL/SQL procedure successfully completed.

Оператор merge работает быстрее, чем процедурный код, и...

 
ops$tkyte@ORA920.US.ORACLE.COM> select a.name, b.value-a.value run1, c.value-b.value run2, 
  2         ( (c.value-b.value)-(b.value-a.value)) diff 
  3    from run_stats a, run_stats b, run_stats c 
  4   where a.name = b.name 
  5     and b.name = c.name 
  6     and a.runid = 'before' 
  7     and b.runid = 'after 1' 
  8     and c.runid = 'after 2' 
  9     and (c.value-a.value) > 0 
 10     and (c.value-b.value) <> (b.value-a.value) 
 11   order by abs( (c.value-b.value)-(b.value-a.value)) 
 12  / 
 
NAME                                 RUN1       RUN2       DIFF 
------------------------------ ---------- ---------- ---------- 
 ... 
STAT...redo entries                 30661      45670      15009 
LATCH.redo allocation               30780      46012      15232 
STAT...db block gets                47239      62630      15391 
STAT...table scan blocks gotte        597      29311      28714 
n 
STAT...buffer is not pinned co        693      29409      28716 
unt 
STAT...index fetch by key               9      29320      29311 
STAT...db block changes             60912      90825      29913 
STAT...no work - consistent re        260      36398      36138 
ad gets 
STAT...calls to get snapshot s        450      44200      43750 
cn: kcmgss 
STAT...execute count                   63      44015      43952 
LATCH.shared pool                     463      44606      44143 
STAT...consistent gets - exami        729      51860      51131 
nation 
STAT...recursive calls                838      73844      73006 
STAT...consistent gets               1748      88444      86696 
LATCH.library cache pin               436      88558      88122 
LATCH.library cache                   757      89093      88336 
STAT...session pga memory           95732          0     -95732 
STAT...session logical reads        48987     151074     102087 
LATCH.cache buffers chains         212197     405774     193577 
STAT...session pga memory max      947700          0    -947700 
STAT...redo size                 12908776   16933156    4024380 
 
100 rows selected.

и выполняет меньше действий - генерирует лишь 75% соответствующего объема данных повторного выполнения...

Далее, я выполнил аналогичные действия с помощью потоковой функции (два оператора merge - merge с результатами merge) и оказалось, что выполнение merge из таблицы, построенной по результатам выполнения потоковой функции (с помощью TABLE), дает примерно те же результаты.

 
 ... 
ops$tkyte@ORA920.US.ORACLE.COM> create type myScalarType as object ( 
  2   OWNER               VARCHAR2(30), 
  3   OBJECT_NAME         VARCHAR2(30), 
  4   SUBOBJECT_NAME      VARCHAR2(30), 
  5   OBJECT_ID           NUMBER, 
  6   DATA_OBJECT_ID      NUMBER, 
  7   OBJECT_TYPE         VARCHAR2(18), 
  8   CREATED             DATE, 
  9   LAST_DDL_TIME       DATE, 
 10   TIMESTAMP           VARCHAR2(19), 
 11   STATUS              VARCHAR2(7), 
 12   TEMPORARY           VARCHAR2(1), 
 13   GENERATED           VARCHAR2(1), 
 14   SECONDARY           VARCHAR2(1) 
 15  ) 
 16  / 
 
Type created. 
 
ops$tkyte@ORA920.US.ORACLE.COM> create type myArrayType as table of myScalarType 
  2  / 
 
Type created. 
 
ops$tkyte@ORA920.US.ORACLE.COM> create or replace function ao_function return myArrayType 
  2  PIPELINED 
  3  as 
  4  begin 
  5      for ao in (select * from all_objects) 
  6      loop 
  7          pipe row( myScalarType( ao.OWNER, ao.OBJECT_NAME, 
  8                   ao.SUBOBJECT_NAME, ao.OBJECT_ID, 
  9                   ao.DATA_OBJECT_ID, ao.OBJECT_TYPE, 
 10                   ao.CREATED, ao.LAST_DDL_TIME, 
 11                   ao.TIMESTAMP, ao.STATUS, ao.TEMPORARY, 
 12                   ao.GENERATED, ao.SECONDARY) ); 
 13      end loop; 
 14      return; 
 15  end; 
 16  / 
 
Function created. 
 
 ... 
 
ops$tkyte@ORA920.US.ORACLE.COM> declare 
  2      l_start number; 
  3      l_run1  number; 
  4      l_run2  number; 
  5 
  6      type rc is ref cursor; 
  7      l_cur rc; 
  8  begin 
  9      insert into run_stats select 'before', stats.* from stats; 
 10 
 11      l_start := dbms_utility.get_time; 
 12      merge into t1 
 13      using ao on ( t1.object_id = ao.object_id ) 
 14      when matched then 
 15          update set owner = ao.owner, 
 16            object_name = ao.object_name, 
 17            subobject_name = ao.subobject_name, 
 18            data_object_id = ao.data_object_id, 
 19            object_type = ao.object_type, 
 20            created = ao.created, 
 21            last_ddl_time = ao.last_ddl_time, 
 22            timestamp = ao.timestamp, 
 23            status = ao.status, temporary = ao.temporary, 
 24            generated = ao.generated, 
 25            secondary = ao.secondary 
 26      when not matched then 
 27          insert ( OWNER, OBJECT_NAME, 
 28                   SUBOBJECT_NAME, OBJECT_ID, 
 29                   DATA_OBJECT_ID, OBJECT_TYPE, 
 30                   CREATED, LAST_DDL_TIME, 
 31                   TIMESTAMP, STATUS, TEMPORARY, 
 32                   GENERATED, SECONDARY ) 
 33          values ( ao.OWNER, ao.OBJECT_NAME, 
 34                   ao.SUBOBJECT_NAME, ao.OBJECT_ID, 
 35                   ao.DATA_OBJECT_ID, ao.OBJECT_TYPE, 
 36                   ao.CREATED, ao.LAST_DDL_TIME, 
 37                   ao.TIMESTAMP, ao.STATUS, ao.TEMPORARY, 
 38                   ao.GENERATED, ao.SECONDARY); 
 39      commit; 
 40      l_run1 := (dbms_utility.get_time-l_start); 
 41      dbms_output.put_line( l_run1 || ' hsecs' ); 
 42 
 43      insert into run_stats select 'after 1', stats.* from stats; 
 44      l_start := dbms_utility.get_time; 
 45      merge into t2 
 46      using (select * from TABLE(ao_function)) ao on ( t2.object_id = ao.object_id ) 
 47      when matched then 
 48          update set owner = ao.owner, 
 49            object_name = ao.object_name, 
 50            subobject_name = ao.subobject_name, 
 51            data_object_id = ao.data_object_id, 
 52            object_type = ao.object_type, 
 53            created = ao.created, 
 54            last_ddl_time = ao.last_ddl_time, 
 55            timestamp = ao.timestamp, 
 56            status = ao.status, temporary = ao.temporary, 
 57            generated = ao.generated, 
 58            secondary = ao.secondary 
 59      when not matched then 
 60          insert ( OWNER, OBJECT_NAME, 
 61                   SUBOBJECT_NAME, OBJECT_ID, 
 62                   DATA_OBJECT_ID, OBJECT_TYPE, 
 63                   CREATED, LAST_DDL_TIME, 
 64                   TIMESTAMP, STATUS, TEMPORARY, 
 65                   GENERATED, SECONDARY ) 
 66          values ( ao.OWNER, ao.OBJECT_NAME, 
 67                   ao.SUBOBJECT_NAME, ao.OBJECT_ID, 
 68                   ao.DATA_OBJECT_ID, ao.OBJECT_TYPE, 
 69                   ao.CREATED, ao.LAST_DDL_TIME, 
 70                   ao.TIMESTAMP, ao.STATUS, ao.TEMPORARY, 
 71                   ao.GENERATED, ao.SECONDARY); 
 72      commit; 
 73      l_run2 := (dbms_utility.get_time-l_start); 
 74      dbms_output.put_line( l_run2 || ' hsecs' ); 
 75      dbms_output.put_line 
 76      ( 'run 1 ran in ' || round(l_run1/l_run2*100,2) || '% of the time' ); 
 77 
 78      insert into run_stats select 'after 2', stats.* from stats; 
 79  end; 
 80  / 
 
494 hsecs 
1737 hsecs 
 
run 1 ran in 28.44% of the time 
 
PL/SQL procedure successfully completed. 
 
ops$tkyte@ORA920.US.ORACLE.COM> select a.name, b.value-a.value run1, c.value-b.value run2, 
  2         ( (c.value-b.value)-(b.value-a.value)) diff 
  3    from run_stats a, run_stats b, run_stats c 
  4   where a.name = b.name 
  5     and b.name = c.name 
  6     and a.runid = 'before' 
  7     and b.runid = 'after 1' 
  8     and c.runid = 'after 2' 
  9     and (c.value-a.value) > 0 
 10     and (c.value-b.value) <> (b.value-a.value) 
 11   order by abs( (c.value-b.value)-(b.value-a.value)) 
 12  / 
 
 ... 
 
STAT...session pga memory          104256     232480     128224 
STAT...session uga memory               0     130928     130928 
STAT...session uga memory max           0     130928     130928 
LATCH.row cache enqueue latch         362     177614     177252 
LATCH.row cache objects               448     184995     184547 
LATCH.cache buffers chains         211442     493338     281896 
STAT...session pga memory max      956224     166944    -789280 
STAT...redo size                 12876460   14459964    1583504 
 
106 rows selected.

Именно удаление процедурного, написанного программистом кода, приводит к такой разнице. Надо стараться решать задачи НА УРОВНЕ МНОЖЕСТВ (непроцедурно). В общем случае, чем меньше процедурного кода вы пишете, тем лучше.

Хорошее сравнение

Приведенные результаты очень впечатляющи. Но я не смог найти структуру таблицы RUN_STATS и таблицы/представления STATS - это что, таблица/представление словаря данных?

Ответ Тома Кайта

См. http://asktom.oracle.com/~tkyte/runstats.html

Оператор merge и переменные

Можно ли использовать переменные вместо таблиц в операторе merge? Например, если таблица для изменения и вставки состоит из 4 полей, можно ли эти 4 поля передать как параметры. Как это сделать - ведь так:

 
merge into t1 
using ao on ( t1.object_id = ao.object_id )

когда используется одна таблица, не получается. Я пытался использовать таблицу dual вместо недостающей и, хотя процедура скомпилировалась, она не работала.

Ответ Тома Кайта

Идентификаторы нельзя заменить параметрами НИ В ОДНОМ операторе - план при этом принципиально меняется. Вам придется использовать динамический SQL.

Что быстрее - delete/insert или upsert?

Если предполагается, что изменяться могут все столбцы, будет ли быстрее выполнить множественное удаление и множественные вставки, или использовать upsert с помощью оператора merge, как вы описали ранее?

Ответ Тома Кайта

truncate+insert /*+ append */ в таблицу с опцией nologging (конечно, с резервным копированием соответствующего табличного пространства сразу после завершения) будет, вероятно, самым быстрым методом, особенно если делать так:

 
truncate 
disable all indexes 
insert /*+ append */ 
перестроить все индексы с распараллеливанием и опцией nologging 
выполнить резервное копирование

Уточнение предыдущего вопроса

А что, если я не могу удалить все данные таблицы (truncate)? Что, если в таблице сейчас 100 миллионов строк, а теперь, скажем, миллион строк стирок надо вставить или изменить.

Сейчас мы удаляем все дублирующиеся строки в таблице, а затем массовыми вставками добавляем в таблицу миллион записей.

Это оказалось быстрее, чем делать изменение и, в случае возбуждения исключительной ситуации из-за отсутствия соответствующей строки, вставлять ее.

Ответ Тома Кайта

Причина в том, что построчные операции практически всегда выполняются медленнее (бывают исключения, но в общем случае)... имеет смысл использовать merge. У меня есть таблица из 3,8 миллиона строк, и я хочу с помощью merge добавить 1% строк:

 
big_table@ORA920> merge into big_table bt 
  2  using merge_data md on ( bt.id = md.id ) 
  3  when matched then 
  4    update set owner = md.owner, 
  5               object_name = md.object_name, 
  6               subobject_name = md.subobject_name, 
  7               data_object_id = md.data_object_id, 
  8               object_type = md.object_type, 
  9               created = md.created, 
 10               last_ddl_time = md.last_ddl_time, 
 11               timestamp = md.timestamp, 
 12               status = md.status, temporary = md.temporary, 
 13               generated = md.generated, 
 14               secondary = md.secondary 
 15  when not matched then 
 16     insert ( id, OWNER, OBJECT_NAME, 
 17              SUBOBJECT_NAME, OBJECT_ID, 
 18              DATA_OBJECT_ID, OBJECT_TYPE, 
 19              CREATED, LAST_DDL_TIME, 
 20              TIMESTAMP, STATUS, TEMPORARY, 
 21              GENERATED, SECONDARY ) 
 22     values ( md.id, md.OWNER, md.OBJECT_NAME, 
 23              md.SUBOBJECT_NAME, md.OBJECT_ID, 
 24              md.DATA_OBJECT_ID, md.OBJECT_TYPE, 
 25              md.CREATED, md.LAST_DDL_TIME, 
 26              md.TIMESTAMP, md.STATUS, md.TEMPORARY, 
 27              md.GENERATED, md.SECONDARY); 
 
38172 rows merged. 
 
Elapsed: 00:05:09.51 
 
Statistics 
---------------------------------------------------------- 
        414  recursive calls 
      84182  db block gets 
      96814  consistent gets 
      45069  physical reads 
   19120100  redo size 
        791  bytes sent via SQL*Net to client 
       1850  bytes received via SQL*Net from client 
          4  SQL*Net roundtrips to/from client 
          2  sorts (memory) 
          0  sorts (disk) 
      38172  rows processed

А если выполнить delete и insert:

 
big_table@ORA920> delete from big_table 
  2  where id in ( select id from merge_data ); 
 
19086 rows deleted. 
 
Elapsed: 00:08:33.78 
 
Statistics 
---------------------------------------------------------- 
        175  recursive calls 
     135948  db block gets 
    3872806  consistent gets 
      78021  physical reads 
   15651020  redo size 
        793  bytes sent via SQL*Net to client 
        832  bytes received via SQL*Net from client 
          4  SQL*Net roundtrips to/from client 
          1  sorts (memory) 
          0  sorts (disk) 
      19086  rows processed 
 
big_table@ORA920> insert /*+ APPEND */ into big_table select * from merge_data; 
 
38172 rows created. 
 
Elapsed: 00:01:19.03 
 
Statistics 
---------------------------------------------------------- 
         39  recursive calls 
      98282  db block gets 
        640  consistent gets 
      19373  physical reads 
    9934016  redo size 
        778  bytes sent via SQL*Net to client 
        829  bytes received via SQL*Net from client 
          4  SQL*Net roundtrips to/from client 
          3  sorts (memory) 
          0  sorts (disk) 
      38172  rows processed

Даже с учетом подсказки /+ APPEND/, которую в реальной ситуации вы вряд ли используете (этот 1% места не будет использован повторно при добавлении).

Прекрасный пример

Ваш пример прекрасно иллюстрирует ваше утверждение. Хочу задать последний вопрос на эту тему. Мне кажется, что вставка данных в таблицу merge_data сопряжена с излишними расходами ресурсов. Если таблица merge_data - глобальная временная, могу ли я считать истинным следующее утверждение?

время_выполнения(insert во временную таблицу + merge) < время_выполнения(delete + insert)

Достаточно простого ответа: да или нет.

Ответ Тома Кайта

Я предполагал, что таблица merge_data уже существует. Но, в общем случае, я считаю, что наполнепние данными merge_data+merge будет более эффективно, чем DELETE по ключу, а затем - INSERT...

Я не люблю давать вростые ответы да или нет ;)

Оператор merge и конструкция TABLE(CAST...

Поддерживается ли оператор MERGE в следующем случае или есть другой способ сделать это:

 
MERGE INTO обычная_таблица t 
     USING TABLE(CAST(мой_набор  
                      AS мой_тип_набора))c 
 ...

Я получаю сообщение об ошибке:

PL/SQL: ORA-00903: invalid table name

Все работае, если я использую обычную таблицу вместо набора. Я проверял на Oracle 9.2.0.1.0

Ответ Тома Кайта

Надо выполнять "select" из функции:

 
ops$tkyte@ORA920LAP> create type myScalarType as object 
  2  ( x int, y date ) 
  3  / 
 
Type created. 
 
ops$tkyte@ORA920LAP> create type myArrayType as table of myScalarType 
  2  / 
 
Type created. 
 
ops$tkyte@ORA920LAP> create or replace function myfunction return myArrayType 
  2  as 
  3          l_data myArrayType := 
  4             myArrayType( myScalarType( 1, sysdate ), 
  5                          myScalarType( 2, sysdate+2 ), 
  6                          myScalarType( 3, sysdate+3 ) ); 
  7  begin 
  8          return l_data; 
  9  end; 
 10  / 
 
Function created. 
 
ops$tkyte@ORA920LAP> create table t 
  2  as 
  3  select rownum x, sysdate-rownum y 
  4    from all_objects 
  5   where rownum <= 3; 
 
Table created. 
 
ops$tkyte@ORA920LAP> merge into t 
  2  using ( select * from TABLE( myFunction ) ) c 
  3  on ( t.x = c.x ) 
  4  when matched then update set y = c.y 
  5  when not matched then insert ( x,y ) values ( c.x, c.y ); 
 
3 rows merged.

Оператор merge

Мне нужна помощь по оператору Merge.

Я создал следующую таблицу:

 
CREATE TABLE TRANSSRL (  
  MY_CODE  VARCHAR2 (12)  NOT NULL,  
  PREFIX     VARCHAR2 (3)  NOT NULL,  
  YEAR       NUMBER (4)    NOT NULL,  
  CURR_NO    NUMBER (16)   NOT NULL,  
  CONSTRAINT PK_TRANS_SRL 
  PRIMARY KEY ( CURR_NO, MY_CODE, PREFIX, YEAR ) ) 
/ 
-- Для демонстрации я добавил в нее запись 
insert into transsrl values('PBM','GE',2002,31) 
/ 
-- Мой оператор Merge 
merge INTO TRANSSRL a 
USING  
( 
SELECT  
       my_code,prefix,year,curr_no  
FROM  
     TRANSSRL  
WHERE  
      my_code = 'PBM' AND  
      prefix = 'GE' AND 
      year = 2002 
) b 
ON 
( 
  a.my_code = b.my_code AND 
  a.prefix = b.prefix AND 
  a.year = b.year 
)                     
WHEN matched THEN UPDATE SET a.curr_no = NVL(curr_no,0) +1 
WHEN NOT matched THEN INSERT (my_code,prefix,year,curr_no) VALUES  
('PBM','GE',2002,1)

Этот оператор срабатывает как ожидалось, поскольку есть данные, удовлетворяющие критерию, и он изменяет значение в столбце currno с 31 на 32...

Если изменить год так, чтобы сработала ветка "when not matched", оператор не срабатывает... Я поменял год с 2002 на 2003:

 
merge INTO TRANSSRL a 
USING  
( 
SELECT  
       my_code,prefix,year,curr_no  
FROM  
     TRANSSRL  
WHERE  
      my_code = 'PBM' AND  
      prefix = 'GE' AND 
      year = 2003 
) b 
ON 
( 
  a.my_code = b.my_code AND 
  a.prefix = b.prefix AND 
  a.year = b.year 
)                     
WHEN matched THEN UPDATE SET a.curr_no = NVL(curr_no,0) +1 
WHEN NOT matched THEN INSERT (my_code,prefix,year,curr_no) VALUES  
('PBM','GE',2003,1)

Логически здесь имеет место ситуация "not matched", поэтому я ожидал вставки в таблицу новой записи со значениями PBM,GE,2003,1... Но этого не произошло. Почему?

Ответ Тома Кайта

Запрос:

 
( 
SELECT  
       my_code,prefix,year,curr_no  
FROM  
     TRANSSRL  
WHERE  
      my_code = 'PBM' AND  
      prefix = 'GE' AND 
      year = 2003 
)

вообще не вернул данных - вот почему. Не с чем вообще сравнивать...

Комментарий читателя от 29 июля 2003 года

Как мне применить merge в данном случае? Мне придется проверять существование записи по первичному ключу и если записи нет, выполнять Insert, а иначе - Update... То же самое сейчас у нас делается с помощью блока кода на pl/sql... Было бы здорово применить merge, потому что это действие будет выполняться очень часто.

Ответ Тома Кайта

Если множество, с которым выполняется слияние (merge), НЕ СОДЕРЖИТ ДАННЫХ, значит, нет данных для сравнения или для вставки. Вот в чем проблема. Вы ищете в ПУСТОМ множестве. Сервер за вас данные не "сделает".

Комментарий читателя от 20 августа 2003 года

Можно ли использовать оператор MERGE, если на сервере (версии 9i) оператор MERGE в конструкции USING выбирает данные через связь (DB Link) с сервера версии 8i?

Что-то типа:

 
MERGE into T1  
  (using Select * from T2@DB_LINK_T2 ) T2 
 on (T1.Id = T2.Id) 
WHEN MATCHED then 
  update set  ... 
WHEN NOT MATCHED then 
  insert (id, ... ) 
      value (id, ... ) ;

Ответ Тома Кайта

 
ops$tkyte@ORA920> select * from v$version@ora817dev; 
 
BANNER 
---------------------------------------------------------------- 
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production 
PL/SQL Release 8.1.7.4.0 - Production 
CORE    8.1.7.0.0       Production 
TNS for Solaris: Version 8.1.7.4.0 - Production 
NLSRTL Version 3.4.1.0.0 - Production 
 
ops$tkyte@ORA920> select * from v$version; 
 
BANNER 
---------------------------------------------------------------- 
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production 
PL/SQL Release 9.2.0.3.0 - Production 
CORE    9.2.0.3.0       Production 
TNS for Linux: Version 9.2.0.3.0 - Production 
NLSRTL Version 9.2.0.3.0 - Production 
 
ops$tkyte@ORA920> @a 
 
ops$tkyte@ORA920> merge into emp 
  2  using emp@ora817dev e2 on ( emp.empno = e2.empno ) 
  3  when matched then update set emp.ename = e2.ename 
  4  when not matched then insert ( empno ) values ( e2.empno ) 
  5  / 
 
14 rows merged.

Использование sql%rowcount для обновления информации о работе пользователя

Я использую твой способ выполнения "upsert", изменяя записи, проверяя затем sql%rowcount, и вставляя данные, если sql%rowcount=0.

Как можно отслеживать отрезки времени, в течение которых пользователь зарегистрирован? Например, если пользователь есть в представлении v$session, я хотел бы вставить sysdate в столбец useractivity.begin_time. Если пользователь остался зарегистрированным при повторной проверке, я хотел бы обновить значение useractivity.end_time. Если пользователь завершает сеанс, я хотел бы ничего не делать (поскольку процедура не будет вызвана - в представлении v$session не окажется строк для соответствующего пользователя). Значение useractivity.end_time всегда окажется равным последнему моенту времени, когда процедура обнаружила присутствие пользователя.

Я не могу понять, как получить несколько записей в таблице useractivity, показывающих каждый отрезок времени, в течение которого пользователь был зарегистрирован.

Например:

 
user begin end 
__________________ 
user1 5pm 5:15pm 
user1 5:35pm 6:55pm 
etc ...

Я хочу избежать записей вида:

 
user1 5pm 
user1 5:01pm 
user1 5:02pm 
user1          5:15pm

Надеюсь, я понятно описал проблему.

Ответ Тома Кайта

Можно просто включить аудит и регистрировать подключения. Это даст "время начала и время заврешения" для сеансов. Простое внешнее соединение с представлением v$session, а еще лучше - скалярный подзапрос к v$session, - позволит заполнить недостающие времена заврешения.

Приведите пример...

Спасибо за совет по использованию аудита.

Не могли бы вы привести пример использования внешнего соединения (outer join) или скалярного подзапроса (scalar subquery) для изменения "времени завершения" сеанса? Я по-прежнему не понимаю, как можно получить запись для каждого отрезка времени, когда моя процедура "застала" пользователя работающим. Я понимаю, как вставлять запись каждый раз, когда пользователь зарегистрирован, но не понимаю, как получить одну запись для каждого отрезка времени:

 
user1  5pm-6pm 
user1  7pm-8pm

Сейчас если изменение не срабатывает, я вставляю строку. Проблема в том, что в конечном итоге получается следующее:

 
user1 5pm-8pm

И я не узнаю, что пользователь user1 не работал в системе с 6 до 7 вечера. Я не применяю аудит, потому что хочу отслеживать и другие события, не охватываемые аудитом, например, периоды, когда количество транзакций в секунду превышает 50.

Ответ Тома Кайта

При использовании аудита вы без проблем получите правильный результат. Я не могу решить проблему с периодом от 6 до 7 вечера иначе - только с помощью адуита.

Как отслеживать периоды, когда файл не доступен?

Я могу использовать аудит для отслеживания действий пользователя. Но как отслеживать, отключен ли файл данных (или другие события на уровне базы данных)? Как добиться того, чтобы была одна запись на отрезок времени, в течение которого файл данных был недоступен, не внося по 12 записей в час (при проверке раз в 5 минут)?

Ответ Тома Кайта

Вам придется:

  1. запросить предыдущее состояние - online или offline
  2. сравнить его с текущим состоянием
  3. если они отличаются (или запрос не дает результатов), зарегистрировать изменение состояния

Оригинал обсуждения этого вопроса можно найти здесь.

Copyright © 2003 Oracle Corporation

Прежние выпуски

Все вышедшие выпуски рассылки можно найти на сайте рассылки. Там же реализована возможность поиска материалов по ключевым словам (с помощью Google...)

В следующем выпуске

Дальше я обещал заняться переводами интересных статей Джонатана Льюиса, опубликованных на сайте DBAzine.com. Кроме того, по совету нескольких хороших людей я собираюсь изменить структуру сайта рассылки. Вот об этом, скорее всего, и будет юбилейный, пятидесятый выпуск рассылки...

С наилучшими пожеланиями,

  В.К.

http://subscribe.ru/
E-mail: ask@subscribe.ru 		Отписаться
Убрать рекламу
В избранное