Уважаемые подписчики рассылки!
Я вернулся из отпуска, пару дней почитал новые обсуждения на сайте
Тома Кайта и нашел
небольшой, но интересный материал, перевод которого и предлагаю вашему вниманию.
Опять про аналитические функции...
Кстати, теперь я снова буду стараться выпускать рассылку раз в неделю. Хотя бы до конца года. Спасибо всем, кто терпеливо этого ждал.
Том! Я создаю отчет, в котором надо сравнивать записи из старой и новой систем. Однако нет гарантии, что для каждой записи из старой системы найдется точное совпадение. Если точно совпадающей строки нет, мне нужно выбирать "наиболее близкую" запись, в которой совпадают два заданных поля. Если такой записи нет, значит, соответствие не найдено. Ниже представлен упрощенный пример:
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, и:
В первом методе для замыкания надо использовать 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" ("расстоянию между строками") есть только следующие требования:
Так что, изменив всего одну строку, мы можем обрабатывать не числовые поля, задавать более сложные правила частичного совпадения и т.д.
Такой запрос можно также легко приспособить к требованию вида "выдать только три лучших совпадения" (с помощью аналитической функции 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
... потому что он не выдает "наилучшего соответствия"? Я думал, задача состоит именно в том, чтобы найтит наилучшее соотвествие.
Оригинал обсуждения этого вопроса можно найти здесь.
Copyright © 2003 Oracle Corporation
Все вышедшие выпуски рассылки можно найти на сайте рассылки. Там же реализована возможность поиска материалов по ключевым словам (с помощью Google...)
Обнаружил недавно, что рекламирующаяся на обороте книги Тома Кайта "Oracle для профессионалов" (Книга 1) компания Softline.ru имеет какое-то отношение к Oracle... И проводит (бесплатные) семинары по Oracle, Linux и другим иногда интересующим меня темам. Не только в Москве, но и в Киеве. Я на такие мероприятия не хожу, но, может, кому и пригодится.
Поскольку я все равно редко выпускаю то, что заранее обещал, ничего конкретного обещать не буду. Разве что сообщу, что, закончив заниматься RAID-массивами в ОС FreeBSD, я снова начинаю переводить главы книги "Mastering Oracle PL/SQL: Practical Solutions". И все еще собираюсь кое-что из переведенного публиковать.
С наилучшими пожеланиями,
В.К.
|
|