Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Запрос к вертикальной таблице - все авто красного цвета  [new]
ХБ
Member

Откуда: USA
Сообщений: 1009
Наверное, такие вопросы на интервью задают...но хотелось бы услышать мнение коллективного разума.
Скажем, база данных по всем автомобилям.
Каждый автомобиль описывается, например, 1000-ю элементами, в виде имя_элемента - значение.
Имеем вертикальную таблицу с тремя колонками: id, element_name, element_value
id это уникальный идентификатор автомобиля, и дальше имя и значение всех элементов этого автомобиля.
Количество элементов на автомобиль может быть разным, но общий набор всех возможных элементов составляет, скажем, тысячу значений. Более того, элементы могут со временем (скажем, раз в месяц) добавляться или убираться в том числе и в этот базовый набор.
Например, элементы:
цвет
пробег
год выпуска
наличие кондиционера
и пр.
И запрос: найти все автомобили красного цвета с пробегом не больше 100000км года выпуска 1995 с кондиционером.
Получается, self-join таблицы 4 раза.
А если нужно отфильтровать по комбинации 100 элементов, то self-join 100 раз.
Размер таблицы, скажем, 50 000 000 записей.

Какие способы могут быть для улучшения таких запросов?
Например, у нас созданы pivot view вида ряд - id автомобиля и колонка - имя элемента.
А еще что-то можно предложить?
24 авг 16, 00:30    [19582150]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к вертикальной таблице - все авто красного цвета  [new]
ХБ
Member

Откуда: USA
Сообщений: 1009
погуглив, нашел хорошее определение этой проблемы - 'name-value curse', с общей рекомендацией - 'don't do it!'
Но как, черт побери, хранить данные об объектах с произвольным числом аттрибутов??
24 авг 16, 01:05    [19582212]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к вертикальной таблице - все авто красного цвета  [new]
пивот животворящий
Guest
ХБ
Какие способы могут быть для улучшения таких запросов?
Базу ВСЕХ автомобилей так не хранят. Только базу школьного автобуса.
24 авг 16, 01:06    [19582216]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к вертикальной таблице - все авто красного цвета  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
ХБ,

group by+having. А вообще имхо лучше уж в json или xml хранить и искать например так:
create table car_elems (id int primary key,
                        elems clob constraint valid_json check (elems is json)
                       )
/
insert into car_elems values(1,'{"color":"red",
                                 "transmission":"automatic",
                                 "mileage":30000,
                                 "year":2013
                                 }'
);
insert into car_elems values(2,'{"color":"blue",
                                 "transmission":"automatic",
                                 "mileage":50000,
                                 "year":2010
                                 }'
);

select *
from car_elems e
where e.elems.mileage<40000
  and e.elems.year>2012
24 авг 16, 01:09    [19582217]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к вертикальной таблице - все авто красного цвета  [new]
ХБ
Member

Откуда: USA
Сообщений: 1009
пивот животворящий
ХБ
Какие способы могут быть для улучшения таких запросов?
Базу ВСЕХ автомобилей так не хранят. Только базу школьного автобуса.

почему не хранят?
Например, база данных железнодорожных вагонов. Фирма владеет скажем полумиллионом вагонов и должна учитывать их техническое состояние.
24 авг 16, 01:53    [19582249]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к вертикальной таблице - все авто красного цвета  [new]
ХБ
Member

Откуда: USA
Сообщений: 1009
xtender
ХБ,

group by+having. А вообще имхо лучше уж в json или xml хранить и искать например так:
create table car_elems (id int primary key,
                        elems clob constraint valid_json check (elems is json)
                       )
/
insert into car_elems values(1,'{"color":"red",
                                 "transmission":"automatic",
                                 "mileage":30000,
                                 "year":2013
                                 }'
);
insert into car_elems values(2,'{"color":"blue",
                                 "transmission":"automatic",
                                 "mileage":50000,
                                 "year":2010
                                 }'
);

select *
from car_elems e
where e.elems.mileage<40000
  and e.elems.year>2012

