Oracle Character Types

Question

Is using a VARCHAR2 (1 BYTE) any less efficient than using CHAR(1 BYTE)?

Is using a VARCHAR2 (2000 BYTE) any less efficient than using CHAR(1 BYTE), if I never put any value longer than one character in the field?

** By efficient, I meant efficient in both time (searching) and space (storing).

Answer 1

Depending on your character set, CHAR(1 BYTE) may not be able to store any particular character, so there may be differences between VARCHAR2(2000 BYTE) and CHAR(1 BYTE) if we're talking about multi-byte characters.

More generally, in SQL (the PL/SQL rules are a bit different) a VARCHAR2(2000 BYTE) isn't going to consume more storage than a CHAR(1 BYTE) if you are storing just 1 byte of data (I assume "efficient" translates primarily to storage efficiency). A VARCHAR2(2000 BYTE) will frequently cause a client application to allocate a 2000 byte buffer to hold the contents of the field, since the client application doesn't know the actual data size in advance, which may cause the client application to use excessive amounts of RAM. That may or may not translate into a real issue, though. Most client applications aren't hurting for RAM and most result sets aren't returning millions of rows to the client, so wasting a couple k on a couple hundred rows may not be a big deal.

There is an AskTom thread that goes into more detail, particularly regarding the PL/SQL rules.