Это сайт — моя персональная записная книжка. Интересна мне, по большей части, история, своя жизнь и немного программирование.

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

 

План №1 (17.65КиБ)

 

SELECT *
FROM event_queue
WHERE status NOT IN (3,4)
AND target='search' AND attempts < 3

 

План №2 (17.77КиБ)

Как видите, разница в стоимости аж в три раза. Спрашивается, почему? Ответ очень простой, конечно: в первом случае если status оказался равен единице, вторая проверка уже не нужна, во втором случае, всегда нужны обе проверки. Почему цифры отличаются именно в два раза, а не в три, я не скажу, это, видимо, прикидка, основанная на статистике использования этой таблицы — одно из этих значений (статус «задание провалено») вообще ещё ни разу не записывалось.

Вообще, есть несколько способов имитировать тип ENUM: добавить ограничение (check), ввести новый тип. Но первое никак не сказывается на плане и требует перехода к строковому типу, а со вторым я ещё не эксперементировал.

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

21 комментарий
kuka 2014

Эти вопросы почти полность решаются т. н. «check constraint»:
ALTER TABLE event_queue
ADD CONSTRAINT check_status
  CHECK (supplier_name IN (1,2,3,4));
скорее всего это и для оптимизации поможет

demas 2014

Обладая Оракл информацией о том, что у нас в этом поле всего четыре возможных значения в этом поле

Oracle и так обладает информацией о том, сколько реально сейчас значений в этом поле и даже какое примерно распределений количеств записей по этим значениям. И эта информация называется статистикой.

kuka 2014

Существуют три вида лжи: ложь, наглая ложь и статистика
constraint позволяет dbms не «гадать» насчет количества уникальных значений, а знать наверняка.
Хотя в любом случае знание о количестве строк имеющих какое-то значение все равно важно
Нет смыса использовать индекс если выберется, например, более 20% строк — дешевле просто все записи отсканировать

Евгений Степанищев (bolknote.ru) 2014

Комментарий для kuka:

Вы предпоследний абзац-то прочитайте. Там я как раз про check пишу. План никак не меняется. Опять же, вопрос наглядности это не решает.

Евгений Степанищев (bolknote.ru) 2014

Комментарий для demas:

Я знаю как собирается статистика. Вы, видимо, знаете что-то другое. Если да, то объясните, может мне разницу в стоимости этих двух запросов?

demas 2014

Слишком мало данных. Непонятно даже, что за индекс используется в плане. Хорошо бы сравнивать планы без дополнительных условий в WHERE. Хорошо бы посмотреть собранную статистику по таблице и по индексу, ну и заодно убедиться, что она вообще собрана.

demas 2014

Ну, а вообще, можно попробовать рассуждать логично.

  1. Enum не дает оптимизатор никакой дополнительной информации, по сравнению с тем, что ты скажешь, что хочешь хранить целые числа в диапазоне от 1 до 4. Ну просто, он все равно enum будет хранить как целое.
  1. Даже информация о том, что ты планируешь хранить в этом поле целые числа от 1 до 4 сама по себе бесполезна. Важно, что там на самом деле хранится. Одно дело, если на долю каждого из чисел приходится 25% записей — есть смысл использовать индекс. Другое дело, если у тебя 1-ка хранится в 99% записей и проще уж сразу делать table scan.
demas 2014

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

Евгений Степанищев (bolknote.ru) 2014

Комментарий для demas:

Нет, статистика пересобирается в 11 вечера, а со вчерашнего дня с базой никто не работал. Так что статистика тут не причём.

Я не понял с чем вы спорите? С тем, что ENUM не нужен? Я в корне не согласен. Да, в Оракле есть возможность его заменить, но это неудобно, очень неудобно.

Даже информация о том, что ты планируешь хранить в этом поле целые числа от 1 до 4 сама по себе бесполезна. Важно, что там на самом деле хранится. Одно дело, если на долю каждого из чисел приходится 25% записей — есть смысл использовать индекс. Другое дело, если у тебя 1-ка хранится в 99% записей и проще уж сразу делать table scan.

Что-то я не понимаю этих рассуждений. Я вам всё расписал, но вы ничего не поняли.

Давайте ещё раз. Если знать, что в столбце status хранятся 4 значения, то условия

status IN (1,2)

и

status NOT IN (3,4)