т.е. по сути слить(конкатенировать) все пары элемент-value в одну строку и искать любым из существующих парсингов текстовой строки, так?
24 авг 16, 01:59    [19582255]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к вертикальной таблице - все авто красного цвета  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
ХБ,

нет, просто подсчитать кол-во совпадений
select id
from car_elems 
where 
   (element_name = 'color'   and element_value = 'red')
or (element_name = 'mileage' and to_number(element_value) < 100000)
or (element_name = 'year'    and to_number(element_value) >= 1995)
group by id
having count(*)=3
24 авг 16, 02:06    [19582258]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к вертикальной таблице - все авто красного цвета  [new]
ХБ
Member

Откуда: USA
Сообщений: 1009
xtender
ХБ,

нет, просто подсчитать кол-во совпадений
select id
from car_elems 
where 
   (element_name = 'color'   and element_value = 'red')
or (element_name = 'mileage' and to_number(element_value) < 100000)
or (element_name = 'year'    and to_number(element_value) >= 1995)
group by id
having count(*)=3

O!
круто, спасибки.
24 авг 16, 02:19    [19582264]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к вертикальной таблице - все авто красного цвета  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54383
ХБ
xtender
ХБ,

нет, просто подсчитать кол-во совпадений
select id
from car_elems 
where 
   (element_name = 'color'   and element_value = 'red')
or (element_name = 'mileage' and to_number(element_value) < 100000)
or (element_name = 'year'    and to_number(element_value) >= 1995)
group by id
having count(*)=3

O!
круто, спасибки.
намберы-даты лучше в отдельных колонках
чтобы не спрашивали, откуда 01722

и уникальность не забыть
24 авг 16, 07:20    [19582387]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к вертикальной таблице - все авто красного цвета  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34630
Размер таблицы, скажем, 50 000 000 записей.

маловато будет, у нас столько только самих автомобилей, а тут надо еще на 10-20 умножить.



Какие способы могут быть для улучшения таких запросов?

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

более ничего не нужно.
24 авг 16, 08:15    [19582485]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к вертикальной таблице - все авто красного цвета  [new]
ы!
Guest
MasterZiv


Какие способы могут быть для улучшения таких запросов?

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

более ничего не нужно.

Если таблица более менее статична - сделать bitmap индексы?
24 авг 16, 09:06    [19582603]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к вертикальной таблице - все авто красного цвета  [new]
Shtock
Member

