Эффективное "изменение и вставка" (update + insert = upsert)

Источник: ln

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

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

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

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

Что эффективнее ((1) или (2)) с точки зрения объема данных повторного выполнения? Генерирует ли сервер данные повторного выполнения для изменений, затрагивающих 0 строк, и для вставок, не срабатывающих из-за нарушения ограничения?
Достаточно ли эффективен оператор 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 минут)?

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

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

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

Ссылки по теме