Oracle: query with function "N" - what data type should I use?

Question

I created an Oracle table with an indexed varchar2 column called "ID".
The software that I use reads this table, but instead of running queries like

select * from table_name where ID='something' 

it (note the extra "N" before the value)

 select * from table_name where ID=N'something' 

which causes some kind of character conversion.
The problem is that although the first query performs a range scan, the second performs a full table scan.

Since I cannot change the queries that run this software, what data type should I use instead of varchar2 so that the conversion performed by the "N" function does not mean a full table scan?

Answer 1

The N prefix before the string is used to indicate the data type of NVARCHAR2 or NCHAR.

When comparing NVARCHAR2s with VARCHAR2s, Oracle converts the VARCHAR2 variable to NVARCHAR2. That is why you experience FULL SCAN.

Use NVARCHAR2 instead of VARCHAR2 in your table if you cannot modify the query.