Oracle: query with 'N' function - which datatype should I use?

Question

I have created an Oracle table with an indexed varchar2 column, called 'ID'.
A software I'm using is reading this table, but instead of running queries like

select * from table_name where ID='something'

it does (notice the extra "N" before the value)

select * from table_name where ID=N'something'

which is causing some kind of character conversion.
The problem is that, while the 1st query is performing a range scan, the 2nd is performing a full table scan.

Since I cannot modify the queries that this software is running, which data type should I use, instead of varchar2, so that the conversion performed by the 'N' function does not imply a full table scan?

Answer 1

The prefix N before the string is used to specify a NVARCHAR2 or NCHAR datatype.

When comparing NVARCHAR2s to VARCHAR2s, Oracle converts the VARCHAR2 variable to NVARCHAR2. This is why you are experiencing a FULL SCAN.

Use a NVARCHAR2 column instead of a VARCHAR2 in your table if you can't modify the query.