На примере одной из витрин отчетности поверх хранилища данных возникла задача по поддержанию актуальности денормализованной таблицы. В рассматриваемом примере есть таблица клиентов и таблица телефонов клиентов. Мы хотим ускорить работу отчетов, дополнив таблицу клиентов актуальными номерами телефонов.
Решение в виде одного оператора Merge предложил Максим Медведев:
- MERGE INTO test_clients dest
- USING
- (WITH f AS
- (SELECT c.ROWID AS rid,
- NVL(p.client_id, c.id) AS id,
- p.phone,
- p.phone_type,
- CASE
- WHEN c.id IS NULL THEN 'I'
- WHEN p.client_id IS NULL THEN 'D'
- ELSE
- CASE ROW_NUMBER()
- OVER (PARTITION BY p.client_id
- ORDER BY p.phone)
- WHEN 1 THEN 'U'
- ELSE 'I'
- END
- END AS r,
- ROW_NUMBER()
- OVER (PARTITION BY NVL(c.id, p.client_id)
- ORDER BY 1) AS rn
- FROM test_phones p
- FULL OUTER JOIN
- test_clients c
- ON c.id = p.client_id AND NVL(c.phone, p.phone) = p.phone
- WHERE (p.client_id IS NOT NULL AND NVL(c.phone_type, '-') != p.phone_type)
- OR (p.client_id IS NULL))
- SELECT f.*, s.name
- FROM f JOIN (SELECT DISTINCT id, name FROM test_clients) s ON s.ID = f.ID) src
- ON (dest.ROWID = src.rid AND src.r IN ('U', 'D'))
- WHEN MATCHED THEN
- UPDATE SET phone = src.phone,
- phone_type = src.phone_type
- DELETE WHERE src.r = 'D' AND src.rn > 1
- WHEN NOT MATCHED THEN
- INSERT VALUES (src.id,
- src.name,
- src.phone,
- src.phone_type);
Теперь можно изучить результат, попробовать добавлять, менять и удалять данные из таблицы с номерами телефонов
- INSERT INTO test_phones
- VALUES (2, '8(496)245-55-86', 'Второй домашний');
-
- DELETE FROM test_phones
- WHERE client_id = 2 AND ROWNUM <= 1;