好清楚的觀念喔,我終於搞懂了

From 網路文章
http://www.orafaq.com/faq/what_is_the_difference_between_varchar_varchar2_and_char_data_types


What is the difference between VARCHAR, VARCHAR2 and CHAR data types?



Both CHAR and VARCHAR2 types are used to store character string values, however, they behave very differently. The VARCHAR type should not be used:

CHAR

CHAR should be used for storing fix length character strings. String values will be space/blank padded before stored on disk. If this type is used to store varibale length strings, it will waste a lot of disk space.

SQL> CREATE TABLE char_test (col1 CHAR(10));

Table created.

SQL> INSERT INTO char_test VALUES ('qwerty');

1 row created.

SQL> SELECT col1, length(col1), dump(col1) "ASCII Dump" FROM char_test;

COL1 LENGTH(COL1) ASCII Dump
---------- ------------ ------------------------------------------------------------
qwerty 10 Typ=96 Len=10: 113,119,101,114,116,121,32,32,32,32

Note: ASCII character 32 is a blank space.


 

VARCHAR

Currently VARCHAR behaves exactly the same as VARCHAR2. However, this type should not be used as it is reserved for future usage.

SQL> CREATE TABLE varchar_test (col1 VARCHAR2(10));

Table created.

SQL> INSERT INTO varchar_test VALUES ('qwerty');

1 row created.

SQL> SELECT col1, length(col1), dump(col1) "ASCII Dump" FROM varchar_test;

COL1 LENGTH(COL1) ASCII Dump
---------- ------------ ------------------------------------------------------------
qwerty 6 Typ=1 Len=6: 113,119,101,114,116,121

VARCHAR2

VARCHAR2 is used to store variable length character strings. The string value's length will be stored on disk with the value itself.

SQL> CREATE TABLE varchar2_test (col1 VARCHAR2(10));

Table created.

SQL> INSERT INTO varchar2_test VALUES ('qwerty');

1 row created.

SQL> SELECT col1, length(col1), dump(col1) "ASCII Dump" FROM varchar2_test;

COL1 LENGTH(COL1) ASCII Dump
---------- ------------ ------------------------------------------------------------
qwerty 6 Typ=1 Len=6: 113,119,101,114,116,121

VARCHAR vs. VARCHAR2

1. VARCHAR is going to be replaced by VARCHAR2 in next version. So, Oracle suggests the use VARCHAR2 instead of VARCHAR while declaring datatype.

2. VARCHAR can store up to 2000 bytes of characters while VARCHAR2 can store up to 4000 bytes of characters.

3. If we declare datatype as VARCHAR then it will occupy space for NULL values, In case of VARCHAR2 datatype it will not occupy any space.

CHAR vs. VARCHAR

VARCHAR is used to store variable length character strings up to 4000 characters. But, remember CHAR is faster than VARCHAR - some times up to 50% faster.

Re: VARCHAR vs VARCHAR2

1. VARCHAR is NOT going to be replaced by VARCHAR2 in the next version. It is reserved for redefinition in some future version, probably well beyond 11g, if ever. This warning has been around since the two types were first introduced in Oracle 6 around 1989. It is there to allow Oracle some flexibility should the need arise.

2. The two types are currently synonymous, and so the storage limitations are identical.

3. As for #2, there is no difference between VARCHAR and VARCHAR2 in handling of NULL values because they are currently the same type.

While I'm at it, VARCHAR2 does NOT contain an additional length indicator (as the DUMP output clearly shows), CHAR is NOT faster than VARCHAR2, and VARCHAR does NOT use space any differently to VARCHAR2 and never has done. There is no good reason to use CHAR for anything, even fixed-length keys, and there never has been. I've never seen so much complete rubbish on one page in my life.

Differences between varchar and varchar2:

[i].
CHAR(5) is fixed length, right padded with spaces.
VARCHAR(5) is fixed length, right padded with null
VARCHAR2(5) is variable length.

Thus the difference between VARCHAR and VARCHAR2 is that VARCHAR is ANSI standard but takes up space whereas VARCHAR2 is Oracle-only but makes more efficient use of space.

But as someone has already pointed out, Oracle have resolved this (in 9.2, maybe earlier) by casting VARCHAR to VARCHAR2.

[ii].

Varchar and Varchar2 both are of variable character. Varchar can have MAximum 2000 character while Varchar can contain maximum 4000 character.

