17 правил хорошего тона при написании SQL запросов


17 правил хорошего тона при написании SQL запросов



При написании SQL запросов есть ряд правил, которым нужно просто следовать. Можно вдаваться в поиски, почему надо писать так, а не иначе, но для понимания нужен багаж и некоторый практический опыт, а ведь зачастую SELECT’ы надо писать уже сейчас, да так, чтобы они летали и после не переписывать.

Как писать смотрим здесь:

Примеры взял из дайджеста Oracle SQL изнутри





Правило WHERE


Условие можно записать так, что оно полностью отключит оптимизатор. Как следствие: запрос выполняется дольше, несмотря на имеющийся индекс.

Так пишут новички Гораздо лучше
SELECT doc_dd, customer_n, total_doc
  FROM exdoc
 WHERE SUBSTR( customer_n, 1, 3) = 'Мор';
SELECT doc_dd, customer_n, total_doc
  FROM exdoc
 WHERE customer_n LIKE 'Мор%';
var ls_n VARCHAR2( 20)
exec ls_n := 'Морейнис'

SELECT doc_dd, customer_n, total_doc
  FROM exdoc
 WHERE customer_n = NVL( :ls_n, customer_n);
var ls_n VARCHAR2( 20)
exec ls_n := 'Морейнис'

SELECT doc_dd, customer_n, total_doc
  FROM exdoc
 WHERE customer_n LIKE NVL( :ls_n, '%');
SELECT doc_dd, customer_n, total_doc
  FROM exdoc
 WHERE TRUNC( doc_dd) = TRUNC( sysdate);
SELECT doc_dd, customer_n, total_doc
  FROM exdoc
 WHERE doc_dd BETWEEN TRUNC( sysdate)
   AND TRUNC( sysdate) + 0.9999;

* Чем больше девяток, тем точнее.

SELECT doc_dd, customer_n, total_doc
  FROM exdoc
 WHERE customer_n || doc_nd = 'Морейнис29';
SELECT doc_dd, customer_n, total_doc
  FROM exdoc
 WHERE customer_n = 'Морейнис'
   AND doc_nd = ‘29';
SELECT doc_dd, customer_n, total_doc
  FROM exdoc
 WHERE total_doc + 500 < 2500;
SELECT doc_dd, customer_n, total_doc
  FROM exdoc
 WHERE total_doc < 2000;
SELECT doc_dd, customer_n, total_doc
  FROM exdoc
 WHERE total_doc != 0;
SELECT doc_dd, customer_n, total_doc
  FROM exdoc
 WHERE total_doc > 0;

* При положительных значениях total_doc.


К списку правил




Правило HAVING


Так пишут новички Гораздо лучше
  SELECT a.customer_n
       , AVG( b.quant)
    FROM exdoc a
       , expos b
   WHERE a.doc_nd = b.doc_nd
GROUP BY a.customer_n
  HAVING customer_n != 'Иванов'
     AND customer_n != 'Петров';
  SELECT a.customer_n
       , AVG( b.quant)
    FROM exdoc a
       , expos b
   WHERE a.doc_nd = b.doc_nd
     AND customer_n != 'Иванов'
     AND customer_n != 'Петров';
GROUP BY a.customer_n

К списку правил




Комбинированные подзапросы


Так пишут новички Гораздо лучше
SELECT customer_n
  FROM exdoc
 WHERE total_doc =
            (SELECT MAX( total_pos)
               FROM expos)
   AND doc_dd =
            (SELECT MAX( doc_dd)
               FROM expos);
SELECT customer_n
  FROM exdoc
 WHERE (total_doc, doc_dd) =
             (SELECT MAX( total_pos)
                   , MAX( doc_dd)
                FROM expos);

К списку правил




Операции EXISTS, IN, Join


Эффективность следующих трёх конструкций зависит от данных в таблицах. Выбирайте исходя из ситуации.

SELECT customer_n
  FROM exdoc a
 WHERE EXISTS
        (SELECT 1
           FROM expos b
          WHERE b.doc_nd = a.doc_nd
            AND b.good_n = 'Грунт для цветов');
SELECT customer_n
  FROM exdoc
 WHERE doc_nd IN
        (SELECT doc_nd
           FROM expos
          WHERE good_n = 'Грунт для цветов');
SELECT a.customer_n
  FROM exdoc a
     , expos b
 WHERE b.doc_nd = a.doc_nd
   AND b.good_n = 'Грунт для цветов';

К списку правил




Операция DISTINCT


Для выполнения DISTINCT требуется, во-первых, иметь под рукой всю выборку, во-вторых, её нужно будет отсортировать и затем удалить дубликаты. Скорость выполнения последних напрямую связана с размером выборки. Поэтому если в запросе есть соединение таблиц, которое порождает большой объём, лучше переписать запрос, используя уникальный ключ (в примере: doc_nd).

