9

I found an Ask Tom article explaining that there is not much difference between CHAR and VARCHAR2 in Oracle. It gave the impression that I should use VARCHAR2(4000 BYTE) for every column where I would like to store strings, even for ISO 639-1 language codes, because it does not make any difference.

I know that it makes sense to use CHAR(2) for ISO 639-1 language codes to enforce a basic data constraint. But this is not a sufficient constraint, because it permits storing 'xy' in the column which is not a valid language code. On the other side I pay for this basic constraint with the limitation that I have to change the database if I want to change my application to use ISO 639-2 language codes, which require 3 characters. So I tend to drop the constraint at all from the database level, because it seems to me that the cost is higher than the benefit.

With that in mind, I am wondering if there is any other significant reason why I should not use VARCHAR2(4000 BYTE) for any string shorter than 4000 bytes that I intend to store in an Oracle database?

Air
  • 8,274
  • 2
  • 53
  • 88
ceving
  • 21,900
  • 13
  • 104
  • 178
  • 3
    +1. why downvote this? somewhat controversial approach, but still a good question that clarifies important things for people. way more interesting than a flood of "hello world" sql questions. – Kirill Leontev Dec 13 '12 at 13:03

1 Answers1

6

Tom Kyte also rebuts your proposition here: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1145132537055

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
  • 1
    I wish Tom Kyte was here on SO – Kirill Leontev Dec 13 '12 at 12:48
  • His answer assumes that Oracle allocates the maximum length for a string although the string is smaller. No other programming language does this. Is it really correct, that Oracle works this way? – ceving Dec 13 '12 at 14:55
  • Ok the index block size problem is a real problem. – ceving Dec 13 '12 at 15:02
  • Some of the problems do, others do not. There are serious issues in index sizing as Oracle will assume that at some point you'll want to use the entire column size, and it has to fit in a single block. Mind you, you could index on substr(column,1,100) and query appropriately. There can also be serious memory issues with client applications. Lastly, you have to make some practical decision on how long it is reasonable for a company name (for example) to be so that you can size user interface elements appropriately, so that might as well be at the data modelling stage. – David Aldridge Dec 13 '12 at 15:03
  • I do not care much about the GUI, because each GUI can scroll everything almost endlessly. I am concerned about Oracle problems. Anther problem I came across is, that the byte limit makes it problematic to store multi byte strings. – ceving Dec 13 '12 at 15:15
  • 3
    My sympathy for your position just evaporated. There's no place in software development for people who care only about their own problems. – David Aldridge Dec 13 '12 at 15:19