Ефективне “зміна і вставка” (update + insert = upsert), Інші СУБД, Бази даних, статті

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


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

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


Мене цікавить наступне:



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

Відповідь Тома Кайта


Оптимізувати обробку можна, якщо знати особливості даних:



Якщо не знаєте точно, доведеться вибирати – обсяг даних повторного виконання в обох випадках буде однаковим.


Оператор 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% відповідного обсягу даних повторного виконання …


Далі, я виконав аналогічні дії з допомогою потокової функції (два оператора mergemerge з результатами 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 000 000 рядків, а тепер, скажімо, мільйон рядків прань треба вставити або змінити.


Зараз ми видаляємо все дублюються рядка в таблиці, а потім масовими вставками додаємо в таблицю мільйон записів.


Це виявилося швидше, ніж робити зміна і, в разі порушення виняткової ситуації через відсутність відповідного рядка, вставляти її.


Відповідь Тома Кайта


Причина в тому, що порядковий операції практично завжди виконуються повільніше (бувають виключення, але в загальному випадку) … має сенс використовувати 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. якщо вони відрізняються (або запит не дає результатів), зареєструвати зміну стану

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*