I've got a table in Oracle that's presently storing close millions of Records. This table is getting 2 CLOB posts that store XML data. I in addition have a search screen designed for this specific table where customers can explore virtually all of the posts aside from the CLOB posts.

Now here's my questions -

Presuming which i have right indexes in position around the frequently search posts, does the presence of the CLOB posts inside a table effect the performance while search the table for records?

Let's say Basically move these 2 CLOB posts right into a new table and define a relation to parent table while using primary key, does it improve performance when it comes to record search and retrieval.

Tell me.

LOBs (including CLOBs) could be saved inline or out-of-line. Inline storage is much more normal for more compact CLOBs (eg where typical values might be a couple of hundred bytes, however with periodic records of countless 1000). More particulars here

Out-of-line storage matches what you're saying about moving these CLOBs to a different table. So, think before doing might, if it can help, simply employ the "DISABLE STORAGE IN ROW" option.

What's best ? It is dependent. Whether it did not, then Oracle would opt for the very best and never provide you with the use of another.

Storing inline implies that each row occupies more room so that you can have less rows with you cache, and that means you have to hit the disk more frequently, that is reduced. Storing them individually implies that, when you wish individuals posts, you need additional data blocks, most likely in the disk instead of cache and will also be reduced.

If you are using the CLOBs frequently, and they're smallish, then you're most likely best in-row.

Why store XML as CLOB instead of XMLType ?