2 Replies Latest reply: Aug 7, 2013 6:14 PM by 9423755 RSS

    Table Compression with LOBs

    user12289108

      Hi,

      I need some help with the Advanced Table Compression when creating a table especially when they contain a LOB.

      Here are 3 examples:

       

      Exp#1

      CREATE TABLE emp (

            emp_id NUMBER, 

            first_name VARCHAR2(128), 

            last_name VARCHAR2(128)

      ) COMPRESS FOR OLTP;

      This one is ok - all elements are compressed.

       

      Exp#2

      CREATE TABLE photos (

            photo_id NUMBER,

            photo BLOB)

            LOB(photo) STORE AS SECUREFILE (COMPRESS LOW);

      This one I am confused - is it just the LOB(photo) that is compressed or the whole table. If it is just the LOB then what syntax do I need for the whole table?

      I also assume that the LOB is being stored in the default tablespace associated with this table - correct me if I am wrong!

       

      Exp#3

      CREATE TABLE images (

            image_id NUMBER,

            image BLOB)

            LOB(image) STORE AS SECUREFILE (TABLESPACE lob_tbs COMPRESS);

      This one I am confused - I think it is telling me that LOB(image) is being compresses and stored in tablespace lob_tbs and the other elements are being stored uncompressed in the default tablespace.

      Again if it is just the LOB then what syntax do I need for the whole table?

       

      Thanks & regards

      -A