Oracle distinct не работает

Обновлено: 06.07.2024

Как-то на практике замечал, что GROUP BY работает быстрее, чем DISTINCT, но всё же такие запросы достаточно медленны. Можно ли как-то ускорить их? Я так понимаю, что индексы они не используют, или только у меня не используют
Подскажите плз выход.

Человек без чувства юмора - не серьёзный человек

DISTINCT, согласно описанию, выполняет неявный GROUP BY. Отличие в производительности может быть, но объяснить его рационально сложно. Индексы использует, но нужны правильные составные индексы, так как GROUP BY выполняется после WHERE. Поясню примером:

SELECT * from x WHERE y=10 GROUP BY z;

Требует индекс KEY(y,z)

Если индекса нет правильного, а есть только KEY(z), то использовать его не имеет смысла, так как придется накладывать на него условие y=10, а это в лучшем случае MERGE индексов, а в худшем, перебор таблицы.

А если в условии WHERE используется несколько полей, то индекса KEY(y,z) будет достаточно, если условие y=10 будет на первом месте? Или же нужен составной индекс на все поля?

Я так понимаю, что поле группировки в составном индексе должно быть последним?

Человек без чувства юмора - не серьёзный человек

Запрос:
SELECT * from x WHERE y1=10 AND y2=10 GROUP BY z;

Требует индекс KEY(y1,y2,z)

Кроме того, условие типа > или < обычно является последним, использующим индекс

Не совсем понял
Если у меня условие идёт по 10 полям, а группировка по одиннадцатому, которое не участвует в условии, для использования индекса при группировке нужен индекс на все 11 полей?

Человек без чувства юмора - не серьёзный человек

Именно так. MySQL сначала накладывает WHERE, затем группирует. Индекс может использоваться только один (за редким исключением ситуаций с MERGE индексов), соответственно последовательность в нем должна соответствовать последовательности использования - сначала 10 полей WHERE, затем 1 поле GROUP BY. Использование индекса остановится, если среди WHERE есть условия < или >

Вот использование < и > тоже интересно. Получается, при их использовании, значения не берутся из индекса и ускорить выборку уже никак нельзя?
В моих запросах есть и <, и >, и группировка. Значит, как я понимаю, включение поля группировки в использующийся индекс не имеет смысла?
Также, получается, что нет смысла включать в индекс поля, которые ищутся по условиям < и/или >? И тем более если таких полей несколько

Человек без чувства юмора - не серьёзный человек

Вот, к примеру, поле даты. Нужно найти записи, удовлетворяющие диапазону дат. Используем `date`>='date1' AND `date`<='date2'. Значит поле `date` не имеет смысла включать в индекс?

Человек без чувства юмора - не серьёзный человек

Neval написал:

Это не так. Индекс используется для такого запроса. Но это последняя часть использованного составного индекса.

Пусть у Вас запрос WHERE a>10 and a<20 and b=11 and c=20
и индекс KEY(c,b, a) в таком случае последовательность такая:
1. Используется c=20 - первая часть индекса
2. Используется b=11 - вторая часть индекса
3. используется третья часть индекса для a>10 and a<20

Другой пример: KEY(c,a,b)
1. Используется c=20 - первая часть индекса
2. используется вторая часть индекса для a>10 and a<20
3. для оставшихся записей используется b=11 (using where), так как проще уже перебрать сами записи, чем обходить все ветви индекса для c=20, 10<a<20 и находить в индекса записи с b=11

Сразу не написал, но в моём случае в индексе есть два поля, каждое из которых ищется по больше/меньше, значит два этих поля точно нет смысла держать в индексе

А есть ли преимущество в использовании BETWEEN вместо больше/меньше?

Человек без чувства юмора - не серьёзный человек

BETWEEN синоним < >, преимущество не дает

Ваш случай просто проверьте, посмотрите что говорит EXPLAIN, какое значение key_len

Я пытался использовать DISTINCT, но он не работает с ORDER BY в одном выражении. Пожалуйста помоги.

Я пробовал это с:

это не работает.

Заказ по CreationDate очень важен.

Проблема в том, что столбцы, используемые в ORDER BY , не указаны в DISTINCT . Для этого вам нужно использовать агрегатную функцию для сортировки и использовать GROUP BY для выполнения DISTINCT работы.

Попробуйте что-то вроде этого:

Вам даже не нужно ключевое слово DISTINCT, если вы группируете по категориям.

Ключевые столбцы расширенной сортировки

