ENUM
Не понимаю, почему в «Оракле» (это СУБД) нет такой полезной вещи как тип ENUM. Вещь совершенно необходимая, как по мне. Вот посмотрите, что понятнее:
CREATE TABLE event_queue (
…
status ENUM('done', 'new', 'failed', 'running'),
…
);или
CREATE TABLE event_queue (
…
status NUMBER(1), -- тут у нас 4 значения будет
…
);Я как-то за первый вариант — всюду сразу видно что означает конкретное поле. Это по-моему, так очевидно, что и обсуждать тут нечего. Вместо этого в запросах появляются либо некие магические константы, либо приходится в них пропихивать что-то именованное из кода, но при этом в выборке всё равно будут те же номера.
Кроме удобства программиста, есть очевидная польза и для самой СУБД. Например, возьмём запрос к таблице выше, где мы определили поле status как число. Мы знаем, что статусов у нас всего четыре, это знаем мы, но это не знает «Оракл». Теперь взглянем на два совершенно эквивалентных с точки зрения программиста запроса (ведь он знает, что значений четыре) и посмотрим планы их выполнения:
SELECT *
FROM event_queue
WHERE status IN (1,2)
AND target='search' AND attempts < 3
 |
SELECT *
FROM event_queue
WHERE status NOT IN (3,4)
AND target='search' AND attempts < 3
 |