[iii]
Varchar is of ANSI SQL standart while Varchar2 is of Oracle standard.

Varchar vs Char

Searching is faster in CHAR as all the strings are stored at a specified position from the each other, the system doesnot have to search for the end of string.
Whereas in VARCHAR the system has to first find the end of string and then go for searching.

Varchar v/s Varchar2

VARCHAR was previosuly of fixed length and right padded with NULL spaces to fill up the total space. However, it seems that Oracle has resolved this issue and has made VARCHAR the same as VARCHAR2. Now, only CHAR is able of fixed length storage.

VARCHAR2 is variable length - according to the values that will be stored in the database.

Thanks
Adilz

varchar v/s Varchar2 v/s char

Varchar and varchar2 are synonyms (now at least) varchar is supposed to be removed from oracle sql, but that will probably never happen. They both use 0 to N characters up on the disk. Depending on your NLS_CHARACTER_FORMAT (its called something like that) each character can take more than one byte on disk. ASCII uses one byte per character. UTF8 I think uses one byte unless the first bit is 1 then it may add another byte (and keep doing so until the first bit of the new byte is not 1) Most other forms of unicode default to two bytes. They can all grow I believe

char() should be thought of as fixd length. It will always take up the same amount of space no matter what is really stored in the column. It is a good idea to use them on fixed length things like MD5 signatures.

Varchars should fragment the datafile if their contents change. If they don't fragment the datafile then Oracle is taking extra steps to prevent this. So IF you are working with a string that will always be the same length (and that is less than 255) the go with char(). Where your string is < 4000 use Varchar2. Anything else go with CLOB.

length of long data type

Hi,
Can you help me out in this...........
I want to find the maximum length of a long data type..

Suppose there is a table called xyz and its column are a, b and c
b is long data type..
and i want to see the largest data in b column..

Please help me out

Thanks in advance

how to find the average of a salary if all r in a row

i have a table where employee is having salries for 12 months but the data is like say

jan feb mar apr .....dec
2000 1000 500 300 250

I have to use a single sql query to get the average salry excluding sum function.

can you guys pls help me out?

how to find avg

Can you clarify if your data looks like
a)
emp_id jan_sal feb_sal... dec sal
321 2000 1000 250
322 1000 1500 1500

or

b)
emp_id mth sal
321 1 2000
321 2 1000
...
321 12 250
322 1 1000
322 2 1500
...

re:how to find the average of a salary if all r in a row

SQL> ed
Wrote file afiedt.buf

1 create table scott.salary
2 (empname varchar2(10),
3 jan number,
4 feb number,
5 mar number,
6* apr number)
SQL> /

Table created.
SQL> insert into scott.salary values ('Gairik',100,200,300,400);

1 row created.

Elapsed: 00:00:00.01
SQL> insert into scott.salary values ('ahmad',200,50,400,900);

1 row created.

Elapsed: 00:00:00.00
SQL> commit;

Commit complete.

SQL> select empname,jan,feb,mar,apr from scott.salary;

EMPNAME JAN FEB MAR APR
---------- ---------- ---------- ---------- ----------
Gairik 100 200 300 400
ahmad 200 50 400 900

SQL> select empname, (jan+feb+mar+apr)/4 avg_salary from scott.salary;

EMPNAME AVG_SALARY
---------- ----------
Gairik 250
ahmad 387.5

Elapsed: 00:00:00.00
SQL>

char and varchar2

I don't feel compelled to comment on varchar. Never used it in Oracle and probably never will.

However I am compelled to comment about char and varchar because of some unhelpful comments here. First of all it is easy enough to verify stuff in Oracle so it comes as a surprise at some of the comments here.

char and varchar properties and length restrictions are widely available, however what is not available widely is perhaps the performance issue. I decided to put it to rest by creating a table of a million rows with essentially two columns (c char(6), vc varchar2(10)) and filled each with same values of 6 characters long (comparing 'apples' to 'apples') from a random table in my app.

I issued a search: select count(*) from mytable where c = 'values' and alternatively vc = 'values'
Invariably the char (c) search performed almost half as fast as the varchar2 (vc) search.

Varchar2 performed much better. Don't take my word for it, try it for yourself.

Vinny

創作者介紹

Thinking in Robert

羅伯特 發表在 痞客邦 PIXNET 留言(0) 人氣()