What limit should I set for Oracle character types, when there is no business constraint?

user_4171861 0
Viewed 96

I'm used to designing for PostgreSQL, whose character types don't have performance differences, and where the community's advice is that an explicit limit only exists to enforce a business rule.

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

Now I am working in Oracle (19c). My choices for character types seem to be either VARCHAR2 with a mandatory limit, or CLOB.

The community advice seems to be to avoid CLOB wherever possible. It's not clear to me whether this is for performance reasons, reasons of tradition, or because CLOBs do not display in query editors without some manipulation.

If there is no rule from the business, or domain, that suggests a maximum length for a text field, what technical, performance, or user experience factors should I consider when choosing a limit?

oracle19c data-modeling oracle
2 Answers

"whether this is for performance reasons" - that. CLOBs are extremely slow in Oracle (especially if you change them a lot)

If there is no business rule and 4000 bytes(!) seem to be enough for the time being, go with varchar2(4000).

Don't be tempted to use extended varchars that allow varchar2(32767) - they are stored as CLOBs in the background and suffer the same performance problems.

user_3303150

TL;DR: Avoid CLOBs, use VARCHAR2 with a reasonable length.

I agree completely with @a_horse_with_no_name regarding CLOBs and varchar2(32767).

However, I would not recommend to the the maximum size for VARCHAR2(4000), but to use a sensible upper limit, which is actually quite difficult to estimate. Users and other developers will hate you if the field is too short. And the database will do strange things if the field is too long.

Because VARCHAR2 stores only the actual used characters, you won't find any difference on the storage side, it's performance during insert, update or delete is very likely identical.

However, sometimes Oracle assumes the maximum length is actually used:

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

Furthermore, there is sometimes an performance impact when the database server (or client application) allocates memory by the maximum length, for example:

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

Now sorting by the VARCHAR2(1) columns happens in memory (which is fast):

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

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

while sorting by the VARCHAR2(4000) columns doesn't fit in memory and has therefore to be sorted on disk, which is slow:

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

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

I have to admit that I set the available memory to a very small amount just to prove the point, but I think you get the idea.

user_18640290
Related