Does an Oracle nvarchar2 take twice as much space as varchar2?

Question

Regarding point 4 of this question (which is in reference to MS SQL Server):

1. VARCHAR does not store Unicode characters.
2. NVARCHAR does store Unicode characters.
3. Today's applications should always be Unicode compatible.
4. NVARCHAR takes twice the amount of space to store it.
5. Point 4 doesn't matter because storage space is extremely inexpensive.

Is the same true for Oracle database as well?

Answer 1

Not exactly. Oracle accommodates both byte semantics and character semantics for VARCHAR. The default for character types is byte semantics; a column defined as VARCHAR2(10) holds 10 bytes, which might not be the same as 10 characters. The default for new columns is controlled by the parameter NLS_LENGTH_SEMANTICS.

But in a UTF-8 database, you can define a column as VARCHAR2(10 CHAR), which will hold 10 characters. Even if five of them are single-byte characters and five of them are multi-byte characters, all 10 will fit.

NCHAR and NVARCHAR2 always use character semantics. A column of NVARCHAR(20) will hold 20 characters, but it might take 60 bytes to hold all of them.

NVARCHAR2 is limited to 4000 bytes, which is 4000 single-byte characters, but only a third that many multi-byte characters. (Assuming a maximum of three bytes per character.)

Disk space is much less expensive than it used to be. But you still have to get data off the disk for it to be useful. More bytes, more time; more time, more expense. IME, cost of disk space doesn't matter only to people who don't have to pay for it.

More details in Oracle Database Concepts.

Answer 2

Yes - if you're using AL32UTF8 for your character set, and using AL16UTF16 for your national character set, and your data is mostly in ASCII.

AL16UTF16 always requires 2 bytes per character, AL32UTF8 is backwards compatible with ASCII and will only use 1 byte for ASCII characters.

But for some languages AL16UTF16 may be more efficient than AL32UTF8.

Answer 3

Twice the amount of what? If we assume that the Character Set is an 8 bit one like we8iso8859p1 and the National Character Set is the default al16utf16 then yes (mostly).