databaseoracle-databaseindexingnclob

Oracle starts to do full table scans when a column is changed from varchar to nclob

I have a table with about 100.000 rows that used to look more or less like this:

id      varchar(20),
omg     varchar(10),
ponies  varchar(3000)

When adding support for international characters, we had to redefine the ponies column to an nclob, as 3000 (multibyte) characters is too big for an nvarchar

id      varchar(20),
omg     varchar(10),
ponies  nclob

We read from the table using a prepared statement in java: 

select omg, ponies from tbl where id = ?

After the 'ponies' column was changed to an NCLOB and some other tables where changed to use nchar columns, Oracle 11g decided to do a full table scan instead of using the index for the id column, which causes our application to grind to a halt.

When adding a hint to the query, the index is used and everything is "fine", or rather just a little bit more slow than it was when the column was a varchar.

We have defined the following connection properties:

 oracle.jdbc.convertNcharLiterals="true"
 defaultNChar=true

Btw, The database statistics are updated.

I have not had time to look at all queries, so I don't know if other indexes are ignored, but do I have to worry that the defaultNChar setting somehow is confusing the optimizer since the id is not a nchar? It would be rather awkward to either sprinkle hints on virtually all queries or redefine all keys.

Alternatively, is the full table scan regarded as insignificant as a "large" nclob is going to be loaded - that assumption seems to be off by 3 orders of magnitude, and I would like to believe that Oracle is smarter than that.

Or is it just bad luck? Or, something else? Is it possible to fix without hints?

Solution

  • The problem turns out to be the jdbc-flag defaultNChar=true.

    Oracles optimizer will not use indexes created on char/varchar2 columns if the parameter is sent as a nchar/nvarchar. This is nearly making sense, as I suppose you could get phantom results.

    We are mostly using stored procedures, with the parameters defined as char/varchar2 - forcing a conversion before the query is executed - so we didn't notice this effect except in a few places where dynamic sql is used.

    The solution is to convert the database to AL32UTF8 and get rid of the nchar columns.