Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Запрос к вертикальной таблице - все авто красного цвета / 25 сообщений из 26, страница 1 из 2
  1  все
24.08.2016, 00:30
    #39296715
ХБ
ХБ
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос к вертикальной таблице - все авто красного цвета
Наверное, такие вопросы на интервью задают...но хотелось бы услышать мнение коллективного разума.
Скажем, база данных по всем автомобилям.
Каждый автомобиль описывается, например, 1000-ю элементами, в виде имя_элемента - значение.
Имеем вертикальную таблицу с тремя колонками: id, element_name, element_value
id это уникальный идентификатор автомобиля, и дальше имя и значение всех элементов этого автомобиля.
Количество элементов на автомобиль может быть разным, но общий набор всех возможных элементов составляет, скажем, тысячу значений. Более того, элементы могут со временем (скажем, раз в месяц) добавляться или убираться в том числе и в этот базовый набор.
Например, элементы:
цвет
пробег
год выпуска
наличие кондиционера
и пр.
И запрос: найти все автомобили красного цвета с пробегом не больше 100000км года выпуска 1995 с кондиционером.
Получается, self-join таблицы 4 раза.
А если нужно отфильтровать по комбинации 100 элементов, то self-join 100 раз.
Размер таблицы, скажем, 50 000 000 записей.

Какие способы могут быть для улучшения таких запросов?
Например, у нас созданы pivot view вида ряд - id автомобиля и колонка - имя элемента.
А еще что-то можно предложить?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.08.2016, 01:05
    #39296720
ХБ
ХБ
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос к вертикальной таблице - все авто красного цвета
погуглив, нашел хорошее определение этой проблемы - 'name-value curse', с общей рекомендацией - 'don't do it!'
Но как, черт побери, хранить данные об объектах с произвольным числом аттрибутов??
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.08.2016, 01:06
    #39296721
пивот животворящий
пивот животворящий
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос к вертикальной таблице - все авто красного цвета
ХБКакие способы могут быть для улучшения таких запросов?Базу ВСЕХ автомобилей так не хранят. Только базу школьного автобуса.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.08.2016, 01:09
    #39296722
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос к вертикальной таблице - все авто красного цвета
ХБ,

group by+having. А вообще имхо лучше уж в json или xml хранить и искать например так:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.08.2016, 01:53
    #39296729
ХБ
ХБ
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос к вертикальной таблице - все авто красного цвета
пивот животворящийХБКакие способы могут быть для улучшения таких запросов?Базу ВСЕХ автомобилей так не хранят. Только базу школьного автобуса.
почему не хранят?
Например, база данных железнодорожных вагонов. Фирма владеет скажем полумиллионом вагонов и должна учитывать их техническое состояние.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.08.2016, 01:59
    #39296731
ХБ
ХБ
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос к вертикальной таблице - все авто красного цвета
xtenderХБ,

group by+having. А вообще имхо лучше уж в json или xml хранить и искать например так:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
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 в одну строку и искать любым из существующих парсингов текстовой строки, так?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.08.2016, 02:06
    #39296732
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос к вертикальной таблице - все авто красного цвета
ХБ,

нет, просто подсчитать кол-во совпадений
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.08.2016, 02:19
    #39296734
ХБ
ХБ
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос к вертикальной таблице - все авто красного цвета
xtenderХБ,

нет, просто подсчитать кол-во совпадений
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
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!
круто, спасибки.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.08.2016, 07:20
    #39296776
andreymx
andreymx
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос к вертикальной таблице - все авто красного цвета
ХБxtenderХБ,

нет, просто подсчитать кол-во совпадений
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
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

и уникальность не забыть
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.08.2016, 08:15
    #39296797
MasterZiv
MasterZiv
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос к вертикальной таблице - все авто красного цвета
Размер таблицы, скажем, 50 000 000 записей.

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



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

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

более ничего не нужно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.08.2016, 09:06
    #39296820
ы!
ы!
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос к вертикальной таблице - все авто красного цвета
MasterZiv

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

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

более ничего не нужно.
Если таблица более менее статична - сделать bitmap индексы?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.08.2016, 09:37
    #39296852
