Битовое поле и select

[andrew.k]

Подскажите как мне сделать чтобы я значения битового поля мог использовать в запросе select в секции where.
Как создать такое битовое поле? Как использовать?

Поясню для чего мне это нужно. Возможно есть другой способ.
Есть журнал таблица
create table events
{
id bigint,
category int,
code int,
timestamp timestamp,
uiser+id bigint,
misc varchar
);
Туда записываются события. У события есть категория.
В общем виде событие может принадлежать к двум или более категориям. Сейчас для одного события приходится вставлять в журнал две записи для двух разных категорий (с разным значением category).
Можно ли это сделать как-то через битовую маску. Т.е. например,
псевдо SQL:
insert into events(code,category) values( 15, 110b ); // что означает, что событие добавлено и принадлежит двум категориям.

select from events where category & 010b; // выбрать все, которые принадлежать второй категории.

Возможно ли нечто подобное? Будет ли это эффективно? Или лучше так и оставить несколько записей для одного события?
Еще вариант который приходит в голову, это добавить несколько bool полей для каждой категории (system bool, admin bool, user bool ...). Что думаете о таком варианте?
Напомню, так как это журнал, записей в таблице очень много, при этом ждать загрузки журнала 10 минут - не есть хорошо.
Т.е. эффективность выборки, все-таки самый приоритетный критерий.

[andrew.k]

нашел в документации тип данных bit.
Удалось реализовать то, что мне нужно вот так.
create table test(cat bit(3), text varchar);

insert into test values( b'101','blablabla');
insert into test values( b'110','karamba');

select * from test where cat & b'100' = b'100';
не смог придумать, как короче записать условие.
нашел функцию get_bit, но не смог воспользоваться, точнее смог, но не применительно к этому решению, так как она работает с типом bytea.

Что думаете о таком решении? Будет ли такой запрос выполняться быстро например на 10М записей?
Или лучше сделать 5 полей bool?

[MoPDoBoPoT]

Пойти классическим путём, а именно - ввести вспомогательную сущность (промежуточную таблицу), ведь здесь используется связь "многие ко многим". Для увеличения скорости выборки, как правило, ещё строят индексы по внешним ключа.
Но для начала советую ознакомиться с основами проектирования реляционных БД.

[andrew.k]

Пойти классическим путём, а именно - ввести вспомогательную сущность (промежуточную таблицу), ведь здесь используется связь "многие ко многим". Для увеличения скорости выборки, как правило, ещё строят индексы по внешним ключа.
Но для начала советую ознакомиться с основами проектирования реляционных БД.

Т.е. ты предлагаешь сделать, таблицу с категориями и дополнительную таблицу, которая будет связывать каждое событие из таблицы events с категориями. Так как категорий у меня 5-6 и каждое событие может принадлежать к от 1 до 3-4 категорий, то соответственно имея 10М записей в таблице events "в нагрузку" получаем еще в среднем 2-3М записей во вспомогательной таблице, которые будут связаны "многие ко многим" и это ускорит процесс выборки? ты действительно так думаешь? и предлагаешь мне ознакомиться с основами проектирования для начала?
А делая селект СУБД будет формировать матрицу записей (10М * 3М * 5(6) ) из которой будет выбирать по заданному условию. Это оптимальный способ?

Т.е. вместо 10М записей и относительно (!) трудоемкой операции сравнения битовых строк, мы получаем 150М(180М) (усредненно) записей и все так же относительно простой критерий отбора записей.

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

Теперь поправьте меня, если  ошибаюсь и скажи мне, Павлик, это действительно оптимально?

[andrew.k]

нашел на эту тему небольшое обсуждение, оно меня склонило все-таки к использованию 5-6 bool полей вместо битовых строк.
http://www.sql.ru/Forum/actualthread.aspx?bid=36&tid=217405&hl=
Выскажитесь уже хоть кто-нибудь толково.

[andrew.k]

отсюда еще один небольшой вопрос, есть ли смысл вместо 5 полей bool использовать 5 полей bit(1).
Это что-нибудь улучшит или ухудшит?
Где-то прочитал, что bit(1) будет все равно храниться как байт.

[MoPDoBoPoT]

Теряется гибкость, наглядность.
Расчёты у тебя хитрые какие-то получились 
В моём случае ты считал соединение с категориями, а в своём нет. Тем более для уменьшения объёма соединяемых таблиц можно использовать селекцию в подзапросе, также для оптимизации есть индексы (выносимые на другой ЖД), кластеры.
Ты явно промахнулся форумом, как минимум, веткой. С такими вопросами тебе на http://www.sql.ru , но итоговое решение было бы неплохо тут описать, думаю многим интересно 

[andrew.k]

Пересчитай правильно и укажи на ошибку.
Потому что в моем варианте, категории являются полями записи events (таблица только одна).
а у тебя это три разные таблицы.
Пока сделал через 5 полей bool
Это просто, удобно, наглядно и более-менее эффективно.
селект выглядит так:
id code comment acsystem acuser acadmin acnsd acmessage
12 40 'sometext' true true false false false
13 26 'sometext' false true false true false
Единственный способ это оптимизировать, это сэкономить немного места. Это допустить что каждый код события всегда состоит только в определенных группах, поэтому можно сделать отдельную таблицу.
codecategories
(
  int code;
  int cat;
);

В этой таблице будет немного записей например 80 (столько всего типов событий ) * 3-4 (в стольки категориях может состоять событие, опять же в среднем) = 240(320)
Поэтому имеем 10М * 240(320) = 2400М(3200)М. Хохохо. Как раз в этом месте я нашел ошибку в своих расчетах
10М * 3М * 5 = это не 150М это 150 М*М = очень много гораздо больше чем 2400М

При этом мы теряем наглядность. А нужна ли она?
Получаем выгоду в 50Мб ( 5 байт на булы * 10М записей). не смешно ли? Всем наплевать на 50Мб.
Усложненный запрос выбора, за счет дополнительной таблицы.
Коды событий расшифровываются в программе, поэтому, для постороннего человека, эти две загадочные таблицы вообще в ступор вгонят
В общем плюсов мало, минусы есть. Если не видно разницы, зачем платить больше? Остановился на булах.

[andrew.k]

Почему отказался от битовых строк.
1. Потому что запросы формировать неудобно. int в программе надо будет приводить к sql строковому представлению b'1010101'.
2. такие запросы будут выполняться не быстрее сравнения трех bool полей (это имхо, я могу ошибаться)
3. на одном форуме один умный человек написал примерно следующее: "битовые строки нужно использовать, когда эти битовые строки откуда-то поступают, и собственно кладутся в базу в неизменном виде, т.е. просто хранятся там, как значение наряду с другими". Собственно он прав. Если нужно хранить битовую строку, пожалуйста, есть такая возможность - храни. И все. Использовать значение отдельных битов для запросов, чересчур неэффективно и неудобно и прочее и прочее.
Не надо так делать
4. по сравнению с 5 булами никаких плюсов, кроме как занимаемое место в 50Мб. Хотя тоже не известно сколько места займет эти самые загадочные bit(5). Кто знает?

ЗЫ. потому что я считаю, что здесь сидят достаточно грамотные люди, поэтому пишу сюда. Зачем мне куда-то еще ходить.
Бывают конечно, отдельные случаи, когда кто-то пишет для того, чтобы отметиться, а не по делу. Дают "дельные советы". но это пустяк. Обычно этим занимаются дети и ущербные взрослые. Поэтому можно не обращать на это внимание

[MoPDoBoPoT]

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

Ну так я думал описания категорий где-то должен быть, а у тебя полный хардкод. Тогда и в моё случае треться таблица не нужна. Ещё ты не учёл то, что в исходной таблице для одного события было несколько записей, т.е. она сокращается в среднем в 2-3 раза, из-за того, что категории выносятся. Ещё я писал, что при соединении объём вспомогательной таблицы уменьшается с помощью указания условия выборки категорий в подзапосе. Но дело не в расчетах... Поразил тот пафос, с которым ты ответил. Ещё и нахомить пытался 
Думаю из-за обиды на то, что я послал тебя изучать основы проектирования БД. Но откуда мне знать твой уровень знаний? Тут бывают такие чайники пишут. Тем более в первом посте, по-моему, такой велосипед...

По поводу оптимизации. Ты пока только уменьшил объем данных, но для поиска у тебя по-прежнему используется полное чтение. Для увеличения скорости доступа к данным (по условию выборки) используют внешние структуры: индексы, кластеры...

Пока сделал через 5 полей bool

По-моему для этого случая подойдёт bitmap-индекс, если СУБД поддерживает.

ЗЫ. потому что я считаю, что здесь сидят достаточно грамотные люди, поэтому пишу сюда. Зачем мне куда-то еще ходить.

Грамотные-неграмотные, а базы данных - это целый "мир". Там куча тонкостей, с которыми знакомы не все. На http://www.sql.ru сидят люди как раз со специализиованными знаниями и, главное, с опытом (имхо).
В общем, бывай.

[andrew.k]

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

Ну так я думал описания категорий где-то должен быть, а у тебя полный хардкод. Тогда и в моё случае треться таблица не нужна. Ещё ты не учёл то, что в исходной таблице для одного события было несколько записей, т.е. она сокращается в среднем в 2-3 раза, из-за того, что категории выносятся. Ещё я писал, что при соединении объём вспомогательной таблицы уменьшается с помощью указания условия выборки категорий в подзапосе. Но дело не в расчетах... Поразил тот пафос, с которым ты ответил. Ещё и нахомить пытался 
Думаю из-за обиды на то, что я послал тебя изучать основы проектирования БД. Но откуда мне знать твой уровень знаний? Тут бывают такие чайники пишут. Тем более в первом посте, по-моему, такой велосипед...

По поводу оптимизации. Ты пока только уменьшил объем данных, но для поиска у тебя по-прежнему используется полное чтение. Для увеличения скорости доступа к данным (по условию выборки) используют внешние структуры: индексы, кластеры...

Пока сделал через 5 полей bool

По-моему для этого случая подойдёт bitmap-индекс, если СУБД поддерживает.

ЗЫ. потому что я считаю, что здесь сидят достаточно грамотные люди, поэтому пишу сюда. Зачем мне куда-то еще ходить.

Грамотные-неграмотные, а базы данных - это целый "мир". Там куча тонкостей, с которыми знакомы не все. На http://www.sql.ru сидят люди как раз со специализиованными знаниями и, главное, с опытом (имхо).
В общем, бывай.

В каком месте ты увидел пафос? я всегда предельно вежлив. Предел определяется обстоятельствами
А вот не разобравшись толком в вопросе, ты оставил бесполезный для дела пост. "Иди учись, мол".
Вот если бы ты сразу написал, про битмап индекс, думаешь, я бы тебе сказал чего кроме спасибо, если бы это помогло?
Что это и как это использовать, если не лень отпиши кратко, чтобы не тратить зря время. Чтобы понять подойдет мне это или нет.

[andrew.k]

читаю потихоньку.
СУБД Postgresql 7.4 битовые строки в нем есть. А вот есть ли эти bitmap-индексы?

[andrew.k]

в принципе нашел кое-что.
Т.е. я просто создаю индекс для поля code.
create index idx_events on events using btree (code);