Optimizing Storage Structures - Oracle Database 11gR2 Performance Tuning - page 91

How to do it...

In this recipe, we will see how to use LOB felds to avoid performance degradation and space
wasting. Follow these steps:

1. Connect to the database as user SH :

CONNECT s.5h@TESTDB/sh

2. Create a table to do some tests copying the CUSTOMERS table and its contents:

CREATE TABLE MyCustomers AS SELECT * FROM Customers;

3. Add a BLOB field to the table:

ALTER TABLE MyCustomers ADD (c_file BLOB)
LOB(c_file) STORE AS SECUREFILE (
tablespace ASSM_TS
enable storage in row
nocache logging
);

4. Create a reference to the $ORACLE_HOME/rdbms/admin folder (fill in the correct

path for your Oracle Database installation):
CREATE DIRECTORY TESTBLOB AS
‹/u01/app/oracle/product/11.2.0/db_1/rdbms/admin›;

5. Load the blob field with some data:

declare
l_file bfile;
l_blob blob;
l_size number;
begin
l_file := bfilename(‹TESTBLOB›, ‹catalog.sql›);
dbms_lob.fileopen(l_file);
l_size := dbms_lob.getlength(l_file);
for J in 1 .. 100 loop
update MyCustomers SET c_file = empty_blob() where CUST_ID = J
returning c_file into l_blob;
dbms_lob.loadfromfile(l_blob, l_file, l_size);
end loop;
commit;
dbms_lob.close(l_file);
end;