[20171203]关于raw类型.txt

最新推荐文章于 2022-03-29 15:52:59 发布

cuixun7780

最新推荐文章于 2022-03-29 15:52:59 发布

阅读量126

点赞数

[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