Why does Oracle varchar2 have a mandatory size as a definition parameter?

Question

I want to know why Oracle needs the size parameter in the definition of the VARCHAR2.

I think that is for constraint. Would it be a better option that oracle takes this parameter as an optional like NUMBER dataType?

I often have problems resizing old tables to larger sizes, because sometimes a value is bigger than the size definition of the VARCHAR2 column.

It's the same to define a type of VARCHAR2(10) or VARCHAR2(1000).

I guess, it's an unnecessary constraint. If not, do you know of a real case when this constraint resulted in something useful? And why no such declaration in NUMBER type ?

Answer 1

It's the same to define a type of varchar2(10) or varchar2(1000).

No, it is not the same thing at all.

1. The length of the column is useful metadata for developers building screens.
2. Similarly automatic query tools like TOAD and SQL Developer use the length of the column when they render results.
3. The database uses the length of a variable when allocating memory for PL/SQL collections. As that memory comes out of the PGA supersizing the variable declaration can lead to programs failing because the server has run out of memory.
4. There are similar issues with the declaration of single variables in PL/SQL programs, it is just that collections tend to multiply the problem.
5. Supersized columns create problems for compound indexes. The following is on a database with 8K blocks

....

SQL> create table t23 (col1 varchar2(4000), col2 varchar2(4000))
  2  /

Table created.

SQL> create index t23_i on t23(col1,col2)
  2  /
create index t23_i on t23(col1,col2)
                      *
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded


SQL>

But above all else, columns sizes are a form of error checking. If the column is supposed to be ten characters long and some autonomic process is trying to load a thousand characters then something is wrong. The process ought to fail, so we can investigate why we are loading duff data. The alternative is a database full of garbage, and if that is what was wanted we should just have given everybody Excel and have done with it.

It is true that changing the column size when it turns out we have underestimated can be tiresome. But it doesn't happen very often, and we can mitigate a lot of the pain by using %TYPE and SUBTYPE declarations in our PL/SQL instead of hard-coding variable lengths.

---

"why no such declaration in NUMBER type"

Numbers are different. For a start, the maximum size of a number is much smaller than the text equivalent (38 digits of guaranteed precision).

But the key difference is that Oracle stores numeric values in scientific notation so there is not a straightforward relationship between the arithmetical size of the number and the storage space it consumes.

SQL> select vsize(123456789012345678901) n1
  2         , vsize(999999999999999999999999999999) n2
  3         , vsize(0.000000000000000000001) n3
  4         , vsize(1000000000000000000000000) n4
  5  from dual
  6  /

        N1         N2         N3         N4
---------- ---------- ---------- ----------
        12         16          2          2

SQL> 

Nevertheless, it remains good practice to specify scale and precision wherever possible, especially when we are dealing with integers, say, or money.