I used datatype varchar2(20) for person name. but I also used varchar2(200) in my database.
so what is major difference between varchar2(20) and varchar2(200)?
Can size of the database also impact the performance or not?
at last
how oracle store varchar2 datatype internally like size of the varchar2(200) and i used only 60 then what is the waste space or not?
Thank you Dear.
The major difference is the maximum length of a varchar2(20) is 20 bytes or characters (depending on how you defined it). The maximum length of a varchar2(200) is 200 bytes/characters:
SQL> create table t (
2 c20c varchar2(20 char),
3 c20b varchar2(20 byte),
4 c200c varchar2(200 char),
5 c200b varchar2(200 byte)
6 );
Table created.
SQL>
SQL> insert into t values (null, null, null, null);
1 row created.
SQL>
SQL> update t
2 set c20c = lpad('x', 21, 'x');
set c20c = lpad('x', 21, 'x')
*
ERROR at line 2:
ORA-12899: value too large for column "CHRIS"."T"."C20C" (actual: 21, maximum: 20)
SQL>
SQL> update t
2 set c200c = lpad('x', 21, 'x');
1 row updated.
SQL>
SQL> -- 2-byte character, can only fit 10 chars if column is defined with byte
SQL> update t
2 set c20b = lpad('é', 11, 'é');
set c20b = lpad('é', 11, 'é')
*
ERROR at line 2:
ORA-12899: value too large for column "CHRIS"."T"."C20B" (actual: 33, maximum: 20)
SQL>
SQL> update t
2 set c200b = lpad('é', 11, 'é');
1 row updated.A varchar2 only uses the space up to the length of the string. So if you store a 1 byte character in a varchar2(200 byte), the other 199 bytes are still available:
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:123212348063 There are some gotchas to defining a varchar2 with a large value "just in case". For example, if the sum of the lengths for a multi-column index is too big, you'll get an error:
create table t (
x varchar2(4000),
y varchar2(4000),
z varchar2(4000)
);
create index i on t (x, y, z);
SQL Error: ORA-01450: maximum key length (6398) exceeded
View the maximum length of a varchar2 as a constraint. Ask yourself, should it be possible to store strings longer than X?
If the answer is no, then use that in the definition. e.g. many ISO codes are defined as 2 or 3 character strings. So these should be varchar2(2 char) or varchar2(3 char) as needed.
You can find further discussion and opinions in this Oracle-L thread:
https://www.freelists.org/post/oracle-l/RE-bytes-vs-chars