PLSQL. Small utility package for grabbing statistical data
Small utility package for grabbing statistical data for getting elapsed time and captured memory (session pga memory):
Oracle. Hash keys
При необходимости хеширования на стороне базы данных вы можете воспользоваться различными способами:
- ORA_HASH, доступно с версии 10.2;
- DBMS_UTILITY.GET_HASH_VALUE, доступно с версии 9.0.1. По функциональности данный метод идентичен предыдущей функции ORA_HASH;
- пакет DBMS_OBFUSCATION_TOOLKIT. С версии 11.2 объявлен как DEPRECATED.
- DBMS_CRYPTO.HASH, доступно с 10 версии;
- STANDARD_HASH, доступно с версии 12.1
Первые два способа, работают одинаково:
select
1 + ora_hash('some_test_text') as ora_hash_value,
dbms_utility.get_hash_value('some_test_text', 1, power(2, 31) - 1) as get_hash_value
from dual
Но их использование для целей хеширования некорректно, так как хеш-функция возвращает 32-битное значение и как итог у Вас будут возникать коллизии, например - вероятность встретить два разных значения с одинаковыми хеш ключами среди набора 9300 строк составляет около 1% (источник). Дальше больше. Вывод очевиден. Данные функции могут использоваться и используются за счет этого при разбиении данных по BUCKET-ам (пример).
Остальные способы относятся к криптографическим.
Пакет DBMS_OBFUSCATION_TOOLKIT предоставляет возможность MD5 хеширования:
select
lower(dbms_obfuscation_toolkit.md5(input => utl_raw.cast_to_raw('qwerty'))) md5hash
from dual
Так как данный пакет объявлен устаревшим, то более правильно использовать пакет DBMS_CRYPTO, а именно его функцию DBMS_CRYPTO.HASH.
Данная функция принимает первым параметром значения следующих типов данных - RAW, BLOB и CLOB, но не VARCHAR2. Вторым параметром передается предустановленная константа, которая определяет алгоритм хеширования. До версии v.12c доступны только следующие константы - HASH_MD4, HASH_MD5 and HASH_SH1. Для новой версии же доступны все остальные константы. Ниже полный список данных констант:
Данные константы недоступны при использовании в SQL, но вместо указания константы можно указать их числовое значение, например - получим хеш ключ для строки 'qwerty':
select
lower(dbms_crypto.hash(utl_raw.cast_to_raw('qwerty'), 2)) md5hash
from dual
Как было отмечено с версии Oracle 11.2 была введена новая функция STANDARD_HASH. Данная функция поддерживает те же алгоритмы хеширования, что и DBMS_CRYPTO.HASH, но отличается тем, что проще используется в SQL и имеет большую скорость работы.
Первым параметром передается входное значение любого типа данных, за исключением LONG и LOB. Вторым параметром передается тип алгоритма хеширования в строковом виде. По-умолчанию используется значение 'SHA1', к остальным возможным значениям относятся следующие - 'MD5', 'SHA256', 'SHA384' и 'SHA512'.
with
test_data(n, hash) as (
select rownum, standard_hash(rownum, 'MD5')
from dual connect by rownum <= 1000000
)
select hash, count(*)
from test_data
group by hash
having count(*) > 1
Данный запрос генерирует 1 миллион хеш ключей без коллизий.
В заключение рассмотрим таблицу с оценкой времени выполнения ряда приведенных методов хеширования (источник).
Из таблицы видно, что самой быстрой является функция STANDARD_HASH. Но если у вас версия Oracle не столь нова, тогда в виде альтернативы можно использовать пакет DBMS_CRYPTO с алгоритмом хеширования SHA-1 (160 битов достаточно, чтобы избежать коллизий).
Напоследок приведу пример функции для получения хеш ключей по заданному алгоритму в зависимости от текущей версии Oracle:
create or replace function hash_key(p_string in varchar2, p_algorithm in varchar2) return varchar2 deterministic
is
l_algorithm varchar2(32767) := upper(trim(p_algorithm));
l_err_num number := -20000;
l_err_msg varchar2(50) := 'Given algorithm is not supported';
begin
-- for 12.1 and higher versions
$if (dbms_db_version.version = 12 and
dbms_db_version.release >= 1) or
dbms_db_version.version > 12 $then
if l_algorithm in ('MD5', 'SHA1', 'SHA256', 'SHA384', 'SHA512') then
return standard_hash(p_string, l_algorithm);
else
raise_application_error (l_err_num, l_err_msg);
end if;
-- for versions between 10.x and 12.0
$elsif dbms_db_version.version >= 10 $then
if l_algorithm = 'SHA1' then
return dbms_crypto.hash(utl_raw.cast_to_raw(p_string), dbms_crypto.hash_sh1);
elsif l_algorithm = 'MD5' then
return dbms_crypto.hash(utl_raw.cast_to_raw(p_string), dbms_crypto.hash_md5);
elsif l_algorithm = 'MD4' then
return dbms_crypto.hash(utl_raw.cast_to_raw(p_string), dbms_crypto.hash_md4);
else
raise_application_error (l_err_num, l_err_msg);
end if;
-- for versions less than 10.x
$else
if l_algorithm = 'MD5' then
return dbms_obfuscation_toolkit.md5(input => utl_raw.cast_to_raw(p_string));
else
raise_application_error (l_err_num, l_err_msg);
end if;
$end
end;
- ORA_HASH, доступно с версии 10.2;
- DBMS_UTILITY.GET_HASH_VALUE, доступно с версии 9.0.1. По функциональности данный метод идентичен предыдущей функции ORA_HASH;
- пакет DBMS_OBFUSCATION_TOOLKIT. С версии 11.2 объявлен как DEPRECATED.
- DBMS_CRYPTO.HASH, доступно с 10 версии;
- STANDARD_HASH, доступно с версии 12.1
Первые два способа, работают одинаково:
select
1 + ora_hash('some_test_text') as ora_hash_value,
dbms_utility.get_hash_value('some_test_text', 1, power(2, 31) - 1) as get_hash_value
from dual
Но их использование для целей хеширования некорректно, так как хеш-функция возвращает 32-битное значение и как итог у Вас будут возникать коллизии, например - вероятность встретить два разных значения с одинаковыми хеш ключами среди набора 9300 строк составляет около 1% (источник). Дальше больше. Вывод очевиден. Данные функции могут использоваться и используются за счет этого при разбиении данных по BUCKET-ам (пример).
Остальные способы относятся к криптографическим.
Пакет DBMS_OBFUSCATION_TOOLKIT предоставляет возможность MD5 хеширования:
select
lower(dbms_obfuscation_toolkit.md5(input => utl_raw.cast_to_raw('qwerty'))) md5hash
from dual
Так как данный пакет объявлен устаревшим, то более правильно использовать пакет DBMS_CRYPTO, а именно его функцию DBMS_CRYPTO.HASH.
Данная функция принимает первым параметром значения следующих типов данных - RAW, BLOB и CLOB, но не VARCHAR2. Вторым параметром передается предустановленная константа, которая определяет алгоритм хеширования. До версии v.12c доступны только следующие константы - HASH_MD4, HASH_MD5 and HASH_SH1. Для новой версии же доступны все остальные константы. Ниже полный список данных констант:
- HASH_MD4 CONSTANT PLS_INTEGER := 1;
- HASH_MD5 CONSTANT PLS_INTEGER := 2;
- HASH_SH1 CONSTANT PLS_INTEGER := 3;
- HASH_SH256 CONSTANT PLS_INTEGER := 4;
- HASH_SH384 CONSTANT PLS_INTEGER := 5;
- HASH_SH512 CONSTANT PLS_INTEGER := 6;
Данные константы недоступны при использовании в SQL, но вместо указания константы можно указать их числовое значение, например - получим хеш ключ для строки 'qwerty':
select
lower(dbms_crypto.hash(utl_raw.cast_to_raw('qwerty'), 2)) md5hash
from dual
Как было отмечено с версии Oracle 11.2 была введена новая функция STANDARD_HASH. Данная функция поддерживает те же алгоритмы хеширования, что и DBMS_CRYPTO.HASH, но отличается тем, что проще используется в SQL и имеет большую скорость работы.
Первым параметром передается входное значение любого типа данных, за исключением LONG и LOB. Вторым параметром передается тип алгоритма хеширования в строковом виде. По-умолчанию используется значение 'SHA1', к остальным возможным значениям относятся следующие - 'MD5', 'SHA256', 'SHA384' и 'SHA512'.
with
test_data(n, hash) as (
select rownum, standard_hash(rownum, 'MD5')
from dual connect by rownum <= 1000000
)
select hash, count(*)
from test_data
group by hash
having count(*) > 1
Данный запрос генерирует 1 миллион хеш ключей без коллизий.
В заключение рассмотрим таблицу с оценкой времени выполнения ряда приведенных методов хеширования (источник).
Из таблицы видно, что самой быстрой является функция STANDARD_HASH. Но если у вас версия Oracle не столь нова, тогда в виде альтернативы можно использовать пакет DBMS_CRYPTO с алгоритмом хеширования SHA-1 (160 битов достаточно, чтобы избежать коллизий).
Напоследок приведу пример функции для получения хеш ключей по заданному алгоритму в зависимости от текущей версии Oracle:
create or replace function hash_key(p_string in varchar2, p_algorithm in varchar2) return varchar2 deterministic
is
l_algorithm varchar2(32767) := upper(trim(p_algorithm));
l_err_num number := -20000;
l_err_msg varchar2(50) := 'Given algorithm is not supported';
begin
-- for 12.1 and higher versions
$if (dbms_db_version.version = 12 and
dbms_db_version.release >= 1) or
dbms_db_version.version > 12 $then
if l_algorithm in ('MD5', 'SHA1', 'SHA256', 'SHA384', 'SHA512') then
return standard_hash(p_string, l_algorithm);
else
raise_application_error (l_err_num, l_err_msg);
end if;
-- for versions between 10.x and 12.0
$elsif dbms_db_version.version >= 10 $then
if l_algorithm = 'SHA1' then
return dbms_crypto.hash(utl_raw.cast_to_raw(p_string), dbms_crypto.hash_sh1);
elsif l_algorithm = 'MD5' then
return dbms_crypto.hash(utl_raw.cast_to_raw(p_string), dbms_crypto.hash_md5);
elsif l_algorithm = 'MD4' then
return dbms_crypto.hash(utl_raw.cast_to_raw(p_string), dbms_crypto.hash_md4);
else
raise_application_error (l_err_num, l_err_msg);
end if;
-- for versions less than 10.x
$else
if l_algorithm = 'MD5' then
return dbms_obfuscation_toolkit.md5(input => utl_raw.cast_to_raw(p_string));
else
raise_application_error (l_err_num, l_err_msg);
end if;
$end
end;
Subscribe to:
Posts
(
Atom
)