Как видите, разница в стоимости аж в три раза. Спрашивается, почему? Ответ очень простой, конечно: в первом случае если status оказался равен единице, вторая проверка уже не нужна, во втором случае, всегда нужны обе проверки. Почему цифры отличаются именно в два раза, а не в три, я не скажу, это, видимо, прикидка, основанная на статистике использования этой таблицы — одно из этих значений (статус «задание провалено») вообще ещё ни разу не записывалось.
Вообще, есть несколько способов имитировать тип ENUM: добавить ограничение (check), ввести новый тип. Но первое никак не сказывается на плане и требует перехода к строковому типу, а со вторым я ещё не эксперементировал.
Обладая Оракл информацией о том, что у нас в этом поле всего четыре возможных значения в этом поле, он мог бы запросто инвертировать значения и снизить стоимость.
Эти вопросы почти полность решаются т. н. «check constraint»:
ALTER TABLE event_queue
ADD CONSTRAINT check_status
CHECK (supplier_name IN (1,2,3,4));
скорее всего это и для оптимизации поможет
Oracle и так обладает информацией о том, сколько реально сейчас значений в этом поле и даже какое примерно распределений количеств записей по этим значениям. И эта информация называется статистикой.
Существуют три вида лжи: ложь, наглая ложь и статистика
constraint позволяет dbms не «гадать» насчет количества уникальных значений, а знать наверняка.
Хотя в любом случае знание о количестве строк имеющих какое-то значение все равно важно
Нет смыса использовать индекс если выберется, например, более 20% строк — дешевле просто все записи отсканировать
Комментарий для kuka:
Вы предпоследний абзац-то прочитайте. Там я как раз про check пишу. План никак не меняется. Опять же, вопрос наглядности это не решает.
Комментарий для demas:
Я знаю как собирается статистика. Вы, видимо, знаете что-то другое. Если да, то объясните, может мне разницу в стоимости этих двух запросов?
Слишком мало данных. Непонятно даже, что за индекс используется в плане. Хорошо бы сравнивать планы без дополнительных условий в WHERE. Хорошо бы посмотреть собранную статистику по таблице и по индексу, ну и заодно убедиться, что она вообще собрана.
Ну, а вообще, можно попробовать рассуждать логично.
И, да, последняя проблема часто встречается на практике, когда делают массовую заливку данных, которая сильно меняет распределение данных в таблице (гистограммы), а статистику не пересобирают.
Комментарий для demas:
Нет, статистика пересобирается в 11 вечера, а со вчерашнего дня с базой никто не работал. Так что статистика тут не причём.
Я не понял с чем вы спорите? С тем, что ENUM не нужен? Я в корне не согласен. Да, в Оракле есть возможность его заменить, но это неудобно, очень неудобно.
Что-то я не понимаю этих рассуждений. Я вам всё расписал, но вы ничего не поняли.
Давайте ещё раз. Если знать, что в столбце status хранятся 4 значения, то условия
и
полностью эквивалентны, тем не менее, планы различаются (даже если навесить на столбец check). Если бы Оракл умел использовать информацию о том, что значений будет только 4, планы не различались бы.
Второй аспект: ENUM нагляднее. Нормальной замены в Оракле этому нет.
Третий аспект: ENUM проще, чем наворачивать check, создавать тип и так далее.
Поэтому считаю, что ENUM необходим.
Комментарий для Евгения Степанищева:
В упор не вижу, где планы отличаются. То, что отличается cost совершенно не важно, так как его имеет смысл сравнивать только в рамках разных планов одного запроса.
Если запросы разные, пусть и дающие одинаковый результат, то увы, только мерять.
P.S. Надеюсь в реальной жизни на диапазон 1-4 вы number не заводите. Слишком жирно.
P.P.S. А при использовании bind переменных оптимизатору становится ещё сложнее. Хотя Oracle научился строить разные планы при разных значениях переменных, но штука это рулеточная.
Комментарий для Сергей:
Да, я имею ввиду cost, конечно, пришу в спешке. Что даёт нам вывод, что ENUM мог бы дать Ораклу необходимую информацию и стоимость была бы одинаковой. Впрочем, ничего не мешало бы ему и check использовать (а мы видим, что он этого не умеет), но ENUM всяко удобнее заводить.
Нет, конечно.
Комментарий для Сергей:
Исправил, чтобы не смущать народ.
Комментарий для Евгения Степанищева:
Для более полной картины необходимо увидеть планы в формате DBMS_XPLAN (с предикатами).
А еще уточните, поле status у вас объявлено как nullable?
Комментарий для KoTTT:
Полная картина не поможет вам меня убедить, что огород с check и собственными типами лучше ENUM.
Комментарий для KoTTT:
Мне ведь не нужна помощь с запросом. Я про удобство ENUM рассказываю.
Комментарий для Евгения Степанищева:
Да, возможно, наличие enum принесло бы некоторые удобства для нас, программистов.
А с точки зрения оракла check + not null + актуальная статистика фактически дает для производительности то, о чем вы писали в первом посте («очевидная польза и для самой СУБД»).
Not null важен именно для NOT IN.
Ну нет, и нет. Да, эмуляция через таблицу- не самое красивое решение. Но меня вот ограничение varchar2 в 4000 больше напрягало всегда.
Вообще постоянно возникает конфликт между хорошо проработанным SQL с его реляционностью, и потребностью в ООП-БД. Как только добавляют новые фишки (типа object и pipelined в оракле) — сразу возникают проблемы, баги, тормоза. Потому и цветут буйным цветом ORM, где свои проблемы :)
В данном случае, если бизнес-логика в ООП-коде, можно просто ввести строковое поле, индекс по нему и писать туда name() из Enum’а (это java, но в php тоже ж что-то есть?).
Да, дятел (записавший в это поле левую фигню) разрушит цивилизацию, но это риск понятный и можно решить constraint’м, триггером, увольнением :D
Комментарий для Евгения Степанищева:
Может, «в три раза, а не полтора»?
Комментарий для fulc.ru:
В худшем случае число проверок в два раза больше.
В свое время столкнулись с неудобством ENUM’ов на нагруженном проекте с большой базой (правда, это был MySQL).
Дело в том, что при изменении (обычно это добавлении значения) в ENUM приходится делать ALTER, а это на большой базе дорого и требует особых телодвижений. В случае с INT ничего делать не нужно — достаточно выкатить новый код.
Комментарий для Станислав Шабалин:
Ровно до случая, пока числа влезают в INT. У всех типов одинаковые проблемы. Только в случае использования INT вместо ENUM без документации даже примерно неясно что значит какое-нибудь «9» в ячейке.