SQL для начинающих: 10 правил построения «точных» запросов часть 2.

6. Не допускай декартового произведения между таблицами

Результатом декартового — или перекрёстного — произведения множеств будет такое множество, элементами которого являются все возможные упорядоченные пары элементов исходных множеств. Рассмотрим пример «Адрес». Возьмём две таблицы «Город», «Улица». В первой таблице «Город» есть две записи: Москва и Санкт-Петербург. Во второй таблице «Улица» сохранены следующие записи:

улица Карла Маркса, которая одновременно есть и в Москве, и в Санкт-Петербурге;
улица Крупской аналогично и в Москве, и в Санкт-Петербурге;
Малый Полуярославский переулок только в Москве.

Пишем запрос: «Получаю из таблицы «Улица», которые принадлежат городу Москва».

⚠️ Опасный подход:

SELECT t1.TYPE AS TYPE_1 /* Колонка TYPE из таблицы TEST_DATA_1 */
       ,t1.VALUE AS VALUE_1 /* Колонка VALUE из таблицы TEST_DATA_1 */
       ,t2.TYPE AS TYPE_2 /* Колонка TYPE из таблицы TEST_DATA_2 */
       ,t2.VALUE AS VALUE_2 /* Колонка VALUE из таблицы TEST_DATA_2 */
  FROM TEST_DATA_1 t1
       ,TEST_DATA_2 t2
WHERE t1.TYPE = 'CITY'
  AND t2.TYPE = 'STREET';

SQL-запрос написан без условия, то есть: «Извлекаю улицы, относящиеся к городам, без соединения таблиц». База данных, не понимая, по какому городу делается SQL-запрос, соединит со всеми улицами и Москву, и Санкт-Петербург. Всего вернётся 2* 5 = 10 записей.

✅ Безопасный подход заключается в наличии связей:

SELECT t1.TYPE AS TYPE_1 /* Колонка TYPE из таблицы TEST_DATA_1 */
       ,t1.VALUE AS VALUE_1 /* Колонка VALUE из таблицы TEST_DATA_1 */
       ,t2.TYPE AS TYPE_2 /* Колонка TYPE из таблицы TEST_DATA_2 */
       ,t2.VALUE AS VALUE_2 /* Колонка VALUE из таблицы TEST_DATA_2 */
  FROM TEST_DATA_1 t1
       ,TEST_DATA_2 t2
WHERE t1.TEST_DATA_1_ID = t2.TEST_DATA_1_ID
  AND t1.TYPE = 'CITY'
  AND t2.TYPE = 'STREET';

Этот SQL-запрос написан с условием, то есть: «Извлекаю улицы, относящиеся к городу Москве, соединяя две таблицы условием». В нём указывается, по какому городу нужно выполнить фильтрацию. Поэтому возвращено 3 записи.

Когда данные извлекаются больше чем из одной таблицы, важно, как они соединяются между собой. Неправильное соединение будет возвращать неверные данные и не в ожидаемом количестве.

7. Проверяй, что имена параметров процедур не совпадают с именами колонок

Процедуры, функции могут использоваться для разных целей. Одно из возможных предназначений — обновление записей в таблице.

Допустим, есть строковый параметр А, который передаётся на вход процедуры с целью фильтрации. Можно сказать, что написано так: «Обновляю таблицу, задав новое значение для колонки, где выполняется фильтрация по колонке А равной параметру А». В этом случае наблюдается полное совпадение А = А. База данных обновит все записи в этой таблице.

Чтобы этого не было, параметру добавляют префикс или постфикс. Например, параметр будет называться не А, а РА. В изменённом виде можно сказать, что написано так: «Обновляю таблицу, задав новое значение для колонки, где выполняется фильтрация по колонке А равной параметру PА».

⚠️ Опасный подход:

/* 1 вариант процедуры с ошибкой */
create or replace procedure e_test_data_1_upd_description(test_data_1_id in TEST_DATA_1.TEST_DATA_1_ID%type
                                                                                                            ,description        in TEST_DATA_1.DESCRIPTION%type) as
