| Performance with char and varchar2 [message #33050] |
Thu, 16 September 2004 23:03  |
Milly
Messages: 72
Registered: March 2004
|
Member |
|
|
Hi all!!
I have to know whether there are some difference about performance setting the key of a table as char or varchar2.
Any advice about it?
thanks
milly
|
|
|
|
|
|
|
|
| Re: Performance with char and varchar2 [message #33055 is a reply to message #33052] |
Fri, 17 September 2004 01:12  |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
It don't think any performance consequences are involved here but mostly the VARCHAR2 is used. This is an excerpt from the docs:
----------------------------------------------------------------------
CHAR Datatype
The CHAR datatype specifies a fixed-length character string. Oracle subsequently ensures that all values stored in that column have the length specified by size. If you insert a value that is shorter than the column length, then Oracle blank-pads the value to column length. If you try to insert a value that is too long for the column, then Oracle returns an error.
The default length for a CHAR column is 1 byte and the maximum allowed is 2000 bytes. A 1-byte string can be inserted into a CHAR(10) column, but the string is blank-padded to 10 bytes before it is stored.
When you create a table with a CHAR column, by default you supply the column length in bytes. The BYTE qualifier is the same as the default. If you use the CHAR qualifier, for example CHAR(10 CHAR), then you supply the column length in characters. A character is technically a codepoint of the database character set. Its size can range from 1 byte to 4 bytes, depending on the database character set. The BYTE and CHAR qualifiers override the semantics specified by the NLS_LENGTH_SEMANTICS parameter, which has a default of byte semantics.
VARCHAR2 Datatype
The VARCHAR2 datatype specifies a variable-length character string. When you create a VARCHAR2 column, you supply the maximum number of bytes or characters of data that it can hold. Oracle subsequently stores each value in the column exactly as you specify it, provided the value does not exceed the column's maximum length. If you try to insert a value that exceeds the specified length, then Oracle returns an error.
You must specify a maximum length for a VARCHAR2 column. This maximum must be at least 1 byte, although the actual string stored is permitted to be a zero-length string (''). You can use the CHAR qualifier, for example VARCHAR2(10 CHAR), to give the maximum length in characters instead of bytes. A character is technically a codepoint of the database character set. CHAR and BYTE qualifiers override the setting of the NLS_LENGTH_SEMANTICS parameter, which has a default of bytes. The maximum length of VARCHAR2 data is 4000 bytes. Oracle compares VARCHAR2 values using nonpadded comparison semantics.
----------------------------------------------------------------------
HTH,
MHE
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