Причина, по которой то, что вы хотите сделать, не работает, заключается в логическом порядке операций в SQL , который для вашего первого запроса (упрощенно):

FROM MonitoringJob
SELECT Category, CreationDate т.е. добавить так называемый расширенный ключевой столбец сортировки
ORDER BY CreationDate DESC
SELECT Category т.е. снова удалите столбец расширенного ключа сортировки из результата.

Таким образом, благодаря стандартной функции расширенного ключевого столбца сортировки SQL , можно полностью упорядочить то, чего нет в SELECT предложении, потому что оно временно добавляется к нему за кулисами.

Итак, почему это не работает DISTINCT ?

Если мы добавим DISTINCT операцию, она будет добавлена между SELECT и ORDER BY :

FROM MonitoringJob
SELECT Category, CreationDate
DISTINCT
ORDER BY CreationDate DESC
SELECT Category

Но теперь, с расширенным ключевым столбцом сортировки CreationDate , семантика DISTINCT операции была изменена, поэтому результат больше не будет прежним. Это не то, что мы хотим, поэтому и стандарт SQL, и все разумные базы данных запрещают такое использование.

обходные

Его можно эмулировать с помощью стандартного синтаксиса следующим образом

Или просто (в данном случае), как показал также Прутсвондер

Я думаю, вы ошибаетесь в том, как DISTINCT ON работает, и почти уверен, что это не поможет. Выражение в круглых скобках - это то, что используется для определения отличимости (условия группировки). Если есть разные категории с одинаковыми CreationDate именами, то в результате появится только одна из них! Так как мне было интересно, не ошибся ли я в чем-то, я также загрузил пример базы данных в ваш блог, чтобы перепроверить: DISTINCT ON запрос, который вы там дали, дал в общей сложности 1000 результатов (с большим количеством дубликатов length ), в то время как запрос ниже дал всего 140 (уникальных) значений.

Если вывод MAX (CreationDate) не требуется - как в примере исходного вопроса - единственным ответом является второе утверждение ответа Прашанта Гупты:

Объяснение: вы не можете использовать предложение ORDER BY во встроенной функции, поэтому оператор в ответе Prutswonder не может использоваться в этом случае, вы не можете поместить вокруг него внешний выбор и отбросить часть MAX (CreationDate).

Просто используйте этот код, если вам нужны значения столбцов [Категория] и [CreationDate]

Или используйте этот код, если вам нужны только значения столбца [Категория].

Скажем, у меня есть табличное имя TableA с приведенными ниже частичными данными:

Я хотел выбрать только 1 строку из 5% и 1 строку из 2% в качестве представления с помощью DISTINCT, но это не так, мой запрос:

Вышеприведенный запрос дает мне результат, как показано ниже.

Но это не мой ожидаемый результат, ожидаемый результат mt показан ниже:

Могу ли я узнать, как я могу достичь этого без указания предложения WHERE? Спасибо!

спросил(а) 2016-01-06T03:08:00+03:00 5 лет, 10 месяцев назад

Я думаю, что вы не понимаете рамки DISTINCT : это даст вам отдельные строки, а не только отдельные в первом поле.

Если вам нужна одна строка для каждого отдельного LOOKUP_VALUE , вам нужно либо WHERE , которое будет работать, какое из них будет показано, либо стратегия агрегации с предложением GROUP BY плюс логика в SELECT которая сообщает запросу, как агрегировать другой столбцы (например, AVG , MAX , MIN )

ответил(а) 2016-01-06T03:31:00+03:00 5 лет, 10 месяцев назад

Здесь моя догадка о вашей проблеме - когда вы говорите

"Вышеуказанный запрос даст мне результат, как показано в таблице данных выше".

это просто неправда - попробуйте его и соответствующим образом обновите свой вопрос.

Я размышляю здесь: я думаю, вы пытаетесь использовать "Distinct", но также выводите другие поля. Если вы запустите:

Тогда ваш вывод будет "одной строкой на каждую комбинацию" из трех полей.

Вместо этого попробуйте GROUP BY - это позволит вам выбрать Max, Min, Sum других полей, все еще принося "одну строку за уникальные комбинированные значения" для полей, включенных в GROUP BY

пример ниже использует вашу таблицу, чтобы вернуть одну строку за LOOKUP_VALUE, а затем максимальную и минимальную оставшиеся поля и количество итоговых записей, используя ваши данные:

ответил(а) 2016-01-06T03:28:00+03:00 5 лет, 10 месяцев назад

