发表于 2004年5/6月号

关于排序、选择和选择性
作者：Tom Kyte

我们的技术专家考虑排序、光标、结构设计、选择和提取。

你可以给我举一个例子说明如何用PL/SQL从一个文件读取数据、然后比如说按照两个字段排序并将结果读入变量吗？

我的回答是：不要用PL/SQL来做这件事。你应该用一个外部表；没有比这更简单的了。使用外部表你能轻松地将SQL的全部功能（包括ORDER BY）应用到读取该文件上。

为了使用外部表，我必须在数据库中建立一个目录对象，它指向文件所在的目录：

SQL> create or replace directory 
  2  data_dir as '/tmp/'
  3  /
Directory created.

然后需要一些数据。我将用到我的纯文本实用程序--可以从asktom.oracle.com/~tkyte/flat上获得：

SQL> host flat scott/tiger - 
>    emp > /tmp/emp.dat

SQL> host head /tmp/emp.dat
7369,SMITH,CLERK,7902,17-DEC-80,800,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81, ...

这样在操作系统中，就会有一个非常简单的界定的文件，现在就可以对它排序（或仅仅一般的查询）。我可以使用一个带有一个ORGANIZATION of EXTERNAL标记的表，该标记告诉Oracle数据库该表的数据不是驻留在数据库中，而是驻留在数据库之外的一个文件中。创建EMP表的语法如下

SQL> create table external_table
  2 (EMPNO NUMBER(4) ,
  3  ENAME VARCHAR2(10),
  4  JOB VARCHAR2(9),
  5  MGR NUMBER(4),
 6   HIREDATE DATE,
 7   SAL NUMBER(7, 2),
 8   COMM NUMBER(7, 2),
 9   DEPTNO NUMBER(2)
10  )
11  ORGANIZATION EXTERNAL
12  ( type oracle_loader
13    default directory data_dir
14    access parameters
15    ( fields terminated by ',' )
16    location ('emp.dat')
17  )
18  /
Table created.

创建外部表的语法中包含了一些与SQL Loader控制文件非常类似的语句（上面的语法中第12行到第16行）。 这不奇怪；一个外部表非常像数据库中的一个SQL Loader。 事实上，外部表能够完成SQL Loader可以完成的每一件事，而且还要更多，因为现在你可以任意使用SQL的全部功能。 要了解外部表的性能，请阅读《Oracle9i数据库实用工具》，网址为download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/part3.htm#436392。

既然创建了外部表，那么对文件排序以及对结果进行处理就变得像使用SQL一样简单了：

SQL> select empno, ename 
   2   from external_table 
   3  order by empno;
 
     EMPNO  ENAME
-----------  ----------
      7369  SMITH
      7499  ALLEN
...
      7934  MILLER
14 rows selected.
SQL> select empno, ename 
  2    from external_table 
  3   order by ename;
 
     EMPNO  ENAME
-----------  ----------
      7876  ADAMS
      7499  ALLEN
...
      7521  WARD
14 rows selected.

光标还是Ref光标

在一次应聘Oracle PL/SQL开发员职位的面谈中，招聘人员让我描述光标和ref光标之间的区别，以及什么时候该正确应用哪一个。请告诉我答案好吗？

好的。从技术上讲，在最基本的层次上它们是相同的。一个典型的PL/SQL光标按定义是静态的。相反，Ref光标可以动态地打开（直到运行时才知道这条查询），或者利用一组SQL静态语句来打开，选择哪种方法由逻辑确定（一个IF/THEN/ELSE代码块将打开一个或其它的查询）。例如，下面的代码块显示一个典型的静态SQL光标，光标C。此外，还显示了如何通过使用动态SQL或静态SQL来用ref光标（在本例中为L_CURSOR）来打开一个查询：

Declare
  type rc is ref cursor;
  cursor c is select * from dual;
  l_cursor rc;
begin
  if (to_char(sysdate,'dd') = 30) 
  then
    -- ref cursor with dynamic sql
    open l_cursor for 
      'select * from emp';
  elsif (to_char(sysdate,'dd') = 29)
  then
    -- ref cursor with static sql
    open l_cursor for 
      select * from dept;
  else
    -- with ref cursor with static sql
    open l_cursor for 
      select * from dual;
  end if;
  -- the "normal" static cursor 
  open c;
end;
/

在这个代码块中，你也许看到了最显而易见的区别：无论运行多少次该代码块，光标C总是select * from dual。相反，ref光标可以是任何结果集，因为"select * from emp"字符串可以用实际上包含任何查询的变量来代替。

在本例中不是显而易见的其他一些区别，包括ref光标能够被返回到客户端。这就是从Oracle的存储过程返回结果集的方式（请参阅asktom.oracle.com/~tkyte/ResultSets，以了解该主题的更多详细信息）。相反，PL/SQL静态光标不能返回到客户端，只有PL/SQL才能利用它。

另外，PL/SQL静态光标可以是全局的，而ref光标则不是。 也就是说，不能在包说明或包体中的过程或函数之外定义ref光标。 只能在定义ref光标的过程中处理它，或返回到客户端应用程序。

还有，ref光标可以从子例程传递到子例程，而光标则不能。 为了共享静态光标，必须在包说明或包体中把它定义为全局光标。 因为使用全局变量通常不是一种很好的编码习惯，因此可以用ref光标来共享PL/SQL中的光标，无需混合使用全局变量。

最后，使用静态光标--通过静态SQL（但不用ref光标）--比使用ref光标效率高，而ref光标的使用仅限于以下几种情况

• 把结果集返回给客户端
• 在多个子例程之间共享光标（实际上与上面提到的一点非常类似）
• 没有其他有效的方法来达到你的目标时，则使用ref光标，正如必须用动态SQL时那样

简言之，首先考虑使用静态SQL，只有绝对必须使用ref光标时才使用ref光标。

体系结构问题

我们在众多生产服务器上有许多满足不同业务需要的大量生产数据库。现在需要使用J2EE系统结构来设计和构建基于Web的应用程序，这些应用程序需要来自多个数据库的信息。我们现在讨论得到这一结果的各种可选方案。

我们分成两组，使用的方法各不相同。由于数据源是同类的--都来自Oracle，因此一组建议使用Oracle数据库链接来从多个数据库中获得数据，这样将获得更好的访问控制和访问并发性，并获得更好的应用程序透明性。另一组反对创建任何数据库链接，希望使用连接并在Java共享池连接级别上捕获数据，并让该应用程序去干这件事。采用这种方法，就完全避免了与供应商特定的软件进行紧密集成的需要。

我想知道在这个问题上你的想法。我想知道设计和构建使用多个数据源的基于Web的应用程序的最好方法是什么。

我非常奇怪这样的问题怎么经常被问到。实际上看起来针对这一问题似乎是要做这么一个简单的决定：是利用你花钱买来的东西（数据库）呢，还是编写需要设计、调试、调优以及后期还要维护的大量定制代码。我一直很难理解为什么这个问题总是被提出来。

简言之，第2组的人想要编写大量代码，并使他们在以后的简单生活中工作很安全。但是他们实际将永远完成不了这一任务，而且要满足对新功能的需求要花上10倍的时间。最终用户会惊奇为什么这件技术工作完成起来如此艰难。第2组的方法会使所有技术人员沮丧。

第1组的人希望使用成本效益更高和更快的方法。他们还能支持"新技术532143"，这项技术明年将会面世。J2EE是"新技术532140"，它只是一个中间层体系结构。

我辞掉后一份工作就是因为那儿的人就像第2组的人！他们就是坚持编写自己的两阶段提交代码（即使数据库已经有了），还坚持自己编写到其他数据库的网关（即使数据库已经有了）。他们要尽可能地执行自己的存储过程语言--所有的借口都是保持数据库的独立性。 现在回忆起来，我可以说他们什么都没完成。要想做成任何一件事，他们最后不得不向下一直进入到数据库去利用一些特性。他们在最顶层又添加了几层，但没有增加任何不存在的功能；事实上，他们向最终用户隐藏了许多功能，使最终用户不能使用数据库。

我不明白你为什么说用一个Oracle数据库（通过数据库链接）而不是n个数据库就会迫使你要"与供应商特定的软件紧密集成"--这究竟是什么意思呢？对我来说，紧密集成意味着实际上你正在用花钱买来的东西！它意味着你不是在重新创造一个优化器、一个连接引擎和一个数据库。它意味着实际上你可以利用那些花大笔钱买来的产品的高级特性。

难道第2组的这些人仅仅因为他们驾驶不止一辆汽车就要发明自己的驾驶装置吗？

关于这个话题网上已有大量讨论（请访问asktom.oracle.com/~tkyte/architecture.html，以了解Web上最初的提问和解答）。其中一个真正好的观点是：

第二种情况有很多问题。如果你不用数据库链接而要用J2EE去实现每件事，那么性能将是一场恶梦。所有查询结果屏幕都会显示一条消息，"请明天再来看结果……"。

请考虑这样一种情况，即从数据库1的查询1返回30万条记录，从数据库2的查询2返回1万条记录。你想返回给用户的最终结果是这两条查询的连接，它最后只返回1000条记录。如果使用数据库链接，那么Oracle数据库会完成所有的优化，并用尽可能最短的时间返回1000条记录。

