Introduction to Oracle Database Limitations and How to Deal with Them
To store large objects in databases, Oracle has a data type named LOB. This allows us to store up to 4GB of data in an Oracle database (9i).
LOB Data type Description
- BLOB(Binary large objects): data type is used to store unstructured binary data up to 4G.
- CLOB(Character large objects): Used to store up to 4G of character data.
- BFILE: This is used to store the locator of the external data file.
LOB Storage Option
- Inline storage: The data stored in the Oracle database is called inline if it’s stored with other columns in the table. Oracle database allows up to 4KB of data to be stored inline. Option ENABLE STORAGE IN ROW allows LOB data to be stored in table.
- Out of line storage: When LOB is larger in size (>4KB) then it does not get stored in the table where other rows get stored. Instead it gets stored in the table space. This is called out-of-line storage. Option DISABLE STORAGE IN ROW allows us to force data storage out-of-line without considering its size.
Oracle Restrictions for LOBs Storage
Oracle databases have a limitation that more than 4kb of data cannot be stored in line for any LOB data type as this will depreciate the performance of the search/retrieve operation on that table due to a full table scan. In that case the data will be stored in the table space and not in the table. If you want to store LOB > 4KB in size then it gets stored in table space. But the retrieval of those LOB > 4kb is not the same as that of other rows.