建表的参数设置
• 存储参数PCTFREE
- 对于经常被UPDATE且被update的列所占字节比较大
的表，需要设置较大的PCTFREE，避免造成过多行迁
移。
-对于可能造成热点块的表，PCTFREE参数需要设置
较大，尽量减少热点块发生概率。
• 事务参数
- 对并发DML事务很大的表，可以在建表初期设置较大
的initrans参数。initrans值过小可能会引起ITL竞争，
导致热点块或者enqueue较为严重。
4
© 2010 Oracle Corporation
建表的参数设置
• Initrans使用原则
• 每个initrans在数据块上占用24Bytes空间
• Initrans不可以超过10
• 不可以修改maxtrans值
• 修改initrans后只对新的数据块起作用，如需要对全部数据起作
用需要重新组织表
• alter index xxx initrans 8;
• Alter table xxx storage (freelists 8) ;
注意: ASSM表空间上无需设置freelists
5
© 2010 Oracle Corporation
列设计
• 定长字符型列使用CHAR类型，不定长字符型列使用VARCHAR2
类型。
• 固定长度用char，相对varchar2可以提高查询速度
• 不定长度用varchar2,如果用char，则会在列末用空格补充，在处
理上需要用trim等函数去空格，因此容易造成处理的不便和效率问
题。
• 日期字段需定义为DATE类型。如果定义为varchar2或者char时需
要进行转换，影响效率.。需要数据精确到微秒的字段需定义为
TIMESTAMP类型。
• 列为null时，需定义default值，避免因为null而造成索引不能被用
到的情况。
• 使用NUMBER型时必须指定长度。由number得精度和密度来保障
数据的一致性
6
© 2010 Oracle Corporation
列设计
• 表中字段的命名长度不应该超过20个字节。
• 记录数达到千万级的表，必须进行分区，分区一般遵
循以下原则：
• 数据具有明显的范围属性，比如日期，大小等，且经常进行范
围条件查询的表，采用范围分区
• 数据具有明显的列表属性，比如地点，省份等，且经常用列表
条件查询的表，采用列表分区
• 数据不具有明显的范围属性或者列表属性，且数据量很大，则
可以采用hash分区
7
© 2010 Oracle Corporation
临时表
• 对于只对本事务有效的临时表使用ON COMMIT
DELETE ROWS关键字创建该表。
• 对于只对本会话有效的临时表使用ON COMMIT
PRESERVE ROWS关键字创建该表。
• 对于临时表空间要求比较大的业务系统，临时表要存
储在独立的表空间中，并且临时表空间的数据文件需
要放在单独的磁盘上。
8
© 2010 Oracle Corporation
索引
• 小表（数据量小于5000条记录为标准）不需要创建索引。
• 对于OLTP应用，分区表使用分区索引。
• 创建或重建大索引时需指定使用NOLOGGING子句，指定并行度
，指定较大的排序区，提高执行效率。
• 将记录差别数最多的列放在索引顺序的最前面。
• 对于OLTP应用的业务系统索引数据的重复率不能超过20%。
• 进行order by column desc排序时，创建column desc索引。
9
EXAMPLE:
ALTER SESSION SET WORKAREA_SIZE_POLICY=MANUAL;
ALTER SESSION SET SORT_AREA_SIZE=2000000000;
ALTER SESSION SET SORT_AREA_RETAINED_SIZE=2000000000;
CREATE INDEX XXX NOLOGGING PARALLEL X;
ALTER INDEX XXX NOPARALLEL;
© 2010 Oracle Corporation
索引
• 频繁使用的index需要放入库缓存的keep池中。
对于频繁使用的较小的index(可以根据sga buffer来确
定大小的标准)可以直接放入buffer的keep池，以提高
效率。对于较大或者更新比较频繁的表或者索引不建
议放在buffer 的keep池。对于系统经常访问的小表，
比如系统配置表等可以直接修改表和索引的属性，以
保留在buffer池中。
• 在OLTP系统中，少用位图索引；对于更新频繁的表，
少用位图索引。
10
© 2010 Oracle Corporation
索引
• 通过索引查询数据，行数一般不会超过全表的20%，否则，采用
全表扫描方式更合适些
• 10,15,20的原则。通过索引查询数据最好是少于表记录数的20%
，否则就最好用全表扫描Tom Kyte建议在20%
• 对索引的使用进行监控，可以发现那些索引有用，那些索引没有
用到。没有用到的索引可以直接删除。
• 重建索引的时间最好选择在维护时间窗口，如果一定要在业务处
理期间重建索引则使用online选项
• 如果索引的level不高，且不需要挪动索引的存储位置，可以选择
alert index ….coalesce
• 当索引的clustering_factor接近表的行数时，可以把表数据按照索
引列排序后进行重建。以提高索引查询的效率，接近表的block数
是最理想的情况。
• 反向索引不适合于表上含有较多>,>=,<,<=范围查询的系统，否则
反而会降低系统的整体性能
11
© 2010 Oracle Corporation
分区索引
12
• 通过下图定义的规则，可确定分区索引类型
索引字段是表分区字段的前缀？
该非分区字段是唯一索引？
Local Prefixed
是否性能在可承受范围，而分区的可管理
性、可用性更重要？
Global Prefixed
Local Non-prefixed
是数据仓库/交易系统？
Local Non-prefixed Global Prefixed
Yes
Yes
Yes
No
No
No
DSS OLTP
© 2010 Oracle Corporation
分区索引
• Hash Global分区索引介绍
• HASH-Partitioned Global索引是Oracle 10g开始提供的新特性。而在以前的版本中
，Oracle只支持
• Range-Partitioned Global索引。HASH-Partitioned Global索引的好处如下：
• 比Range-Partitioned Global索引易于实施。HASH-Partitioned Global索引是根据
索引字段值，通过Oracle内部的HASH算法自动均匀散列到定义的分区中。而
Range-Partitioned Global索引需要根据索引字段值的范围进行分区，因此实施和
维护的难度都大。
• HASH-Partitioned Global索引适合于在并发量、吞吐量很大的交易系统（OLTP）
中，对某些字段的访问冲突。尤其是sequence字段值。
• HASH-Partitioned Global索引适合于大批量的数据查询。HASH-Partitioned
Global索引不仅可以提供分区之间的并行查询，而且在分区内也可进行并行查询的
处理。
• 建立分区索引必须指定表空间，并且指定的表空间要与数据表空间分开，
这样便于管理，同时尽可能分开索引和数据的IO访问，提高效率
13
© 2010 Oracle Corporation
索引
14
• 索引数量
• 对于OLTP应用的业务系统，单个表上索引的个数不超过5个。
• 对于OLTP系统来说，索引数量太多会严重影响DML操作
• 索引类型选择btree or bitmap
• B-TREE索引，一般情况下选择普通的b-tree索引
• 位图索引
对于列的distinct value很少，且数据量较大，且DML操作很少，这
种情况可以选择使用位图索引。比如性别，distinct value只有男和女
且DML操作也会很少，此时选择位图索引，效率可能会更高。
© 2010 Oracle Corporation
复合索引
当需要创建复合索引时，需要慎重考虑列在索引中的顺
序，主要影响的是INDEX SKIP SCAN
• 当查询条件中有第一列时，不需要考虑列的分布情况
• 当查询条件中没有第一列时，需要考虑列的分布情况
。尽量把distinct value不多的列放在复合索引的第一列
• 如果复合索引所包含的字段超过3 个，那么仔细考虑其
必要性，考虑减少复合的字段；
• 不管是单列索引还是复合索引，当列允许为空，且实
际有空值时，在查询中可能会不走索引扫描，因为null
值不在索引条目中。因此尽可能选择not null的列做为
索引列，如果不能避免时，可以采用列为空时去一个
默认值的方法解决。
15
© 2010 Oracle Corporation
索引
• 函数索引
• 尽量不采用函数索引，函数索引会在insert以及select时多一次
函数计算的消耗。
• 递减索引
• 进行order by column desc排序时，综合考虑，创建column
desc索引，不再额外进行排序操作。
16
© 2010 Oracle Corporation
视图
• 物化视图的刷新间隔时间最小为3分钟。
• 物化视图的基表必须创建主键
17
© 2010 Oracle Corporation
存储过程、函数和包
• 存储过程、函数和包中不允许频繁的使用DDL语句。
• 存储过程、函数和包必须有相应的出错处理功能。
• 存储过程、函数和包中变量在引用表字段的时候，需
使用％rowtype类型。
• rowtype可以方便，容易的定义字段类型，当表结构或者字段
类型发生变化是不再修改程序
• rowtype在游标使用中可以整行的接受数据定义，而不需要对
一行数据的每个字段进行分别定义
18
© 2010 Oracle Corporation
Directory
• 目录设置要求与Oracle系统用户目录分开。
• Directory使用权限只赋予需要使用的数据库用户。
• 对应的操作系统目录必须对oracle操作系统用户开放读
写权限。
• 定期清理和备份Directory对应的操作系统目录。
19
© 2010 Oracle Corporation
同义词
• 对于只读用户，必须创建与表相同名字的别名。
• 别名的访问顺序：public别名-> private别名-> 与表同
名的对象。
20
© 2010 Oracle Corporation
Sequence
• sequence要求不连续时，需要指定cache和noorder，
特别是对于RAC系统而言，sequence 的nocache属性
可能会带来较大的性能影响。
• 对于RAC系统，sys.audses$,sys.idgen1$两个
sequence的cache值调整为10000或者更高，特别是短
连接且logon频率高的系统更需要调整
21
© 2010 Oracle Corporation
LOB对象
Oracle对大对象的操作机制象普通数据一样，因此大对
象需要被分解成多个小的数据片段才能完成这种数据操
作机制，这种小的数据片段就是CHUNK。
• 使用CLOB/BLOB
• 不可使用raw/raw long
• 分开存储, uniform. size tbs
• 使用16K CHUNK SIZE
• 升级到11g的SecureFiles LOB
22
© 2010 Oracle Corporation
数据库安全设计
数据库用户安全设计原则
• 数据库用户权限授权按照最小分配原则。
• 数据库用户要分为管理、应用、维护、备份四类用户
。
• 不允许使用sys和system用户建立数据库应用对象。
• 禁止grant dba to user。
23
© 2010 Oracle Corporation
数据库安全设计
序号用户类型描述
1 DATA OWNER 该类用户拥有数据库业务schema 对象，特别是tables及package和
Procedure以及其他对象。不对应用程序开放。只有通过对象授权和系
统授权，Transaction类型用户才可访问DATA OWNER类型用户；表空间
的使用，要通过空间授权配额，才可访问。
2 Transaction 该类型用户拥有数据库最小权限。只有通过角色ROLE对DATA OWNER
中的对象进行授权才可访问DATA OWNER中的对象（如GRANT
SELEECT,INSERT,UPDATE,DELETE ON XXX TO ROLE_1; GRANT TO ROLE_1 TO
Transaction等）。一般用于应用程序直接连接数据库进行数据更新操
作。
3 READ ONLY 该类型用户拥有且只拥有对DATA OWNER的表或视图有SELECT的权限，
并且必须不能有for update的子语句。必须通过角色授权才可以访问。
一般用于应用数据查询。
4 Monitor 该类型用户一般用于监控数据库性能，或者是第三方工具使用。
作为监控软件，如QUEST监控软件、patrol等；
24
© 2010 Oracle Corporation
数据库用户角色及权限设计规范
• 删除或锁定数据库测试用户scott。
• 修改可用用户的默认密码。
• 对查询用户只能开放查询权限。
• 对新建用户初次登录数据库强制修改密码。
• 开发环境，测试环境，生产环境中相同用户的权限设
置必须完全一致。
25
© 2010 Oracle Corporation
数据库用户角色及权限设计规范
序号角色类型描述
1 DATA OWNER R__DB：X为长度为4-8个字符的业务功能简称。
.2 Transaction R__T：X为长度为4-8个字符的业务功能简称。
3 Read Only R__Q：X为长度为4-8个字符的业务功能简称。
4 Monitor 应按照第三方厂商提供的方式命名。