Галерея
7757 8119 8300 8698 8817 9504 9722 9937
Интересные записи
Топовые новости
Новое

Предложение ORDER BY cоветы и хитрости Oracle (продолжение 1)

TABLE

Это предложение необходимо при запросе к иерархической вложенной таблице.

PARTITION

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

SUBPARTITION

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

SAMPLE [BLOCK] [процент_для_образца] [SEED (начальное_значение)

Это предложение указывает, что записи будут отбираться из случайного набора строк в результирующем наборе, указанного как процентная доля строк или блоков, а не из всей таблицы. Предложение BLOCK заставляет Oracle отбирать образцы по блокам, а не по строкам. Параметр процент_для_рбразца, который показывает, какая доля строк или блоков будет включена в образец, может изменяться в пределах от 0.000001 до 99. Дополнительное предложение SEED используется для обеспечения ограниченной воспроизводимости результатов. При указании начального _значенш Oracle попытается возвращать при разных выполнениях запроса один и тот же образец. Начальное значение может изменяться от 0 до 4 294 967 295. Если предложение SEED опущено, то результирующий набор при каждом запросе будет разный. Отбор образца может использоваться только в запросах к одной таблице.

JOIN

Результирующие наборы из одной или нескольких таблиц объединяются в одном запросе. Подробно описывается ниже.

PARTITION ВY выражение [, …]

Определяется специальный тип запроса, называющийся секционированное_внешнее_соединение, в котором стандартный синтаксис внешнего соединения расширяется путем применения правого и левого внешних соединений к секционированию одной или нескольких строк, указываемых при помощи выражения. Этот запрос особенно полезен при запросах к данным, разбросанным по конкретному измерению, поскольку он возвращает строки, которые иначе не попали бы в результирующий набор. За примерами обращайтесь к подразделу «Огас1е» раздела «Предложение JOIN». Предложение PARTITION В У можно использовать на любой стороне внешнего соединения, что аналогично предложению UNION, примененному к внешним соединениям каждого из разделов секционированного результирующего набора с таблицей на другой стороне соединения. (Если данное предложение опущено, Oracle рассматривает весь результирующий набор как один раздел.) Предложение PARTITION BY нельзя использовать с предложением FULL OUTER JOIN.

WHERE… [[START WITH значение] CONNECT BY [PRIOR] условие]

Предложение фильтрует записи, попадающие в результирующий набор. Платформа Oracle позволяет использовать в таблицах иерархическую информацию, фильтрацию которой можно осуществлять при помощи предложения START WITH. Предложение START WITH указывает строки, которые будут выполнять в результирующем наборе роль родительских. Предложение CONNECT ВY определяет условие соотношения между родительскими строками и их строками-потомками. Ключевое слово PRIOR используется для указания родительских строк вместо строк-потомков. В иерархических запросах псевдостолбец LEVEL используется для указания корневой точки (1), точек-первичных потомков (2), точек-вторичных потомков (3) и так далее. Также в иерархических запросах доступны такие псевдостолбцы, как CONNECT BYISCYCLE и CONNECT ВYJSLEAF. Иерархические запросы являются взаимоисключающими с предложениями ORDER BY и GROUP BY. Не используйте эти предложения в запросах, содержащих предложения START WITH или CONNECT BY. Вы можете сортировать записи одноранговых потомков одной родительской таблицы при помощи предложения ORDER SIBLINGS BY.

ORDER [SIBLINGS] В Y выражение_для_сортировки {NULLS FIRST NULLS LAST]}

Результирующий набор запроса сортируется в порядке, определяемом выражением для сортировки. Это выражение может представлять собой имена столбцов, их псевдонимы или целые числа, указывающие порядковое положение столбцов. Предложение ORDER SIBLINGS BY используется при запросах к иерархическим таблицам с предложением CONNECT BY. Предложение ORDER SIBLINGS BY заставляет Oracle сохранять сортировку, определяемую предложением CONNECT BY, и применять ее только к одноранговым строкам (то есть строкам, находящимся на одном уровне иерархии). Предложения NULLS FIRST и NULLS LAST указывают, что записи, содержащие пустые значения, должны ставиться либо в начало, либо в конец соответственно.

FOR UP DA ТЕ [OF [схема.] …

Строки в результирующем наборе блокируются, так что другие пользователи не смогут заблокировать или изменить их до тех пор, пока вы не завершите свою транзакцию. Предложение FOR UPDATE нельзя использовать в подзапросах, в запросах, использующих предложения DISTINCT или GROUP BY, или в запросах, использующих операторы для работы с наборами данных или агрегатные функции. Строки-потомки в иерархической таблице не блокируются при запросе к родительским строкам. Ключевое слово OF используется для блокировки только указанной таблицы или представления. В противном случае Oracle блокирует все таблицы и представления, указанные в предложении FROM. При использовании предложения OF столбцы не имеют особого значения, но вы должны использовать реальные имена столбцов, а не псевдонимы. Предложения NOWAIT и WAIT заставляют Oracle либо возвратить вам управление немедленно (если блокировка уже установлена), либо подождать перед возвратом управления целое_число секунд соответственно. Если не указано ни предложение WAIT, ни NOWAIT, то Oracle ждет до тех пор, пока строки не станут доступны.

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

SELECT 2+2;

Чтобы обойти это ограничение, в Oracle существует специальная таблица под названием dual. Каждый раз при выполнении запроса, который не извлекает никаких данных из пользовательских таблиц, например при выполнении вычисления, используйте синтаксис FROM dual. Все приведенные ниже запросы являются допустимыми.

SELECT 2+2 FROM dual,
SELECT (((52-4) * 5) * 8) FROM dual;

Реализация инструкции SELECT в Oracle достаточно понятна, если вы хотите извлечь данные из таблицы. Например, Oracle позволяет использовать именованные запросы. Именованный запрос — это, по сути, псевдоним всего запроса, который позволяет экономить время при написании инструкций SELECT с множеством подзапросов. Например:

WITH pub_costs AS
(SELECT pub_id, SUM(job_lvl) dept_total
FROM employees e
GROUP BY pub_id), avg_costs AS
(SELECT SUM(dept_total)/COUNT(*) avg FROM employee)
SELECT * FROM pub_costs
WHERE dept.total > (SELECT avg FROM avg_cost)
ORDER BY department_name;

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

Платформа Oracle позволяет отбирать строки из одного раздела секционированной таблицы (при помощи предложения PARTITION) или получать только статистическую выборку строк из результирующего набора в виде процентной доли строк или блоков. Для этого используется предложение SAMPLE. Например:

SELECT *
FROM sales PARTITION (sales.20C4_q3) sales
WHERE sales.qty > 1000,
SELECT FROM sales SAMPLE (12);

Ретроспективные запросы (flashback query) — это возможность Oracle, которая позволяет извлекать результирующие наборы на определенные моменты времени. Например, мы можем выяснить, какая зарплата была у любого сотрудника вчера, до того как в базу данных были внесены крупные изменения.

SELECT
job_lvl,
lname,
fname
FROM employee AS CF TIMESTAMP (SYSI’IMESTAMP — INTERVAL ‘1’ DAY).

Еще одно интересное расширение стандартного формата запроса в Oracle — это иерархический запрос. Иерархический запрос возвращает результаты запросов к иерархически построенным таблицам в том порядке, какой вы определите. Например, следующий запрос возвращает имена сотрудников, их положение в иерархии (которое отражается местоположением в столбце org_chart), ID сотрудника, ID менеджера и ID должности.

[CROSS] JOIN

Из таблиц таблица1 и таблица2 извлекаются все записи. По смыслу данное предложение идентично предложению FROM таблица1, таблица2 без указания условий соединения в предложении WHERE.

INNER JOIN

Из таблиц 1 и 2 извлекаются те записи, для которых в обеих таблицах есть значения, соответствующие друг другу согласно условию соединения. Обратите внимание, что предложение INNER JOIN по смыслу аналогично предложению FROM таблица!, таблица2 с указанием условий соединения в предложении FROM.

NATURAL

Это предложение используется для объявления условия соединения так, как если бы использовалось предложение USING с указанием всех столбцов, общих для обеих таблиц. (Будьте внимательны, если столбцы имеют одинаковые названия, но разные типы данных или виды значений!) В соединении типа NATURAL нельзя ссылаться на столбцы LOB. Если сослаться в таком соединении на столбец LOB или на столбец-коллекцию, инструкция возвратит ошибку.

LEFT [OUTER] JOIN

Извлекаются все записи из левой таблицы (то есть таблицы 1) и соответствующие записи из правой таблицы (то есть таблицы2). Если в таблице2 не обнаруживается соответствующей записи, в столбцы помещаются пустые значения (NULL). Этот тип соединения вы можете использовать, чтобы извлечь все записи из таблицы, даже в том случае, если в соединяемой таблице не обнаруживается соответствий.

SELECT j, jobid, е.lname FROM jobs]

В приведенном выше запросе предложение CONNECT В Y определяет иерархическое отношение значения emp_id как родительской строки равным значению mgr id в строке-потомке, а предложение START WITH указывает, с какого места иерархии должен начинаться результирующий набор.

Oracle поддерживает следующие типы синтаксиса предложения JOIN (за дополнительной информацией обращайтесь к разделу «Предложение JOIN»).

RIGHT [OUTER] JOIN

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

FULL [OUTER] JOIN

Указывается, что будут извлекаться все строки из обеих таблиц независимо от того, соответствует ли столбец одной таблицы столбцу другой. Всем столбцам, для которых не находится соответствующего значения в другой таблице, назначается пустое значение (NULL).

ON условие соединения

Объявляется условие (условия), которое объединяет вместе результирующие наборы двух таблиц. Делается это в форме объявления столбцов из таблицы! и таблицы2, которые должны соответствовать условию соединения. Если необходимо проводить сравнение по нескольким столбцам, используйте предложение AND.

USING (список_столбцов)

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

Обратите внимание, что старый синтаксис Oracle использует предложение WHERE, а внешние соединения описываются при помощи знака (+). Синтаксис соединений стиля ANSI стал доступен только начиная с Oracle 9i Release 1. Поэтому вы часто будете сталкиваться в уже существующем коде со старым синтаксисом. Например, следующий запрос по смыслу эквивалентен приведенному выше примеру левого внешнего соединения.

Старый синтаксис иногда вызывает проблемы и является сложным для восприятия. Мы настоятельно рекомендуем использовать стандартный синтаксис ANSI.

Источник