Oracle首选列长度 [英] Oracle preferred columns lengths

问题描述

列的长度的乘法因子是否会影响数据库的性能？

Does the multiplication factor of a column's length somehow influence the database performance?

换句话说，以下两个表的性能有什么不同？ / p>

In other words, what is the difference between the performance of the following two tables:

TBL1:
  - CLMN1 VARCHAR2(63)
  - CLMN2 VARCHAR2(129)
  - CLMN3 VARCHAR2(250)

和

TBL2:
  - CLMN1 VARCHAR2(64)
  - CLMN2 VARCHAR2(128)
  - CLMN3 VARCHAR2(256)

如果我们总是尝试将列的长度设置为 2 最大大小的事情？

Should we always attempt to make a column's length to some power of 2 or does only the maximum size matter?

一些开发人员声称数据库中列的长度的乘法因子之间存在一些链接，因为它影响Oracle分发的方式并将数据保存在磁盘上，并将其缓存共享在内存中。有人可以证明还是反驳？

Some of the developers claim that there is some link between the multiplication factor of the length of the columns in a database, because it influences how Oracle distributes and saves the data on the disk and shares its cache in memory. Can someone prove or disprove this?

推荐答案

性能没有差异。而且没有任何隐藏的优化，因为功率为2。

There is no difference in performance. And there are no hidden optimizations done because of power of 2.

唯一可以使存储方式有所不同的是实际数据。存储在 VARCHAR2（2000）列中的100个字符与存储在 VARCHAR2（500）中的100个字符完全相同的方式列。

The only thing that does make a difference in how things are stored is the actual data. 100 characters stored in a VARCHAR2(2000) column are stored exactly the same way as 100 characters stored in a VARCHAR2(500) column.

将长度视为业务约束，而不是数据类型的一部分。唯一应该推动您对长度的决定的事情是有关数据的业务限制。

Think of the length as a business constraint, not as part of the data type. The only thing that should driver your decision about the length are the business constraints about the data that is put in there.

修改：唯一的长度 有差异的情况是当您需要该列上的索引时。较旧的Oracle版本（< 10）确实对密钥长度有限制，并在创建索引时进行了检查。

Edit: the only situation where the length does make a difference, is when you need an index on that column. Older Oracle versions (< 10) did have a limit on the key length and that was checked when creating the index.

即使在Oracle 11中有可能，在4000个字符的值上设置索引可能不是最明智的选择。

Even though it's possible in Oracle 11, it might not be the wisest choice to have an index on a value with 4000 characters.

编辑2 ：

所以我很好奇，并设置了一个简单的测试：

So I was curious and setup a simple test:

create table narrow (id varchar(40));
create table wide (id varchar(4000));

然后用由40'X'组成的字符串填充两个表。如果存储之间确实存在差异，那么在检索数据时应该会显示出来，对吗？

Then filled both tables with strings composed of 40 'X'. If there was indeed a (substantial) difference between the storage, this should show up somehow when retrieving the data, right?

两个表格正好有1048576行。

Both tables have exactly 1048576 rows.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set autotrace traceonly statistics
SQL> select count(*) from wide;


Statistics
----------------------------------------------------------
          0  recursive calls
          1  db block gets
       6833  consistent gets
          0  physical reads
          0  redo size
        349  bytes sent via SQL*Net to client
        472  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select count(*) from narrow;


Statistics
----------------------------------------------------------
          0  recursive calls
          1  db block gets
       6833  consistent gets
          0  physical reads
          0  redo size
        349  bytes sent via SQL*Net to client
        472  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

所以这两张表的全表扫描完全一样。那么当我们真正选择数据时会发生什么？

So the full table scan for both tables did exactly the same. So what happens when we actually select the data?

SQL> select * from wide;

1048576 rows selected.


Statistics
----------------------------------------------------------
          4  recursive calls
          2  db block gets
      76497  consistent gets
          0  physical reads
          0  redo size
   54386472  bytes sent via SQL*Net to client
     769427  bytes received via SQL*Net from client
      69907  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1048576  rows processed

SQL> select * from narrow;

1048576 rows selected.


Statistics
----------------------------------------------------------
          4  recursive calls
          2  db block gets
      76485  consistent gets
          0  physical reads
          0  redo size
   54386472  bytes sent via SQL*Net to client
     769427  bytes received via SQL*Net from client
      69907  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1048576  rows processed

SQL>

一致的获取有轻微的差异，但这可能是由于缓存。

There is a slight difference in consistent gets, but that could be due to caching.

这篇关于Oracle首选列长度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！