如果在你小组中的某个人开始编写这一代码，那么他要把所有30万条记录都放到服务器的内存中，还要进行内存分页。这将毁掉整个系统的性能，所有用户都会遭殃。如果应用程序完全以数据为中心，那么在数据库连接级别上进行这种查询就不是正确的选择。

但是如果暂时忘掉性能问题，那么你是否想过这种情况会多么复杂？你真地想让你的商务应用开发人员编写那些实际上是模仿数据库所有功能的代码吗？他们不得不创建临时空间用于结果集的转入、转出。他们要执行嵌套循环、连接技术、散列连接、排序合并等等。数据库（每个数据库）都能完成这些事。在J2EE层上重新创建数据库可能是最糟糕的决定。请考虑一下当数据源、要求或最终用户的需要发生变化时会出现什么情况。当商务应用开发人员的注意力100%地放到创建他们自己新的数据库引擎上时，他们如何对此做出反应？（创建其中一件东西要花一些时间；而Oracle从事这种工作已经有25年多了。）

有选择的检索

我使用Oracle8i第3版（8.1.7）。用这个版本有可能只对选定的行创建索引吗？例如，当被检索的列为空（NULL）时（在我接触的各种应用程序中有很多这种情况），我知道事实上，用这个索引我根本看不到列中有NULL值的行。我突然想到，如果有可能告诉索引"只检索取值为……的列"或者相反，"不要对……列进行检索，"那么就能潜在地节省空间（内存和磁盘）。

这是从Oracle8i第1版（8.1.5）推出以来，我们利用基于函数的索引（由Oracle数据库企业版提供）所能做的事情。其工作过程是：检索一个函数，它返回你想要检索的数据值，如果没有这些值则返回NULL。由于全部NULL键没有进入B*树索引，所以，实际上你可以利用这项技术对你只感兴趣的那些行进行有选择的检索。 请看下面这个小例子：

SQL> create table t ( x int );
Table created.
 
SQL> create index t_idx on t(x);
Index created.
 
SQL> insert into t 
  2  select null from all_users;
51 rows created.
 
SQL> analyze index t_idx 
  2  validate structure;
Index analyzed.
 
SQL> select name, lf_rows 
  2    from index_stats;
 
NAME                      LF_ROWS
-------------------------  --------
T_IDX                          0

索引是空的。表中有51行，而在索引结构中只有0行，因为关键字全部为NULL。现在我如果插入一个非NULL值：

SQL> insert into t values ( 1 );
1 row created.
 
SQL> analyze index t_idx 
  2  validate structure;
Index analyzed.

SQL> select name, lf_rows 
  2    from index_stats;

NAME                       LF_ROWS
-------------------------  ---------
T_IDX                            1

最终有了一个索引项。接下来我让所有值都不为NULL：

SQL> update t set x = rownum;
52 rows updated.
 
SQL> analyze index t_idx 
  2  validate structure;
Index analyzed.
 
SQL> select name, lf_rows 
  2    from index_stats;
 
NAME                       LF_ROWS
-------------------------  ---------
T_IDX                           53

所有行都在检索出来了。现在，为了进行有选择的检索，我可以使用带有复杂判定条件的CASE（或DECODE，实际上可以是任意函数）函数来识别我想要的值。下面的例子对所有X取值小于25的行进行检索。如果表有许多行，而且大多数行的X值比如说都是100（表示要以某种方式处理和完成这些记录），而很小一部分X的值为1、2、3、……、25（表示它们正处于处理的某个阶段），这时这样的检索方式就很有用。它不仅节省空间，而且对优化器也很有吸引力，因为检索量很小，还非常具有选择性。在下面的例子中，只检索x < 25的行：

SQL> create index t_idx2 on 
  2  t( case when x < 25 then x end );
Index created.
 
SQL> analyze index t_idx2 
  2  validate structure;
Index analyzed.
 
SQL> select name, lf_rows 
  2    from index_stats;

NAME                        LF_ROWS
-------------------------  ----------
T_IDX2                           24
 

此外，我可以（我认为也应该）使用一个视图来通过这种检索方式隐藏查询的复杂性：

SQL> create or replace view v 
  2  as 
  3  select x, 
  4  case when x<25 then x end another_x
  5  from t;
View created.
 
SQL> analyze table t 
  2  compute statistics;
Table analyzed.
 
SQL> set autotrace traceonly explain

SQL> select * from v 
  2   where another_x = 5;

