Forum Stats

  • 3,851,383 Users
  • 2,263,969 Discussions
  • 7,904,691 Comments

Discussions

advance compression how to use

431744
431744 Member Posts: 48
edited Jan 7, 2011 12:37PM in Advanced Compression
hi export, I need to used advance compression option. I have planed to store 2T size images in oracle DB and need to improve faster query.

I used systax this way (sample table):

SQL> CREATE TABLE cus_sig
2 (MOBILE_NO varchar2(50),
3 IMAGE_NAME varchar2(50),
4 IMAGE BLOB)
5 LOB(IMAGE) STORE AS SECUREFILE (
6 COMPRESS HIGH
7 CACHE
8 );


1. pl confirm when Im going to store data in this does it compress?

2 when i check the user_tables option , compression is disabled.

SQL> select table_name, compression, compress_for from user_tables where table_name in ('CUS_SIG');
TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
CUS_SIG DISABLED

then how to enable this option ...what is the correct way i should use it


waiting for response..

Thanks

ashwan

Answers

  • 431744
    431744 Member Posts: 48
    hi, need more clarification.

    1.if I compress tablespaces belong to table, rather than table compression, does it have same impact? or whch one is best.
    ex:SQL> create tablespace USER_DATA_COMPRESSED default compress for OLTP datafile '/u01/oracle/oradata/DET01/user_data_compressed.dbf' size 10M autoextend on;

    need help
    Thanks
    ashwan
  • damorgan
    damorgan Member Posts: 14,464 Bronze Crown
    You didn't ask Oracle to compress the table ... you asked it to compress the LOB.
    SQL> select object_name, object_type from user_objects where created > sysdate-1/24;
    
    OBJECT_NAME                    OBJECT_TYPE
    ------------------------------ -------------------
    CUS_SIG                        TABLE
    SYS_LOB0029168942C00003$$      LOB
    
    SQL> select table_name, segment_name, compression, securefile
      2  from user_lobs
      3  where segment_name = 'SYS_LOB0029168942C00003$$';
    
    TABLE_NAME                     SEGMENT_NAME                   COMPRE SEC
    ------------------------------ ------------------------------ ------ ---
    CUS_SIG                        SYS_LOB0029168942C00003$$      HIGH   YES
This discussion has been closed.