полностью эквивалентны, тем не менее, планы различаются (даже если навесить на столбец check). Если бы Оракл умел использовать информацию о том, что значений будет только 4, планы не различались бы.

Второй аспект: ENUM нагляднее. Нормальной замены в Оракле этому нет.

Третий аспект: ENUM проще, чем наворачивать check, создавать тип и так далее.

Поэтому считаю, что ENUM необходим.

Сергей 2014

Комментарий для Евгения Степанищева:

В упор не вижу, где планы отличаются. То, что отличается cost совершенно не важно, так как его имеет смысл сравнивать только в рамках разных планов одного запроса.
Если запросы разные, пусть и дающие одинаковый результат, то увы, только мерять.

 P.S. Надеюсь в реальной жизни на диапазон 1-4 вы number не заводите. Слишком жирно.
P.P.S. А при использовании bind переменных оптимизатору становится ещё сложнее. Хотя Oracle научился строить разные планы при разных значениях переменных, но штука это рулеточная.

Евгений Степанищев (bolknote.ru) 2014

Комментарий для Сергей:

Да, я имею ввиду cost, конечно, пришу в спешке. Что даёт нам вывод, что ENUM мог бы дать Ораклу необходимую информацию и стоимость была бы одинаковой. Впрочем, ничего не мешало бы ему и check использовать (а мы видим, что он этого не умеет), но ENUM всяко удобнее заводить.

P. S. Надеюсь в реальной жизни на диапазон 1-4 вы number не заводите. Слишком жирно.

Нет, конечно.

Евгений Степанищев (bolknote.ru) 2014

Комментарий для Сергей:

Надеюсь в реальной жизни на диапазон 1-4 вы number не заводите

Исправил, чтобы не смущать народ.

KoTTT 2014

Комментарий для Евгения Степанищева:

Для более полной картины необходимо увидеть планы в формате DBMS_XPLAN (с предикатами).
А еще уточните, поле status у вас объявлено как nullable?

Евгений Степанищев (bolknote.ru) 2014

Комментарий для KoTTT:

Полная картина не поможет вам меня убедить, что огород с check и собственными типами лучше ENUM.

Евгений Степанищев (bolknote.ru) 2014

Комментарий для KoTTT:

Мне ведь не нужна помощь с запросом. Я про удобство ENUM рассказываю.

KoTTT 2014

Комментарий для Евгения Степанищева:

Да, возможно, наличие enum принесло бы некоторые удобства для нас, программистов.
А с точки зрения оракла check + not null + актуальная статистика фактически дает для производительности то, о чем вы писали в первом посте («очевидная польза и для самой СУБД»).
Not null важен именно для NOT IN.

alxt 2014

Ну нет, и нет. Да, эмуляция через таблицу- не самое красивое решение. Но меня вот ограничение varchar2 в 4000 больше напрягало всегда.

Вообще постоянно возникает конфликт между хорошо проработанным SQL с его реляционностью, и потребностью в ООП-БД. Как только добавляют новые фишки (типа object и pipelined в оракле) — сразу возникают проблемы, баги, тормоза. Потому и цветут буйным цветом ORM, где свои проблемы :)

В данном случае, если бизнес-логика в ООП-коде, можно просто ввести строковое поле, индекс по нему и писать туда name() из Enum’а (это java, но в php тоже ж что-то есть?).
Да, дятел (записавший в это поле левую фигню) разрушит цивилизацию, но это риск понятный и можно решить constraint’м, триггером, увольнением :D

Vladimir Moskva (fulc.ru) 2014

Комментарий для Евгения Степанищева:

Почему цифры отличаются именно в два раза, а не в три

Может, «в три раза, а не полтора»?

Евгений Степанищев (bolknote.ru) 2014

Комментарий для fulc.ru:

В худшем случае число проверок в два раза больше.

Станислав Шабалин 2014

В свое время столкнулись с неудобством ENUM’ов на нагруженном проекте с большой базой (правда, это был MySQL).

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

Евгений Степанищев (bolknote.ru) 2014

Комментарий для Станислав Шабалин:

В случае с INT ничего делать не нужно — достаточно выкатить новый код.

Ровно до случая, пока числа влезают в INT. У всех типов одинаковые проблемы. Только в случае использования INT вместо ENUM без документации даже примерно неясно что значит какое-нибудь «9» в ячейке.