Hi,

I have a column (in Oracle 11) which looks like:

translation varchar2(2000)

If i change it to:

translation varchar2(4000)

Will i suffer any penalties?

Is there any advantage, other than documentation and as a sort of informal
constraint, to sizing a varchar column smaller than its maximum possible
size?

Thanks,
tom

--
It's a surprising finding, but that's science all over: the results
are often counterintuitive. And that's exactly why you do scientific
research, to check your assumptions. Otherwise it wouldn't be called
"science", it would be called "assuming", or "guessing", or "making it
up as you go along". -- Ben Goldacre

Op 18-6-2010 15:13, Tom Anderson schreef:
could you explain that?

its a bit confusing why you would have it sized smaller that its maximum
possible size

--
Luuk

On Sat, 19 Jun 2010, Luuk wrote:

Sorry.

By 'maximum possible size', i mean 'the maximum size the implementation
allows'. So, for example, 4000 in recent Oracle versions.

I have a table that looks like:

create table translation (
id integer primary key,
locale char(4) not null,
value varchar(2000) not null
);

We chose 2000 as the size of the value column because we didn't think we'd
need values bigger than that.

However, i now have some data to put in it in which the longest value is
just over 2600 characters long. I need to change the size of the value
column, and i'm wondering if i should make it just big enough (3000, say),
or just go straight for the maximum possible size, 4000.

I'm also wondering if i should invest time in breaking down or eliminating
the largest values, so that i can carry on using a size 2000 column.

tom

--
People should not be afraid of their governments. Governments should be
afraid of their people. -- V

Op 19-6-2010 13:25, Tom Anderson schreef:
This seems to be wrong, because now you would like to store a longer
value than this 2000, so your thought where wrong...

You should define the length to the size you want to store at maximum.

Because you did not know the max.size, you choose 2000

Now you have an example of a value with bigger length (2600)

Because you 'choose' the length to be 2000, you should increase it
length now to a suitable size (i.e. 2700)

But, if this 2700 is again a 'choice', than you might end up with the
same problem next week, when someone tries to store a value with the
length of 2800.

The only solution is, DEFINE the maximum length of that field, and
change your database to that value.

--
Luuk

On Sat, 19 Jun 2010, Luuk wrote:

What do you mean by 'DEFINE'? Are you saying i should use 4000? Or that i
should set a limit and then impose it on the content?

In any case, none of this answers the question i posted, which is 'Will i
suffer any penalties?' for doing so.

tom

--
They travel the world in their ice cream van ...

Tom Anderson wrote on 18.06.2010 15:13:
No

Op 19-6-2010 15:15, Tom Anderson schreef:
ok, i forgot to answer the question

but the answer to the question is not rellevant if you DEFINE the
correct size.

If the correct size has penalties, than you are out of luck, but that is
of minor importance than a field having the correct size.

I dont think there are penalties to having a size of 4000, maybe your
database uses some more memory.....

but you should not drive a bus, when a two-seater will do... ;-)
http://www.chinacartimes.com/wp-cont...ic-2seater.jpg

--
Luuk

On Sat, 19 Jun 2010, Thomas Kellerer wrote:

Thanks.

tom

--
drink beer and forget about gods. -- derslangerman

Look at the UK Postal Code; it is a VARCHAR(n) encoing scheme and it
is nowhere near even VARCHAR(1000).

The right answer is that the length of a column ins an important
constraint and needs to be as carefully designed as any other
constraint.