Архив Категории: SQL and PL/SQL

SQL and PL/SQL

Мистический пробел, или неправильный способ обработки исключений

На днях коллеги из соседнего отдела столкнулись с проблемой. Иногда один и тот же вызов процедуры на PL/SQL с одними и теми же параметрами давал различные результаты. Причем, если в проблемном блоке, извлекающем одно значение из выборки в переменную, поставить пробел перед точкой с запятой, ошибки нет. Если убрать пробел, все хорошо. Но чудес не бывает. Выяснилось, что практически во всем пакете запросы, возвращающие одну строку в переменные, были обрамлены в блоки обработки исключений следующим образом:

BEGIN
   SELECT VAL
     INTO v_val
     FROM TBL
    WHERE condition ; -- мистический пробел!
EXCEPTION
   WHEN no_data_found OR too_many_rows
   THEN 
      NULL;
END;

И вот когда случалось, что точная выборка возвращает больше одной строки, ошибка гасилась на извлечении второй строки, а в переменную v_val уже успевало попасть значение из первой строки выборки. Установка или удаление мистического пробела меняла план запроса, а может, сам Oracle что-то предпринимал, одно ему, оракулу, ведомое, и в переменную в разных случаях попадало разное значение с последующей неправильной работой всей процедуры.

Посмотрите, как работает пример ниже:

declare
   v_1 NUMBER := 1;
begin
   dbms_output.put_line(v_1);
   begin
      SELECT 2
        INTO v_1
        FROM DUAL
       WHERE 1 = 0;
   exception
      when no_data_found or too_many_rows then
         null;
   end;
   dbms_output.put_line(v_1);
   dbms_random.seed(TO_CHAR(SYSTIMESTAMP, 'DD.MM.YYYY HH24:MI:SS.FF9'));
   begin
      SELECT Q.L
        INTO v_1
        FROM (SELECT LEVEL + 2 AS L, DBMS_RANDOM.VALUE(1, 100) O
                FROM DUAL
             CONNECT BY LEVEL <= 4) Q
       ORDER BY O;
   exception
      when no_data_found or too_many_rows then
         null;
   end;
   dbms_output.put_line(v_1);
end;

Вместо разных планов запроса здесь выступает сортировка по случайному значению. Прогнав данный скрипт, вы увидите, что при ошибке too_many_rows почти каждый раз меняется значение, попадающее в переменную v_1.

Рекомендация в данном случае – в обработке ошибок исключение каждого типа проверять отдельно и в каждом из вариантов инициализировать переменную нужным для дальнейшей корректной работы значением! Может появиться соблазн агрегировать результат при помощи MAX() или MIN(), но это тоже может впоследствии привести – нет, не к ошибке, – к неверной работе реального кода.

Oracle SQL and PL/SQL

Полезный DUAL

Псевдотаблица DUAL с единственным полем DUMMY и единственной строкой, содержащей “X” в качестве этого самого DUMMY, это просто бесценное изобретение инженеров Oracle, нелогично звучащая, но на самом деле изначально в ней было 2 строки. При помощи инструкции CONNECT BY из единственной строки DUAL получаются самые разные кортежи данных. Аналоги существуют во многих базах данных, ну, скажем так, не обязательно существуют, но могут быть созданы. К примеру, при переносе АПК “Нострадамус” (ныне – “ПрограмБанк.БизнесАнализ”) на рельсы СУБД Firebird была искусственно создана таблица DUAL (позже – процедура селектного типа), в которую включили ряд вычисляемых столбцов по аналогии с Oracle, например, SYSDATE (для запросов SELECT SYSDATE FROM DUAL и ряда схожих).

В ходе разработки на Oracle DUAL проявляет себя в совершенно разных ипостасях. Например, запрос

SELECT * FROM DUAL WHERE TO_NUMBER('A') = 1

позволит определить номер (ORA-1722) и название оракловой ошибки invalid number, по аналогии в предикате можно определить ряд других ошибок преобразования типов, арифметики, логических операций.

SELECT NVL(MAX(DUMMY), '-') FROM DUAL WHERE 1 = 0

– запрос, демонстрирующий логику неявного GROUP BY.

SELECT LAST_DAY(DATE '2017-11-20') AS NOVEMBER_LAST_DAY FROM DUAL

– легко отлаживать функции даты-времени, CASE-конструкции и вообще любые системные или самописные функции, а также их вариации, за исключением попадающих под ограничения ANSI SQL – т.е. без использования типично PL/SQL типов вроде BOOLEAN или с OUT-параметрами.

И эти несколько запросов – лишь в ходе двух дней. Причем несколько куда более простых или зависимых от конкретной схемы запросов сюда не включил. Пользуйтесь DUAL, и будет Вам счастье!

Oracle SQL and PL/SQL

A problem with Bind Variable Peeking in DSS systems turns out to be a confederate

written by Victor Varlamov, OCP at 07-JUL-2017

