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

Откуда:
Сообщений: 3117
Есть такая таблица:
CREATE TABLE INV_RES_MAC
(
  ADR          NVARCHAR2(20)                    NOT NULL,
  MAC          NUMBER(16),
  LEN          NUMBER(3),
  HOSTNAME     NVARCHAR2(80),
  DESCRIPTION  NVARCHAR2(200),
  ...
);

CREATE UNIQUE INDEX INV_RES_MAC_FULL ON INV_RES_MAC (MAC, LEN);
CREATE UNIQUE INDEX INV_RES_MAC_PK ON INV_RES_MAC (ADR);

ALTER TABLE INV_RES_MAC ADD (
  CONSTRAINT INV_RES_MAC_CHK_LEN  CHECK (LEN between 0 and 48),
  CONSTRAINT INV_RES_MAC_CHK_MAC  CHECK (MAC between 0 and 281474976710655),
  CONSTRAINT INV_RES_MAC_PK  PRIMARY KEY (ADR) USING INDEX INV_RES_MAC_PK);


В таблице перечислены MAC-адреса и группы MAC-адресов. Для групп задается LEN, который определяет количество бит, принадлежащих группе (в основном там 24).
Мне нужно для индивидуальных MAC-адресов определить группу, к которой они принадлежат.
В лоб это делается так:
select ...
from INV_RES_MAC mac
left join INV_RES_MAC p on (bitand(mac.MAC,281474976710656-power(2,48-p.LEN)) = p.MAC and nvl(mac.LEN,48) >= p.LEN and mac.ADR != p.ADR)
where mac.LEN is null
order by mac.MAC, p.MAC, p.LEN

Но выполняется такой запрос неторопливо, что неудивительно.

Такой запрос выполняется в несколько раз быстрее:
select ...
from INV_RES_MAC mac
left join INV_RES_MAC p on (p.MAC <= mac.MAC and p.MAC+power(2,48-p.LEN)-1 > mac.MAC)
where mac.LEN is null
order by mac.MAC, p.MAC, p.LEN


Можно ли оптимизировать еще больше?
Сейчас в таблице примерно 35к записей, из них примерно 40 записей-хостов (LEN is null), но запрос выполняется почти 300мс.
Мне кажется, что для индексированной таблицы с числовыми столбцами это долго.
6 апр 19, 01:00    [21854520]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать соединение  [new]
Alibek B.
Member

Откуда:
Сообщений: 3117
С соединениями мне кажется лучше уже не сделать, по крайней мере мне в голову ничего не приходит.
Разве что power заменить на заранее вычисленные числа.
Но теперь хочу спросить совет по хранению.

Например есть такой блок MAC-адресов (префикс), выделенный для Ауди: 70-B3-D5-01-Bx-xx.
В числовом виде это значение 123917675114496 с длиной маски 36 бит.
Если его хранить в символьном виде VARCHAR2, то оно соответствует "70B3D501B000/36".
Таблица в примере хранит адреса именно так, в столбце VARCHAR2(20).

Но намного красивее смотрится, если хранить в бинарном формате.
Если использовать столбец с типом RAW(6), то в нем хранится значение 0x70B3D501B0.
Занимает намного меньше места, лучше индексируется, а бонусом еще и правильно сортируется (при строковом типе буквы предшествовали цифрам).
Проблема только в том, что в этом типе минимальная единица байт, а 36 бит это четыре с половиной байта.
То есть более правильным было бы хранить в столбце значение 0x70B3D501B, а не 0x70B3D501B0, но половину байта хранить нельзя, поэтому я "добиваю" нулями оставшиеся биты.
В принципе сейчас с типом RAW у меня все замечательно работает и даже быстрее. чем со строковым типом данных.
Но потенциально возможна следующая проблема.
Сейчас у меня в таблицу внесен префикс 70-B3-D5-01-Bx-xx. Допустим завтра мне нужно будет добавить в таблицу более "узкий" префикс 70-B3-D5-01-B0-xx. Однако в бинарном виде это получится 0x70B3D501B0 и констрейн PK не даст сохранить эту запись.

Как тут лучше поступить? Сделать столбец LEN обязательным и добавить его в PK? Или вернуться к строковому типу?
6 апр 19, 22:41    [21854873]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать соединение  [new]
SeaGate
Member

Откуда: Новосибирск
Сообщений: 1610
Alibek B.,

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

Я бы следующий вариант сравнил с текущими 300мс:
PDB> create index inv_res_mac_individ_i on inv_res_mac(case when len is null then 1 end);

Index created.

PDB> create index inv_res_mac_end_i on inv_res_mac(mac, mac + power(2, 48 - len) - 1);

Index created.
PDB> explain plan for
  2  select mac.*
  3  from INV_RES_MAC mac
  4  left join INV_RES_MAC p on (p.MAC <= mac.MAC and p.MAC+power(2,48-p.LEN)-1 > mac.MAC)
  5  where case when mac.LEN is null then 1 end = 1
  6  order by mac.MAC, p.MAC, p.LEN;

Explained.

PDB> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3793981070

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                       |     1 |   234 |     2  (50)| 00:00:01 |
|   1 |  SORT ORDER BY                        |                       |     1 |   234 |     2  (50)| 00:00:01 |
|   2 |   NESTED LOOPS OUTER                  |                       |     1 |   234 |     1   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| INV_RES_MAC           |     1 |   195 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | INV_RES_MAC_INDIVID_I |     1 |       |     1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| INV_RES_MAC           |     1 |    39 |     0   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN                  | INV_RES_MAC_END_I     |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access(CASE  WHEN "LEN" IS NULL THEN 1 END =1)
   6 - access("MAC"."MAC"<"P"."SYS_NC00007$"(+) AND "P"."MAC"(+)<="MAC"."MAC")
       filter("MAC"."MAC"<"P"."SYS_NC00007$"(+))

20 rows selected.

Можно индексы на виртуальные колонки сделать, чтобы не указывать эти выражения постоянно.
7 апр 19, 23:01    [21855344]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать соединение  [new]
xtender
Member

Откуда: Мск
Сообщений: 5146
Alibek B.,

проще хранить в виде строки битов, и пользоваться like для поиска по индексу. Всего 48 символов будет максимум
8 апр 19, 01:09    [21855364]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать соединение  [new]
Alibek B.
Member

Откуда:
Сообщений: 3117
SeaGate
Можно индексы на виртуальные колонки сделать, чтобы не указывать эти выражения постоянно.

Да, я про индексы на вычисляемые столбцы не подумал.

xtender
проще хранить в виде строки битов, и пользоваться like для поиска по индексу. Всего 48 символов будет максимум

Уже само использование 48 символов вместо 6 байт мне кажется сомнительным.
А как именно использовать like? Если что-то вроде ADR like substr(lpad('%', LEN, '1')), то разве вычисления строки не съедят всю выгоду?
8 апр 19, 09:23    [21855443]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать соединение  [new]
xtender
Member

Откуда: Мск
Сообщений: 5146
Alibek B.
А как именно использовать like?

как-то так:
+
create or replace function mac_to_bin(n int) return varchar2 deterministic as 
 res varchar2(96);
begin
  if n <1 or n>=power(2,48) then raise value_error; end if;
  for i in reverse 0..47 loop
     res:=res||sign(bitand(n,power(2,i)));
  end loop;
  return res;
end;
/
create or replace function mac_to_bin_mask(n int, len int) return varchar2 deterministic as 
 res varchar2(96);
begin
  return substr(mac_to_bin(n),1,len)||'%';
end;
/
alter table INV_RES_MAC add mac_bin varchar2(48) generated always as (mac_to_bin(mac));
alter table INV_RES_MAC add mac_bin_mask varchar2(48) generated always as (mac_to_bin_mask(mac));

и просто искать p.mac_bin like mac.mac_bin_mask
8 апр 19, 12:16    [21855732]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать соединение  [new]
Alibek B.
Member

Откуда:
Сообщений: 3117
Понял, то есть 96 символов вместо 6 байт на MAC-адрес и 1 байта на длину маски.
Мне это не кажется хорошим решением. Избыточно, ненаглядно и сложно.
Тогда уж лучше префикс хранить не как MAC/LEN, а в виде диапазона MAC1-MAC2 (начальный и конечный адрес префикса в числовом формате). Это тоже ненаглядно, но зато неизбыточно и несложно. И скорее всего between быстрее, чем like.
8 апр 19, 12:35    [21855750]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить