Ежедневный Архив: 13.04.2016

Новости

Мужской квест… год спустя

Квест Диверсант
Спустя год наши коллеги-женщины опять подарили нам участие в квесте. За год из команды ушел Дима Грачев, а пришли новые сотрудники Виталий и Евгений. И если мы с Сергеем знали, с чем столкнемся, то для новичков квест был в диковинку. На самом деле час (58 минут 40 секунд) в двух комнатах с участием актера и холостой стрельбы из боевых пистолетов вышли очень напряженными, и если бы не некоторые подсказки, нас бы точно застрелили нам не удалось бы выбраться из запертых комнат и не пришлось бы сломя голову бежать куда глаза глядят. Было офигенно и с атмосферой Великой Отечественной Войны.

SQL and PL/SQL

Super Merge

На примере одной из витрин отчетности поверх хранилища данных возникла задача по поддержанию актуальности денормализованной таблицы. В рассматриваемом примере есть таблица клиентов и таблица телефонов клиентов. Мы хотим ускорить работу отчетов, дополнив таблицу клиентов актуальными номерами телефонов.

Решение в виде одного оператора Merge предложил Максим Медведев:

  1. MERGE INTO test_clients dest
  2. USING
  3. (WITH f AS
  4. (SELECT c.ROWID AS rid,
  5. NVL(p.client_id, c.id) AS id,
  6. p.phone,
  7. p.phone_type,
  8. CASE
  9. WHEN c.id IS NULL THEN 'I'
  10. WHEN p.client_id IS NULL THEN 'D'
  11. ELSE
  12. CASE ROW_NUMBER()
  13. OVER (PARTITION BY p.client_id
  14. ORDER BY p.phone)
  15. WHEN 1 THEN 'U'
  16. ELSE 'I'
  17. END
  18. END AS r,
  19. ROW_NUMBER()
  20. OVER (PARTITION BY NVL(c.id, p.client_id)
  21. ORDER BY 1) AS rn
  22. FROM test_phones p
  23. FULL OUTER JOIN
  24. test_clients c
  25. ON c.id = p.client_id AND NVL(c.phone, p.phone) = p.phone
  26. WHERE (p.client_id IS NOT NULL AND NVL(c.phone_type, '-') != p.phone_type)
  27. OR (p.client_id IS NULL))
  28. SELECT f.*, s.name
  29. FROM f JOIN (SELECT DISTINCT id, name FROM test_clients) s ON s.ID = f.ID) src
  30. ON (dest.ROWID = src.rid AND src.r IN ('U', 'D'))
  31. WHEN MATCHED THEN
  32. UPDATE SET phone = src.phone,
  33. phone_type = src.phone_type
  34. DELETE WHERE src.r = 'D' AND src.rn > 1
  35. WHEN NOT MATCHED THEN
  36. INSERT VALUES (src.id,
  37. src.name,
  38. src.phone,
  39. src.phone_type);

Теперь можно изучить результат, попробовать добавлять, менять и удалять данные из таблицы с номерами телефонов

  1. INSERT INTO test_phones
  2. VALUES (2, '8(496)245-55-86', 'Второй домашний');
  3.  
  4. DELETE FROM test_phones
  5. WHERE client_id = 2 AND ROWNUM <= 1;
Страница 1 из 11