Я хотел выбрать только 1 строку из 5% и 1 строку из 2%

Это позволит получить наименьшее значение lookups_code для каждого значения lookup_value :

Вы также можете использовать GROUP BY :

ответил(а) 2016-01-06T03:35:00+03:00 5 лет, 10 месяцев назад

Я собираюсь сделать полный снимок в темноте на этом, но из-за того, как вы назвали свои поля, это означает, что вы пытаетесь имитировать функцию vlookup в Microsoft Excel. Если это так, поведение, когда есть несколько совпадений, состоит в том, чтобы выбрать первое совпадение. Как это звучит, так оно и работает.

Если это то, что вы хотите, И первое значение не обязательно является самым низким (или самым высоким, или наилучшим образом выглядящим или каким-либо другим), тогда функция агрегации row_number, вероятно, удовлетворит ваши потребности.

Я предупреждаю вас, что мои критерии упорядочения основаны на номере строки базы данных, который, возможно, может отличаться от того, что вы думаете. Если, однако, вы вставляете их в чистый стол (с отметкой о сбросе воды), то я думаю, что это довольно безопасная ставка, которая будет вести себя так, как вы хотите. Если нет, тогда вам лучше включить поле, чтобы указать, какой порядок вам нужен.

ответил(а) 2016-01-06T05:35:00+03:00 5 лет, 10 месяцев назад

Как насчет функции MIN()

Я считаю, что это работает для вашего желаемого результата, но в настоящее время я не могу его протестировать.

Здравствуйте, dvd00, Вы писали:

D>Коллеги! Мне нужно оптимизировать SELECT запрос, который использует keyword DISTINCT так, чтобы этого кейворда не было, но повторяющиеся записи не выбирались. Подозреваю, что это мона сделать с помощью хитрых join-ов, но не могу их придумать. Мот кто сталкивался с такой проблемой — подскажите пжлст.

Универсальный метод один — использовать вместо DISTINCT GROUP BY.

На деле же необходимость в DISTINCT как правило означает некорректный запрос либо плохо спроектированную базу. В первую очередь надо понять, откуда идет дублирование записей. Достаточно часто это результат некорректной структуры запроса, в который втягиваются лишние записи (которые потом и приходится подавлять).

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

Не совсем очевидна твоя "проблема".
А какой смысл избавится от этого слова. ухо режет ?

Дубли убрать из набора данных без кей слова DISTINCT можно так:

1.
select a,b,c from t
group by a,b,c
2.
select a,b,c from t
union
select a,b,c from t where 1=2
3.
джойном(даже хитрым) имхо это сделать нельзя.

Здравствуйте, Softwarer, Вы писали:

S>Здравствуйте, dvd00, Вы писали:

D>>Коллеги! Мне нужно оптимизировать SELECT запрос, который использует keyword DISTINCT так, чтобы этого кейворда не было, но повторяющиеся записи не выбирались. Подозреваю, что это мона сделать с помощью хитрых join-ов, но не могу их придумать. Мот кто сталкивался с такой проблемой — подскажите пжлст.

S>Универсальный метод один — использовать вместо DISTINCT GROUP BY.

S>На деле же необходимость в DISTINCT как правило означает некорректный запрос либо плохо спроектированную базу. В первую очередь надо понять, откуда идет дублирование записей. Достаточно часто это результат некорректной структуры запроса, в который втягиваются лишние записи (которые потом и приходится подавлять).

S>Если же причина в данных — тут так или иначе надо давить дубли. Постановка задачи заставляет предположить, что этот запрос необходимо втиснуть в прокрустово ложе какого-то стандартного генератора, поэтому ключевой вопрос — а какие еще ограничения?

Оптимизация запроса — это вообще отдельная песня. Насколько я помню, кто-то здесь говорил, что в MS SQL distinct то ли заметно быстрее group by, то ли наоборот. В Oracle, например, они не то что одинаковы — физически одинаково выполняются.

Из общих соображений — надо все-таки посмотреть, из данных ли идет это дублирование, либо из плохо сконструированного запроса. Поскольку бывает, что в запросе оказывается что-нибудь типа select distinct master_id from details. На самом деле я не помню ни одного случая, когда мне приходилось в программе использовать distinct — каждый раз оказывалось, что вместо этого надо аккуратнее написать запрос.

Это можно сделать с помощью EXISTS.

Причем вариант 2 работает значительно быстрее, тк без Distinct нет необходимости в сортировке строк.

Читайте также: