Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers 3

up vote 10 down vote accepted

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.

share|improve this answer
1  
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 '11 at 20:09
add comment

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.

share|improve this answer
    
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 '11 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 '11 at 7:22
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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