Так пишут новички Гораздо лучше
SELECT DISTINCT a.doc_nd
     , a.customer_n
  FROM exdoc a
     , expos b
 WHERE a.doc_nd = b.doc_nd
   AND b.good_n = 'Грунт для цветов';
SELECT a.doc_nd
     , a.customer_n
  FROM exdoc a
 WHERE EXISTS
        (SELECT 1
           FROM expos b
          WHERE b.doc_nd = a.doc_nd
            AND b.good_n = 'Грунт для цветов');

К списку правил




Функция DECODE


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

SELECT COUNT(*)
  FROM exdoc
 WHERE doc_nd = '139'
   AND customer_n = 'Морейнис';

...
SELECT COUNT(*)
  FROM exdoc
 WHERE doc_nd = '152'
   AND customer_n = 'Морейнис';
SELECT COUNT( DECODE( doc_nd, 139, 'X', NULL )) cnt_139
     , COUNT( DECODE( doc_nd, 152, 'X', NULL )) cnt_152
  FROM exdoc
 WHERE customer_n = 'Морейнис';

К списку правил




Операция UNION ALL


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

Так пишут новички Гораздо лучше
SELECT doc_nd, sum_tax
  FROM expos
 WHERE doc_dd = '12-sep-12'
UNION
SELECT doc_nd, sum_tax
  FROM exsvc
 WHERE doc_dd = '12-sep-12';
SELECT doc_nd, sum_tax
  FROM expos
 WHERE doc_dd = '12-sep-2012'
UNION ALL
SELECT doc_nd, sum_tax
  FROM exsvc
 WHERE doc_dd = '12-sep-2012';

К списку правил




Anti-Join


Запросы, возвращающие записи, для которых нет данных в связанной таблице, называют anti-join. Далее три типичных варианта (но лучше их избегать, если честно )).

SELECT *
  FROM exdoc
 WHERE doc_nd NOT IN
         (SELECT doc_nd
            FROM expos
           WHERE good_n = 'Грунт для цветов');
SELECT *
  FROM exdoc a
 WHERE NOT EXISTS
        (SELECT NULL
           FROM expos b
          WHERE a.doc_nd = b.doc_nd
            AND b.good_n = 'Грунт для цветов');
SELECT a.*
  FROM exdoc a
     , expos b
 WHERE b.doc_nd (+) = a.doc_nd
   AND b.good_n (+) = 'Грунт для цветов'
   AND b.ROWID IS NULL;





Опубликован: 27 января 2013, 23:29


Похожие статьи:

  1. Научим SQL*Plus говорить по-русски

    Научим SQL*Plus говорить по-русски Долго не мог понять, почему люди не любят пользоваться SQL*Plus. Оказывается: интерфейс убогий и бестолковый. Словом, не графический – мышкой ткнуть не куда (значит интуитивно не понятный). Мда.... ..редко встретишь кодера, умеющего мышкой воять SELECT’ы.

  2. Как создавать дампы в Oracle с помощью Data Pump

    Если нужно перенести объекты схемы из одной базы в другую, то проще всего это сделать с помощью технологии Oracle Data Pump. Прежде чем вникать в детали работы с Data Pump, давайте уточним: у нас есть физические резервные копии и логические дампы.

  3. Как и чем создать Backup в Oracle

    Чтобы после сбоя в системе (например, отказа жесткого диска) восстановить базу нужно иметь под рукой резервную копию. (Нет резервной копии — прощай работа )) Чем свежее копия, тем лучше. Более того: резервная копия базы должна быть полной и непротиворечивой.


  • # 2

    Спасибо! неплохие рекомендации, раньше мне приходилось частенько работать с такими запросами.

  • # 4

    SELECT doc_dd, customer_n, total_doc
    FROM exdoc
    WHERE doc_dd BETWEEN TRUNC( sysdate)
    AND TRUNC( sysdate) + 0.999;

    не эквивалентно сравнению TRUNC( doc_dd) = TRUNC( sysdate), так как теряется почти 2 минуты от конца суток

    SELECT doc_dd, customer_n, total_doc
    FROM exdoc
    WHERE total_doc > 0;

    Сделайте пометку, что справедливо только для положительных total_doc. Иначе - сравнение только по модулю ABS(total_doc) > 0;

  • # 5

    В случае

    WHERE [столбец1] NOT IN
    (SELECT [столбец2]

    столбец2 должен иметь ограничение NOT NULL, иначе можем получить NULL на выходе, что будет неверным результатом.

    Если мы не уверены в отсутствии NULL-значений, используем

    WHERE NOT EXISTS
    (SELECT [value]

    value может быть идентификатором стобца подзапроса, выражением, построенным на значении этого столбца, NULL, *, любая числовая или символьная константа, или символьная строка.

    Интересно, влияет ли выбор значения value на скорость выполнения запроса?


Ваш комментарий:



cod

Все поля обязательны для заполнения.
Ваш email не публикуется.


Адрес заметки: