Defining size of CLOB in Oracle
I am making a table in which i am storing XML. To store XML i am using CLOB data type. The max size of my XML would be 5kb. What size of CLOB column should i define while creating the table?
Answer
you don't define a size exactly when setting a clob (unlike a varchar). it is just simply clob.
The max size of a lob is 4Gb.
Storage wise it will use space as follows:
- if the lob is defined as in-row, and the lob is less than ~ 4kb , it will just take up the amount of space that the document is. (eg store an xml of 512 bytes in-row and it will use 512 bytes.
- if the lob is defined out of row (or exceeded 4kb) then it will use a multiple of the "chunk" size that you defined when creating the table.
when creating a lob column, you can specify its storage clause to control in-row and out-of-row options like this:
LOB (c) STORE AS lobseg (DISABLE STORAGE IN ROW CHUNK 16384)
i.e. that would specify that the lob can never be stored inline with the regular table data, and will allocate storage in multiples of 16kb per chunk, so even if your document was 1kb, it would take 16kb of storage.
if your xml documents are only 5kb, you may want to consider in-row storage, and put a small chunk size (the min chunk size is 1 block, so if you have a tablespace with 4kb extents, your minumum chunk size will be 4kb; if you specify less, it will be ignored). the disadvantage of allowing in-row lobs, is that the table will be larger, so large range scans may suffer a bit (but lob retrieval is faster).
Also in 11g you have the option to compress lob segments which you may want to consider (if your licence covers it). Though with such small documents you may not benefit greatly.
read more here : http://docs.oracle.com/cd/B28359_01/appdev.111/b28393/adlob_tables.htm