ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Запрос к вертикальной таблице - все авто красного цвета
25 сообщений из 26, страница 1 из 2
  1  все
Запрос к вертикальной таблице - все авто красного цвета
    #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, 00:30
| Ответить | Цитировать | Написать  
Запрос к вертикальной таблице - все авто красного цвета
    #39296720
Фотография ХБ
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ХБ
Участник
погуглив, нашел хорошее определение этой проблемы - 'name-value curse', с общей рекомендацией - 'don't do it!'
Но как, черт побери, хранить данные об объектах с произвольным числом аттрибутов??
...
Рейтинг: 0 / 0
24.08.2016, 01:05
| Ответить | Цитировать | Написать  
Запрос к вертикальной таблице - все авто красного цвета
    #39296721
пивот животворящий
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
пивот животворящий
Гость
ХБКакие способы могут быть для улучшения таких запросов?Базу ВСЕХ автомобилей так не хранят. Только базу школьного автобуса.
...
Рейтинг: 0 / 0
24.08.2016, 01:06
| Ответить | Цитировать | Написать  
Запрос к вертикальной таблице - все авто красного цвета
    #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:09
| Ответить | Цитировать | Написать  
Запрос к вертикальной таблице - все авто красного цвета
    #39296729
Фотография ХБ
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ХБ
Участник
пивот животворящийХБКакие способы могут быть для улучшения таких запросов?Базу ВСЕХ автомобилей так не хранят. Только базу школьного автобуса.
почему не хранят?
Например, база данных железнодорожных вагонов. Фирма владеет скажем полумиллионом вагонов и должна учитывать их техническое состояние.
...
Рейтинг: 0 / 0
24.08.2016, 01:53
| Ответить | Цитировать | Написать  
Запрос к вертикальной таблице - все авто красного цвета
    #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, 01:59
| Ответить | Цитировать | Написать  
Запрос к вертикальной таблице - все авто красного цвета
    #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:06
| Ответить | Цитировать | Написать  
Запрос к вертикальной таблице - все авто красного цвета
    #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, 02:19
| Ответить | Цитировать | Написать  
Запрос к вертикальной таблице - все авто красного цвета
    #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, 07:20
| Ответить | Цитировать | Написать  
Запрос к вертикальной таблице - все авто красного цвета
    #39296797
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
Размер таблицы, скажем, 50 000 000 записей.

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



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

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

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

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

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

более ничего не нужно.
Если таблица более менее статична - сделать bitmap индексы?
...
Рейтинг: 0 / 0
24.08.2016, 09:06
| Ответить | Цитировать | Написать  
Запрос к вертикальной таблице - все авто красного цвета
    #39296852
Фотография Shtock
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shtock
Участник
to xtender: фиг знает, я бы поостерёгся юзать в оракле json для чего-то сложнее чем хранение конфигов. он там очень сырой. быстрый только с индексами, но они сурово глючат (например, там тупо не работает or). более того, они уже сделали 2 json bundle fix ибо куча багов, но они конфликтуют с другими патчами, мы им об этом написали и oracle слёзно божится, что мол всё исправят, но за 2 недели ничего не произошло :(
...
Рейтинг: 0 / 0
24.08.2016, 09:37
| Ответить | Цитировать | Написать  
Запрос к вертикальной таблице - все авто красного цвета
    #39296896
надо пробовать
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
надо пробовать
Гость
имхо, если физически кластеризовать все items вокруг id то будет польза.
да, вставка будет тупить
...
Рейтинг: 0 / 0
24.08.2016, 10:36
| Ответить | Цитировать | Написать  
Запрос к вертикальной таблице - все авто красного цвета
    #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
10.02.2017, 23:46
| Ответить | Цитировать | Написать  
Запрос к вертикальной таблице - все авто красного цвета
    #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:09
| Ответить | Цитировать | Написать  
Запрос к вертикальной таблице - все авто красного цвета
    #39402527
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
забыл добавить, что лучше раскомментировать invisible если ты на 12с
...
Рейтинг: 0 / 0
11.02.2017, 00:10
| Ответить | Цитировать | Написать  
Запрос к вертикальной таблице - все авто красного цвета
    #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, 00:23
| Ответить | Цитировать | Написать  
Запрос к вертикальной таблице - все авто красного цвета
    #39402592
schi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
schi
Участник
ХБпивот животворящийпропущено...
Базу ВСЕХ автомобилей так не хранят. Только базу школьного автобуса.
почему не хранят?
Например, база данных железнодорожных вагонов. Фирма владеет скажем полумиллионом вагонов и должна учитывать их техническое состояние.

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

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

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

С телефона лень писать, но там действительно был конструктор и генератор запросов, поэтому столбцы тупо добавляли в GUI. Достаточно было вынести лишнее в другие таблицы и изменить метаданные этого конструктора, генератор остальное сам делал.
...
Рейтинг: 0 / 0
11.02.2017, 15:00
| Ответить | Цитировать | Написать  
Запрос к вертикальной таблице - все авто красного цвета
    #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
11.02.2017, 16:47
| Ответить | Цитировать | Написать  
Запрос к вертикальной таблице - все авто красного цвета
    #39403407
Алымов Анатолий
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Алымов Анатолий
Участник
schixtenderschi,

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

В реальной жизни столько важных характеристик в предметной области и не встречается, потому что нельзя объять необъятное в одной таблице.
Да возьмите любой интернет магазин. Количество атрибутов у товаров ОЧЕНЬ большое.
...
Рейтинг: 0 / 0
13.02.2017, 13:01
| Ответить | Цитировать | Написать  
25 сообщений из 26, страница 1 из 2
  1  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Запрос к вертикальной таблице - все авто красного цвета
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39296722&tid=1886451]:
 0ms
get settings:
 8ms
get forum list:
 19ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 177ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 70ms
get tp. blocked users:
 1ms
others: 239ms
total:  535ms
созд. / загр.: 535ms
x
x
Закрыть


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