Inlined LOBs are also qualified for compression. This page shows you how to compress the inlined LOBs. We will then run some performance tests and analysis against the regular LOB table, the inlined LOB table, and the compressed inlined LOB table. Select the actions on this page in order.

For more in depth analysis of data and index compression, refer to the Automated Compression and Compression Performance tutorials.

Create and populate a table with inlined and compressed LOB

The first step creates the INLINE_COMPRESS table with inlined LOB and compression enabled.

CREATE TABLE INLINE_COMPRESS
  LIKE INLINELOBDATA COMPRESS YES
   NOT LOGGED INITIALLY

INSERT INTO INLINE_COMPRESS
  SELECT * FROM INLINELOBDATA

REORG and RUNSTATS all three tables

Reorganize and update statistics for each table so that the optimizer can choose the best access path possible for all of them.

CALL ADMIN_CMD('REORG TABLE LOBDATA');
CALL ADMIN_CMD('RUNSTATS ON TABLE LOBDATA');

CALL ADMIN_CMD('REORG TABLE INLINELOBDATA');
CALL ADMIN_CMD('RUNSTATS ON TABLE INLINELOBDATA');

CALL ADMIN_CMD('REORG TABLE INLINE_COMPRESS');
CALL ADMIN_CMD('RUNSTATS ON TABLE INLINE_COMPRESS');

Run performance tests

The second step runs a select with an ORDER BY clause against the regular LOB, inlined LOB, and compressed inlined LOB tables.

SELECT * FROM LOBDATA ORDER BY CUSTOMER_ID;
SELECT * FROM INLINELOBDATA ORDER BY CUSTOMER_ID;
SELECT * FROM INLINE_COMPRESS ORDER BY CUSTOMER_ID;

Graph the results

You can then graph the results for runtime, IO and CPU performance

If you hover your cursor over the bars in the graph you will see the exact sizes of each table.