Oracle LOB Not getting stored inline

Question

Setup

I have an oracle table that has couple of attributes and a CLOB datatype. The table below I have create with two ways ,each of which should give the same behavior.

CREATE TABLE DEMO(
a number (10, 2),
data CLOB
)

CREATE TABLE DEMO(
a number (10, 2),
data CLOB
) LOB (data) Stored AS (STORAGE IN ROW ENABLED)

Scenario

As per the oracle documentation when the CLOB is greater the 4000 bytes it will be stored outline else inline.
When I store the data in this table for a clob value say "Hello" and then I see the segment information for the "Demo table" and "Demo table LOB segment" , it shows that all the data is going to table and no new blocks are being consumed in the Lob Segment.
When I store a bigger data with total character less than 1500 , then also I get the same behavior as above.
But when I store a data with total character > 2000 and < 3000 , then the LOB data is going to the LOB segment even though total character are less than 3000.

Question

Why is the data smaller than 3000 characters is going to the LOB Segment ? . Is that each character takes 2 bytes , which justifies that data till 1500 is going to the data instead of Log Segment.

Problem

Lots of disk space is getting wasted because of the LOB Table , since the CHUNK size is 8kb and the data per block will always be around 3 - 4K character and in some cases exceeding that. So essential for each row 4Kb space is wasted and in out case of 20 mn rows , its running in 50's of GBs

Do you use UTF8? What characters do you store? If they are mostly non ASCII chars, 2000 characters may translate to more than 4000 bytes. — Drunix
I am using UTF-8 characters only. Also I created a table with varchar2(4000) and tried to insert the same data that was stored outline. and the storage was successfull, which basically means that the total bytes are < 4000 — Pratik Garg

Pratik Garg Pratik Garg · Accepted Answer · 2014-04-16T06:48:07

This may explain the above behaviour..

"The CLOB and NCLOB datatypes store up to 4 gigabytes of character data in the database. CLOBs store database character set data and NCLOBs store Unicode national character set data. For varying-width database character sets, the CLOB value is stored in the database using the two-byte Unicode character set, which has a fixed width. Oracle translates the stored Unicode value to the character set requested on the client or on the server, which can be fixed-width or varying width. When you insert data into a CLOB column using a varying-width character set, Oracle converts the data into Unicode before storing it in the database."

http://docs.oracle.com/cd/B10500_01/server.920/a96524/c13datyp.htm#3234

Oracle LOB Not getting stored inline

1 Answers