Oracle数据库开发指南（原创）

Oracle数据库系统是我们公司软件的核心部分，数据库性能的好坏直接关系到整个计算机系统的好坏乃至成败。Oracle数据库系统是一个高效，同时也是一个复杂的系统，不同的结构设计，不同的编程方法虽然都可以完成特定的功能，但是在性能、可靠性以及可维护性上可能会有巨大的差别。我们写这篇文档的目的是针对在数据库使用开发中发现过的问题，为公司内从事Oracle数据库开发的技术人员提供在设计和开发上的一些建议，使得我们的数据库系统和软件系统达到一定程度上的规范化，提高系统的性能，也使得维护应用程序、改变工作方式和增加新功能更为容易。

[@more@]

1．设计时的考虑

由于数据库物理结构上的设计主要是由DBA完成，所以这里主要是针对数据库逻辑结构，例如表和索引的设计，创建等，提供一些建议：

l         数据库的表和列的命名应使用具有意义的名称，避免使用ID、TEMP1、A1、A2等无意义或含义不够明确的标识符作为表或列的名称。

l         对于每个表和列，应尽可能地使用COMMENT命令增加注释，这样有助于其他人员更好地理解表中数据。例如

COMMENT ON TABLE emp IS ‘Employee information ’;

COMMENT ON COLUMN emp.emp_id IS ‘Unique Employee Identity Number’;

l         对于变长字符串的列应使用VARCHAR2类型，而不是CHAR。在Oracle数据库中，任何非空的CHAR(255)数据，即使其值只有一个字符，它也将占据255个字节的磁盘空间，并且在它所出现的任何索引中也要占用255个字节的空间。

l         表和索引应放在不同的表空间（tablespace）中，表和索引的分离有益于性能，也利于维护和管理。对于SCM系统，创建新表时可不指定表空间（我们已经指定了缺省的表空间SCMD），但是创建索引（包括Primary key，Unique Key）时，应指定表空间SCMX。其语法如下：

CREATE TABLE emp_info

   ( emp_id         INTEGER,

enp_name      VARCHAR2(32),

create_date    DATE,

CONSTRAINT pk_emp_info PRIMARY KEY (emp_id)

   USING INDEX TABLESPACE scmx

);

        或者

　　　　 CREATE INDEX idx_emp_info_name

           　ON emp_info ( emp_name )

           　TABLESPACE scmx;

l         对于程序中使用比较频繁，比较复杂冗长的SQL查询，例如三个以上表的连接条件查询，应通过预先创建视图VIEW来简化程序中的SQL语句，这样既使得程序更易读，同时也在表结构和关系发生变化时，我们可以不需要修改程序，而只需要修改相应的视图的定义即可。

2．开发时应注意的地方

数据库性能变差80％是由糟糕的SQL语句造成的，所以编写好的SQL对于保证数据库的正常运行非常重要，以下是我们DBA根据数据库的特点而给出的建议。

2.1 让你的SQL 语句更易读

尽管实际上易读性不会影响SQL 语句的性能，好的程序员会习惯于调用易读的代码，当你在WHERE 子句中存在多个条件的时候尤为重要，任何人读到这个子句的时候都可以确切地知道表是否已经被正确地归并了，并且也可以确切地知道条件的次序。这对我们DBA来说非常重要，可以快速有效的检测SQL语句的性能。

 

试着读下边的语句：

 

SQL>　SELECT EMPLOYEE_TBL.EMPLOYEE_ID， EMPLOYEE_TBL.NAME，

EMPLOYEE_PAY_TBL.SALARY， EMPLOYEE_PAY_TBL.HIRE_DATE

FROM EMPLOYEE_TBL EMPLOYEE_PAY_TBL

WHERE EMPLOYEE_TBL.EMPLOYEE_ID=EMPLOYEE_PAY_TBL.EMPLOYEE_ID

AND EMPLOYEE_PAY_TBL.SALARY>30000 OR (EMPLOYEE_PAY_TBL.SALARY

BETWEEN 25000 AND 30000 AND EMPLOYEE_PAY_TBL.HIRE_DATE < SYSDATE -

365)

 

