Том! Я создаю отчет, в котором надо сравнивать записи из старой и новой систем. Однако нет гарантии, что для каждой записи из старой системы найдется точное совпадение. Если точно совпадающей строки нет, мне нужно выбирать "наиболее близкую" запись, в которой совпадают два заданных поля. Если такой записи нет, значит, соответствие не найдено. Ниже представлен упрощенный пример:
amathur> create table old_table(old_meas_id varchar2(20),
2 field1 number(11),
3 field2 number(11),
4 field3 number(11));
Table created.
amathur> create table new_table(new_meas_id varchar2(20),
2 field1 number(11),
3 field2 number(11),
4 field3 number(11));
Table created.
amathur> insert into old_table values ('A_OLD',1,2,3);
1 row created.
amathur> insert into old_table values ('B_OLD',4,5,6);
1 row created.
amathur> insert into old_table values ('C_OLD',7,8,9);
1 row created.
amathur> insert into new_table values ('A_NEW',1,2,3);
1 row created.
amathur> insert into new_table values ('B_NEW',4,5,6);
1 row created.
amathur> insert into new_table values ('C_NEW',7,8,10);
1 row created.
amathur> commit;
amathur> select * from old_table;
OLD_MEAS_ID FIELD1 FIELD2 FIELD3
-------------------- ---------- ---------- ----------
A_OLD 1 2 3
B_OLD 4 5 6
C_OLD 7 8 9
amathur> select * from new_table;
NEW_MEAS_ID FIELD1 FIELD2 FIELD3
-------------------- ---------- ---------- ----------
A_NEW 1 2 3
B_NEW 4 5 6
C_NEW 7 8 10
Если найдено точное совпадение (значения столбцов field1, field2 и field3 совпадают), надо выдать эти записи. Пусть "наиболее близкой" будет запись, в которой совпадают значения столбцов field1 и field2, но не field3 (как станет понятно далее, предполагается, что значение в этом столбце минимально отличается - Прим. В.К. ). Надо выдать также наиболее близкую запись.
Итак, на основе этих данных мой отчет должен выявить полное совпадение A_OLD с A_NEW и B_OLD с B_NEW. Запись C_OLD не совпадает в точности ни с одной из записей таблицы new_table, но можно найти достаточно близкую запись, поскольку столбцы field1 и field2 совпадают:
amathur> l 1 select old.old_meas_id,new.new_meas_id 2 from old_table old,new_table new 3 where old.field1=new.field1 4 and old.field2=new.field2 5 and old.field3=new.field3 6 or 7 (old.field1=new.field1 8 and old.field2=new.field2 9 and (select count(*) from new_table 10 where field1=old.field1 11 and field2=old.field2 12 and field3=old.field3)=0 13* ) amathur> / OLD_MEAS_ID NEW_MEAS_ID -------------------- ------------------ C_OLD C_NEW A_OLD A_NEW B_OLD B_NEW
В моем случае, таблица "old_table" содержит данные объемом 2-3 Гбайта и несколько дополнительных столбцов, как и таблица "new_table". Я выполнил запрос к этим таблицам как описано выше. Через два дня выбрана была только половина строк. Анализ трассировочного файла с помощью TKPROF показал множество ожиданий ввода-вывода, работы с временным пространством и т.д., так что мне понятно, почему так происходит. Но, мне интересно, нельзя ли получить требуемый отчет другим запросом?
Ответ Тома Кайта
Проблема может быть связана с коррелированным подзапросом, да и "соединение с or" такого типа требует существенных ресурсов.
Если есть индекс по old_table(field1,field2), следующий запрос будет работать намного лучше - замечательный полный просмотр старой и новой таблицы, которые соединяются хешированием, а скалярный подзапрос выполняется только для "не совпадающих" строк, по индексу.
ops$tkyte@ORA9IR2> select nt.new_meas_id, 2 nvl( ot.old_meas_id, 3 (select old_meas_id 4 from old_table 5 where old_table.field1 = nt.field1 6 and old_table.field2 = nt.field2 7 and rownum = 1 ) ) old_meas_id 8 from new_table nt left join old_table ot on 9 ( nt.field1 = ot.field1 10 and 11 nt.field2 = ot.field2 12 and nt.field3 = ot.field3 ); NEW_MEAS_ID OLD_MEAS_ID -------------------- -------------------- A_NEW A_OLD B_NEW B_OLD C_NEW C_OLD
Еще можно так:
ops$tkyte@ORA9IR2> insert into old_table values ('XXXXX',1,2,44);
1 row created.
ops$tkyte@ORA9IR2> select *
2 from (
3 select nt.new_meas_id, ot.old_meas_id,
4 count(*) over (partition by nt.field1, nt.field2) cnt1,
5 count(decode(nt.field3,ot.field3,1)) over (partition by nt.field1, nt.field2) cnt2,
6 nt.field3 nt_f3,
7 ot.field3 ot_f3
8 from new_table nt left join old_table ot on
9 ( nt.field1 = ot.field1
10 and
11 nt.field2 = ot.field2 )
12 )
13 where cnt1 = 1
14 or (cnt1 > 1 and ((cnt2 > 0 and nt_f3 = ot_f3) or (cnt2 = 0)))
15 /
NEW_MEAS_ID OLD_MEAS_ID CNT1 CNT2 NT_F3 OT_F3
-------------------- -------------------- ---------- ---------- ---------- ----------
A_NEW A_OLD 2 1 3 3
B_NEW B_OLD 1 1 6 6
C_NEW C_OLD 1 0 10 9
Здесь мы соединяем по столбцам f1 и f2, и:
- а) считаем, сколько (cnt) строк получается для этой пары f1/f2. Если cnt = 1, мы знаем, что эту строку надо выдать;
б) если cnt > 1 (мы получили частичные совпадения), мы оставляем строку, где f3 = f3 если cnt2 больше 1 (т.е. было точное совпадение), или оставляем все частичные совпадения, если cnt2 = 0 (т.е. есть много частичных совпадений, но нет ни одного точного)
nvl?
В первом методе для замыкания надо использовать nvl или, может, decode?
Ответ Тома Кайта
Отличное замечание
Используйте
decode( c, null, ( скалярный_подзапрос ), c )
вместо NVL, - или case:
case when c is null then ( скалярный_подзапрос ) else c end
Функция nvl не дает замыкания, она будет повторно выполнять скалярный подзапрос.
Расстояние...
Как насчет такого варианта использования второго подхода, на базе аналитических функций:
SQL> insert into new_table values ('NEW' ,99,99,99);
SQL> insert into old_table values ('XXXXX',1 ,2 ,44);
SQL> l
1 select y.*
2 from (
3 select x.*, min(distance) over (partition by nt_f1, nt_f2) min_dist
4 from (
5 select nt.new_meas_id, ot.old_meas_id,
6 nt.field1 nt_f1, nt.field2 nt_f2, nt.field3 nt_f3,
7 ot.field1 ot_f1, ot.field2 ot_f2, ot.field3 ot_f3,
8 abs (nt.field3-ot.field3) distance
9 from new_table nt left join old_table ot on
10 ( nt.field1 = ot.field1
11 and
12 nt.field2 = ot.field2
13 )
14 ) x
15 ) y
16 where (min_dist = 0 and distance = 0) -- полное совпадение
17 or min_dist is null -- нет совпадения
18* or min_dist > 0 -- частичное совпадение
SQL> /
NEW_M OLD_M NT_F1 NT_F2 NT_F3 OT_F1 OT_F2 OT_F3 DISTANCE MIN_DIST
----- ----- ----- ----- ----- ----- ----- ----- -------- --------
A_NEW A_OLD 1 2 3 1 2 3 0 0
B_NEW B_OLD 4 5 6 4 5 6 0 0
C_NEW C_OLD 7 8 10 7 8 9 1 1
NEW 99 99 99
К выражению "distance" ("расстоянию между строками") есть только следующие требования:
- 1) возвратить 0 для точного совпадения
2) возвратить > 0 для частичного совпадения
3) возвратить null, если совпадения нет
Так что, изменив всего одну строку, мы можем обрабатывать не числовые поля, задавать более сложные правила частичного совпадения и т.д.
Такой запрос можно также легко приспособить к требованию вида "выдать только три лучших совпадения" (с помощью аналитической функции dense_rank...).
Ответ Тома Кайта
Менее процедурное, более ориентированное на множества решение.
У нас тут есть реклама пива, с таким текстом:
"tastes great, less filling"
("Отличный вкус при меньшем объеме" - я бы так перевел. Прим. В.К. )
который описывает этот подход :) Мне он нравится. Хотя я и не тестировал его производительность, но идея кажется разумной.
А почему не сделать проще?
Поскольку вас не интересует совпадение по столбцу field3, (Конечно, если значения в столбце field3 совпадают - это лучше всего. Если же нет, вы ищете строки, в которых совпадают значения в столбцах field1 и field2). Поэтому простой запрос по столбцам feild1 и feild2 решит вашу проблему. Результат запроса, в любом случае, не позволит понять, какое соответствие - наилучшее, а какое - следующее по близости.
select old.old_meas_id,new.new_meas_id
from old_table old,new_table new
where old.field1=new.field1
and old.field2=new.field2
Ответ Тома Кайта
... потому что он не выдает "наилучшего соответствия"? Я думал, задача состоит именно в том, чтобы найтит наилучшее соотвествие.