Для реализации логики 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
- сравнить его с текущим состоянием
- если они отличаются (или запрос не дает результатов), зарегистрировать изменение состояния