Re: Magic of varchar2

From: Laurenz Albe <invite_at_spam.to.invalid>
Date: 15 Mar 2006 09:36:12 GMT
Message-ID: <1142415370.934272@proxy.dienste.wien.at>

fitzjarrell_at_cox.net <fitzjarrell_at_cox.net> wrote:
> milind.namjoshi_at_trans.ge.com wrote:

>> What difference does it make :
>>
>> 1. I define a column as varchar2(500) and store only 10 bytes
>>
>> 2. I define that column as varchar2(10) .
>>
>> Any impact on storage / performance ?
>>
>> My data is static no updates happen once the data is inserted. So no
>> row chaining.

>
> There is no row chaining if your row size < available space in an empty
> block. What you meant to say was you'll have no row migration, which
> is different. If the maximum length of the values for that column
> never exceed 10 characters then it's not the best practice to declare a
> varchar2{500) to store those values. That being said I don't know of
> any reason to expect either definition to impact performance or storage
> given your scenario (no column value is greater than 10 characters).
> But why make the barn 50 times larger than you need?

I can think of a very good reason for choosing the length of a VARCHAR2 column as small as possible: if you want to create an index on it.

Firstly, according to the documentation, the total size of indexed columns cannot exceed 75% of the database block size 'minus some overhead', and secondly the total size of the index will depend on the length of the indexed columns.

Yours,
Laurenz Albe Received on Wed Mar 15 2006 - 03:36:12 CST