database - Oracle 10g 上 NUMBER 列的索引

Question

目前我们有一列仅包含声明为 NUMBER 的整数值。同时它是我们的(唯一的)索引。我想知道如果将索引声明为 INTEGER 是否会对性能产生影响？或者 Oracle 是否足够聪明，可以看出它是一个整数？非常感谢。

Answer 1

不，不会。

利用 Florin 的测试表，您可以设置一个小型测试工具，将每个查询运行数百次并对耗时进行平均。就我而言，我分别运行了两个查询 500 次。

有时，NUMBER 版本的运行速度会稍快(1.232 百分之一秒对 1.284 百分之一秒)。

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    l_start_time number;
  3    l_end_time   number;
  4    l_cnt        number;
  5    l_iterations number := 500;
  6  begin
  7    l_start_time := dbms_utility.get_time();
  8    for i in 1 .. l_iterations
  9    loop
 10      select count(*)
 11        into l_cnt
 12        from fg_test;
 13    end loop;
 14    l_end_time := dbms_utility.get_time();
 15    dbms_output.put_line( 'Average elapsed (number) = ' ||
 16                             (l_end_time - l_start_time)/l_iterations ||
 17                             ' hundredths of a second.' );
 18    l_start_time := dbms_utility.get_time();
 19    for i in 1 .. l_iterations
 20    loop
 21      select count(*)
 22        into l_cnt
 23        from fg_test1;
 24    end loop;
 25    l_end_time := dbms_utility.get_time();
 26    dbms_output.put_line( 'Average elapsed (integer) = ' ||
 27                             (l_end_time - l_start_time)/l_iterations ||
 28                             ' hundredths of a second.' );
 29* end;
 30  /
Average elapsed (number) = 1.232 hundredths of a second.
Average elapsed (integer) = 1.284 hundredths of a second.

PL/SQL procedure successfully completed.

Elapsed: 00:00:12.60

但是，如果您立即再次运行相同的代码块，您很可能会看到整数版本运行速度稍快的相反情况。

SQL> /
Average elapsed (number) = 1.256 hundredths of a second.
Average elapsed (integer) = 1.22 hundredths of a second.

PL/SQL procedure successfully completed.

Elapsed: 00:00:12.38

实际上，在您尝试以毫秒或几分之一毫秒为单位测量差异时，您已经进入了系统噪声发挥作用的领域。尽管我的机器除了我正在运行的测试之外是“空闲”的，但系统可能会在耗时中增加一两毫秒来处理一些中断或运行一些执行某些操作的后台线程，原因有很多用于操作系统。

当您认为 INTEGER 只是 NUMBER(38) 的同义词时，这个结果就有意义了

SQL> desc fg_test1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER(38)

SQL> desc fg_test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER

更新:

即使使用 NUMBER(6)(注意必须将 INSERT 更改为仅加载 999,999 行而不是 100 万行)，也没有任何变化

创建表格

SQL> create table fg_test2(a number(6));

Table created.

Elapsed: 00:00:00.01

SQL> ed
Wrote file afiedt.buf

  1  insert into fg_test2
  2* select level from dual connect by level <= 1000000-1
SQL> /

999999 rows created.

Elapsed: 00:00:07.61

SQL> create index fg_ix2 on fg_test2(a);

Index created.

Elapsed: 00:00:00.01

运行脚本。请注意，四次运行中的任何一次都没有显着差异，并且(偶然)在这四种情况中，NUMBER(6) 表都不是最有效的。

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    l_start_time number;
  3    l_end_time   number;
  4    l_cnt        number;
  5    l_iterations number := 500;
  6  begin
  7    l_start_time := dbms_utility.get_time();
  8    for i in 1 .. l_iterations
  9    loop
 10      select count(*)
 11        into l_cnt
 12        from fg_test;
 13    end loop;
 14    l_end_time := dbms_utility.get_time();
 15    dbms_output.put_line( 'Average elapsed (number) = ' ||
 16                             (l_end_time - l_start_time)/l_iterations ||
 17                             ' hundredths of a second.' );
 18    l_start_time := dbms_utility.get_time();
 19    for i in 1 .. l_iterations
 20    loop
 21      select count(*)
 22        into l_cnt
 23        from fg_test1;
 24    end loop;
 25    l_end_time := dbms_utility.get_time();
 26    dbms_output.put_line( 'Average elapsed (integer) = ' ||
 27                             (l_end_time - l_start_time)/l_iterations ||
 28                             ' hundredths of a second.' );
 29    l_start_time := dbms_utility.get_time();
 30    for i in 1 .. l_iterations
 31    loop
 32      select count(*)
 33        into l_cnt
 34        from fg_test2;
 35    end loop;
 36    l_end_time := dbms_utility.get_time();
 37    dbms_output.put_line( 'Average elapsed (number(6)) = ' ||
 38                             (l_end_time - l_start_time)/l_iterations ||
 39                             ' hundredths of a second.' );
 40* end;
SQL> /
Average elapsed (number) = 1.236 hundredths of a second.
Average elapsed (integer) = 1.234 hundredths of a second.
Average elapsed (number(6)) = 1.306 hundredths of a second.

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.89
SQL> /
Average elapsed (number) = 1.208 hundredths of a second.
Average elapsed (integer) = 1.228 hundredths of a second.
Average elapsed (number(6)) = 1.312 hundredths of a second.

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.74
SQL> /
Average elapsed (number) = 1.208 hundredths of a second.
Average elapsed (integer) = 1.232 hundredths of a second.
Average elapsed (number(6)) = 1.288 hundredths of a second.

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.66
SQL> /
Average elapsed (number) = 1.21 hundredths of a second.
Average elapsed (integer) = 1.22 hundredths of a second.
Average elapsed (number(6)) = 1.292 hundredths of a second.

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.62