ReSQL.ru
     
powered by simpleCommunicator - 2.0.50     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Выбрать одну строку из множества в условии join
4 сообщений из 4, страница 1 из 1
Выбрать одну строку из множества в условии join
    #40065828
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Внезапно обнаружил, что в версии Oracle 10g работают функции inet_aton/inet_ntoa (раньше думал, что они доступны только с версии 11 и приходилось использовать громоздкие выражения).
И тут же образовалась задача, где эти функции хотелось бы применить.
Есть запрос, возвращающий набор строк, в одной из которых содержится IP-адрес.
Есть вспомогательная таблица с подсетями:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
CREATE TABLE INV_RES_IP4 (
	ADR VARCHAR2(20) NOT NULL,
	IP NUMBER(10,0), 
	MASK NUMBER(10,0), 
	DESCRIPTION VARCHAR2(200), 
	...
)


Подсети могут быть вложенными, то есть в большой подсети (с широкой MASK) может быть несколько более мелкий подсетей. Маска битовая и широкая маска означает меньшее число установленных битов.
Принадлежность адреса к подсети определяется таким выражением:
Код: plsql
1.
bitand(IP,MASK) = bitand(inet_aton(table.ipaddress),MASK)


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

Ранее и в другом месте я делал так:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
...
join (
select ip.ADR
, max(p.ADR) keep (dense_rank last  order by p.ADR) as ADR_PARENT
, max(p.ADR) keep (dense_rank first order by p.ADR) as ADR_ROOT
from INV_RES_IP4 ip
left join INV_RES_IP4 p on (bitand(ip.IP,p.MASK) = p.IP and nvl(ip.MASK,4294967295) >= p.MASK and ip.ADR != p.ADR)
group by ip.ADR
)


Но это было самосоединение (чтобы таблицу INV_RES_IP4 отобразить в виде иерархии).
А можно ли обойтись без подзапроса с группировкой, а прямо в условии join указать соединение с определенной строкой?
...
Рейтинг: 0 / 0
26.04.2021, 10:33
| Ответить | Цитировать | Написать  
Выбрать одну строку из множества в условии join
    #40065903
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Сейчас у меня сделано так:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
select CS.CURSESSIONS_ID as "#"
...
, IP.ADR as "SUBNET"
...
from CURSESSIONS CS
...
join RADACCT R on (R.RADACCTID = CS.RADACCTID)
--left join INV_RES_IP4 IP on (IP.TYPE = 'net' and bitand(IP.IP,IP.MASK) = bitand(inet_aton(R.FRAMEDIPADDRESS),IP.MASK))
left join (
  select CS.CURSESSIONS_ID, inet_aton(R.FRAMEDIPADDRESS) as IP
  , max(IP.ADR) keep (dense_rank last order by IP.MASK)) as ADR
  , max(IP.DESCRIPTION) keep (dense_rank last order by IP.MASK)) as DESCRIPTION
  , min(4294967296-ip.MASK) as NET
  from CURSESSIONS CS
  join RADACCT R on (R.RADACCTID = CS.RADACCTID)
  left join INV_RES_IP4 IP on (IP.TYPE = 'net' and bitand(IP.IP,IP.MASK) = bitand(inet_aton(R.FRAMEDIPADDRESS),IP.MASK))
  group by CS.CURSESSIONS_ID, inet_aton(R.FRAMEDIPADDRESS)
) IP on (IP.CURSESSIONS_ID = CS.CURSESSIONS_ID)
order by AP.AP_NAME, CS.START_TIME


То есть значительную часть запроса я просто повторил внутри подзапроса, что мне не нравится.
Если использовать WITH, то визуально запрос будет красивее (лаконичнее), но как я понимаю, WITH вызывает материализацию, что мне кажется нежелательным. В таблице CURSESSIONS число строк невелико (тысячи), но таблица RADACCT большая, там сотни миллионов и миллиарды записей.
По RADACCTID есть индекс, соединение между CURSESSIONS и RADACCT работает быстро (доли секунды), но весь приведенных запрос исполняется секунд 15-20. Если убрать в конце сортировку, то раза в 3-4 быстрее, но все равно хотелось бы ускорить.
Не посоветуете, как улучшить запрос? Лучше всего было бы вообще избегать лишних подзапросов и группировок, а сразу выполнить джойн с одной нужной строкой — но я не могу ее знать без IP-адреса от вышестоящих строк.
...
Рейтинг: 0 / 0
26.04.2021, 12:18
| Ответить | Цитировать | Написать  
Выбрать одну строку из множества в условии join
    #40065957
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
Alibek B.
Внезапно обнаружил, что в версии Oracle 10g работают функции inet_aton/inet_ntoa (раньше думал, что они доступны только с версии 11
Ты заблуждаешься, это не встроенные функции.
...
Рейтинг: 0 / 0
26.04.2021, 14:39
| Ответить | Цитировать | Написать  
Выбрать одну строку из множества в условии join
    #40065965
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Действительно.
Я искал в системной схеме (в function и package), не нашел.
Оказалось, что это функция в пользовательской БД.
Ну тогда вопрос снимается.
...
Рейтинг: 0 / 0
26.04.2021, 14:55
| Ответить | Цитировать | Написать  
4 сообщений из 4, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Выбрать одну строку из множества в условии join
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&fpage=21&tid=1880247]:
 0ms
get settings:
 12ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 80ms
get topic data:
 15ms
get forum data:
 3ms
get page messages:
 65ms
get tp. blocked users:
 2ms
others: 260ms
total:  459ms
созд. / загр.: 459ms
x
x
Закрыть


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