Data Retrieval - Performance with CLOB Columns in Oracle

Question

I have a table in Oracle that is currently storing close a Million Records. This table is having 2 CLOB columns that store XML data. I also have a search screen built for this particular table where users can search on pretty much all the columns except for the CLOB columns.

Now here is my questions -

Assuming that I have right indexes in place on the frequently search columns, does the existence of the CLOB columns in a table effect the performance while search the table for records?

Let us say If I move these 2 CLOB columns into a new table and define a relation to its parent table using the primary key, will it improve performance in terms of record search and retrieval.

Let me know.

Answer 1

LOBs (including CLOBs) can be stored inline or out-of-line. Inline storage is more normal for smaller CLOBs (eg where typical values may be a few hundred bytes, but with occasional entries of several thousand). More details here

Out-of-line storage corresponds to what you are saying about moving these CLOBs to a new table. So, don't bother doing that and, if it will help, simply use the "DISABLE STORAGE IN ROW" option.

Which is better ? It depends. If it didn't, then Oracle would go with the best and not give you the option of the other.

Storing inline means that each row takes up more space so you can have fewer rows in your memory cache, which means you need to hit the disk more often, which is slower. Storing them separately means that, when you want those columns, you need to get additional data blocks, probably from the disk rather than cache and this will be slower.

If you use the CLOBs often, and they are smallish, then you are probably better off in-row.

But why store XML as CLOB rather than XMLType ?