0

In PL/SQL, if we create a variable name with varchar2(100) and then assign a value with length of 50 to the variable, then the total memory used is 100 characters.

but if you declare it as varchar2[4000] or more it will use only the 50 characters.

Does a varchar column always allocate only what you have assigned?

Also, if this is the case, is it a good practice to declare all varchar2 variables to max length as the memory would be dynamically allocated? Please clarify. Thank you

MarianD
  • 13,096
  • 12
  • 42
  • 54
Pratyusha Vemulapalli
  • 1
  • 6
  • perhaps this might help you http://stackoverflow.com/questions/1882073/impact-of-defining-varchar2-column-with-greater-length – Frederick Álvarez Mar 22 '17 at 04:43
  • Actually, thats the post which actually confused me. If the variable is declared with a size 1 to 3999 (11g+) / 1999 (10g or earlier), memory will be allocated for the maximum length (i.e. VARCHAR2(100) will require at least 100 bytes of memory). If the variable is declared with a size 4000 (11g+) / 2000 (10g or earlier) or greater, memory will be allocated according to the size of the data stored. So, when the memory gets allocated dynamically when declared it maxlength, then why declare with smaller length – Pratyusha Vemulapalli Mar 22 '17 at 04:55

2 Answers2

0

You're wrong there. varchar2(100) uses one extra byte than the raw data, to store the length as an unsigned byte).

For max lengths over 255, an extra 2 bytes than the raw data are used, to store the length as an unsigned 16-bit (2 byte) integer.

So yes, there's a small penalty.

  • use fixed width char(n) if your values are (mostly) the same length (no extra bytes)
  • use varchar(n), with n < 256 if all your values will fit (1 extra byte)
  • use varchar(4000) otherwise (2 extra bytes)

I wouldn't worry about the bytes - just use whatever is convenient.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
0

I guess data integrity would be a concern if we use a blind size apart from memory. How can we be sure of the data size if we don't know what we are storing. And what if the value exceeds the 4000 mark ?

Besides I have experienced problems while creating index on combination columns with varchar2(4000). You would get something like this Error report: SQL Error: ORA-01450: maximum key length (6398) exceeded 01450. 00000 - "maximum key length (%s) exceeded" *Cause: *Action:

Dileep Krishnamurthy
  • 281
  • 3
  • 3