下边是相同的查询的更易读的写法

 

SQL> SELECT E.EMPLOYEE_ID, E.NAME, P.SALARY, P.HIRE_DATE

2 FROM EMPLOYEE_TBL E,

3 EMPLOYEE_PAY_TBL P

4 WHERE E.EMPLOYEE_ID = P.EMPLOYEE_ID

5 AND P.SALARY > 30000

6 OR (P.SALARY BETWEEN 25000 AND 30000

7 AND P.HIRE_DATE < SYSDATE - 365)

 

注：注意在上边的查询中使用了表的别名，在第2 行语句中EMPLOYEE_TBL 被赋予了简单的别名E，在第3 行中EMPLOYEE_PAY 被赋予了别名P 。你可以看到在第4 5 6 行中E 和P 已经取代了表的名字，别名需要输入的字符量比输入全名时要少许多，更重要的是，使用别名以后查询变得更有组织和易读性，而使用表的命名则引出不必要的混乱。这两个查询是一样的，但是第二个显然是更易读的它非常具有结构性那就是查询的各个成份被回车合理地分开了，你可以很容易地找到哪里是查询的SELECT 部分（在SELECT 子句）中和都有哪些表被访问在FROM 子句中哪些是需要指定的条件（在WHERE 子句中）。

当使用表的别名时，请把别名前缀于每个Column上。这样可以减少解析的时间并减少那些由Column歧义引起的语法错误。

2.2　在程序中的SQL应省略数据库用户的名称

在Java、JSP、C/C++或其他程序语言中，我们不应在表名称前加用户名称前缀，这样将会使得数据库对象和程序移植时变得困难，即使该表不是属于所连接的用户，我们可以在数据库内部通过同义词SYNONYM来实现数据库表的位置的透明性，所以下面的SQL

 

SELECT a.emp_name, a.email, b.dept_name

  FROM scott.emp a, iv.dept b

 WHERE a.dept_id = b.dept_id

 

应改写为

 

SELECT a.emp_name, a.email, b.dept_name

  FROM emp a, dept_iv b

 WHERE a.dept_id = b.dept_id

 

其中dept_iv为同义词的名字，指向iv用户的dept表。

2.3 尽量避免全表扫描

当数据库服务为执行某一个SQL语句需要对表中的每一个记录进行检查时就会发生全表扫描，它通常在执行SELECT 语句时发生，但有时也会在更新和删除记录时发生，全表扫描通常是因为在WHERE 子句中使用了索引中没有的字段时发生，它就像对一本书一页一页地来看以找到所需内容一样，在大多数情况下，我们使用索引。

我们通常通过对经常在WHERE子句中使用的字段建立索引来避免全表扫描索引，所提供的找数据的方法与通过目录找书中的指定内容方法一样，使用索引可以提高数据的访问速度。

尽管程序员们并不赞成使用全表扫描，但是有时使用它也是适当的，例如：

 

l         你选择了一个表中的大多数行的时候

l         你在对表中的每一行记录进行更新的时候

l         表非常小的时候

 

对于头两种情况索引的效率是非常低的，因为数据库服务程序不得不频繁地读表和索引内容，也就是说索引只有在你所要找的数据只在表中所占比率很小的时候才会非常地有效，通常不会超过表中全部数据量的10%到15%。

此外最好在大型表中使用索引，当你设计表和索引的时候你要考虑表的大小，合适的索引应该是建立在对数据的熟悉上，知道那一列数据是最经常引用的。如果想让索引工作得好，你需要做一些试验。

注:当说到大表的时候这里的大是相对而言的，一个表比某个表相比可以说很大而它与另一个表相比时却又很小，表的大小的概念是与数据库中其它表的大小，可用的磁盘空间，可用的磁盘的数量以及类似的因素相关的。很明显：2GB 的表很大，而16KB的表是小的。如果一个数据库中表的平均大小是100MB，那么一个500MB 的表就是大的。

