Re: NVARCHAR2 for unicode database

"wangbin" <wangbin_at_start.com.au> wrote in message news:2d15bd69.0403182022.2e390753_at_posting.google.com...
> 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
> CLIENTUSERNAME is VARCHAR2(32) .
>
> 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

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