Shtock
Shtock
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос к вертикальной таблице - все авто красного цвета
to xtender: фиг знает, я бы поостерёгся юзать в оракле json для чего-то сложнее чем хранение конфигов. он там очень сырой. быстрый только с индексами, но они сурово глючат (например, там тупо не работает or). более того, они уже сделали 2 json bundle fix ибо куча багов, но они конфликтуют с другими патчами, мы им об этом написали и oracle слёзно божится, что мол всё исправят, но за 2 недели ничего не произошло :(
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.08.2016, 10:36
    #39296896
надо пробовать
надо пробовать
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос к вертикальной таблице - все авто красного цвета
имхо, если физически кластеризовать все items вокруг id то будет польза.
да, вставка будет тупить
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.02.2017, 23:46
    #39402522
ХБ
ХБ
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос к вертикальной таблице - все авто красного цвета
xtenderХБ,

нет, просто подсчитать кол-во совпадений
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
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 это обойти?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.02.2017, 00:09
    #39402526
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос к вертикальной таблице - все авто красного цвета
ХБ,


 
допустим у тебя есть такая таблица
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
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:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
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. Виртуальные столбцы
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
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

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.02.2017, 00:10
    #39402527
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос к вертикальной таблице - все авто красного цвета
забыл добавить, что лучше раскомментировать invisible если ты на 12с
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.02.2017, 00:23
    #39402533
ХБ
ХБ
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос к вертикальной таблице - все авто красного цвета
xtenderХБ,


 
допустим у тебя есть такая таблица
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
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:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
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. Виртуальные столбцы
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.02.2017, 11:25
    #39402592
schi
schi
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос к вертикальной таблице - все авто красного цвета
ХБпивот животворящийпропущено...
Базу ВСЕХ автомобилей так не хранят. Только базу школьного автобуса.
почему не хранят?
Например, база данных железнодорожных вагонов. Фирма владеет скажем полумиллионом вагонов и должна учитывать их техническое состояние.

Фирма на каждую учитываемую характеристику вагона создаст отдельное поле в таблице и сведет задачу к уже решенным.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.02.2017, 12:35
    #39402620
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос к вертикальной таблице - все авто красного цвета
schi,

Больше 1000 - нельзя, больше 255 - не стоит(row piece может хранить не больше 255, дальше начинается intra-block chaining, даже если есть куча нуллов в промежуточных полях), поэтому eav не так уж плох порой, чем вынос атрибутов по куче дополнительных таблиц.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.02.2017, 12:39
    #39402623
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос к вертикальной таблице - все авто красного цвета
Тюнил как-то базу где основная таблица откуда большинство запросов читали, содержала больше 750 полей (кажется 756), так это была полная ж...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.02.2017, 12:47
    #39402625
K790
K790
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос к вертикальной таблице - все авто красного цвета
xtender,

поделитесь методами тюнинга :) конструктор?
а то тут тема всплывала подобная
таблица жуткая
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.02.2017, 15:00
    #39402676
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос к вертикальной таблице - все авто красного цвета
K790,

С телефона лень писать, но там действительно был конструктор и генератор запросов, поэтому столбцы тупо добавляли в GUI. Достаточно было вынести лишнее в другие таблицы и изменить метаданные этого конструктора, генератор остальное сам делал.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.02.2017, 16:47
    #39402711
schi
schi
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос к вертикальной таблице - все авто красного цвета
xtenderschi,

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

В реальной жизни столько важных характеристик в предметной области и не встречается, потому что нельзя объять необъятное в одной таблице.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.02.2017, 16:47
    #39402712
schi
schi
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос к вертикальной таблице - все авто красного цвета
Уточню - мне не встречалось
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.02.2017, 13:01
    #39403407
Алымов Анатолий
Алымов Анатолий
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос к вертикальной таблице - все авто красного цвета
schixtenderschi,

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

В реальной жизни столько важных характеристик в предметной области и не встречается, потому что нельзя объять необъятное в одной таблице.
Да возьмите любой интернет магазин. Количество атрибутов у товаров ОЧЕНЬ большое.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
  1  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Запрос к вертикальной таблице - все авто красного цвета / 25 сообщений из 26, страница 1 из 2
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 434ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1886451]:
 0ms
get settings:
 8ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 169ms
get topic data:
 6ms
get forum data:
 2ms
get page messages:
 35ms
get tp. blocked users:
 1ms
others: 195ms
total:  434ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]