title: Oracle starts to do full table scans when a column is changed from varchar to nclob
body: <p>I have a table with about 100.000 rows that used to look more or less like this:</p>
  
  <pre><code>id      varchar(20),
  omg     varchar(10),
  ponies  varchar(3000)
  </code></pre>
  
  <p>When adding support for international characters, we had to redefine the <code>ponies</code> column to an nclob, as 3000 (multibyte) characters is too big for an nvarchar</p>
  
  <pre><code>id      varchar(20),
  omg     varchar(10),
  ponies  nclob 
  </code></pre>
  
  <p>We read from the table using a prepared statement in java: </p>
  
  <pre><code>select omg, ponies from tbl where id = ?
  </code></pre>
  
  <p>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 <code>id</code> column, which causes our application to grind to a halt.</p>
  
  <p>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.</p>
  
  <p>We have defined the following connection properties:</p>
  
  <pre><code> oracle.jdbc.convertNcharLiterals="true"
   defaultNChar=true
  </code></pre>
  
  <p>Btw, The database statistics are updated.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Or is it just bad luck?  Or, something else? Is it possible to fix without hints?</p>