Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: NVARCHAR2 for unicode database

Re: NVARCHAR2 for unicode database

From: Douglas Hawthorne <>
Date: Sun, 21 Mar 2004 07:37:04 GMT
Message-ID: <ACb7c.118304$>

"wangbin" <> wrote in message
> We use a third party software. To enable multilingual support, we use
> unicode database: AL32UTF8 as character set, and AL16UTF16 as national
> character set.
> When I monitor the performance of the database, I notice the following
> query doesn't use the index on clientusername.
> select * from client where clientusername = N'testuser';
> If I get rid of N, it starts to use the index. The datatype of
> I have two questions.
> 1. Why doesn't CBO use index on clientusername?
> It appares that Oracle implicitly add TO_NCHAR on clientusername.
> 2. "A national character set is an alternate character set that
> enables you to store Unicode character data in a database that does
> not have a Unicode database character set."
> We have chosen AL32UTF8 as character set. Is there any reason we need
> to use Unicode String Literals(N)?
> Thanks,
> Bin


The reason for the use of a function in the equality test is that the left-hand side is of data type 'VARCHAR2' while the right-hand side is 'NVARCHAR2'. These are differing data types. According to table 2-10 on p.2-49 of 9i SQL Reference, an automatic data type conversion is performed.

As you have discovered, the column is converted from VARCHAR2 to NVARCHAR2. Table 2-11 on p.2-51 shows that when VARCHAR2 is compared to NVARCHAR2, the VARCHAR2 column or literal is converted to NVARCHAR2. This involves a function call (TO_NCHAR) thereby invalidating the use of the index for the column.

If you had columns of data type NVARCHAR2, then you would use the N text literals to do comparisons without involving automatic datatype conversions.

I think you might be confused about the N text literals being Unicode String Literals. P.2-54 of SQL Reference Manual describes them as literals in the National Character Set.

I suggest that you might want to read "9i Globalization Support Guide" for more information about Unicode in your situation. See p.2-25 of that manual for a description of your chosen character sets. The ones you have chosen give you "unrestricted multilingual support". Both of your character sets are Unicode but with differing encoding schemes.

Douglas Hawthorne Received on Sun Mar 21 2004 - 01:37:04 CST

Original text of this message