How to compress tables in Oracle 9i
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: How to compress tables in Oracle 9i

Hybrid View

  1. #1
    Join Date
    Dec 2002
    Location
    India
    Posts
    34

    Lightbulb How to compress tables in Oracle 9i

    Hi,

    I am working in Data warehousing project as per my client requirement i have to compress the tables using the feature in Oracle 9i Release 2. As Data warehouse contains Millions of records i need to cmopress and also test the Query performance of Compressed tables in oracle 9i against uncompressed tables in Oracle 8i, I read many articles regarding Compression of tables in oracle 9i but no where did they mentioned how to compress the tables!!! can anyone pls help me

    Thanks
    Sridhar

  2. #2
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    I think you will have to create a new table, with compression enabled and afterwards insert the data into this new table.

    HTH
    Gert

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I think that some reading of documentation is called for.

    You can "ALTER TABLE ... COMPRESS" to enable compression for new data, or "ALTER TABLE ... MOVE COMPRESS" to compress data already in the table.

    PANDO, I think you were a little premature in closing ksridhar's thread, eh?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Mar 2002
    Posts
    534
    When you are inserting the data you have to use the APPEND option with the INSERT statement or DIRECT LOAD if you are using SQLLOADER otherwise your data will not be compressed.

    HTH
    Mike

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    premature? I close dupe threads nothing else nothing more

  6. #6
    Join Date
    Dec 2002
    Location
    India
    Posts
    34
    Hi Mr Pando!!

    The reason for Discussion forum is to get the knowledge or to get a solution from experienced guys in taht area .. What made u think that this is a Dupe thread i strongly oppose this !!! As i could not get much info regarding Oracle 9i compression feature so thought i could post in it this forum to get a solution... I feel bad regarding the closing of this thread

    Thanks
    Sridhar
    Last edited by ksridhar; 12-30-2002 at 03:03 AM.

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you post in a place not two like you do in two forums, that's dupe and will always be closed, people not only visit in one forum so what's the point posting in two?

    what if everyone acts like you and post twice all questions? post twice doesnt mean you get your answers

    I dont understand why you say you cannot get much info on table compression because it's all explained in the documentation

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    sorry pando, didn't spot that both threads were Sridhar's.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Dec 2002
    Location
    India
    Posts
    34

    Table Compression

    Hi ,

    The Table compression works for Partitioned and non-partitioned
    tables the syntax for Non-Partitioned tables is
    the statement "ALTER TABLE MOVE COMPRESS"
    is going to compress the existing rows and also the New rows to be
    added

    "ALTER TABLE EMP
    MERGE PARTITIONS P_2000,P_3000 INTO PARTITION
    P_2000_3000_compress
    COMPRESS;" is going to merge and compress the partitions
    saving lot of disk space.

    for a Partitioned Tables u can only merge and compress or if u want to
    compress a partitioned table ur going to loose the partitions
    "create table
    tablespace
    COMPRESS
    as
    select * from "

    I have worked on Table Compression feature the results are amazing!!
    The Table size was reduced from 800MB to 120MB and 230MB to 32 MB
    The query performance is also good.. these results were done on
    Partitioned Tables!!! all partitions were removed... This is
    recommended on Tables where no DMLs are performed. If i come to know
    any results i'll post them

    compression for partitioned tables
    USE THIS way and also remove the clause COMPRESS in this definition
    load the data in both the tables see the table size i did not find and
    difference in the table size!!!

    CREATE TABLE EMP
    (EMPNO NUMBER(4) NOT NULL,
    ENAME CHAR(30),
    AGE NUMBER(3)
    )
    COMPRESS
    PARTITION BY RANGE(EMPNO)
    (
    PARTITION P_1000 VALUES LESS THAN (1000)
    TABLESPACE
    STORAGE PARAMETERS < >
    ),
    (
    PARTITION P_2000 VALUES LESS THAN (2000)
    TABLESPACE
    STORAGE PARAMETERS < >
    ),
    (
    PARTITION P_3000 VALUES LESS THAN (3000)
    TABLESPACE
    STORAGE PARAMETERS < >
    ),

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •  


    Click Here to Expand Forum to Full Width