This discussion is archived
6 Replies Latest reply: Aug 24, 2011 8:40 AM by Kerry.Osborne RSS

DML on Exadata Hybrid Columnar Compression

Saurabh Gupta-OC Newbie
Currently Being Moderated
Dear Team,

We are working on Exadata - V2 Quarter Rack. Our Database size is 7 TB and now we want to implement the Hybrid Columnar Compression feature on some of the big sized tables here.
I just applied this feature (compress for query low) 6.x reduction on one of the table and reduced the size of the table from 48G to 8G.

The performance of the simple select statement on this compressed table is also seems to be good.

Please find the command details:

CREATE TABLE OUT_DATA_COMP COMPRESS FOR QUERY LOW AS
SELECT * FROM OUT_DATA_L2 WHERE 1=2;

ALTER TABLE OUT_DATA_COMP NOLOGGING

INSERT /*+ APPEND */ INTO OUT_DATA_COMP SELECT * FROM OUT_DATA_L2
185356159 rows inserted

12:48:56 SQL> SELECT COUNT(*) FROM OUT_DATA_L2;
COUNT(*)
----------
185356159
Elapsed: 00:00:09.66

12:49:16 SQL> SELECT COUNT(*) FROM OUT_DATA_COMP;
COUNT(*)
----------
185356159
Elapsed: 00:00:03.61


Could you please let me know what would be the DML queries (Insert/Update) performance would be here?
And after inserting the rows, will the data would be in OLTP or NO compression status or in the warehouse compressed mode?
Do we need to alter the table before firing Insert/Update/Delete statement on this compressed table?

Any one who has experince on EHCC please share me the detail.

Would appreciate your quick reply.

