Векторные запросы. Оракл и все остальные СУБД

← →
ANB (2008-02-08 12:48) [0]

В честь пятницу разомнем мозги ?
Классическая задача на векторные запросы.
Часто задают и частенько решения частные.
Имеем
Table1
(
ID,
F1,
F2,
F3,
F4
)
и
Table2
(
ID,
F5,
F6,
F7,
F8
)
Задача - выбрать все записи из Table1 и по одной, ей соответсвующей в Table2.
Если бы Table1 ссылалась на Table2 многие к одному - лефт джойн и полное счастье.
Однако проблема наоборот.
Добавляем в Table2 поле ID_Table1 (и соответствующий FK). Связка многие к одному.

Нужно - вытащить все записи из Table1 и все поля Table2, привязанные к таблице1, но только по одной записи (которая в группе имеет самое большое поле F5).

Кто как это делает в разных субд ?

← →
Sergey13 © (2008-02-08 13:50) [1]

Не знаю понял ли я вопрос (мозги уже мягкие после обеда, пятница действует 8-), но я бы наверное копал в сторону подзапросов с агрегатами. По крайней мере это первое, что приходит на ум.

← →
Stas © (2008-02-08 14:09) [2]

Если правильно понял, то так, как присоединить table1, думаю догадаться можно.

select table2 from
(select id_tabl1 as IDT,max(F5) mf5 from table2) as t inner join table2 on table2.id_table1=t.idt and table2.f5=t.mf5

← →
Stas © (2008-02-08 14:10) [3]

ошибся так:
select table2 from
(select id_tabl1 as IDT,max(F5) mf5 from table2 GROUP BY id_tabl1) as t inner join table2 on table2.id_table1=t.idt and table2.f5=t.mf5

← →
Johnmen © (2008-02-08 14:33) [4]

SELECT * FROM T1, T2 WHERE (T1.ID=T2.ID) AND ((SELECT MAX(T2.F5) FROM T2 WHERE T1.ID=T2.ID) = T2.F5)
Почти не зависит от СУБД :)

← →
ANB (2008-02-08 17:41) [5]

Доп. условие - F5 не уникально.

← →
Stas © (2008-02-08 17:44) [6]

ну и что?

← →
ANB (2008-02-08 17:46) [7]

> select table2 from
> (select id_tabl1 as IDT,max(F5) mf5 from table2 GROUP BY
> id_tabl1) as t inner join table2 on table2.id_table1=t.idt
> and table2.f5=t.mf5

Это даже не скомпилится

← →
ANB (2008-02-08 17:49) [8]

Уточняю еще раз условие : результат запроса - все поля обоих таблиц.
Можно еще уточнить : внутри группы по ID_Table1 все поля неуникальны, кроме Table2(ID).
Ну сортировать по F5, F6, F7, ID

← →
Johnmen © (2008-02-08 20:19) [9]

> ANB (08.02.08 17:41) [5]

Тогда тебе надо определиться с

но только по одной записи (которая в группе имеет самое большое поле F5).

Если зн.полей полей одинаковы - не понятно, какую запись ты предпочитаешь из двух одинаковых :)))

← →
Petr V. Abramov © (2008-02-09 17:12) [10]

> ANB (08.02.08 17:49) [8]

select * from ( select *, max(f5) over (partition by table1_id) maxf5 from t1, t2 where t1.id = t2.table1_id ) where f5 = maxf5

ессно, будет выбрано не по одной записи из t2, а по столько, сколько записей имеют max значение для группы по table1_id, т.к. [9]

← →
Кщд (2008-02-11 06:41) [11]

Oracle
select t.id, t.f1, t.f2, t.f3, t.f4, t3.id, t3.id_table1, t3.f5, t3.f6, t3.f7, t3.f8 from table1 t left join (select t2.id_table1, max(t2.id) keep (dense_rank last order by t2.f5) id, max(t2.f5) keep (dense_rank last order by t2.f5) f5, max(t2.f6) keep (dense_rank last order by t2.f5) f6, max(t2.f7) keep (dense_rank last order by t2.f5) f7, max(t2.f8) keep (dense_rank last order by t2.f5) f8, from table2 t2 group by t2.id_table1 ) t3 on t.id = t3.id_table1

← →
ANB (2008-02-11 15:48) [12]

> Кщд (11.02.08 06:41) [11]

А тормозить сильно будет ?

← →
ANB (2008-02-11 16:06) [13]

select /*+ ordered index(LH AD_CRLIM)*/
L.*, LH.*, C.*
from (select L.CONTNUM, max(L.Btrv_Address) keep(dense_rank last order by L.DATEOPER, L.OPERNUM) id
from CredCont C, CrLim L
where C.Contnum in ("1", "2", "3")
and L.Contnum(+) = C.Contnum
and L.ACC(+) = 1
group by L.CONTNUM) L,
CredCont C,
CrLim LH
where C.CONTNUM = L.Contnum
and LH.BTRV_ADDRESS(+) = L.id

Вот такой запрос (Т1 - CredCont, T2 - CrLim) примерно похож на то, что надо. К сожалению, на всех записях он дохнет. А на ограниченной выборке, как в примере - работает шустро.

← →
Stas © (2008-02-11 16:50) [14]

ANB (08.02.08 17:46) [7] Это почему не скомпилится?
Ну еще так можно.
select table2.F5, MAX(F1),MAX(F2)... from
(select id_tabl1 as IDT,max(F5) mf5 from table2 GROUP BY id_tabl1) as t inner join table2 on table2.id_table1=t.idt and table2.f5=t.mf5
GROUP BY table2.F5

← →
ANB (2008-02-11 17:24) [15]

> Ну еще так можно.

На максах :
1. Жестко попадаем, что сортировка тока по одному полю
2. Если это поле неуникально, то записи в запросе подублятся

← →
Stas © (2008-02-11 17:28) [16]

сортировать можно по любому

order by 1,2,3

← →
ANB (2008-02-11 17:39) [17]

> 1. Жестко попадаем, что сортировка тока по одному полю

Жестко попадаем, что сортировка в Т2 больше чем по одному полю, т.к. оператора макс(список полей) нету. А по отдельности приедет ерунда.

← →
Petr V. Abramov © (2008-02-12 01:14) [18]

> ANB (11.02.08 15:48) [12]

ты на [9] ответишь четко?

← →
ANB (2008-02-12 09:55) [19]

> Тогда тебе надо определиться с
>
> но только по одной записи (которая в группе имеет самое
> большое поле F5).
>
> Если зн.полей полей одинаковы - не понятно, какую запись
> ты предпочитаешь из двух одинаковых :)))

Звиняйте, не заметил.

Задача на самом деле сложнее.
1. С самой большой комбинацией F5, F6 (как в ордер бае).
2. Комбинация не уникальна, если есть совпадения, то берем любую, но только одну. Для упрощения можно считать - с самым большим ID. Т.е. ИД уникален, то комбинация Ф5, Ф6, ИД - уникальна.

Я написал через аналитику - но громоздко и неудобно. Плюс по таблицам приходится 2 раза лазить.

Код в Кщд (11.02.08 06:41) [11] очень даже рабочий, сами часто так пишем. Но если полей много - то тоже громоздко и неудобно.

Все потому что макс(список полей) нету. а если склейку лепить, то придется соответствующий функциональный индекс заводить, иначе тормозить будет безбожно.

← →
Johnmen © (2008-02-12 10:42) [20]

> ANB (12.02.08 09:55) [19]

Какого типа F5 и F6? Каковы диапазоны их значений в жизни?

← →
ANB (2008-02-12 11:17) [21]

> Какого типа F5 и F6? Каковы диапазоны их значений в жизни?

Самое частое - Ф5 - дата, Ф6 - интегер (нумерация внутри даты). Из за особенностей генерации Ф6 оно иногда может быть неуникальным.

← →
ЮЮ © (2008-02-12 11:44) [22]

Не прнял, откуда здесь ещё F6 всплыло. Но если строго по [0] - [1], т.е.

последнюю запись из Table2 c максимальным F5? то

SELECT Table1.*, Table2.* FROM Table1 LEFT JOIN ( SELECT t2.ID_Table1, Max(t2.Id) t2.ID_Table2 FROM (SELECT ID_Table1, Max(F5) MaxF5 FROM Table2 GROUP BY ID_Table1) t1Max5 JOIN Table2 t2 ON -- это соединение оставит те записи, где возможно дублирование t2.ID_Table1 в парах(t2.Id, t2.ID_Table1) (t1Max5.ID_Table1 = t2.ID_Table1) and -- индекс по t2.ID_Table1, полагаю есть (t1Max5.Max5 = t2.F5) -- а вот по t2.F5 очень не помешает GROUP t2.ID_Table1 ) t1t2 ON Table1.Id = t1t2.ID_Table1 LEFT JOIN Table2 ON t1t2.ID_Table2 = Table2.Id

MS SQL, естественно :)

← →
ЮЮ © (2008-02-12 12:45) [23]

> Не прнял, откуда здесь ещё F6 всплыло

Теперь увидел [19] ANB (12.02.08 09:55)

Но если "Ф6 - интегер нумерация внутри даты" не противоречит ID-у, т.е. наибольшему Ф6 соответствует наибльший ID, то на Ф6 можно и забить.

← →
ANB (2008-02-12 13:12) [24]

> т.е. наибольшему Ф6 соответствует наибльший ID

К сожалению, допускается противоречие. Юзер руками может это поле перебивать - иначе нафиг бы оно не нужно было.
Ф5 и ИД - тоже порядок может не совпадать.

← →
Johnmen © (2008-02-12 13:43) [25]

> ANB (12.02.08 11:17) [21]

Тогда делаем так.
1. пишем функцию (MyFunc) преобразования полей F5, F6 и ID к строковому виду:
YYYY.MM.DD00000006660000000123, где 10 первых символов получены из F5, вторые 10 - из F6 и третьи 10 - из ID.
2. собственно сам запрос (из [4] с учетом исправления ошибки) теперь такой:
SELECT * FROM Table1 T1, Table2 T2 WHERE (T1.ID=T2.ID) AND ((SELECT MAX(MyFunc(T3.F5,T3.F6,T3.ID)) FROM Table2 T3 WHERE T1.ID=T3.ID) = MyFunc(T2.F5,T2.F6,T2.ID))

← →
ANB (2008-02-12 14:21) [26]

> Johnmen © (12.02.08 13:43) [25]

Сдохнет такой запрос.
1) на малых объемах - не подключаться индексы
2) на больших объемах не будет распараллеливаться фулл скан.
Иначе было бы все ок - самое удобное решение.
Хотя, если запросы частые, мона денормализовать Т2, т.е. триггером считать спец. поле и на это поле повесить индекс.

← →
Johnmen © (2008-02-12 14:26) [27]

> ANB (12.02.08 14:21) [26]
> Сдохнет такой запрос.

Ты проверил?

1) на малых объемах - не подключаться индексы

Что значит "не подключатся"? А на больших "подключатся"?

2) на больших объемах не будет распараллеливаться фулл скан.

Где это "фулл скан" ты увидел? И что ты под этим подразумеваешь?

← →
ANB (2008-02-12 16:01) [28]

> Где это "фулл скан" ты увидел? И что ты под этим подразумеваешь?

Если в таблице больше 10 миллионов записей, при этом из них выбирается и джойнится с другими (большими) таблицами более миллиона, то распаралеленный фулсканн по всем таблицам однозначно быстрее.

А вот какой метод применять - это уже в зависимости от задачи.
Использование же в запросе функций однозначно отрубает возможность распараллелится, а в where части - использование индекса. Можно, конечно, повесить функциональный индекс, но он не решит проблему больших объемов.

← →
Johnmen © (2008-02-12 16:35) [29]

Ещё раз - где ты увидел фулскан?
М.б. у нас разные понятия о том, что это? Тогда повторю [27].

Использование же в запросе функций однозначно отрубает возможность распараллелится,

Если ты внимательно посмотришь, то увидишь, что функция в соединении никак не участвует.

а в where части - использование индекса

Да, индекс не используется. А какой у тебя индекс(ы)?
И разве использование индексов - это самоцель?

Короче, ты не теоретизируй, а смотри на практически полученные результаты на реальных данных.

← →
ANB (2008-02-12 16:45) [30]

> SELECT * FROM Table1 T1, Table2 T2
> WHERE (T1.ID=T2.ID) AND
> ((SELECT MAX(MyFunc(T3.F5,T3.F6,T3.ID)) FROM Table2 T3 WHERE
> T1.ID=T3.ID) = MyFunc(T2.F5,T2.F6,T2.ID))

Тут используется функция в соединении.

> Ещё раз - где ты увидел фулскан?

Фуллскан я сам включаю по надобности хинтами.

← →
Johnmen © (2008-02-12 17:05) [31]

> Тут используется функция в соединении.

М-дя...
Вообще-то соединение это чуть повыше, первое условие в where.
М.б. стОит подчитать теорию?

← →
ANB (2008-02-12 17:21) [32]

> М-дя...
> Вообще-то соединение это чуть повыше, первое условие в where.
>
> М.б. стОит подчитать теорию?

Значит можно будет по индексу отсечь подчиненные записи в Т2, а вот отбор нужной единственной будет идти перебором. И тут уж - если записей мало, то быстро. Иначе - медленно. Впрочем, я ща попробую - прикрутится индекс или нет.

← →
ANB (2008-02-12 17:23) [33]

Вдогонку : я говорил не о том, что получим вредный фулл-скан. А о том, что я не смогу запустить полезный фулл скан из-за использования функции.

← →
Johnmen © (2008-02-12 17:34) [34]

> а вот отбор нужной единственной будет идти перебором.
> И тут уж - если записей мало, то быстро. Иначе - медленно.

Что такое "быстро" и что такое "медленно"? Какие у тебя координаты для измерения? Относительно чего?

> Впрочем, я ща попробую - прикрутится индекс или нет.

Да что ты зациклился на индексе?! Религия что-ли?
:))

← →
ANB (2008-02-12 18:03) [35]

> Что такое "быстро" и что такое "медленно"? Какие у тебя
> координаты для измерения? Относительно чего?

Имеем запись в Т1. Имеем 200 000 подчиненных ей записей в Т2, из которых отобрать надо одну ("последнюю" с точки зрения пользователя). Ну и в Т1 записей штук 5 000 (каждой из которых подчинено от 10 000 до 200 000 записей в Т2). В таких условиях использование индекса по "естественному" ключу в Т2 становится сильно полезным.

← →
Кщд (2008-02-12 18:35) [36]

>ANB (11.02.08 16:06) [13]
конечно, будет - сами понимаете - group by)
план Вашего запроса можно увидеть?

> ANB
ты б изначальную задачу привел, а то фиг поймешь, что же надо

← →
ANB (2008-02-13 09:30) [38]

> план Вашего запроса можно увидеть?

SELECT STATEMENT, GOAL = CHOOSE Cost=6 Cardinality=3 Bytes=1011
NESTED LOOPS OUTER Cost=6 Cardinality=3 Bytes=1011
NESTED LOOPS Cost=5 Cardinality=3 Bytes=834
VIEW Object owner=QUORUM Cost=4 Cardinality=3 Bytes=57
SORT GROUP BY Cost=4 Cardinality=3 Bytes=135
NESTED LOOPS OUTER Cost=2 Cardinality=5 Bytes=225
INLIST ITERATOR
INDEX RANGE SCAN Object owner=QUORUM Object name=CNUM Cost=1 Cardinality=3 Bytes=30
TABLE ACCESS BY INDEX ROWID Object owner=QUORUM Object name=CRLIM Cost=1 Cardinality=2 Bytes=70
INDEX RANGE SCAN Object owner=QUORUM Object name=CRLIM4 Cost=1 Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=QUORUM Object name=CREDCONT Cost=1 Cardinality=1 Bytes=259
INDEX UNIQUE SCAN Object owner=QUORUM Object name=CNUM Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=QUORUM Object name=CRLIM Cost=1 Cardinality=1 Bytes=59
INDEX UNIQUE SCAN Object owner=QUORUM Object name=AD_CRLIM Cardinality=1

← →
ANB (2008-02-13 09:32) [39]

> ты б изначальную задачу привел, а то фиг поймешь, что же
> надо

Задача теоретическая : накопать самый удобный способ для векторных запросов. На практике проблема решается кажный раз по разному.

← →
ANB (2008-02-13 09:44) [40]

Откуда взялся вопрос :
Пример :
имеем Т1 и Т2. У Т1 - составной ПК по ИД_1 и ИД_2. Т2 ссылается на Т1 полями Т1_ИД_1 и Т1_ИД_2.
Надо : вытащить ином (часто бывает нужно) записи из Т2 по определенному набору записей в Т1. Раньше чесал репу и таки обычно джойнил. А недавно накопал фичу :
select * from T2 where (Т1_ИД_1, Т1_ИД_2) in (select ИД_1, ИД_2 from T1).
Удобно. Аналогичная фича нашлась и для update - когда надо обновить набор полей из одной записи другой таблицы.
update T1
set (F1, F2, F3) = (select F1, F2, F3 from T2).

Причем фичу накопал в книжке для чайников. Абыдно.

Вот я и интерисуюсь - а нету ли какой нибудь фичи для облегчения векторных запросов. Аналитика помогает, но не очень.

ЗЫ. Век живи век учись.

> А недавно накопал фичу :select * from T2 where (Т1_ИД_1,
> Т1_ИД_2) in (select ИД_1, ИД_2 from T1).

Эта "фича" на порядок медленнее, чем соединение.
Т.е. налицо твой регресс :)

← →
Кщд (2008-02-13 10:30) [42]

если так
select /*+ use_nl(t1 t2) no_merge(t2) push_pred(t2) index(t1 pk_t1)*/ t1.*, t2.* from t1 t1 join ( select /*+ index(it i_t2$id_t1_id) */it.* from t2 it where it.id = ( select it2.id from t2 it2 where it2.t1_id = it.t1_id and it2.f5 = (select max(it3.f5) from t2 it3 where it3.t1_id = it.t1_id ) and rownum < 2 ) ) t2 on t1.id = t2.t1_id

← →
ANB (2008-02-13 11:19) [43]

> Кщд (13.02.08 10:30) [42]

3 обращения к Т2. Мона обойтись двумя.
А вот это :
and it2.f5 = (select max(it3.f5)
не будет толком работать, если есть дубли Ф5.

> Johnmen © (13.02.08 09:57) [41]

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

> ANB (13.02.08 11:19) [43]
> Соединение иногда может дать дубли,

Что за дубли?
А "фича" дублей не дает?

ЗЫ
У меня такое ощущение, что в изучении SQL ты свернул на какую-то боковую дорожку...:)
И при этом тебе кажется, что она основная.

← →
Кщд (2008-02-13 13:56) [45]

>ANB (13.02.08 11:19) [43]
>3 обращения к Т2. Мона обойтись двумя.
обращения не к таблице, но строго к индексу
необходимы для повышения производительности

>А вот это :
>and it2.f5 = (select max(it3.f5)
>не будет толком работать, если есть дубли Ф5.
работать будет, т.к. rownum

могу выложить скрипты на создание таблиц/индексов
у меня при мощности в 45млн на T2 запрос работает 5-10 секунд

← →
ANB (2008-02-13 14:30) [46]

> Кщд (13.02.08 13:56) [45]

Ну и плюс сортировка по 2-м полям не учтена.
Это частное решение. Таким я тоже пользуюсь.

> А "фича" дублей не дает?

Когда это ИН дублил записи ? Его всегда и юзают, чтобы дублей не было
select * from T where ID in (2,2,2,2,2) - вернет всегда одну запись.

← →
Кщд (2008-02-13 14:54) [47]

>ANB (13.02.08 14:30) [46]
ответ ориентирован на первоначальную постановку задачи)
включить в него частные случае (типа сортировки и т.п.) легко без существенного ущерба производительности
и, собственно, применим как минимум на MS SQL, Oracle & FB

← →
ANB (2008-02-14 09:08) [48]

> ответ ориентирован на первоначальную постановку задачи)
> включить в него частные случае (типа сортировки и т.п.)
>

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

← →
Кщд (2008-02-14 09:25) [49]

>ANB (13.02.08 09:32) [39]
укажите, пожалуйста, номер Вашего поста в этой ветке, с пояснением про сортировку
видимо, я Вас неверно понял

← →
Кщд (2008-02-14 09:26) [50]

>В оракле через аналитику попроще выходит
через аналитику будет гарантированно дольше

← →
ANB (2008-02-14 13:34) [51]

> укажите, пожалуйста, номер Вашего поста в этой ветке, с
> пояснением про сортировку

да мне не лень еще раз написать.

В подчиненной таблице Т2 данные сортируются по ИД_Т1, Ф5, Ф6, Ф7, ИД.

И вот по этому набору полей надо искать последнюю в группе по ИД_Т1.

> через аналитику будет гарантированно дольше

Практически так же как с агрегатными функциями. А если полей сортировки больше одного, то агрегатом вообще задерешься делать.

Векторные запросы. Оракл и все остальные СУБД Найти похожие ветки