优化Oracle库表计划的若干办法[Oracle防范]

　　前言

　　绝大大都的Oracle数据库性能问题都是由于数据库计划不公道造成的,只有少部份问题根植于Database Buffer、Share Pool、Redo Log Buffer等内存模块配置不公道,I/O争用,CPU争用等DBA职责范围上.所以除非是面对一个业已完成不可变更的系统,不然我们不该过量地将关注点投向内存、I/O、CPU等性能调整项目上,而应关注数据库表本身的计划能否公道,库表计划的公道性才是程序性能的真正执牛耳者.
公道的数据库计划需求考虑以下的方面:

　　·业务数据以何种方法表达.如一个员工有多个Email,你可以在T_EMPLOYEE表中成立多个Email字段如email_1、email_2、email_3,也可以成立一个T_EMAIL子表来存储,乃至可以用逗号脱离开多个Email地址存放在一个字段中.

　　·数据以何种方法物理存储.如大表的分区,表空间的公道计划等.

　　·若何成立公道的数据表索引.表索引几近是提高数据表查询性能最有效的办法,Oracle拥有范例丰富的数据表索引范例,若何取舍挑选显得分外重要.

　　本文我们将目光主要聚焦于数据表的索引上,同时也将说起其他两点的内容.通过对一个简单的库表计划实例的解析引出计划中的不足,并一一改正.考虑到手工编写库表的SQL脚本原始且低效,我们将用目前最风行的库表计划工具PowerDesigner 10来报告表计划的历程,所以在本文中你还会理解到一些相关的PowerDesigner的利用本领.

　　一个简单的例子

　　某个开辟人员着手计划一个订单的系统,这个系统中有两个主要的业务表,辨别是订单基本信息表和订单条目表,这两张表具有主从关系的表,此中T_ORDER是订单主表,而T_ORDER_ITEM是订单条目表.数据库计划人员的计划成果如图 1所示:

图 1 订单主从表

　　ORDER_ID是订单号,为T_ORDER的主键,通过名为SEQ_ORDER_ID的序列产生键值,而ITEM_ID是T_ORDER_ITEM表的主键,通过名为SEQ_ORDER_ITEM的序列产生键值,T_ORDER_ITEM通过ORDER_ID外键关联到T_ORDER表.

　　需求文档指出订单记录将通过以下两种方法来查询数据:

　　·CLIENT + ORDER_DATE+IS_SHPPED:按照"客户＋订货日期+能否发货"条件查询订单及订单条目.

　　·ORDER_DATE+IS_SHIPPED:按照"订货日期+能否发货"条件查询订单及订单条目.

　　数据库计划人员按照这个要求,在T_ORDER表的CLIENT、 ORDER_DATE及IS_SHPPED三字段上成立了一个复合索引IDX_ORDER_COMPOSITE;在T_ORDER_ITEM为外键ORDER_ID成立IDX_ORDER_ITEM_ORDER_ID索引.

　　让我们看一下该份计划的终究SQL脚本:

/*订单表*/ create table T_ORDER ( 　　ORDER_ID NUMBER(10) not null, 　　ADDRESS VARCHAR2(100), 　　CLIENT VARCHAR2(60), 　　ORDER_DATE CHAR(8), 　　IS_SHIPPED CHAR(1), 　　constraint PK_T_ORDER primary key (ORDER_ID) ); create index IDX_CLIENT on T_ORDER ( 　CLIENT ASC, 　ORDER_DATE ASC, 　IS_SHIPPED ASC); /*订单条目子表*/ create table T_ORDER_ITEM ( 　ITEM_ID NUMBER(10) not null, 　ORDER_ID NUMBER(10), 　ITEM VARCHAR2(20), 　COUNT NUMBER(10), 　constraint PK_T_ORDER_ITEM primary key (ITEM_ID) ); create index IDX_ORDER_ITEM_ORDER_ID on T_ORDER_ITEM ( 　ORDER_ID ASC); 　alter table T_ORDER_ITEM add constraint FK_T_ORDER__REFERENCE_T_ORDER foreign key (ORDER_ID) references T_ORDER (ORDER_ID);

　　我们承认在ER关系上,这份计划并不存在的缺陷,但却存在以下有待优化的地方:

　　·没有将表数据和索引数据存储到差别的表空间中,而不加辨别地将它们存储到同一表空间里.这样,不但会造成I/O竞争,也为数据库的保护工作带来不便.

　　·ORACLE会自动为表的主键列成立一个普通B-Tree索引,但由于这两张表的主键值都通过序列供应,具有严峻的次序性（升序或降序）,此时手工为其指定一个反键索引（reverse key index）将越发公道.

　　·在子表T_ORDER_ITEM外键列ORDER_ID上成立的IDX_ORDER_ITEM_ORDER_ID的普通B-Tree索引非常合适设置为紧缩型索引,即成立一个紧缩型的B-Tree索引.因为一份订单会对应多个订单条目,这就意味着T_ORDER_ITEM表存在很多同值的ORDER_ID列值,通过将其索引指定为紧缩型的B-Tree索引,不但可以削减IDX_ORDER_ITEM_ORDER_ID所需的存储空间,还将提高表操作的性能.

　　·计划仅通过成立一个包含3字段IDX_ORDER_COMPOSITE复合索引满意如前所述的两种查询条件方法的索引是有问题的,事实上利用ORDER_DATE+IS_SHIPPED复合条件的查询将操纵不到IDX_ORDER_COMPOSITE索引.