ORACLE 对二进制字符串的存储空间占用进行压缩

2022 年 03 月 07 日

349 次浏览

2883字数

前言

老早就想整这个东西了,但之前一直在找oracle有没有原生的函数能进行相关处理,至今还没找到。
目前oracle提供的自带函数里,只有一个bin_to_num函数能对二进制进行转换,但其输入参数却是个数不定的1或者0。假设我们有一个由1和0构成的超大文本,怎么把这个文本当成真正的二进制进行存储？

为什么要这么做？

我们做个这样的实验

create table bin_test (numb int,text varchar2(8),bina blob);
insert into bin_test values 
(11111111,'11111111',TO_BLOB(TRIM(to_char(bin_to_num(1,1,1,1,1,1,1,1),'XX'))));

插入的这三个值,分别是数字11111111,字符串'11111111',以及二进制的11111111。
接下来我们查看一下这3个值分别占用的字节数

select vsize(numb),vsize(text),dbms_lob.getlength(bina) from bin_test;

可以看到,用字符串方式存储的占用空间最大,数字其次,而二进制类型的占用空间最小,8个1仅仅只占用1个byte,是用文本存储的八分之一！

开整

首先当然是搜索引擎找解决方案了,但是目前在百度上搜到的进制转换都是针对很短的文本进行的处理,而且有些转换结果还是错的,好几页的搜索结果,文章标题都一样的。于是只能去BING碰碰运气。
果然,在著名的stackoverflow上找到了和我有一样想法的人
https://stackoverflow.com/questions/11188292/oracle-binary-data-types

但是,这个回答问题的,只给了2000长度的参数,假设我要处理的数据是超过2000的,则他这个方式会报错。
因此我对其进行了修改,让其支持clob参数的输入。
但是,问题又来了,他这个处理只是输出十六进制的字符串,实际上空间占用大小是真二进制的两倍,因此,我们要对它的输出结果进行转换。
oracle在12c版本新增了TO\_BLOB函数,可以直接将十六进制的文本转换为二进制数据,但是其实这是个隐式转换,to\_blob的传入参数是raw,传字符串进去时,它自动的先转成了raw。因此它只支持2000的长度。
这个时候我们就只能一段一段的去转换了(找了好久没找到官方有提供自带函数可以转的)。

create or replace function binstr_to_hexblob(as_binstr in clob) return blob is
  li_n      binary_integer default 0;
  ls_hexstr varchar2(16) default '0123456789ABCDEF';
  ls_return blob;
  C         CLOB;
begin
  if (as_binstr is null) then
    return null;
  end if;
  dbms_lob.createtemporary(ls_return, false);
  dbms_lob.createtemporary(C, false);
  li_n := 0;
  for i in 1 .. dbms_lob.getlength(as_binstr) loop
    li_n := li_n * 2 +
            abs(instr('01', dbms_lob.substr(as_binstr, 1, i)) - 1);
    if mod(i, 4) = 0 then
      dbms_lob.append(C, substr(ls_hexstr, li_n + 1, 1));
      li_n := 0;
    end if;
  end loop;
  FOR I IN 1 .. TRUNC(dbms_lob.getlength(C) / 2) + 1 LOOP
    BEGIN
      dbms_lob.append(ls_return,
                      TO_BLOB(DBMS_LOB.substr(C, 2, 1 + 2 * (I - 1))));
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
  return ls_return;
end;
/

逻辑就这么个逻辑,但这个函数问题很明显