[20171203]關於raw型別.txt

[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函式.