#sql #oracle
#sql #Oracle
Вопрос:
Большинство источников, включая собственную статью Oracle о Top-N запросах, предоставляют синтаксис, аналогичный следующему для выполнения запроса Top-N:
SELECT val
FROM (
SELECT val
FROM rownum_order_test
ORDER BY val DESC
)
WHERE ROWNUM <= 5;
Это гарантированно приведет к получению 5 лучших значений; однако гарантировано ли их предоставление в правильном порядке? Этот вопрос со ссылкой на Википедию указывает на обратное:
Хотя некоторые системы баз данных допускают спецификацию предложения ORDER BY в подвыборках или определениях представлений, присутствие там не влияет.
Означает ли это, что вышеупомянутый код не гарантирует никакого упорядочения и требует дополнительного ORDER BY , или это исключительный случай?
SELECT val
FROM (
SELECT val
FROM rownum_order_test
ORDER BY val DESC
)
WHERE ROWNUM <= 5
ORDER BY val DESC; -- Is this line necessary?
Редактировать: Предположим, что внешний запрос будет выполняться напрямую (не вложенный в другой запрос). Я задаю этот вопрос, потому что кажется вопиющим упущением, что ни один из источников (включая Oracle) не удосужился упомянуть о необходимости ORDER BY , несмотря на то, что упорядочение было бы интуитивно ожидаемым от таких запросов top-n.
Комментарии:
1. требуется дополнительный порядок по, если вы пытаетесь отобразить конечный результат в определенном порядке. без этого первые 5 строк возвращались бы в случайном порядке, который, как и ожидалось, иногда, но не всегда.
2. Спасибо @vkp, я этого и боялся. Хотя я нахожу подозрительным, что все источники пренебрегают указанием на эту важную деталь, учитывая, что можно было бы интуитивно ожидать, что строки будут возвращены в отсортированном порядке при выполнении запроса top-n.
3. использование аналитических функций, подобных
row_number, rank, dense_rank, даст вам гарантированные результаты.4. Нет. Если вы хотите, чтобы ваши результаты были упорядочены (в Oracle), тогда используйте order by . Это так просто.
5. Сама концепция «возврата строк в правильном порядке» не имеет 100% смысла. Внешний запрос «не знает», как вы будете использовать строки (является ли он сам по себе подзапросом, который будет использоваться в соединении, запросе более высокого уровня, предложении WHERE ?) Как таковой, «правильного порядка» не существует. Если способ использования результирующих строк заключается в отображении их на экране или записи в файл, и они нужны вам в определенном порядке, вам следует использовать дополнительный ПОРЯДОК BY . Это теория. На практике возможно, что Oracle, после того как она уже упорядочила строки во внутреннем запросе, извлекет их в таком порядке.
Ответ №1:
Порядок по здесь явно имеет значение, потому что, если бы это было не так, запрос просто не работал бы для предоставления значений top-n.
Вполне могут быть случаи, когда оптимизатор может обнаружить, что ЗАКАЗ BY не оказывает никакого эффекта, и в таких случаях он может преобразовать запрос, чтобы удалить ЗАКАЗ BY, но это не один из тех случаев.
Если ваш внешний запрос не изменяет порядок строк ни явно, ни неявно (возможно, через объединение), то я был бы уверен, что в этом случае порядок будет сохранен.
Комментарии:
1. Я бы предположил то же самое, но я хотел бы найти некоторую документацию об этом поведении, прежде чем принимать его как окончательное. Многие люди делают одно и то же предположение о порядке
Dictionary<TKey, TValue>в .NET, который, по-видимому, сохраняется в большинстве случаев, но затем оказывается, что при определенных обстоятельствах он не выполняется .
Ответ №2:
В более старых версиях ORACLE (8.0) у вас нет возможности использовать предложение ORDER BY в подзапросе. Итак, только для тех из нас, кто все еще использует некоторые древние версии, есть другой способ справиться: магия оператора ОБЪЕДИНЕНИЯ. UNION отсортирует записи по столбцам в запросе:
Пример:
SELECT * FROM
(SELECT EMP_NO, EMP_NAME FROM EMP_TABLE
UNION
SELECT 99999999999,'' FROM DUAL)
WHERE ROWNUM<=5;
где 99999999999 больше, чем все значения в EMP_NO
Или, если вы хотите выбрать сотрудников с 5 самыми высокими зарплатами:
SELECT EMP_NO, EMP_NAME, 99999999999999-TMP_EMP_SAL
FROM
(SELECT 99999999999999-EMP_SAL TMP_EMP_SAL, EMP_NO, EMP_NAME
FROM EMP_TABLE
UNION
SELECT 99999999999999,0,'' FROM DUAL)
WHERE ROWNUM<=5;
С уважением,
Вирджил Ионеску