Unanswered: NCLOB column datatype causes full table scan.
I have a simple SQL statement joining two tables (hash join) , one of the columns in the table is a NCLOB datatype. When executing the query the explain plan shows a [INDEX FAST FULL SCAN] as expected. However when I include the column with the NCLOB datatype in the select statement the explain shows a full table scan.
Why would the optimizer choose a full table scan? even though the NCLOB is not included in the where clause statement.
Database Version: 10g rel2
Optimizer: default choose
Statistics: up to date
Because if you include the NCLOB column in the select it then has to fetch that. And seeing as its not in your index it will have to goto the table anyway so it may have costed that cost of going to the index and then the table is greater than doing a full table scan.
I have noticed that 10g has had a number of improvements to speed up full table scans so it does seem that it goes for FTS more than previous versions.
Was reading around the topic, I understand by default when using the NCLOB datatype the lobsegment is created with "no cache" meaning it forces a full block read which I believe results in the full table scan.
Changing the lobsegment to "cache" results in improved read, however may flush out other buffer blocks from the db_cache_size pool.
I will test whether this is true, using the cache option and update the thread.
I dont believe the nocache means it does a FTS, just that when it goes to fetch the LOB data it uses a direct read bypassing the buffer cache. After all if your hypothesis were true noone would use the nocache option as it would completely destory performance on any lob table with a reasonable number of rows.