Execution Plan
-----------------------------------
SELECT STATEMENT Optimizer=CHOOSE 
  TABLE ACCESS (BY INDEX ROWID) OF 'T' 
    INDEX (RANGE SCAN) OF 'T_IDX2'
 
SQL> set autotrace off

正如你所看到的，我可以有选择地只检索我感兴趣的那些行，而且我可以让应用程序透明地访问该检索结果。请参阅asktom.oracle.com/~tkyte/article1，以便更深人地了解有关使用基于函数的索引的更多信息。

提取DDL

我的一个同事正在编写一个脚本用以提取DDL。他问我，从数据字典的角度，是否有一种方法可以确定NOT NULL约束（当创建包含大量非零值的表时建立的）与IS NOT NULL条件的检查约束之间的区别。我们无法通过查看数据字典视图来确定二者的区别。你能给与帮助吗？

有时做一件事最简单的办法就是根本不去做它。提取DDL就是这种事。你不必编写提取DDL的脚本。你可以使用下面两种方法之一：

• 使用EXP（导出）和IMP（导入）实用程序
• 使用Oracle9i及更高版本--DBMS_METADATA包把这个要花一周多时间完成的过程变成一个只需一步的简单查询！

下面的例子使用EXP和IMP：

$ exp userid=/ tables=t rows=n
Export: Release 9.2.0.4.0 - 
...
About to export specified tables 
. . exporting table           T
Export terminated successfully.

$ imp userid=/ full=y indexfile=t.sql
Import: Release 9.2.0.4.0 
...
Import terminated successfully.

$ cat t.sql
REM  CREATE TABLE "OPS$TKYTE"."T" 
REM  ("X" NUMBER(*,0), "Y" NUMBER(*,0))
REM  PCTFREE 10 PCTUSED 40 INITRANS 
REM  1 MAXTRANS 255 STORAGE(INITIAL 
REM  65536 FREELISTS 1 FREELIST GROUPS 
REM  1) TABLESPACE "USERS" LOGGING 
REM  NOCOMPRESS ;
CONNECT OPS$TKYTE;
CREATE INDEX "OPS$TKYTE"."T_IDX" ON 
"T" ("X" ) PCTFREE 10 INITRANS 2
MAXTRANS 255 STORAGE(INITIAL 65536 
FREELISTS 1 FREELIST GROUPS 1)
TABLESPACE "USERS" LOGGING ;

正如你所看到的，T.SQL完成了你要干的所有事。在Oracle9i中，用DBMS_METADATA包甚至更简单：

下一步 下载Tom的平面实用程序 阅读 有关外部表的文章 Oracle9i Database Utilities Guide（《Oracle9i数据库实用程序指南》） 有关从结果集中返回查询的文章 有关基于函数的索引的文章

package:

SQL> select dbms_metadata.get_ddl
  2         ( 'TABLE', 'T' ) ddl
  3   from dual;

DDL
------------------------------------
CREATE TABLE "OPS$TKYTE"."T"
(       "X" NUMBER(*,0),
"Y" NUMBER(*,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1
MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576
MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST
GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"

但实际上，你不需要写任何代码；这些工具已经都做了！

采用散列法还是不采用散列法

我正开始一个项目，要对密码进行加密并将它们存到数据库中。你在你的网站上提到决不要做这件事，散列法是正确的方法。散列法比加密法有什么好处？ 散列法能反向设计吗？

最根本的好处就是散列法无法逆向处理，而加密必须是可逆向处理的。登录过程的步骤是：

1. 用户给出用户名和密码
2. 验证它们的正确性
3. 如果正确则授予访问权限
4. 如果不正确则此次登录尝试失败

我们是不是必须保存密码以便执行第2步？绝对没必要。我们可以用Oracle、UNIX和大多数其他操作系统所用的方式来做：利用散列法。应用散列法的步骤非常简单。我们只需要用标准的散列函数传递所提供的用户名和密码。（Oracle在Oracle8i和Oracle9i中提供了DBMS_OBFUSCATION_TOOLKIT.MD5，在Oracle 10g中提供了DBMS_CRYPTO.HASH，它们都正是用于这一目的。）我们获得由这样的函数返回的散列值，并将它与已存储的该用户的散列值相比较。用这种方法，我们可以执行验证过程，但我们永远不必担心有人窃走我们的密码，因为我们没有保存密码！没有办法根据存储的散列值推导出密码。

每个负责安全性的人可以更安心地睡大觉，因为他们知道他们没有一张可能被窃取的满是密码（加密的或没有加密的）的表。请参阅asktom.oracle.com/~tkyte/hash.html，以了解有关这个重要话题的更多信息。