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

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

Добавить комментарий Edit

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

Правило 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.99;

//Чем больше девяток
//, тем точнее.
 
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
 WHERE doc_nd NOT IN
         (SELECT doc_nd
            FROM expos
           WHERE 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; 
 

Поделиться заметкой:

0 коммент.:

Отправить комментарий

Подписаться на: Комментарии к сообщению ( Atom )