Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    99

    Unanswered: NCLOB column datatype causes full table scan.

    Hi,

    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

    any help or suggestions much appreciated?

    thanks

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    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.

    Alan

  3. #3
    Join Date
    Jan 2004
    Posts
    99
    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.

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    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.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •