Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Выбрать одну строку из множества в условии join  [new]
Alibek B.
Member

Откуда:
Сообщений: 3921
Внезапно обнаружил, что в версии Oracle 10g работают функции inet_aton/inet_ntoa (раньше думал, что они доступны только с версии 11 и приходилось использовать громоздкие выражения).
И тут же образовалась задача, где эти функции хотелось бы применить.
Есть запрос, возвращающий набор строк, в одной из которых содержится IP-адрес.
Есть вспомогательная таблица с подсетями:
CREATE TABLE INV_RES_IP4 (
	ADR VARCHAR2(20) NOT NULL,
	IP NUMBER(10,0), 
	MASK NUMBER(10,0), 
	DESCRIPTION VARCHAR2(200), 
	...
)

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

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

Ранее и в другом месте я делал так:
...
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 указать соединение с определенной строкой?

Сообщение было отредактировано: 26 апр 21, 10:27
26 апр 21, 10:33    [22314280]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать одну строку из множества в условии join  [new]
Alibek B.
Member

Откуда:
Сообщений: 3921
Сейчас у меня сделано так:
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-адреса от вышестоящих строк.
26 апр 21, 12:18    [22314379]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать одну строку из множества в условии join  [new]
Elic
Member

Откуда:
Сообщений: 30166
Alibek B.
Внезапно обнаружил, что в версии Oracle 10g работают функции inet_aton/inet_ntoa (раньше думал, что они доступны только с версии 11
Ты заблуждаешься, это не встроенные функции.
26 апр 21, 14:39    [22314473]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать одну строку из множества в условии join  [new]
Alibek B.
Member

Откуда:
Сообщений: 3921
Действительно.
Я искал в системной схеме (в function и package), не нашел.
Оказалось, что это функция в пользовательской БД.
Ну тогда вопрос снимается.
26 апр 21, 14:55    [22314489]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить