经常见到有人在系统设计时用GUID或MD5产生的32位字符串做主键,即使一些按日期产生的业务编码如2001010100001这样的字符串,都用varchar2来存储,总觉得不是很好,如果数据量不大的话,也无可厚非,但如果运用于存储海量的数据,那将会造成存储成本的巨增,请看下面的测试:
[@more@]16:58:49 SQL> create table test(a1 varchar2(32),a2 number);
Table created.
16:59:03 SQL> insert into test values('20010101000001',20010101000001);
1 row created.
16:59:26 SQL> commit;
Commit complete.
16:59:27 SQL> select dump(a1),dump(a2) from test;
DUMP(A1)
----------------------------------------
DUMP(A2)
----------------------------------------
Typ=1 Len=14: 50,48,48,49,48,49,48,49,48,48,48,48,48,49
Typ=2 Len=8: 199,21,2,2,2,1,1,2
begin
for i in 1..100000 loop
insert into test values('200101010000010000',200101010000010000);
end loop;
commit;
end;
17:05:59 SQL> analyze table test compute statistics;
Table analyzed.
17:07:20 SQL> select column_name,avg_col_len from user_tab_columns where table_name='TEST';
COLUMN_NAME AVG_COL_LEN
------------------------------ -----------
A1 18
A2 8
各位自己看,如果编码不含一些字符的话,用number确实很有优势的。。。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16648/viewspace-981145/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16648/viewspace-981145/