ORA FAQ 性能调整系列之——当索引第一列由序列产生，一个逆序索引有什么用？

答案：
ORA FAQ 性能调整系列之——
The Oracle (tm) Users' Co-Operative FAQ
Why would a reverse index be useful when the leading column of the index is generated from a sequence ?
当索引第一列由序列产生，一个逆序索引有什么用？
--------------------------------------------------------------------------------

Author's name: Norman Dunbar 
Author's Email: Oracle@bountifulsolutions.co.uk
 Date written: 18/04/2004
Oracle version(s): 9.2.0.3
 
--------------------------------------------------------------------------------

When you store data in an indexed table, certain columns of data are copied into the index alongside the rowid of the data row in the table. The data in the table is stored 'randomly', or at least, not necessarily in the order you put them there.
当你在一个索引表中存储数据时，数据的某些列同表中数据行的ROWID被拷贝进索引。表中的数据是“随机地”存放的，或者至少不需要按照你放入它们的顺序。

The index entries, on the other hand, must be stored in order, otherwise the usability of the index is removed. If you could walk through the entries in an index, you would see that they are in order, usually ascending, but since 8i, descending also works. 
而索引项应当按顺序存储，否则其毫无用途。如果你深入一个索引的各项，会看到他们是有序的，通常升序，但从8i开始，也可以降序。

Entries are stored in order of their internal representation, not necessarily the same as what you see on screen when you SELECT the columns from a table. 
项目是根据内部表示的顺序存储的，并不需要与你从表中SELECT列时在屏幕上看到的一致。

If the indexed column(s) contain character data (CHAR, NCHAR, VARCHAR2 or NVARCHR2) then the data will appear on screen exactly as it does in the index. For example, if the column contains 'ORACLE' the index entry will also be 'ORACLE'. 
若索引列含有字符数据（CHAR, NCHAR, VARCHAR2或NVARCHR2），那么数据在屏幕上的显示将和在索引中一致。例如，若列中含有“ORACLE”，那么索引项中也是“ORACLE”。

We can use the DUMP command to show us the internal representation of any data type. This command takes four parameters. The first is the data you wish to dump, the second is the base you wish to dump it in. The default is 10 which means that DUMP will display the characters in decimal, or base 10. The other allowed values are 8 (Octal), 16 (Hexadecimal) or 17 (Characters). 
我们可以使用DUMP指令来显示任何数据类型的内部表示。这一命令有四个参数，第一个是要dump的数据，第二个是要dump出的基数，缺省是10，即DUMP将显示十进制字符。其他的可选参数是8（八进制），16（十六进制），17（字符）。

The third parameter is the start position in the data you wish to dump from and the final parameter is the amount of data you wish to dump. All but the first parameter have sensible defaults. 
第三个参数是数据中要dump的开始位置，最后一个参数是要dump的数据量。除了第一个参数外，其他都有合理的缺省值。

Using DUMP, we can see the individual character codes for our 'ORACLE' data : 
使用DUMP，我们可以看到“ORACLE”数据的各个字符码：

SQL> select dump('ORACLE',10) from dual;

DUMP('ORACLE',10)
-------------------------------
Typ=96 Len=6: 79,82,65,67,76,69


We can prove that this is correct by converting back from decimal character codes to actual characters : 
我们可以证明，可以正确的将十进制字符码转换回真实字符：

SQL> select chr(79),chr(82),chr(65),chr(67),chr(76),chr(69) from dual;

C C C C C C
- - - - - -
O R A C L E


We could have used base 17 to do the same thing : 
我们可以使用17作为基数实现同样的事情：

SQL> select dump('ORACLE',17) from dual;

DUMP('ORACLE',17)
-------------------------
Typ=96 Len=6: O,R,A,C,L,E


Numeric columns are very much different. The internal format of a number is different from that which appears on screen after a SELECT because the internal format is converted to ASCII format so that it can be displayed. We can see this in the following, first in character format : 
数字列是不同的。数的内部格式与SELECT后在屏幕上的显示是不同的，内部格式转换为ASCII格式才能正常显示出来。我们可以如下察看，首先是字符格式：

SQL> select '1234' as "1234",
  2  dump('1234', 17)
  3  from dual;

1234 DUMP('1234',17)
---- ---------------------
1234 Typ=96 Len=4: 1,2,3,4


Then in internal format :
然后内部格式：

SQL> select 1234 as "a number",
  2  dump(1234, 17) 
  3  from   dual;

a number   DUMP(1234,17)
---------- --------------------
   1234 Typ=2 Len=3: c2,^M,#


The first columns in both examples look identical, but this is only because SQLPlus has converted the internal format of the number 1,234 into the character format so that the display device (the monitor screen) is able to show it. Binary characters have a nasty tendency to disrupt character devices like computer monitors when running in text mode. 
两个例子的第一列看起来一样，但这只是因为SQLPlus将数1,234的内部格式转换为了字符格式，从而显示设备（显示器屏幕）可以显示它。二进制字符总是会讨厌的扰乱运行于文本模式的类似计算机显示器这样的字符设备。

Take a look at the second column in the above examples and notice the difference. In the first example we see the individual characters '1', '2', '3' and '4' while the second example shows only three bytes in the internal format of the number 1,234. Lets change the DUMP calls slightly, and do the whole lot in one command :
看看上面例子的第二列并注意不同点。第一个例子中我们看到一个一个字符'1', '2', '3'和'4'，而第二个例子只显示数1,234内部格式中的三个字节。让我们稍微修改一下DUMP的调用，用一个指令完成全部比较：

SQL> select '1234' as "1234",
  2  dump('1234', 10),
  3  1234 as "a number",
  4  dump(1234, 10)
  5  from dual;

1234 DUMP('1234',10)     a number   DUMP(1234,10)
---- ------------------------- ---------- ----------------------
1234 Typ=96 Len=4: 49,50,51,52   1234 Typ=2 Len=3: 194,13,35


This time, we see the actual character codes used internally. Once again columns 2 and 4 differ. Column 4 is showing three bytes and these three bytes are the internal binary representation of the number 1,234. 
这次，我们看到内部真正的字符码。列2和4还是不同。列4显示了三个字节，这三个字节就是数1,234的内部二进制表示。

It is this binary representation that is used in the index entry when a number column is indexed. 
当数字列建立索引时，正是这个二进制表示用于索引项中。

Take a few minutes and experiment with dumping a few other numbers - stick to integers for now as those are what sequences generate. 
用几分钟来实验一下dump一些其他数字——现在关注整数，正是序列生成的类型。

SQL> create table test (a number);

Table created.

SQL> begin
  2    for x in 1 .. 1e6
  3    loop
  4     insert into test values (x, substr(dump(x,10), 14));
  5    end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.


If we have a look at the 'b' column of the table, we can see that each entry is ascending in a similar manner to the 'a' column. Here are the first 20 rows : 
如果我们看看表的b列，可以看到每个条目是递增的，正和a列类似。这里是前20行：

SQL> col b format a20 wrap
SQL> select a,b from test where a < 21;

   A B
---------- ----------
   1 193,2
   2 193,3
   3 193,4
   4 193,5
   5 193,6
   6 193,7
   7 193,8
   8 193,9
   9 193,10
  10 193,11
  11 193,12
  12 193,13
  13 193,14

上一个：经典Oracle图书推荐
下一个：Install oracle client on Solaris