在没有业务约束的情况下，应该为Oracle字符类型设置什么限制？

Question

我习惯于为PostgreSQL设计，它的字符类型没有性能差异，社区的建议是，明确的限制只存在于强制执行业务规则。

https://www.postgresql.org/docs/current/datatype-character.html

现在我在甲骨文(19c)工作。我对字符类型的选择似乎要么是强制限制的VARCHAR2，要么是CLOB。

社区的建议似乎是尽可能避免CLOB。我不清楚这是出于性能原因、传统原因，还是因为CLOB在查询编辑器中不显示，而不需要进行一些操作。

如果没有来自业务或领域的规则，这意味着文本字段的最大长度，那么在选择限制时，应该考虑哪些技术、性能或用户体验因素？

Answer 1

“这是否出于性能方面的原因”--这一点。CLOBs在Oracle中非常慢(特别是如果您经常更改它们)

如果没有业务规则和4000字节(！)现在看来已经足够了，跟varchar2(4000)一起去吧。

不要试图使用允许varchar2(32767)的扩展varchars --它们作为CLOBs存储在后台，并遇到相同的性能问题。

Answer 2

TL;DR:避免使用CLOBs，使用长度合理的VARCHAR2。

我完全同意@a_horse_with_no_name关于CLOBs和varchar2(32767)的看法。

但是，我不建议VARCHAR2(4000)的最大大小，而是使用一个合理的上限，这实际上是很难估计的。如果领域太短，用户和其他开发人员会讨厌你。如果字段太长，数据库会做一些奇怪的事情。

因为VARCHAR2只存储实际使用的字符，所以在存储端不会发现任何差异，所以它在插入、更新或删除过程中的性能很可能是相同的。

但是，有时Oracle假定实际使用了最大长度：

CREATE TABLE t (
  a VARCHAR2(   1 CHAR),
  b VARCHAR2(   1 CHAR),
  c VARCHAR2(4000 CHAR),
  d VARCHAR2(4000 CHAR)
);

CREATE INDEX i1 ON t(a,b);
Index I1 created.

CREATE INDEX i1000 ON t(c, d);
ORA-01450: maximum key length (6398) exceeded

此外，当数据库服务器(或客户端应用程序)以最大长度分配内存时，有时会对性能产生影响，例如：

INSERT INTO t SELECT 'a','a','a','a' FROM all_objects;
INSERT INTO t SELECT 'b','b','b','b' FROM all_objects;
INSERT INTO t SELECT 'c','c','c','c' FROM all_objects;
INSERT INTO t SELECT 'd','d','d','d' FROM all_objects;
EXECUTE dbms_stats.gather_table_stats(null, 't');
SET AUTOTRACE TRACEONLY STAT

现在，按VARCHAR2(1)列排序发生在内存中(速度很快)：

SELECT a,b FROM t ORDER BY a,b;

Statistics
----------------------------------------------------------
      1  sorts (memory)
      0  sorts (disk)
 268520  rows processed

虽然按VARCHAR2(4000)列进行排序不适合内存，因此必须在磁盘上进行排序，这是缓慢的：

SELECT c,d FROM t ORDER BY c,d;

Statistics
----------------------------------------------------------
      0  sorts (memory)
      1  sorts (disk)
 268520  rows processed

我不得不承认，为了证明这一点，我把可用内存设置为非常小的数量，但我认为你明白这个想法。