[20171203]关于raw类型.txt
--//从来没有关注raw类型,昨天看https://blog.dbi-services.com/doag-2017-avg_row_len-with-virtual-columns/
--//我第一次接触一个应用使用sys_guid()函数生成键值,不过对方使用-分割,36位字符串长度.一个表有6个这样的字段.昏..
--//还真的没有反过来想利用raw类型可以减少磁盘空间占用.
1.环境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
-------------------- ---------- ----------------------------------------------------------------------------- -----
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
SCOTT@test01p> create table t (id number ,text raw(16));
Table created.
SCOTT@test01p> create index i_t_text on t(text);
Index created.
--//索引可以建立.
2.测试一:
SCOTT@test01p> insert into t values (1,'aa');
1 row created.
SCOTT@test01p> insert into t values (2,'AA');
1 row created.
SCOTT@test01p> commit;
Commit complete.
SCOTT@test01p> select * from t;
ID TEXT
--- -----
1 AA
2 AA
SCOTT@test01p> select dump(text) c20 ,dump(text,16) c30 from t;
C20 C30
----------------- ----------------
Typ=23 Len=1: 170 Typ=23 Len=1: aa
Typ=23 Len=1: 170 Typ=23 Len=1: aa
--//不管大小写实际上保存的都是大写AA.一样的东西. 可以发现长度占1个字符.
3.测试二:
SCOTT@test01p> insert into t values (3,'ZZ');
insert into t values (3,'ZZ')
*
ERROR at line 1:
ORA-01465: invalid hex number
--//仅仅支持0-9,A-F等16进制的字符.其他英文字符不行.
4.测试三:
SCOTT@test01p> update t set text='41' where id=2;
1 row updated.
SCOTT@test01p> commit ;
Commit complete.
SCOTT@test01p> select * from t where text='AA';
ID TEXT
---------- ----------------------------------------
1 AA
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID a4pp1xwt277wq, child number 0
-------------------------------------
select * from t where text='AA'
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| T | 1 | 23 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(RAWTOHEX("TEXT")='AA')
--//查询注意条件,要注意隐式转换.使用hextoraw函数.
SCOTT@test01p> select * from t where text=hextoraw('AA');
ID TEXT
---------- -----
1 AA
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 0vqkyw1y8mu1s, child number 0
-------------------------------------
select * from t where text=hextoraw('AA')
Plan hash value: 108023753
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 23 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T_TEXT | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
2 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TEXT"=HEXTORAW('AA'))
--//这样给开发提出一些特殊要求.
5.看看块内保存信息.
SCOTT@test01p> insert into t values (3,sys_guid());
1 row created.
SCOTT@test01p> commit ;
Commit complete.
SCOTT@test01p> select rowid , t.* from t;
ROWID ID TEXT
------------------ --- --------------------------------
AAAaIuAAJAAACSFAAA 3 506C93AB29AF454AA8967ACBF651E8A5
AAAaIuAAJAAACSFAAB 1 AA
AAAaIuAAJAAACSFAAC 2 41
SCOTT@test01p> @ rowid AAAaIuAAJAAACSFAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- ----------- ------ ----------------------------------------
107054 9 9349 0 0x2402485 9,9349 alter system dump datafile 9 block 9349
SCOTT@test01p> alter system dump datafile 9 block 9349;
System altered.
--//查看转储信息:
Block header dump: 0x02402485
Object id on Block? Y
seg/obj: 0x1a22e csc: 0x00.1953fb3 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2402480 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.010.0000607a 0x0140138f.065f.11 --U- 1 fsc 0x0000.01954151
0x02 0x000b.018.000012de 0x014001ba.040c.1a --U- 1 fsc 0x0000.01953fb5
bdba: 0x02402485
data_block_dump,data header at 0x1ee5064
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x01ee5064
76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f5a
avsp=0x1f57
tosp=0x1f57
0xe:pti[0] nrow=3 offs=0
0x12:pri[0] offs=0x1f5a
0x14:pri[1] offs=0x1f79
0x16:pri[2] offs=0x1f71
block_row_dump:
tab 0, row 0, @0x1f5a
tl: 23 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 04
col 1: [16] 50 6c 93 ab 29 af 45 4a a8 96 7a cb f6 51 e8 a5
tab 0, row 1, @0x1f79
tl: 8 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 02
col 1: [ 1] aa
tab 0, row 2, @0x1f71
tl: 8 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 03
col 1: [ 1] 41
end_of_block_dump
End dump data blocks tsn: 3 file#: 9 minblk 9349 maxblk 9349
总结:
1.占用空间减少1半.
2.输入类型存在限制,仅仅16进制字符,大小写不区分.
3.注意类型转换问题,要使用hextoraw函数.
