I've got a table about 100.000 rows that accustomed to look pretty much such as this:

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

When adding support for worldwide figures, we needed to redefine the ponies column for an nclob, as 3000 (multibyte) figures is simply too large to have an nvarchar

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

We read in the table utilizing a prepared statement in java:

select omg, ponies from tbl where id = ?

Following the column was transformed for an NCLOB, Oracle 11g made the decision to perform a full table scan rather than while using index for that id column, which in turn causes our application to grind to some halt.

When adding an indication towards the query, the index can be used and things are "fine", in other words a little bit more slow of computer was once the column would be a varchar.

We've defined the next connection qualities:

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

Btw, The database statistics are up-to-date.

I haven't had time for you to look whatsoever queries, so I'm not sure if other indexes are overlooked, but must i worry the defaultNChar setting in some way is confusing the optimizer because the id isn't a nchar? It might be rather awkward either to sprinkle hints on almost all queries or redefine all secrets.

Alternatively, may be the full table scan regarded as as minor like a "large" nclob will probably be loaded - that assumption appears to become off by 3 orders of magnitude, and I must think that Oracle is wiser than that.

Or perhaps is it simply misfortune? Or, another thing? Can you really fix without hints?

Whenever you redid the data have you estimate or use dbms_stats.gather_table_stats by having an estimate_percentage > 50%? Should you did not then use dbms_stats having a 100% estimate_percentage.

In case your table is just 3 posts and fundamental essentials ones you are coming back then your best index is 3 posts regardless of what you hint and even when the id index is exclusive. Because it stands your explain plan should with a unique index scan then a table access by rowid. Should you index all 3 posts this turns into a unique scan as all the details you are coming back come in the index already and you shouldn't have to re-access the table to have it. An order could be id, omg, ponies to take advantage from it within the where clause. This could effectively build your table an index organized table, which may be simpler than getting another index. Clearly, gather stats after.

Saying everything I am not really certain you are able to index a nclob and regardless of what you need to do how big the column will have an effect because the longer it's the more disk reads you'll have to do.

The issue works out to become the jdbc-flag defaultNChar=true.

Oracles optimizer won't use indexes produced on char/varchar2 posts when the parameter is distributed like a nchar/nvarchar. This really is nearly making sense, when i suppose you can get phantom results.

We're mostly using saved methods, using the parameters understood to be char/varchar2 - forcing a conversion prior to the totally performed - therefore we did not notice this effect except inside a couple of places where dynamic sql can be used.

The answer would be to convert the database to AL32UTF8 and eliminate the nchar posts.

Sorry, however i do not understand why had you alter your column ponies from varchar to clob. In case your maximum lenght is 3000 char within this column, why not make use of a NVARCHAR2 column rather? So far as I understand, nvarchar2 can support 4000 figures.

But you are right, the utmost column size permitted is 2000 figures once the national character set is AL16UTF16 and 4000 when it's UTF8.