ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Оптимизировать соединение
7 сообщений из 7, страница 1 из 1
Оптимизировать соединение
    #39797567
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Есть такая таблица:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
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-адресов определить группу, к которой они принадлежат.
В лоб это делается так:
Код: plsql
1.
2.
3.
4.
5.
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


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

Такой запрос выполняется в несколько раз быстрее:
Код: plsql
1.
2.
3.
4.
5.
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мс.
Мне кажется, что для индексированной таблицы с числовыми столбцами это долго.
...
Рейтинг: 0 / 0
06.04.2019, 01:00
| Ответить | Цитировать | Написать  
Оптимизировать соединение
    #39797721
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
С соединениями мне кажется лучше уже не сделать, по крайней мере мне в голову ничего не приходит.
Разве что 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? Или вернуться к строковому типу?
...
Рейтинг: 0 / 0
06.04.2019, 22:41
| Ответить | Цитировать | Написать  
Оптимизировать соединение
    #39797901
Фотография SeaGate
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SeaGate
Участник
Alibek B.,

Alibek B. С соединениями мне кажется лучше уже не сделать, по крайней мере мне в голову ничего не приходит.
Я бы следующий вариант сравнил с текущими 300мс:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
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.


Можно индексы на виртуальные колонки сделать, чтобы не указывать эти выражения постоянно.
...
Рейтинг: 0 / 0
07.04.2019, 23:01
| Ответить | Цитировать | Написать  
Оптимизировать соединение
    #39797910
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
Alibek B.,

проще хранить в виде строки битов, и пользоваться like для поиска по индексу. Всего 48 символов будет максимум
...
Рейтинг: 0 / 0
08.04.2019, 01:09
| Ответить | Цитировать | Написать  
Оптимизировать соединение
    #39797942
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
SeaGateМожно индексы на виртуальные колонки сделать, чтобы не указывать эти выражения постоянно.
Да, я про индексы на вычисляемые столбцы не подумал.

xtenderпроще хранить в виде строки битов, и пользоваться like для поиска по индексу. Всего 48 символов будет максимум
Уже само использование 48 символов вместо 6 байт мне кажется сомнительным.
А как именно использовать like? Если что-то вроде ADR like substr(lpad('%', LEN, '1')), то разве вычисления строки не съедят всю выгоду?
...
Рейтинг: 0 / 0
08.04.2019, 09:23
| Ответить | Цитировать | Написать  
Оптимизировать соединение
    #39798016
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
Alibek B.А как именно использовать like?
как-то так:
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
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
...
Рейтинг: 0 / 0
08.04.2019, 12:16
| Ответить | Цитировать | Написать  
Оптимизировать соединение
    #39798021
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Понял, то есть 96 символов вместо 6 байт на MAC-адрес и 1 байта на длину маски.
Мне это не кажется хорошим решением. Избыточно, ненаглядно и сложно.
Тогда уж лучше префикс хранить не как MAC/LEN, а в виде диапазона MAC1-MAC2 (начальный и конечный адрес префикса в числовом формате). Это тоже ненаглядно, но зато неизбыточно и несложно. И скорее всего between быстрее, чем like.
...
Рейтинг: 0 / 0
08.04.2019, 12:35
| Ответить | Цитировать | Написать  
7 сообщений из 7, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Оптимизировать соединение
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&gotonew=1&tid=1882608]:
 0ms
get settings:
 10ms
get forum list:
 16ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 46ms
get topic data:
 11ms
get first new msg:
 8ms
get forum data:
 3ms
get page messages:
 51ms
get tp. blocked users:
 1ms
others: 16ms
total:  170ms
созд. / загр.: 170ms
x
x
Закрыть


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