Откуда: СПб
Сообщений: 3049
to xtender: фиг знает, я бы поостерёгся юзать в оракле json для чего-то сложнее чем хранение конфигов. он там очень сырой. быстрый только с индексами, но они сурово глючат (например, там тупо не работает or). более того, они уже сделали 2 json bundle fix ибо куча багов, но они конфликтуют с другими патчами, мы им об этом написали и oracle слёзно божится, что мол всё исправят, но за 2 недели ничего не произошло :(
24 авг 16, 09:37    [19582725]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к вертикальной таблице - все авто красного цвета  [new]
надо пробовать
Guest
имхо, если физически кластеризовать все items вокруг id то будет польза.
да, вставка будет тупить
24 авг 16, 10:36    [19583015]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к вертикальной таблице - все авто красного цвета  [new]
ХБ
Member

Откуда: USA
Сообщений: 1009
xtender
ХБ,

нет, просто подсчитать кол-во совпадений
select id
from car_elems 
where 
   (element_name = 'color'   and element_value = 'red')
or (element_name = 'mileage' and to_number(element_value) < 100000)
or (element_name = 'year'    and to_number(element_value) >= 1995)
group by id
having count(*)=3

давно поднимал этот вопрос, получил от xtender дельный совет и вот пришло время это реализовать.
И сразу вылезла бяка, может быть ув.xtender опять подскажет?
Бяка такая: оказалось, element_name и element_value для данного id _не уникальны_.
т.е. для какого-то id есть:
одна запись с element_name = 'color' and element_value = 'red';
две(!) записи с element_name = 'mileage' and to_number(element_value) < 100000
и
ни одной(!!) записи с element_name = 'year' and to_number(element_value) >= 1995
А в сумме получается 3 и этот id считается удовлетворившим условию, хотя это не так.

Есть какой-нибудь trick это обойти?
10 фев 17, 23:46    [20202237]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к вертикальной таблице - все авто красного цвета  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
ХБ,


+ допустим у тебя есть такая таблица
create table car_elems (
  id            int          not null, 
  element_name  varchar2(30) not null, 
  element_value varchar2(30) not null
)
/
insert into car_elems(id,element_name,element_value) values(1,'color','red');
insert into car_elems(id,element_name,element_value) values(1,'mileage','100');
insert into car_elems(id,element_name,element_value) values(1,'year','2010');

insert into car_elems(id,element_name,element_value) values(2,'color','red');
insert into car_elems(id,element_name,element_value) values(2,'mileage','100');
insert into car_elems(id,element_name,element_value) values(2,'mileage','200');

insert into car_elems(id,element_name,element_value) values(3,'color','red');
insert into car_elems(id,element_name,element_value) values(3,'mileage','100');
commit;


Вариант 1. Это тупо повесить уникальный ключ на id,element_name.
+ Вариант 2. Чисто SQL:
select id
from car_elems 
where 
   (element_name = 'color'   and decode(element_name,'color'  ,element_value           ) = 'red')
or (element_name = 'mileage' and decode(element_name,'mileage',to_number(element_value)) < 100000)
or (element_name = 'year'    and decode(element_name,'year'   ,to_number(element_value)) >= 1995)
group by id
having count(distinct element_name)=3

+ Вариант 3. Виртуальные столбцы
alter table car_elems add 
    v_color   /* invisible */ generated always as (decode(element_name,'color'  ,element_value           ));
alter table car_elems add 
    v_mileage /* invisible */ generated always as (decode(element_name,'mileage',to_number(element_value)));
alter table car_elems add 
    v_year    /* invisible */ generated always as (decode(element_name,'year'   ,to_number(element_value)));
    
select 
  id
from car_elems
where v_color = 'red' or v_mileage < 100000 or v_year >= 1995
group by id
having count(distinct element_name)=3
11 фев 17, 00:09    [20202269]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к вертикальной таблице - все авто красного цвета  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
забыл добавить, что лучше раскомментировать invisible если ты на 12с
11 фев 17, 00:10    [20202270]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к вертикальной таблице - все авто красного цвета  [new]
ХБ
Member

Откуда: USA
Сообщений: 1009
xtender
ХБ,


+ допустим у тебя есть такая таблица
create table car_elems (
  id            int          not null, 
  element_name  varchar2(30) not null, 
  element_value varchar2(30) not null
)
/
insert into car_elems(id,element_name,element_value) values(1,'color','red');
insert into car_elems(id,element_name,element_value) values(1,'mileage','100');
insert into car_elems(id,element_name,element_value) values(1,'year','2010');

insert into car_elems(id,element_name,element_value) values(2,'color','red');
insert into car_elems(id,element_name,element_value) values(2,'mileage','100');
insert into car_elems(id,element_name,element_value) values(2,'mileage','200');

insert into car_elems(id,element_name,element_value) values(3,'color','red');
insert into car_elems(id,element_name,element_value) values(3,'mileage','100');
commit;


Вариант 1. Это тупо повесить уникальный ключ на id,element_name.
+ Вариант 2. Чисто SQL:
select id
from car_elems 
where 
   (element_name = 'color'   and decode(element_name,'color'  ,element_value           ) = 'red')
or (element_name = 'mileage' and decode(element_name,'mileage',to_number(element_value)) < 100000)
or (element_name = 'year'    and decode(element_name,'year'   ,to_number(element_value)) >= 1995)
group by id
having count(distinct element_name)=3

+ Вариант 3. Виртуальные столбцы
alter table car_elems add 
    v_color   /* invisible */ generated always as (decode(element_name,'color'  ,element_value           ));
alter table car_elems add 
    v_mileage /* invisible */ generated always as (decode(element_name,'mileage',to_number(element_value)));
alter table car_elems add 
    v_year    /* invisible */ generated always as (decode(element_name,'year'   ,to_number(element_value)));
    
select 
  id
from car_elems
where v_color = 'red' or v_mileage < 100000 or v_year >= 1995
group by id
having count(distinct element_name)=3

Спасибо, буду думать.
Естественно, остальные варианты не катят потому что "котлеты с рисом, тефтели с картошкой, менять нельзя!"
Т.е. никакие изменения в архитектуре не разрешены, только SQL
11 фев 17, 00:23    [20202294]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к вертикальной таблице - все авто красного цвета  [new]
schi
Member

Откуда: Москва
Сообщений: 2601
ХБ
пивот животворящий
пропущено...
Базу ВСЕХ автомобилей так не хранят. Только базу школьного автобуса.

почему не хранят?
Например, база данных железнодорожных вагонов. Фирма владеет скажем полумиллионом вагонов и должна учитывать их техническое состояние.


Фирма на каждую учитываемую характеристику вагона создаст отдельное поле в таблице и сведет задачу к уже решенным.
11 фев 17, 11:25    [20202768]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к вертикальной таблице - все авто красного цвета  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
schi,

Больше 1000 - нельзя, больше 255 - не стоит(row piece может хранить не больше 255, дальше начинается intra-block chaining, даже если есть куча нуллов в промежуточных полях), поэтому eav не так уж плох порой, чем вынос атрибутов по куче дополнительных таблиц.
11 фев 17, 12:35    [20202894]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к вертикальной таблице - все авто красного цвета  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
Тюнил как-то базу где основная таблица откуда большинство запросов читали, содержала больше 750 полей (кажется 756), так это была полная ж...
11 фев 17, 12:39    [20202904]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к вертикальной таблице - все авто красного цвета  [new]
K790
Member

Откуда:
Сообщений: 633
xtender,

поделитесь методами тюнинга :) конструктор?
а то тут тема всплывала подобная
таблица жуткая
11 фев 17, 12:47    [20202919]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к вертикальной таблице - все авто красного цвета  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
K790,

С телефона лень писать, но там действительно был конструктор и генератор запросов, поэтому столбцы тупо добавляли в GUI. Достаточно было вынести лишнее в другие таблицы и изменить метаданные этого конструктора, генератор остальное сам делал.
11 фев 17, 15:00    [20203152]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к вертикальной таблице - все авто красного цвета  [new]
schi
Member

Откуда: Москва
Сообщений: 2601
xtender
schi,

Больше 1000 - нельзя, больше 255 - не стоит(row piece может хранить не больше 255, дальше начинается intra-block chaining, даже если есть куча нуллов в промежуточных полях), поэтому eav не так уж плох порой, чем вынос атрибутов по куче дополнительных таблиц.


В реальной жизни столько важных характеристик в предметной области и не встречается, потому что нельзя объять необъятное в одной таблице.
11 фев 17, 16:47    [20203309]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к вертикальной таблице - все авто красного цвета  [new]
schi
Member

Откуда: Москва
Сообщений: 2601
Уточню - мне не встречалось
11 фев 17, 16:47    [20203311]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к вертикальной таблице - все авто красного цвета  [new]
Алымов Анатолий
Member

Откуда: Оренбург
Сообщений: 1309
schi
xtender
schi,

Больше 1000 - нельзя, больше 255 - не стоит(row piece может хранить не больше 255, дальше начинается intra-block chaining, даже если есть куча нуллов в промежуточных полях), поэтому eav не так уж плох порой, чем вынос атрибутов по куче дополнительных таблиц.


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

Да возьмите любой интернет магазин. Количество атрибутов у товаров ОЧЕНЬ большое.
13 фев 17, 13:01    [20207163]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить