18

Googling around there seems to be mixed reports whether the size of a VARCHAR2 column in Oracle impacts performance or not.

I would like to give the question of VARCHAR size a little twist and hope to get some insight in this:

Given (multiline) free-text fields (not short stuff like names) that you want to store in an (Oracle) database, is there any point (wrt. performance or otherwise) in not maxing out the VARCHAR capacity (VARCHAR2(4000) on Oracle) but choosing a smaller value such as 1024 or 512 because that will likely be sufficient in 98% of the cases anyway.

Improve this question
1

3 Answers 3

Reset to default
11

It does impact memory usage, especially when a client program has to allocate enough memory to receive a dataset.

Bear in mind that a lot of apps (especially web apps) use UTF-8 which is a multi-byte character set. As such, you should really consider characters rather than bytes.

If I was expecting over a thousand characters, then I'd actively consider a CLOB. I'd be thinking about whether it will store plain text or some form of markup (wiki / html ?), usage with non-Euro languages. The Questions and Answers here, for example, would be CLOB, but comments can fit in a VARCHAR.

If you max out a VARCHAR, then in six months someone will want to make it bigger again, and you'd be kicking yourself for not using a CLOB.

Improve this answer
1
  • 2
    UTF-8 will generally use one byte for one character for western languages. It's multi-byte in the sense that it allows for multi-byte "escape" sequences to represent non-Western characters.
    – Eric J.
    Aug 10, 2011 at 20:09
8

Generally there are no performance considerations although there are side issues that might matter to you. The limit for a varchar should be thought of as a constraint like any other - it is there to enforce a business rule.

IMO the question you should be asking is "Do I want to prevent the free-text data stored in this field being longer then n bytes/chars" - that is the only determining factor when choosing between varchar(512) and varchar(4000).

Note that I am assuming you are talking about varchar the SQL type - the situation is different with pl/sql and choosing the length can be crucial for memory allocation reasons.

Improve this answer
2
  • Thanks. As far as my (very limited) experience goes, any "business rule" stating a limit between "500 - 3999" is simply arbitrary, that is, someone just liked the number. IMHO, if I'm going for free-text and there are no implementation consequences (the context of this question), either it's maxed out (4000) or it's not free-text. --- The point I'm trying to make in this comment: I think there won't ever be a business rule helping to choose btw. 512 and 4000 (unless it's: "as many chars as possible")
    – Martin
    Aug 9, 2011 at 7:05
  • If it is really "as many chars as possible" then as @gary says, you should consider a clob, shouldn't you?
    – Jack Douglas
    Aug 9, 2011 at 7:22
4

If a smaller value will work for 98% of the cases, but it takes a Varchar2(4000) to work for 100% of the cases, then you have little choice but to use the larger value. Creating a separate table for 2% of the values and then coordinating inserts/selects etc. would add complexity that would obliterate any memory or performance benefits from not extending the field.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.