Основы работы с ORACLE для начинающих
При написании SQL запросов есть ряд правил, которым нужно просто следовать. Можно вдаваться в поиски, почему надо писать так, а не иначе, но для понимания нужен багаж и некоторый практический опыт, а ведь зачастую SELECT’ы надо писать уже сейчас, да так, чтобы они летали и после не переписывать.
Как писать смотрим здесь:
Примеры взял из дайджеста Oracle SQL изнутри
Условие можно записать так, что оно полностью отключит оптимизатор. Как следствие: запрос выполняется дольше, несмотря на имеющийся индекс.
Так пишут новички | Гораздо лучше |
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. |
Так пишут новички | Гораздо лучше |
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);
|
Эффективность следующих трёх конструкций зависит от данных в таблицах. Выбирайте исходя из ситуации.
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 требуется, во-первых, иметь под рукой всю выборку, во-вторых, её нужно будет отсортировать и затем удалить дубликаты. Скорость выполнения последних напрямую связана с размером выборки. Поэтому если в запросе есть соединение таблиц, которое порождает большой объём, лучше переписать запрос, используя уникальный ключ (в примере: 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.
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 и 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. Далее три типичных варианта (но лучше их избегать, если честно )).
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; |
Долго не мог понять, почему люди не любят пользоваться SQL*Plus. Оказывается: интерфейс убогий и бестолковый. Словом, не графический – мышкой ткнуть не куда (значит интуитивно не понятный). Мда.... ..редко встретишь кодера, умеющего мышкой воять SELECT’ы.
Если нужно перенести объекты схемы из одной базы в другую, то проще всего это сделать с помощью технологии Oracle Data Pump. Прежде чем вникать в детали работы с Data Pump, давайте уточним: у нас есть физические резервные копии и логические дампы.
Чтобы после сбоя в системе (например, отказа жесткого диска) восстановить базу нужно иметь под рукой резервную копию. (Нет резервной копии — прощай работа )) Чем свежее копия, тем лучше. Более того: резервная копия базы должна быть полной и непротиворечивой.