有如下一个A601_H1表,共有20个字段,原始数据类型及取值范围如下: A601_H1 类型宽度 取值 位数 A601_H1_H1_01 c2 01-99 7 A601_H1_H1_02 c3 001-999 10 A601_H1_H1_03 n1 1-3 2 A601_H1_H1_04 b1 1-2 1 A601_H1_H2_02 b1 1-2 1 A601_H1_H2_03 n3 1-255 8 A601_H1_H2_04 n2 1-60 6 A601_H1_H2_05 b1 1-2 1 A601_H1_H2_06 n1 1-4 2 A601_H1_H2_07 n1 1-6 3 A601_H1_H2_08 n1 1-4 2 A601_H1_H2_09 n1 1-7 3 A601_H1_H2_10 b1 1-2 1 A601_H1_H2_11 n2 1-12 4 A601_H1_H2_12 n2 1-12 4 A601_H1_H2_13 n2 1-11 4 A601_H1_H2_14 n2 1-12 4 A601_H1_H2_15 n2 1-40 6 A601_H1_H2_16 n1 1-4 2 A601_H1_H2_17 n1 1-9 4 58 分别按照number,char(存储数字字符) ,char(存储ASCII值),char(存储ASCII值合并为一个串)char(位存储合并为一个串)的办法建立 A601_H1,A601_H1_CHAR,A601_H1_ASC,A601_H1_COMB,A601_H1_BIT 同样插入40000条数据,如果只插入A601_H1_H1_02,那么不论哪种方式存储空间都是63 BLOCK,但是如果所有字段都插入,则不同。 因为时间限制,没有在存储ASCII值合并为一个串,位存储合并为一个串中填入实际字符,改为等长字符串。 create table A601_H1( A601_H1_H1_01 number(2), A601_H1_H1_02 number(3), A601_H1_H1_03 number(1), A601_H1_H1_04 number(1), A601_H1_H2_02 number(1), A601_H1_H2_03 number(3), A601_H1_H2_04 number(2), A601_H1_H2_05 number(1), A601_H1_H2_06 number(1), A601_H1_H2_07 number(1), A601_H1_H2_08 number(1), A601_H1_H2_09 number(1), A601_H1_H2_10 number(1), A601_H1_H2_11 number(2), A601_H1_H2_12 number(2), A601_H1_H2_13 number(2), A601_H1_H2_14 number(2), A601_H1_H2_15 number(2), A601_H1_H2_16 number(1), A601_H1_H2_17 number(1) ); insert into A601_H1 select 99,mod(level,1000),3,2,2,255,60,2,4,6,4,7,2,12,12,11,12,65,4,9 from dual connect by level <=40000; /* insert into A601_H1 ( A601_H1_H1_02) select mod(level,1000) from dual connect by level <=40000; */ commit; analyze table A601_H1 compute statistics; select blocks from tabs where table_name=''''A601_H1''''; /*
BLOCKS ---------- 370
*/ create table A601_H1_CHAR( A601_H1_H1_01 char(2), A601_H1_H1_02 char(3), A601_H1_H1_03 char(1), A601_H1_H1_04 char(1), A601_H1_H2_02 char(1), A601_H1_H2_03 char(3), A601_H1_H2_04 char(2), A601_H1_H2_05 char(1), A601_H1_H2_06 char(1), A601_H1_H2_07 char(1), A601_H1_H2_08 char(1), A601_H1_H2_09 char(1), A601_H1_H2_10 char(1), A601_H1_H2_11 char(2), A601_H1_H2_12 char(2), A601_H1_H2_13 char(2), A601_H1_H2_14 char(2), A601_H1_H2_15 char(2), A601_H1_H2_16 char(1), A601_H1_H2_17 char(1) ); insert into A601_H1_CHAR select 99,mod(level,1000),3,2,2,255,60,2,4,6,4,7,2,12,12,11,12,65,4,9 from dual connect by level <=40000; /* insert into A601_H1_CHAR ( A601_H1_H1_02) select to_char(mod(level,1000)) from dual connect by level <=40000; */ commit; analyze table A601_H1_CHAR compute statistics; select blocks from tabs where table_name=''''A601_H1_CHAR''''; /* BLOCKS ---------- 315 */ create table A601_H1_ASC( A601_H1_H1_01 char(2), A601_H1_H1_02 char(3), A601_H1_H1_03 char(1), A601_H1_H1_04 char(1), A601_H1_H2_02 char(1), A601_H1_H2_03 char(1), A601_H1_H2_04 char(1), A601_H1_H2_05 char(1), A601_H1_H2_06 char(1), A601_H1_H2_07 char(1), A601_H1_H2_08 char(1), A601_H1_H2_09 char(1), A601_H1_H2_10 char(1), A601_H1_H2_11 char(1), A601_H1_H2_12 char(1), A601_H1_H2_13 char(1), A601_H1_H2_14 char(1), A601_H1_H2_15 char(1), A601_H1_H2_16 char(1), A601_H1_H2_17 char(1) ); insert into A601_H1_ASC select 99,mod(level,1000),chr(3),chr(2),chr(2),chr(255),chr(60),chr(2),chr(4),chr(6),chr(4),chr(7),chr(2),chr(12),chr(12),chr(11),chr(12),chr(65),chr(4),chr(9) from dual connect by level <=40000;
/* insert into A601_H1_ASC ( A601_H1_H1_02) select chr((mod(level,255))) from dual connect by level <=40000; */ commit; analyze table A601_H1_ASC compute statistics; select blocks from tabs where table_name=''''A601_H1_ASC''''; /*
BLOCKS ---------- 265 */ create table A601_H1_COMB( A601_H1_H1_01 char(2), A601_H1_H1_02 char(3), A601_H1_H2 char(18) ); /* insert into A601_H1_COMB ( A601_H1_H1_02) select chr((mod(level,255))) from dual connect by level <=40000; */ insert into A601_H1_COMB (A601_H1_H1_01, A601_H1_H1_02,A601_H1_H2) select ''''99'''',chr((mod(level,255))),''''ABCDEFGHIABCDEFGHI'''' from dual connect by level <=40000; commit; analyze table A601_H1_COMB compute statistics; select blocks from tabs where table_name=''''A601_H1_COMB''''; /* BLOCKS ---------- 172 */
create table A601_H1_BIT( A601_H1_H1_01 number(2), A601_H1_H1_02 number(3), A601_H1_H2 char(8) ); insert into A601_H1_BIT (A601_H1_H1_01, A601_H1_H1_02,A601_H1_H2) select ''''99'''',mod(level,1000),''''AbCdEfGh'''' from dual connect by level <=40000; commit; analyze table A601_H1_BIT compute statistics; select blocks from tabs where table_name=''''A601_H1_BIT''''; /*
BLOCKS ---------- 116
*/
查询合并字符字段的各位数字的视图
create view A601_H1_V1 AS SELECT A601_H1_H1_01 , A601_H1_H1_02 , ascii(substr(A601_H1_H2,1 ,1)) A601_H1_H1_03 , ascii(substr(A601_H1_H2,2 ,1)) A601_H1_H1_04 , ascii(substr(A601_H1_H2,3 ,1)) A601_H1_H2_02 , ascii(substr(A601_H1_H2,4 ,1)) A601_H1_H2_03 , ascii(substr(A601_H1_H2,5 ,1)) A601_H1_H2_04 , ascii(substr(A601_H1_H2,6 ,1)) A601_H1_H2_05 , ascii(substr(A601_H1_H2,7 ,1)) A601_H1_H2_06 , ascii(substr(A601_H1_H2,8 ,1)) A601_H1_H2_07 , ascii(substr(A601_H1_H2,9 ,1)) A601_H1_H2_08 , ascii(substr(A601_H1_H2,10,1)) A601_H1_H2_09 , ascii(substr(A601_H1_H2,11,1)) A601_H1_H2_10 , ascii(substr(A601_H1_H2,12,1)) A601_H1_H2_11 , ascii(substr(A601_H1_H2,13,1)) A601_H1_H2_12 , ascii(substr(A601_H1_H2,14,1)) A601_H1_H2_13 , ascii(substr(A601_H1_H2,15,1)) A601_H1_H2_14 , ascii(substr(A601_H1_H2,16,1)) A601_H1_H2_15 , ascii(substr(A601_H1_H2,17,1)) A601_H1_H2_16 , ascii(substr(A601_H1_H2,18,1)) A601_H1_H2_17 FROM A601_H1_COMB;
从不同字段向合并字段后的表传输数据
insert into A601_H1_COMB select A601_H1_H1_01,A601_H1_H1_02,A601_H1_H1_03||A601_H1_H1_04||A601_H1_H2_02||A601_H1_H2_03||A601_H1_H2_04||A601_H1_H2_05||A601_H1_H2_06||A601_H1_H2_07||A601_H1_H2_08||A601_H1_H2_09||A601_H1_H2_10||A601_H1_H2_11||A601_H1_H2_12||A601_H1_H2_13||A601_H1_H2_14||A601_H1_H2_15||A601_H1_H2_16||A601_H1_H2_17 from A601_H1_ASC;
没有相关教程
|