There is a Reporting system, where a lot (more than 100) of complex long running queries are launched by Reporting Engine, triggered by different business events. Queries are executed with quite different input parameters (list of customers) and data ranges (daily, weekly, monthly). Because of skewed data in tables, one report can return anywhere from 1 row to 1 million rows depending on the input parameters of the report (different clients have different quantities of rows in fact tables). Every report is implemented as a PL/SQL package with a main function that accepts input parameters, prepares some additional data, and then opens a static cursor with PL/SQL variables as parameters, and finally returns the opened cursor. The CURSOR_SHARING parameter is set to FORCE in the DB server. In such scenarios, SQL can lead to poor performance if the optimizer re-uses unsuitable execution plans that otherwise would not be used if a hard parse was forced using literals. Bind variable peeking can cause a suboptimal plan.

In the book Expert Oracle Practices, Alex Gorbachev relates a story – told to him by Tom Kyte – in which the query plan would change on Monday mornings if it was raining. It’s a true story.

“According to the end-users’ observations, if it was raining heavily Monday morning, then database performance was terrible. Any other day of the week, or on Mondays without rain, there were no problems. Talking to the DBA responsible for the system, Tom found that the performance problems continued until the DBA restarted the database, at which point performance went back to normal. That was the workaround: Rainy Mondays = Reboots.”

It is a real case, and this case was solved without any mysteries and magic, just good knowledge of how to Oracle DB works. I’ll show the solution at the end of the article.

Here is a simple example of how bind variable peeking works:

читать далее »
Oracle Beer Day SQL and PL/SQL

Песня про 99 бутылок пива

Поистине замечательный ресурс для программиста любого ранга и языка – www.99-bottles-of-beer.net, в котором более чем 1500 примеров реализации вывода песни про 99 бутылок пива на самых разных языках программирования и с использованием различных приемов. Есть и варианты на разных диалектах SQL. В свободное от работы время я соорудил и свой скрипт, не совсем верно передав текст песни, но идея, думаю, будет понятна.

SELECT CASE MOD (400 - LEVEL, 4)
          WHEN 3
          THEN
             TRUNC ((400 - LEVEL) / 4) || ' bottles of beer on the wall'
          WHEN 2
          THEN
             TRUNC ((400 - LEVEL) / 4) || ' bottles'
          WHEN 1
          THEN
             'Drink 1 bottle...'
          ELSE
             CASE
                WHEN LEVEL > 396
                THEN
                   TRUNC ((400 - LEVEL) / 4) - 1 || ' bottles left'
                ELSE
                   'No more bottles on the wall'
             END
       END
  FROM DUAL CONNECT BY LEVEL >= 396

Результат:

99 bottles of beer on the wall
99 bottles
Drink 1 bottle…
98 bottles left
98 bottles of beer on the wall
98 bottles
Drink 1 bottle…

Drink 1 bottle…
1 bottles left
1 bottles of beer on the wall
1 bottles
Drink 1 bottle…
No more bottles on the wall

SQL and PL/SQL

Отчеты по самодокументированной базе данных

Хорошей практикой в разработке баз данных Oracle являются комментарии к объектам физического и логического слоя (таблицам, представлениям, полям) – такая БД является самодокументированной. Вместе со стандартом именования объектов это значительно облегчает жизнь команды разработчиков, облегчает введение в должность новых сотрудников.

Однако приходится иногда делать отчет с группировкой по тем или иным объектам так, чтобы он хорошо выглядел в технической документации по модулю. Приводим пример запроса, который извлекает из БД документацию по интерфейсным представлениям бизнес-модуля “Форма 302”. Использованы аналитические функции LAG (предыдущее значение согласно сортировке) и DENSE_RANK (порядковый номер согласно сортировке).

SELECT CASE
          WHEN NVL(LAG(uv1.view_name) 
                   OVER (ORDER BY uv1.view_name, tc1.column_name), '#') <> uv1.view_name
          THEN DENSE_RANK() 
                   OVER (ORDER BY uv1.view_name)
          ELSE NULL
       END AS r_num,
       CASE
          WHEN NVL(LAG(uv1.view_name) 
                   OVER (ORDER BY uv1.view_name, tc1.column_name), '#') <> uv1.view_name
          THEN uv1.view_name
          ELSE NULL
       END AS first_view_name,
       tc1.column_name,
       cc1.comments
  FROM USER_VIEWS uv1
       LEFT JOIN USER_TAB_COLUMNS tc1 
         ON tc1.table_name = uv1.view_name
       LEFT JOIN USER_COL_COMMENTS cc1 
         ON cc1.table_name = uv1.view_name
        AND cc1.column_name = tc1.column_name
 WHERE LOWER(uv1.view_name) LIKE '%302%gui%v%' /* используйте свои критерии */
 ORDER BY uv1.view_name, tc1.column_name

Часть результатов, включая недокументированные представления, пустая – есть где поработать техническим писателям!

1CBR_302_GUI_DICT_BS_VBUSINESS_SIZE_CD 
  BUSINESS_SIZE_NAME 
  SORT_ID 
2CBR_302_GUI_DICT_OKATO_VOKATO_CD 
  OKATO_CODE 
  SORT_ID 
3CBR_302_GUI_DICT_OKVED_VOKVED_CD 
  OKVED_CODE 
  SORT_ID 
4CBR_302_GUI_OKATO_VAS_OF_DATEОтчетная дата
  FDDR_IDID ФДП-отчета
  OKATOКод Окато
  SECT_IDКод раздела
SQL and PL/SQL

Super Merge

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

CREATE TABLE test_clients
(
   id         NUMBER,
   name       VARCHAR2(30),
   phone      VARCHAR2(30),
   phone_type VARCHAR2(20)
);

INSERT INTO test_clients
VALUES (1, 'Вася', NULL, NULL);

INSERT INTO test_clients
VALUES (2, 'Петя', NULL, NULL);

CREATE TABLE test_phones
(
   client_id   NUMBER,
   phone       VARCHAR2 (30),
   phone_type  VARCHAR2 (20)
);

INSERT INTO test_phones
VALUES (1, '8(905)133-30-10', 'Мобильный');

INSERT INTO test_phones
VALUES (2, '8(905)247-89-17', 'Мобильный');

Решение в виде одного оператора 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;
SQL and PL/SQL

Повторы

Задача: есть таблица с одним полем из целых положительных чисел. Нужно запросом вывести все числа столько раз, чему равно само число.

Последовательность
N
1
3
2

Должно получиться
N
1
3
3
3
2
2

SELECT q.n
  FROM (    SELECT DISTINCT t.ROWID rn, 
                            t.n, 
                            LEVEL l
              FROM test_numbers t
           CONNECT BY LEVEL <= t.n) q
 ORDER BY q.rn

Интересный вариант с рекурсивным запросом предложил Дмитрий Грачев. Обратите внимание, что такой пример сработает только начиная с 11-ой версии Oracle и результат, вообще говоря, будет отличаться от того, что выдаст первый пример:

WITH f(a, b) 
  AS (SELECT n a, n b
        FROM test_numbers
       WHERE n > 0
       UNION ALL
      SELECT a - 1, b
        FROM f
       WHERE a > 1)
SELECT b
  FROM f
 ORDER BY b
SQL and PL/SQL

Наращивание пустых строк непустыми записями старше по дате

Вопрос родился в качестве теста на испытательном сроке по одной из реальных задач отчетности. Есть таблица с пропусками в атрибутах, для целей примера считаем, что записи упорядочены по дате создания или по иной дате. В данном примере записи упорядочиваются по ROWID, что не мешает заменить нужным критерием.

Col1 Col2
1  
1 10
1  
1 15
2  
2 20

Нужно SELECT-запросом во втором столбце пустоты заполнить нижеследующими непустыми значениями вот так:

Col1 Col2
1 10
1 10
1 15
1 15
2 20
2 20
SELECT col1,
       NVL(col2, 
           LEAD(col2) OVER (PARTITION BY col1 
                            ORDER BY ROWID)) col2
  FROM t1

Это, пожалуй, один из самых простых примеров, когда использование аналитических функций значительно облегчает разработку запроса. В Учебном Центре РДТЕХ читается двухдневный, очень интенсивный курс по аналитическим функциям в применении к хранилищам данных: Oracle Database 12c: Analytic SQL for Data Warehousing.

SQL and PL/SQL

Разбиение суммы на монетки

Сумму в S рублей нужно разбить монетами по 1, 2 или 5 рублей.

WITH t
     AS (    SELECT LEVEL n
               FROM DUAL
         CONNECT BY LEVEL <= :S)
SELECT t1.n || ' по 1 рублю, ' ||
       t2.n || ' по 2 рубля, ' ||
       t3.n || ' по 5 рублей'
  FROM t t1, t t2, t t3
 WHERE 1 * t1.n + 2 * t2.n + 5 * t3.n = :S
 ORDER BY t1.n DESC, t2.n DESC, t3.n DESC
SQL and PL/SQL

Простые числа

Знаменитая задачка про поиск простых чисел средствами чистого SQL. Используется обобщенное табличное выражение – иерархический запрос, отбирающий все натуральные числа вплоть до максимального, определяемого параметром S, и полный перебор с дополнительным условием, что возможные делители проверяемого на простоту числа нужно искать среди чисел вплоть до квадратного корня из этого числа.

WITH t
     AS (    SELECT LEVEL n, LEVEL * LEVEL n2
               FROM DUAL
         CONNECT BY LEVEL <= :S)
SELECT t1.n
  FROM t t1
 WHERE NOT EXISTS
          (SELECT 1
             FROM t t2
            WHERE t1.n >= t2.n2 
              AND t2.n != 1 
              AND MOD(t1.n, t2.n) = 0)

P.S. Способ полностью рабочий, но не самый быстрый. Скорее, классический в плане знаний диалекта SQL от Oracle и обобщенных выражений.