2.4 使用CASE语句

    我们经常需要在同一个表（或同一个表集）上进行不同的统计，通常的做法需要对同一个表作多次扫描操作，但是在Oracle中提供了一个简便的方法，使用CASE语句，使得只需要对表作一次扫描就可以得到多个统计结果，从而极大地提高了性能。

下面我们用一个例子来说明。如果我们分别需要知道月薪少于2000，月薪在2000到4000之间以及月薪大于4000的雇员人数，通常的做法是用三个查询

SELECT COUNT (*)

FROM employees

WHERE salary < 2000;

 

SELECT COUNT (*)

FROM employees

WHERE salary BETWEEN 2000 AND 4000;

 

SELECT COUNT (*)

FROM employees

WHERE salary>4000;

 

这样就需要对表作三次扫描，而使用如下的SQL语句则只需对表作一次扫描就可以得到所有数据。

SELECT COUNT (CASE WHEN salary < 2000

                   THEN 1 ELSE null END) count1,

       　COUNT (CASE WHEN salary BETWEEN 2001 AND 4000

                   THEN 1 ELSE null END) count2,

       　COUNT (CASE WHEN salary > 4000

                   THEN 1 ELSE null END) count3

  FROM employees;

 

2.5 加入一个新的索引

你经常会发现一些SQL 语句运行的时间长得不合情理，尽管其它的语句运行的性能看起来是可以接受的,例如当数据的检索条件改变或表的结构改变以后。

当我们加入一个WINDOWS 的应用前端时我们也会发现速度的下降，对于这种情况你首先要检查的是所用的目标表是否存在索引。然后大多数情况下我们会发现表是有索引的，但是在WHERE 子句中所使用的新条件没有索引，看一下SQL 语句中的WHERE 子句，我们要问的是：“是否可以加入其它的索引”。如果是在下列条件下，那么答案是肯定的。

 

l         最大的限制条件返回数据少于表总数据量的10%

l         最大的限制条件在SQL语句中是经常使用的

l         条件列的查询将会返回一个唯一的值

l         列经常被ORDER BY 或GROUP BY 子句所引用

 

也可以使用复合索引，复合索引是基于表中两个或更多列的索引，如果在SQL 语句中经常将两列一起使用时这种索引会比单列索引更有效，如果在一起的索引列经常是分开使用的，特别是在其它的查询中，那么单列索引则是更合适的，所以你要经过试验来判断在你的数据库中使用哪一种索引会是更合适的。

2.6 在查询中各个元素的布局

在你的查询中最好的元素布局，尤其是在WHERE 子句中，是根据解释器处理SQL 语句的步骤和次序而定的，在条件中安排被索引过的列，这样的条件将会查找最少的记录。

你不一定非要在WHERE 子句中使用已经被索引过的列，但是显然这样做会更有效。试着调整SQL语句以使它返回的记录数最少。在一个表中返回记录数最少的条件就是最大的限制条件。在通常的语句中。你应该把最大的条件限制语句放在WHERE 子句的最后（ORACLE 查询优化会对WHERE 子句从后向前读，所以它会最先处理我们放置的条件语句）。

当优化器首先读到最大条件限制语句以后，它就将为以后的条件所提供的结果集缩减至最小了，下一个条件将不再搜索整个表，而是搜索经过最大条件限制过的子集，所以数据的返回就会更快。在复杂查询中的多个查询，子查询，计算以及使用逻辑条件（AND/OR/NOT）中最大限制条件可能并不清晰。

下边的测试是我们对用两种不同的方法来查询相同的内容所耗用时间的差异，该例子使用ORACLE 9关系数据库系统切记在解释器中的优化是从后向前进行的在创造一个SELECT语句之前，对于每一个条件我们都选择了独立的行，下边是不同的条件给出的数值。

 

Condition                   Distinct Values

calc_ytd = '-2109490.8'     13,000 +

dt_stmp = '01-SEP-96'       15

output_cd = '001'           13

activity_cd = 'IN'          10

status_cd = 'A'             4

function_cd = '060'         6

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/41224/viewspace-780380/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/41224/viewspace-780380/