Avoid defining maximum data type length in Oracle

One of my co-worker asked me this question... Why can't we define VARCHAR2(4000) for all char data type.. So that, we don't need to increase the length in future.. Since VARCHAR2 data type is variable length, it would not occupy the defined field width in database.

I got an answer when i googled this question and i would like to write the answer in this blog..

Here are the answers..

1. When users runs a query in query tool(say for example TOAD, SQL Navigator), it display the data based on the width of the column. It will be very hard to scroll to see the data from left to right and right to left.

2. When developer use the array fetch(Bulk binds, bulk collects) for better performance, oracle allocate the memory as we defined in the field length. Let us say, developers wanted to fetch 10 columns for 100 rows . So, 100 * 10 * 4000 => almost 4MB of RAM, oracle has to allocate to run this query. One connected session eats 4MB memory for this query. Imagine, if 5 people runs the same query at the same time, it will be 20MB RAM..

PLSQL Developers might use column%type to declare PLSQL variable. This might waste system memory badly if we define the max field length.

3. Developers build the data entry screen to load the data into the table. Let us say, Product_code is VARCHAR2(4000), Product_comment is VARCHAR2(4000). No one knows what sort of data can go into the database.

4. Oracle throw ORA-01450 maximum key length (string) exceeded error when combined length of all the columns specified in a CREATE INDEX statement exceeded the maximum index length. The maximum index length varies by operating system. If you are more interested in how the maximum index key length is computed, there is a document (Doc.Id 136158.1) on Metalink that explains this in details

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production

SQL> create table test(text1 varchar2(4000),
2 text2 varchar2(4000),
3 text3 varchar2(4000),
4 text4 varchar2(4000));

Table created.

SQL> create index idx on test(text1,text2);
create index idx on test(text1,text2)
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded

SQL>

Conclusion :- We should not allocate maximum data length. We should use proper size for Primary key & foreign keys. We can always expand whenever we need it. Max size will hurt the application performance, because, it will mistakenly allocate more memory as we discussed in the seocnd point.

If any one wanted to read more on this, please read this link.

http://tkyte.blogspot.com/2006/11/see-you-just-cannot-make-this-stuff-up.html