|
任何应用系统的高性能运行，最基本的是数据库结构的设计。数据库结构是整个应用系统的根基，如果结构设计不好，只在数据库的参数来优化恐怕也不理想。下面给出关于Oracle环境的数据库结构设计的一点介绍，希望指出的是，这里的内容仅作者本人的一些经验和体会，不是理论和方法。仅供Oracle应用开发人员参考。
§9.1 分析阶段的对表的理解
在系统需求分析阶段，一般需要有经验的系统分析员及编程人一起与用户进行交流。这个阶段主要是听用户对需求的描述。但是当我们对用户的需求有初步的了解后，就需要分析员将这些用户需求变为文档，即写出数据需求定义。
一般来说，对用户的复杂的表结构、表中再套小表这样的大表，需要将它们的数据之间理清，拆开成几个相互有关系的表结构。不要简单地将用户的表原封不动地进行转换。
§9.2 正确的主键字段的选择
选择主键字段的前提是，该列的值不能有重复，也不能空，这是基本的要求。此外，建议注意下面的问题：
该列的值不能过长，比如不能使用单位名称作为主键；
建议用字符型或数字型（整数）作为主键；
不要用日期型，浮点型之类的字段作为主键；
如果只用一个字段作为主键出现重复，可采用加校验位或选用两字段作为主键，但不推荐用3个以上的字段作为主键。
§9.3 字段类型及长度的选择
对于一个表的字段来说，不同的设计者可能给出不同的类型，有时字段确实可以定义成不同的字段。在这方面，目前从理论上没有明确的限制和规定。因而许多人就认为字段的定义只要能满足用户的要求即可。其实对于一个要求很高且复杂的应用系统。定义字段可以说是一项值得认真考虑的技术问题。本人多年的应用设计和开发中的一点经验，仅供参考。
§9.3.1 如果能用字符型就不要用数字型
在许多地方，有一些字段你可以用数字型也可以用字符型，比如员工的身份证号，从表面看，它的内容全是数字。就由于它的表面特点，所以大多数人认为用数字类型是肯定的。其实我们想一下，身份证的每一位都有其特殊的意义，在数据的输入和查询中，都有一套严密的核查方法。比如最后一位表示男（1）或女（2）。当我们用字符型时，可以在输入中用
where substr(per_id,15,1)='1' or substr(per_id,15,1)='2' 来检查数据的正确性。如果采用数字这样的判断就不那么容易了。
另外，应该提醒许多新的Oracle使用的是，在Oracle里，字符型也能进行运算。如：
SQL> desc abc
名称 空? 类型
----------------------------------------- -------- --------------------
NAME VARCHAR2(20)
SAL VARCHAR2(5)
COMM VARCHAR2(5)
TOT NUMBER(6)
SQL> select * from abc;
NAME SAL COMM TOT
-------------------- --------- ---------- ---------------
Jodan 2234 1000 0
Johan 2000 2000 3000
SQL> select sum(sal),sum(comm),sum(to_number(sal)+to_number(comm)) tot
2 from abc;
SUM(SAL) SUM(COMM) TOT
---------- ---------- ----------
3234 3000 6234
§9.3.2 相互产生运算的数字型字段长度和精度要一致
在字段定义中，除了要求数字型必须能容纳下以后可能变化的数据外，还需要注意的地方就是：凡是相互可能参加运算的字段，它们的长度及精度最好要一致。这样做，初看起来，好象有的数值小的字段很浪费空间，其实，不要担心空间浪费而专门考虑需要多少字节就够了的想法。因为如果数字型的字段长度和精度参差不齐，有可能在某些运算中产生不必要的错误结果。比如，我们可能用PRO*C来编程，当我们根据表中的数字字段定义变量时，C编译不会检查这些变量与表中字段的一致性关系。举例来说，在表中字段说明很大，在C语言中可以说明的很小，但是当你将表中的字段的数据取出放到变量时，C 语言并不提示任何错误，这样，如果我们不作任何转换就直接进行运算的话，可能出现另外的结果。
§9.3.2 不要为了节省空间而将字段的长度缩小或拆开
现在的存储硬件发展很快，不要将以前的教科书的一些过时的理论带到当前的应用设计中来。这里所说的是指，实际的用户需要就是字段的长度需要。比如，有人经常将日期分成三个字段来定义，可能写 nian char(2), 即两位的年； yue char(1), 即 1 位的月，ri char(2), 两位的日。这样在年份只能存放两位，2000年存为00，2001年存为01 ；而 月份就不同了，设计者要求用户 1 月输入成 1， 2月输入成2 ,...而10月输入成a ；11月输入成b等。
这样的设计好象节省了空间，其实也没有节省多少，一条记录节省2个字节，几千万条记录才节省才节省几十兆字节。这样做无形中增加了程序的处理时间。对于优化和将来的移植很不利。
§9.4 将LOB类型的字段与其它的类型分开
Oracle8i提供了许多可以用于存储大对象数据的类型，如LONG、LONG RAW等。从性能出发，建议在设计表结构时将这些大对象类型与其它的类型数据分开存储。比如职工的基本档案，档案上有职工的基本情况信息和照片，在设计最好将照这样的LONG RAW类型与职工基本信息分开。然后采用唯一关键字段进行连接。
§9.5 采用具有编码的设计方法
对于具有在多处被使用的值应采用编码来设计，如职工的单位名称，因为一个单位有多名职工，如果每个职工对应的记录都有单位名称的话，就出现所谓的冗余。编码一般有两种，除了前面提到的冗余以外，另外还考虑一些在应用中的使用的方便性的问题。比如银行的存款应用。可以考虑设计一个叫“交易代码”的代码，它表示分别表示“存入”、“取出”及“结息”。可以将该字段取名为：
tran_code char(1) check (tran_code='1' or tran_code='2' or tran_code='3') ,
在设计操作处理界时，只给操作操作者选择“存入”、“取出”或“结息”三种可能，这样可以避免让操作员直接输入字符所带来的不一致等的问题。
除了上面提到的将具有共性的内容建立统一的代码表外，一个在设计中经常采用的方法就是：建立对象数据字典，与Oracle系统的数据字典类似。应用系统的数据字典也是为了各个对象的命名标准而采用的方法。凡是表名、索引名、表中的列名以及过程所用名称等最好以数据字典的方式在数据库建立。这样可以让Oracle系统来帮助我们进行检查。当一个新的对象需要命名时，可将其建立在这个字典中。字典中的名称要经过设计小组和用户讨论通过，通过之后就不能随便改动。在必须改动的时候，一定要经过ＤＢＡ的同意并且背案才能修改。另外一个建议的是。在建立对象名称时一定要避免与Oracle的保留字同名。为了做到这一点，建议先将Oracle的保留字（各种关键字）放入一个表里，然后再建立时进行比较，以达到避免重复的目的。
§9.7 哪种类型的表设为cache 方式
Oracle提供了一种方法，可以将表的数据驻留在内存的ＳＧＡ区内，这样叫做缓存（cache）。一般来说，把那些数据量不大，而且使用频率很高的表采用驻留的方式使Oracle系统完成启动后就被读到内存的SGA区中。这样的操作命令非常简单。比如：
ALTER TABLE emp CACHE;
§9.8 数据表和索引分开原则
按照Oracle的性能要求，建议在大型应用系统中，将表和索引分别存放在不同的表空间里，以得到较高的性能效果。一般在建立表和索引之前，先按照应用系统的年数据量估计表空间的大小，要考虑该应用系统的所有表的可能数据量来估计。如果在Oracle系统内要存放多个应用系统，如人事子系统，工资子系统等。则应该为每个子系统建立相应的表空间。在数据表空间规划好后，接着考虑规划对应索引的表空间。索引表空间一般要比数据的表空间小些，一般考虑是数据表空间的1/3或1/2即可。
在数据表空间和索引表空间都建立好后，要在建立表结构和建立索引的脚本上指定将它们存放在哪个表空间上。这样可避免缺省使用系统的SYSTEM表空间的问题。
§9.9 是否采用簇和分区
对于那些具有主－从关系的表，并且在处理时，经常是由主表来访问子表的相应记录的要求，建议采用簇 结构来建立表。比如会计到银行去给单位的所有职工发工资，就要找到该单位，然后对该单位的每个职工进行工资拨付处理。这样的应用使用簇进行设计要比用主键外部键要有效的多。
§9.10 表和索引的空间预分配
在设计Oracle应用时，我们经常采用在分析阶段得到的数据来估计空间的需要量。这样当然是由用户给出的，我们一般都是根据经验来决定。虽然是经验，但也有一个基本的规则。这些规则就是：以用户给出的各种表的每个字段的类型及长度、各表的年数据量（不要以月来估计）、加上相应的余量后得到表及索引的预分配空间大小。这里所谓的预分配就是说先给每个对象分配一个估计的空间。在以后运行一段时间后在进行调整。
另外建议是：在对象的存储空间分配中，不要使用pctincrease 这样的参数，最好是一次就分配够一年的数据量，而且下次扩展（NEXT 参数）也要分配以半年左右的数据量为基本值。不要过于小气。这样对于性能有好处。
§9.11 确定数据库对象存储大小
下面给出Oracle8i数据库（包括表、索引及簇）对象大小估计的方法简单介绍：
§9.11.1 非簇表的大小计算
一般在创建表、索引时，除了指定初始空间外，这应指定记录的年增长值。要估计其大小，需要考虑下面方面：
1．计算表或索引的块数：
数据库块＝2048bytes
块头信息＝90bytes(oracle8/8i)
块有效空间＝
2048－90＝1958 bytes available （有效字节）
用于更新的空间 = 块有效空间*(pctfree/100）：
假如pctfree=10，则
＝用于更新空间＝1958*(pctfree/100）
=1958*(10/100)
=196 bytes
即在有效的自由空间中，仅有196字节用于行扩展。
行记录使用的有效空间
＝1958-196=1762 bytes available
行平均长度
如果表中已有数据，则可以用下面命令计算（假定只有三列）：
Select AVG( NVL (vsite (column 1 ),0 )+
AVG(NUL (vsite (column 2 ),0 )+
AVG(NUL (vsite (column 3 ),0 ) ) Avg_Row_len
From table_name;
每一行使用的空间：
如果平均长度AVG_ROW_LEN＝24，这应加上每一列加1个字节，
如果表中有一个列（long字段）宽度超出250个字符，则这应加上1个字节。所以有：
space_used_per_row=AVG_ROW_len
+3
+Number of columns
+Number of long columns
对于本列，则
space used per row=24
+ 3
+ 3
+ 0
=30 bytes per row
块中可以存放多少行
对于一个块有1762字节的自由空间，每行长度为30个字节
则该块中可以存放
rows per block =TRUNC(1762 free bybes/30 bytes per row)
=58 row per block
由此可以估算出一个表所需的块数。
2．确定合适的pctfree
由于pctfree值控制了一个数据库块中存储记录的数量，所以该值设置是否合适是至关重要，要了解pctfree设置是否合适，可用：
analyze table table_name compute statistics；
命令来对表进行分析，然后查询user_tables数据字典中的记录：
select Num_Rows , /*number of rows*/
blocks , /*mumber of block*/
Num_Rows/blocks /* number of blocks per block */
From user_tables
Where table_name= UPPER('table_name');
由Num_Rows/Blocks可以知道每一个块中行的数量；
模仿用户的实际应用，用update更新表中的记录；
用analyze对该表再次分析，查询块中行的数量；
如果块的行数量没有变化，一些行没有被移到新的块中去，则说明pctfree值是合适的。
如果Avg_Space值在updade 后变大，则说明pctfree值还可以减少些。
3．正确设置pctused值
可用空间低于pctused值（百分比）时，则插入时可以使用刚被删除而空出的空间。它的缺省值是40%，它表示数据库块中只有40%的空间被使用。所以最好不要使用缺省值。一般建议将pctfree值和pctused 的总和为95比较合适，比如将pctused 75,则pctfree 20 。
§9.11.2 索引大小计算
块头占161字节
块大小＝2048 bytes
可用空间＝2048－161＝1887bytes
用于更新的空间，假设pctfree=10，
用于更新的空间＝1887*（pctfree/100）
=1887*0.1＝189 bytes
索引有效使用空间＝1887－189＝1698 bytes
索引列平均长度＝索引列中每一个值平均长度之和
如 table_name有column1和column2列用作建立索引，则：
select AVG(NVL(vsite(column1),0)) +
AVG(NVL(vsite(column2),0)) AVG_Row_length
From table_name
行的平均空间（space used per row）：
每一索引列加1个字节；
有索引列超出127字符的加1个字节；
索引头加8个字节
space used per row=Avg_Row_Length
+Number of columns
+Number of long columns
+8 header bytes
假设索引到的平均长度为16字节，则
Space used per Row=16
+2
+0
+8
+26 bytes per index [1] [2] 下一页
|
