performance difference between varchar2(20) and varchar2(2000)
Hi Friends ,
I have a fundamental question on how Oracle internally handles columns with varchar2(20) datatype and varchar2(2000) datatype .
according to theory , oracle uses same amount of space in column with varchar2(20) and varchar2(2000) when identical data is entered.
How does test tables test_tab_A differ from test_tab_B
create table test_tab_A
( name varchar2(20)
...... ) ;
create table test_tab_B
( name varchar2(2000)
...... ) ;
could anybody please suggest,
1) which is a better design practice ,
2) are there any limitations in designing tables in test_tab_B type.
3) how does Oracle internally handle varchar2(20) and varchar2(2000) column regarding performance.
This is in initial design stage ,
we are identifying pros and cons , and any known issues
while designing table with bigger column widths varchar2(2000) or does this effect anything while loading this table into buffer pool.
also design people doesn't know how much column width should be .
so there question was can we have varchar2(2000) instead of varchar(20) .
You probably won't find any performance difference ever. The width of a VARCHAR2 column should be treated merely as an additional built-in constraint.
If you know the dataa in a particular column will never and should never exceed 20 characters (or bytes, depnds on how you define it), then define it as VARCHAR2(20). That gives you extra data integrity security, because now you know that noone will be able to insesrt text exceeding 20 characters by mistaake. If you know that maximum length of the column will be 2000 characters then by all means define it as VARCHAR2(2000), but there is no reason to define it as VARCHAR2(4000). Got he point?
Now if you are 95% sure that lenth will not exceed 20, but there is slight posibilty that your assumptions are wrong and it might be longer, I would still define it as VARCHAR2(20). If longer text actualy appears in the input side of that column it will error out, but you allways have the ability to make the column wider with a simple ALTER command if and when this happens.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Also, a point to remember is that a varchar2(2000) has the potential of introducing row chaining or migration if you have a really small block size and the varchar2(2000) keeps getting updated ever larger.