Thanks in Advance!!
  • 1. Re: DML on Exadata Hybrid Columnar Compression
    Marc Fielding Journeyer
    Currently Being Moderated
    I just applied this feature (compress for query low) 6.x reduction on one of the table and reduced the size of the table from 48G to 8G.
    Looks good. You may also want to experiment with QUERY HIGH; I've gotten better compression here without significant performance degradation
    Could you please let me know what would be the DML queries (Insert/Update) performance would be here?
    If rows are updated they would be migrated out of the EHCC-compressed compression unit, so would invovle more work for the database. Inserts without direct path would work just like OLTP compression.
    And after inserting the rows, will the data would be in OLTP or NO compression status or in the warehouse compressed mode?
    OLTP, if not using direct path
    Do we need to alter the table before firing Insert/Update/Delete statement on this compressed table?
    No changes required: both EHCC and OLTP compression are transparent to SQL and applications
  • 2. Re: DML on Exadata Hybrid Columnar Compression
    Kerry.Osborne Oracle ACE Director
    Currently Being Moderated
    A couple of other things to keep in mind.

    1. Row level locking goes away when using HCC. So an update of a single row will lock the entire Compression Unit. Since many more rows are stored in a compression unit (orders of magnitude in some cases) than in an Oracle block, this is much worse than block level locking. If you plan on updating while concurrently accessing the data you should definitely not attempt to use HCC for the active rows.

    2. Every update of a record stored in HCC format results in a migrated row. The new row is stored in a new block that is marked for OLTP compression.

    3. As Mark mentioned, non-direct path inserts will be loaded into OLTP compressed blocks as opposed to HCC format.

    4. For the above reasons, mixing HCC with DML is not recommended. Partitioning can provide a mechanism for avoiding these issues since each partition can have it's own storage format. For example, you could have your newest (active) data uncompressed and you oldest (presumably non-active historical data) compressed for Archive High.
  • 3. Re: DML on Exadata Hybrid Columnar Compression
    Saurabh Gupta-OC Newbie
    Currently Being Moderated
    Dear friends,

    Thanks for putthing your experience here.

    Some more doubts:

    1)
    As you told that in HCC suppose a table is in warehouse compression (query low) and after insert the new rows are inserted in OLTP compression, then how about the whole table should be compressed in OLTP compression instead so after insert/update whole table would be in same compression type.

    2)
    Can we move the newly inserted rows from OLTP compression type to Warehouse Compression (query low) using the below command, if a table is already in warehouse compression:
    "Alter Table table_name move compress for query low"

    3)
    ALTER TABLE <TABLE_NAME> MOVE NOCOMPRESS
    using the above command we can disable the table compressesion for an existing compressed
    table but this method will uncompress only the new inserted data, all data that was already compressed
    remains compressed.
    How can we uncompress all data in a compressed table?

    We have data warehouse env and having 15 TB total storage, out of 15 we have occupied 8 TB and rest is 7 TB. Now we are seriously looking for our storage capacity plan for next 3-4 year and trying to get benefited of compression feature of exadata. We dont run dml queries quite often, it is run by daily jobs in the night on daily basis.
    Reports from the DB data is being fetched regularly.
    Please let us know the best compression we can apply here.

    Thanks for your assistance.
  • 4. Re: DML on Exadata Hybrid Columnar Compression
    603349 Explorer
    Currently Being Moderated
    If you haven't, I'd suggest checking out these links on Exadata Hybrid Columnar Compression
    http://blogs.oracle.com/datawarehousing/entry/new_whitepaper_on_exadata_hybr
    http://www.oracle.com/technetwork/middleware/bi-foundation/ehcc-twp-131254.pdf
    As you told that in HCC suppose a table is in warehouse compression (query low) and after insert the new rows are inserted in OLTP compression, then how about the whole table should be compressed in OLTP compression instead so after insert/update whole table would be in same compression type.
    I'm not sure of the question you are asking here. Can you clarify? I think your question might be answered in the tech white paper above, so see that and follow up if it does not answer it.
    Can we move the newly inserted rows from OLTP compression type to Warehouse Compression (query low) using the below command, if a table is already in warehouse compression:
    "Alter Table table_name move compress for query low"
    This command will recompress all of the table in query low format.
    ALTER TABLE <TABLE_NAME> MOVE NOCOMPRESS
    using the above command we can disable the table compressesion for an existing compressed
    table but this method will uncompress only the new inserted data, all data that was already compressed
    remains compressed.
    How can we uncompress all data in a compressed table?
    This command will uncompress all data into a new segment(s), and new data added will also be in uncompressed format.

    If you want to change the compression new data will get in a given segment, you can do an ALTER TABLE. e.g:
    create table foo () compress;
    insert some data
    alter table foo compress for query high;
    direct path insert some data
    alter table foo nocompress;
    insert some data
    NOTE: this does not change existing data, only new data.

    --
    Regards,
    Greg Rahn
    http://structureddata.org
  • 5. Re: DML on Exadata Hybrid Columnar Compression
    848750 Newbie
    Currently Being Moderated
    The performance of the simple select statement on this compressed table is also seems to be good.
    6x compression and 2.7x table scan improvement with simple count(*).
  • 6. Re: DML on Exadata Hybrid Columnar Compression
    Kerry.Osborne Oracle ACE Director
    Currently Being Moderated
    Just a quick follow on to Greg's comments.

    On your question number 2.

    Yes "alter table move" will compress the whole segment in the specified format. But your question implies that you're thinking about loading some data into a large existing table. It is unlikely that you would want to move the whole table to get the newly loaded rows compressed. The better approach would be to load the rows with direct path inserts so they are compressed on the way in, or use partitioning to allow you to move a subset of the rows (the new ones) instead of the whole table. I'm sure the doc's Greg referenced cover this in detail.


    On your question number 3.

    "Alter table move" moves the whole table into the specified storage format (HCC Query High for example). "Alter table" (without specifying the move keyword) only changes the table definition which will affect future inserts. Existing records are not affected.

    Edited by: Kerry.Osborne on Aug 24, 2011 8:39 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points