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), ввести новый тип. Но первое никак не сказывается на плане и требует перехода к строковому типу, а со вторым я ещё не эксперементировал.

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

kuka (инкогнито)
27 сентября 2014, 17:56

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

demas (инкогнито)
27 сентября 2014, 18:24

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

kuka (инкогнито)
27 сентября 2014, 19:12

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

Евгений Степанищев (bolknote.ru)
27 сентября 2014, 19:21, ответ предназначен kuka

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

Евгений Степанищев (bolknote.ru)
27 сентября 2014, 19:41, ответ предназначен demas

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

demas (инкогнито)
27 сентября 2014, 20:55

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

demas (инкогнито)
27 сентября 2014, 20:59

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

1. Enum не дает оптимизатор никакой дополнительной информации, по сравнению с тем, что ты скажешь, что хочешь хранить целые числа в диапазоне от 1 до 4. Ну просто, он все равно enum будет хранить как целое.

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

demas (инкогнито)
27 сентября 2014, 21:01

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

Евгений Степанищев (bolknote.ru)
27 сентября 2014, 21:54, ответ предназначен 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 необходим.

Сергей (инкогнито)
28 сентября 2014, 08:44, ответ предназначен Евгений Степанищев (bolknote.ru):

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

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

Евгений Степанищев (bolknote.ru)
28 сентября 2014, 09:29, ответ предназначен Сергею

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

Евгений Степанищев (bolknote.ru)
28 сентября 2014, 09:43, ответ предназначен Сергею

Надеюсь в реальной жизни на диапазон 1-4 вы number не заводите
Исправил, чтобы не смущать народ.

KoTTT (инкогнито)
29 сентября 2014, 06:56, ответ предназначен Евгений Степанищев (bolknote.ru):

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

Евгений Степанищев (bolknote.ru)
29 сентября 2014, 07:12, ответ предназначен KoTTT

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

Евгений Степанищев (bolknote.ru)
29 сентября 2014, 07:45, ответ предназначен KoTTT

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

KoTTT (инкогнито)
29 сентября 2014, 07:46, ответ предназначен Евгений Степанищев (bolknote.ru):

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

alxt (инкогнито)
29 сентября 2014, 09:35

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

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

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

Vladimir Moskva (fulc.ru)
30 сентября 2014, 13:45, ответ предназначен Евгений Степанищев (bolknote.ru):

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

Евгений Степанищев (bolknote.ru)
1 октября 2014, 07:31, ответ предназначен Vladimir Moskva (fulc.ru):

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

Станислав Шабалин (инкогнито)
12 октября 2014, 17:07

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

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

Евгений Степанищев (bolknote.ru)
12 октября 2014, 23:58, ответ предназначен Станиславу Шабалину

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

Ваше имя или адрес блога (можно OpenID):

Текст вашего комментария, не HTML:

Кому бы вы хотели ответить (или кликните на его аватару)