Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2004
    Posts
    60

    Question Unanswered: 'Alter tablespace' with COMPRESS option in 9iR2

    Hi All,

    Oracle 9iR2 have a new feature :: "Compress".
    Tablespace, table, partitions & Materialized View(MV) can be created with compress option. I found out that one can alter table & MV to compress option & vice versa.

    Is ther any way to alter the existing nocompress/compress tablespace to compress/ocompress ??

    Following link from OTN give syntax for same in alter tablespace clause, but actually redirect to alter table clause for data_segment_compression

    http://www.lc.leidenuniv.nl/awcourse...3a.htm#2093898

    Any knowladge sharing in this regards would be helpful.

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    be careful. you don't want to compress tables that have activity on them.
    static tables, maybe.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    In my opinion, today - when hard disks are cheaper than ever and there's no problem to have 2GB of RAM - compressing your database should really be called a feature. OK, Oracle offers it, it is here, I know about it, but - no, thank you, I'd rather keep my database as it was. Compression surely offers some advantages, but one should ask himself: do I really need it?

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    I am a fan of INDEX compression however. with that you have performance
    gain possibilities that you might not get otherwise.

    obviously finding appropriate candidates for index compression is the key.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I have to go with Duck, sometimes compression can have significant performance benefits especially when you have good compression ratios. For example if you have a very big tables and if you have a compression ratio of 3:1 then your FTS may need to do one third the number of IOs (MAY please note ). Dont forget as CPUs get more powerful you quite often find the overhead of CPU decompression is outweighed by the benefit of signicantly less IO. Aswell as the nice side benefit of taking less diskspace.

    Alan

  6. #6
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    As I understand it compression is only used in bulk insert operations anyway, so it should not have any impact on normal processing. Possibly I am missing something.

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    As far as I know both index and table compression work on normal inserts/updates.

    Alan

  8. #8
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    From the Concepts Guide, Table compression:
    Compression occurs while data is being bulk inserted or bulk loaded.

  9. #9
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Yep you are correct, for table compression you need bulk inserts or direct inserts for it to work. I was going by the Oracle magazine article which misses out those minor details

    Alan

  10. #10
    Join Date
    Sep 2004
    Posts
    60
    I was on short leave, thus not posted note.
    Thanks for your time & comments but I feel actual question is still unanswered.

  11. #11
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    I can't test this right now, but you should be able to

    Code:
    ALTER TABLESPACE tablespacename DEFAULT COMPRESS;
    This will change the default compression behaviour for new segments.

  12. #12
    Join Date
    Sep 2004
    Posts
    60
    Thanks William.

    It worked .. I tested it.

    would have been tried little code by myself.

Posting Permissions

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