Forum Stats

  • 3,855,570 Users
  • 2,264,523 Discussions
  • 7,906,069 Comments

Discussions

Compression with Partitioned data

186011
186011 Member Posts: 49
edited Nov 13, 2010 5:27PM in Advanced Compression
We use range partition based on day. After 14days the data is not updated. We retain the data for 6 months and would like to have it online because it is queried upon with low frequency (less than 100 queries a day). We are not using Exadata -

My question:
What compression options do I have -
a) Compress the table or Table space - For all the data
b) Compress the data after 14 days

Also, we are using Physical data guard so we would like whatever is done at the active to be replicated at the standby.

Thanks for your help.

Best Answer

  • mrmessin
    mrmessin Member Posts: 287
    Answer ✓
    Unfortunately with a physical standby force logging could be the issue, like anything you will need to test this to see the affect you experience.

    Remember with a physical standby is a long distance location if you have the advanced compression license I would look into compression of the archive redo sent to the physical standby as this can help a lot esp in the event of short term bursts going to the physical standby.

    If you keep the partitions fairly small and then execute the move off hours from prime activity and compress the archive redo you may find that the periodic daily partitiong bursts may not be the heavy issue you are thinking it might be, test and see.

Answers

  • mrmessin
    mrmessin Member Posts: 287
    For the physical standby there is no issues for the compression. as for partitioning older partitions OLTP compression is an option works for paritions of older data.

    CREATE TABLE "CLINICAL_RESULT_T"
    ( "FILLER_APP" VARCHAR2(8 BYTE) NOT NULL ENABLE,
    "FILLER_APP_ORDER_NBR" VARCHAR2(55 BYTE) NOT NULL ENABLE,
    "RESULT_TEST_CODE" VARCHAR2(10 BYTE) NOT NULL ENABLE,
    "RESULT_TEST_NAME" VARCHAR2(30 BYTE),
    "RESULT_SEQ" NUMBER(*,0),
    "RESULT_STATUS" CHAR(1 BYTE),
    "RESULT_TYPE" CHAR(2 BYTE),
    "RESULT_VALUE" VARCHAR2(78 BYTE),
    "REFERENCES_RANGE" VARCHAR2(20 BYTE),
    "HI_LOW_AA_FLAG" CHAR(1 BYTE),
    "RESULT_UNITS" VARCHAR2(60 BYTE),
    "DATETIME_TEST_PERFORMED" DATE,
    "VERIFY_TECH" VARCHAR2(8 BYTE),
    "DATE_VERIFIED" DATE,
    "UPDATE_SRC" VARCHAR2(8 BYTE),
    "LAST_UPDATED" DATE,
    "BEGIN_DATE" DATE,
    "PATIENT_CDR_ID" NUMBER(*,0) NOT NULL ENABLE,
    "LIS_PO1_KEY" VARCHAR2(53 BYTE),
    "EVENT_CDR_ID" NUMBER(*,0),
    "CDR_UPDATE_SRC" VARCHAR2(8 BYTE),
    "CDR_LAST_UPDATED" DATE,
    "RESULT_TREND_CODE" VARCHAR2(10 BYTE),
    "ORDER_LOC_TYPE" VARCHAR2(5 BYTE),
    "TRENDABLE_TEST_FLAG" VARCHAR2(1 BYTE)
    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
    PARTITION BY RANGE ("LAST_UPDATED")
    (PARTITION "P001" VALUES LESS THAN (TO_DATE(' 1998-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
    PARTITION "P002" VALUES LESS THAN (TO_DATE(' 1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
    PARTITION "P003" VALUES LESS THAN (TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
    PARTITION "P004" VALUES LESS THAN (TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
    PARTITION "P005" VALUES LESS THAN (TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
    PARTITION "P006" VALUES LESS THAN (TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) COMPRESS FOR ALL OPERATIONS,
    PARTITION "P007" VALUES LESS THAN (TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) COMPRESS FOR ALL OPERATIONS,
    PARTITION "P008" VALUES LESS THAN (TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) COMPRESS FOR ALL OPERATIONS,
    PARTITION "P009" VALUES LESS THAN (TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) COMPRESS FOR ALL OPERATIONS,
    PARTITION "P010" VALUES LESS THAN (TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) COMPRESS FOR ALL OPERATIONS,
    PARTITION "P011" VALUES LESS THAN (TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) COMPRESS FOR ALL OPERATIONS,
    PARTITION "P012" VALUES LESS THAN (TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) COMPRESS FOR ALL OPERATIONS,
    PARTITION "PMAX" VALUES LESS THAN (MAXVALUE)
    ) ENABLE ROW MOVEMENT ;

    If you do not have an advanced compression license you can compress with the COMPRESS FOR DIRECT LOADS option rathern then the COMPRESS FOR ALL OPERATIONS option.

    as data ages you can re-org the partition to compress it from uncompressed.

    alter table CLINICAL_RESULT_T move partition P002 COMPRESS FOR ALL OPERATIONS ;
  • 186011
    186011 Member Posts: 49
    Thanks fior your response.

    Is there anyway to avoid generating the redo with the nologging option ? Of concern is the amount of redo log that will be generated because of the compression at the active - this would ofcourse increase the apply lag and the potential loss of data.

    Related queustion - What are the benefits of Adavanced compression for RMAN . My understanding is that without the license for Advanced Compression RMAN does provide for compression. So - If I have the license - how do I use it to - and what are the benifits ?
  • mrmessin
    mrmessin Member Posts: 287
    With the nologging are you referring to the no logging for the move operation?
    you can, but if will turn the logging off for the parition and you would need to turn it back on manually if it required going forward, but if the partition does not change then this may not really be needed as the data will not change. If you are using the table compression without the advance compression option any change to the data will be uncompress, one of the draw backs to table compress and not having the advanced compression option.

    With good bandwidth between the primary and standby I have never had a great issue with lag, how ever with the advanced compression option you can compress the archive redo to the standby that can help with lag if that is an issue for you. I have found it is a matter of bandwidth to the transaction rate.

    You can compress using RMAN without an advanced compression license with the basic RMAN compression the new compression option with RMAN that came in with 11g however would require the advanced compression license.

    The new advanced compression option for RMAN is the ZLIB compression just set the RMAN configuration parameter, use with the following:
    RMAN> configure compression algorithm 'ZLIB' ;

    To use the RMAN compression that does not require the advance compression license it is bzip2 and is the default.
    RMAN> configure compression algorithm 'bzip2';
  • 186011
    186011 Member Posts: 49
    Thank your Sir.

    So basically what you are saying is - If I use the move to compress (with nologging) & mark the tablespace as read only then I will have no issues at the standby,

    The reason the bandwidth is an issue for us is because the standby site is in Europe - so would like prevent bursts of high redo generation that the compress will generate.
  • mrmessin
    mrmessin Member Posts: 287
    Answer ✓
    Unfortunately with a physical standby force logging could be the issue, like anything you will need to test this to see the affect you experience.

    Remember with a physical standby is a long distance location if you have the advanced compression license I would look into compression of the archive redo sent to the physical standby as this can help a lot esp in the event of short term bursts going to the physical standby.

    If you keep the partitions fairly small and then execute the move off hours from prime activity and compress the archive redo you may find that the periodic daily partitiong bursts may not be the heavy issue you are thinking it might be, test and see.
This discussion has been closed.