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), ввести новый тип. Но первое никак не сказывается на плане и требует перехода к строковому типу, а со вторым я ещё не эксперементировал.
Обладая Оракл информацией о том, что у нас в этом поле всего четыре возможных значения в этом поле, он мог бы запросто инвертировать значения и снизить стоимость.
Комментарии 21
ALTER TABLE event_queue
ADD CONSTRAINT check_status
CHECK (supplier_name IN (1,2,3,4));
скорее всего это и для оптимизации поможет
Oracle и так обладает информацией о том, сколько реально сейчас значений в этом поле и даже какое примерно распределений количеств записей по этим значениям. И эта информация называется статистикой.
constraint позволяет dbms не «гадать» насчет количества уникальных значений, а знать наверняка.
Хотя в любом случае знание о количестве строк имеющих
Нет смыса использовать индекс если выберется, например, более 20% строк — дешевле просто все записи отсканировать
Вы предпоследний
1. Enum не дает оптимизатор никакой дополнительной информации, по сравнению с тем, что ты скажешь, что хочешь хранить целые числа в диапазоне от 1 до 4. Ну просто, он все равно enum будет хранить как целое.
2. Даже информация о том, что ты планируешь хранить в этом поле целые числа от 1 до 4 сама по себе бесполезна. Важно, что там на самом деле хранится. Одно дело, если на долю каждого из чисел приходится 25% записей — есть смысл использовать индекс. Другое дело, если у тебя
Комментарий для demas:
Нет, статистика пересобирается в 11 вечера, а со вчерашнего дня с базой никто не работал. Так что статистика тут не причём.
Я не понял с чем вы спорите? С тем, что ENUM не нужен? Я в корне не согласен. Да, в Оракле есть возможность его заменить, но это неудобно, очень неудобно.
Давайте ещё раз. Если знать, что в столбце status хранятся 4 значения, то условия
и
полностью эквивалентны, тем не менее, планы различаются (даже если навесить на столбец check). Если бы Оракл умел использовать информацию о том, что значений будет только 4, планы не различались бы.
Второй аспект: ENUM нагляднее. Нормальной замены в Оракле этому нет.
Третий аспект: ENUM проще, чем наворачивать check, создавать тип и так далее.
Поэтому считаю, что ENUM необходим.
В упор не вижу, где планы отличаются. То, что отличается cost совершенно не важно, так как его имеет смысл сравнивать только в рамках разных планов одного запроса.
Если запросы разные, пусть и дающие одинаковый результат, то увы, только мерять.
P.S. Надеюсь в реальной жизни на диапазон
P.P.S. А при использовании bind переменных оптимизатору становится ещё сложнее. Хотя Oracle научился строить разные планы при разных значениях переменных, но штука это рулеточная.
Да, я имею ввиду cost, конечно, пришу в спешке. Что даёт нам вывод, что ENUM мог бы дать Ораклу необходимую информацию и стоимость была бы одинаковой. Впрочем, ничего не мешало бы ему и check использовать (а мы видим, что он этого не умеет), но ENUM всяко удобнее заводить.
Нет, конечно.
Исправил, чтобы не смущать народ.
Для более полной картины необходимо увидеть планы в формате DBMS_XPLAN (с предикатами).
А еще уточните, поле status у вас объявлено как nullable?
Да, возможно, наличие enum принесло бы некоторые удобства для нас, программистов.
А с точки зрения оракла check + not null + актуальная статистика фактически дает для производительности то, о чем вы писали в первом посте («очевидная польза и для самой СУБД»).
Not null важен именно для NOT IN.
Вообще постоянно возникает конфликт между хорошо проработанным SQL с его реляционностью, и потребностью в
В данном случае, если
Да, дятел (записавший в это поле левую фигню) разрушит цивилизацию, но это риск понятный и можно решить constraint'м, триггером, увольнением :D
Комментарий для Евгения Степанищева:
Может, «в три раза, а не полтора»?
Дело в том, что при изменении (обычно это добавлении значения) в ENUM приходится делать ALTER, а это на большой базе дорого и требует особых телодвижений. В случае с INT ничего делать не нужно — достаточно выкатить новый код.
Ровно до случая, пока числа влезают в INT. У всех типов одинаковые проблемы. Только в случае использования INT вместо ENUM без документации даже примерно неясно что значиткакое-нибудь «9» в ячейке.