begin
  update TEST_DATA_1 t1 –
     set t1.DESCRIPTION = description /* Обновление записи */
   where t1.TEST_DATA_1_ID = test_data_1_id;
exception
  when others then
    /* Блок перехвата ошибок */
    null;
end e_test_data_1_upd_description;
/

/* Пример вызова 1 варианта процедуры с ошибкой */
declare
begin
  e_test_data_1_upd_description(test_data_1_id => 4 /* ID EMPLOYEE = СОТРУДНИК 2 */
                              ,description        => 'ДОПОЛНИТЕЛЬНЫЕ ДАННЫЕ ПО СОТРУДНИКУ 2');
end;
/

/*
 * Результат изменений
 * Визуально изменений нет
 */
SELECT T1.TEST_DATA_1_ID AS TEST_DATA_1_ID
       ,T1.TYPE AS TYPE_1
       ,T1.VALUE AS VALUE_1
       ,T1.DESCRIPTION AS DESCRIPTION
  FROM TEST_DATA_1 T1;

⚠️ Опасный подход:

/* 2 вариант процедуры с ошибкой */
create or replace procedure e_test_data_1_upd_description(test_data_1_id    in TEST_DATA_1.TEST_DATA_1_ID%type
                                                        ,description_new in TEST_DATA_1.DESCRIPTION%type) as
begin
  update TEST_DATA_1 t1 –
     set t1.DESCRIPTION = description_new /* Обновление записи */
   where t1.TEST_DATA_1_ID = test_data_1_id;
exception
  when others then
    /* Блок перехвата ошибок */
    null;
end e_test_data_1_upd_description;
/

/* Пример вызова 2 варианта процедуры с ошибкой */
declare
begin
  e_test_data_1_upd_description(test_data_1_id    => 4 /* ID EMPLOYEE = СОТРУДНИК 2 */
                               ,description_new  => 'ДОПОЛНИТЕЛЬНЫЕ ДАННЫЕ ПО СОТРУДНИКУ 2');
end;
/

/*
 * Результат изменений
 * Изменены все записи
 */
SELECT T1.TEST_DATA_1_ID AS TEST_DATA_1_ID
      ,T1.TYPE AS TYPE_1
      ,T1.VALUE AS VALUE_1
      ,T1.DESCRIPTION AS DESCRIPTION
  FROM TEST_DATA_1 T1;

✅ Безопасный подход заключается в передаче параметра, имя которого не совпадает с именем колонки в таблице:

/* Вариант процедуры без ошибки */
create or replace procedure e_test_data_1_upd_description(p_test_data_1_id in TEST_DATA_1.TEST_DATA_1_ID%type
                                                                                                            ,p_description        in TEST_DATA_1.DESCRIPTION%type) as
begin
  update TEST_DATA_1 t1 –
     set t1.DESCRIPTION = p_description /* Обновление записи */
   where t1.TEST_DATA_1_ID = p_test_data_1_id;
exception
  when others then
    /* Блок перехвата ошибок */
    null;
end e_test_data_1_upd_description;
/

/* Пример вызова варианта процедуры без ошибки */
declare
begin
  e_test_data_1_upd_description(p_test_data_1_id => 4 /* ID EMPLOYEE = СОТРУДНИК 2 */
                               ,p_description        => 'ДОПОЛНИТЕЛЬНЫЕ ДАННЫЕ ПО СОТРУДНИКУ 2');
end;
/

/*
 * Результат изменений
 * Изменена 1 требуемая запись
 */
SELECT T1.TEST_DATA_1_ID AS TEST_DATA_1_ID
      ,T1.TYPE AS TYPE_1
      ,T1.VALUE AS VALUE_1
      ,T1.DESCRIPTION AS DESCRIPTION
  FROM TEST_DATA_1 T1;

8. Следи за временем выполнения SQL-запроса

Время, пожалуй, один из самых бесценных ресурсов. Пренебрежение за контролем времени выполнения SQL-запроса приведёт к трате усилий и денег.

Рассмотрим пример «Мониторинг времени выполнения». Допустим, на уровне базы данных продуктовой среды настроен специальный триггер. Его предназначение сводится к следующему:

прерывать сессию, которая выполняется дольше N-минут;
сохранить информацию об SQL-запросе в журнал для последующего анализа или постановки на мониторинг.

Вариант триггера на таблицу с искусственно генерируемой ошибкой в момент обновления данных:

/* Вариант триггера */
create or replace trigger TEST_DATA_1_AIUDR_PTCL
  after insert or update or delete on TEST_DATA_1
  for each row
begin
  if UPDATING then
    if (:old.test_data_1_id = 5 and :new.description is not null) then
     DBMS_OUTPUT.PUT_LINE('Log entry.');
     raise_application_error(-20001, 'No Update with id 5 and new description.');
     rollback;
    end if;
  end if;
end;
/

Специалисту рассказывали про этот триггер. Он проигнорировал это или забыл — и реализовал, поставленную задачу на непродуктовой среде таким образом, что одно из действий выполняется больше N-минут. Передал всё на установку в продуктовую среду. Получилось, что реализованный функционал не работает полностью или частично.

Вариант процедуры с искусственно завышенным временем выполнения

/* Вариант процедуры */
create or replace procedure e_test_data_1_upd_description(p_test_data_1_id in TEST_DATA_1.TEST_DATA_1_ID%type
                                                      ,p_description    in TEST_DATA_1.DESCRIPTION%type) as
begin
   /* Цикл добавлен для увеличения времени выполнения блока программной логики */
  for indx in 1 .. 1000000 loop
    for cur in (select t1.TYPE  as TYPE_1
                      ,t1.VALUE as VALUE_1
                      ,t2.VALUE as VALUE_2
                 from TEST_DATA_1 t1
                     ,TEST_DATA_2 T2
                where t1.TEST_DATA_1_ID = t2.TEST_DATA_1_ID
                  and t1.TEST_DATA_1_ID = p_test_data_1_id) loop
      null;
    end loop;
  end loop;
/* Блок программной логики */
  update TEST_DATA_1 t1 –
     set t1.DESCRIPTION = p_description /* Обновление записи */
   where t1.TEST_DATA_1_ID = p_test_data_1_id;
end e_test_data_1_upd_description;
/

/* Пример вызова процедуры */
declare
begin
  e_test_data_1_upd_description(p_test_data_1_id => 5 /* ID EMPLOYEE = СОТРУДНИК 3 */
                               ,p_description        => 'ДОПОЛНИТЕЛЬНЫЕ ДАННЫЕ ПО СОТРУДНИКУ 3');
end;
/

Задача специалиста смотреть на поставленную задачу шире, учитывая разные аспекты, применяя разные подходы. Можно попробовать оптимизировать SQL-запрос, например, добавляя индексы. Можно менять алгоритмы выполнения действий, добиваясь требуемого результата.

9. Используй копию данных для построения отчётности

Отчётность — это извлечение массива данных из базы для последующей обработки, аналитики, построения прогноза, прочее. Для неё может извлекаться значительный объём данных.

Рассмотрим пример «Отчёт о расходах за период». У нас есть промышленная среда, на которой развёрнуто приложение с подключением к базе данных. С приложением работают сотрудники. Задачей одних является внесение информации о приходе и расходе денежных средств. Задачей других — подготовка отчёта о расходе денежных средств за период. Информация вносится периодически и в небольшом объёме. Извлекается реже, но вся, что была внесена за конкретный период.

При ограниченных ресурсах базы данных извлечение может приводить к замедлению работы приложения. Потому что на стороне БД подключаются сотрудники из обеих групп, ресурсы делятся между ними, и отклик происходит медленнее. Избежать подобного эффекта можно при помощи копии базы данных с применением механизма репликации. Так, клон клон с определённой периодичностью синхронизируется с основной базой данных (их может быть несколько).

Создание копии базы данных — задача администраторов базы данных (Database administrator, DBA). Для большего погружения в механизм репликации можно обратиться к официальной справочной информации соответствующей базы данных. Например:

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

Подход использовать не основную базу данных для отчётности, а её клон (копию) — это рекомендация. И не для любой отчётности требуется создавать копию. Например, если с базой данных работает один сотрудник, который практически не загружает её. Или если у неё много свободных ресурсов.

10. Проверяй формат данных

Бывает, что отчёт, который обычно работает хорошо, возвращает ошибку, если ввести другие входные данные. Это связано с тем, что у новых входных данных другой формат.

Рассмотрим пример «Отчёт». У нас есть отчёт, строящийся на данных, которые заполняются внешним приложением. Одна из его колонок — дата. Поле ввода на форме, в которой происходит её заполнение — строковое. В подавляющем большинстве случаев формат: день числом, месяц числом, год числом, например, 01.01.2001. Изредка — день числом, месяц словом, год числом, например, «1 января 2001».

Приложение позволяет вводить в любом виде. Конечные пользователи ошибку не видят, но для отчёта это — потенциальная проблема. Она может заключаться в неверном предположении, что дата всегда заносится в базу данных в одном виде.

⚠️ Опасный подход заключается в игнорировании формата используемых данных:

SELECT t1.TYPE AS TYPE_1 /* Колонка TYPE из таблицы TEST_DATA_1 */
      ,to_date(t1.VALUE, 'DD.MM.RRRR') AS VALUE_1 /* Колонка VALUE из таблицы TEST_DATA_1 */
      ,t2.TYPE AS TYPE_2 /* Колонка TYPE из таблицы TEST_DATA_2 */
      ,t2.VALUE AS VALUE_2 /* Колонка VALUE из таблицы TEST_DATA_2 */
  FROM TEST_DATA_1 t1
      ,TEST_DATA_2 t2
WHERE t1.TEST_DATA_1_ID = t2.TEST_DATA_1_ID
  AND t1.TYPE = 'DATE'
  AND t2.TYPE = 'DATE';

✅ Безопасный подход заключается в понимании формата используемых данных:

SELECT t1.TYPE AS TYPE_1 /* Колонка TYPE из таблицы TEST_DATA_1 */
      ,to_date(t1.VALUE, 'DD.MM.RRRR') AS VALUE_1 /* Колонка VALUE из таблицы TEST_DATA_1 */
      ,t2.TYPE AS TYPE_2 /* Колонка TYPE из таблицы TEST_DATA_2 */
      ,t2.VALUE AS VALUE_2 /* Колонка VALUE из таблицы TEST_DATA_2 */
  FROM TEST_DATA_1 t1
      ,TEST_DATA_2 t2
WHERE t1.TEST_DATA_1_ID = t2.TEST_DATA_1_ID
  AND t1.TYPE = 'DATE'
  AND t2.TYPE = 'DATE'
  AND t2.VALUE = 'Формат день числом, месяц числом, год числом';

Наличие разных данных можно узнать заранее. Для этого, когда делается отчёт, можно выполнить проверку на всех данных, а не только на части. Это — залог стабильной работать и уверенность, что созданный отчёт будет работать.

Вспомним, что написано выше, и закрепим правила:

Объявляя имена таблиц, обращайся к записям через имена таблиц.
Извлекай только те данные, которые планируешь использовать.
По максимуму используй данные, которые извлёк из таблицы.
Проверяй запросы SQL на индексы.
Начинай запрос SQL с таблицы с меньшим набором записей.
Не допускай декартового произведения между таблицами.
Проверяй, что имена параметров процедур не совпадают с именами колонок.
Следи за временем выполнения SQL-запроса.
Используй копию данных для построения отчётности.
Проверяй формат данных.

SQL для начинающих: 10 правил построения «точных» запросов часть 2.

6. Не допускай декартового произведения между таблицами

7. Проверяй, что имена параметров процедур не совпадают с именами колонок

8. Следи за временем выполнения SQL-запроса

9. Используй копию данных для построения отчётности

10. Проверяй формат данных

Вспомним, что написано выше, и закрепим правила:

Report Page