DOES ADDING NEW PARTITION TO A COMPRESSED PARTITIONED TABLE ENABLE COMPRESSION AUTOMATICALLY TO ITS LOCAL INDEX ?

Main advantage of Advanced compression is to perform DML without decompression of compressed data.

There is NO article or feature found currently for automatically compressing a newly added local index during a partition addition to a compressed partitioned table. So, DBA should manually rebuild the new index partition with compression. But newly added table partition is compressed automatically.

There are three types of index for partitioned table

  • Local prefixed(OLTP, less partitions, less downtime for maintenance)
  • Local non prefixed(DSS, many partitions, more downtime for maintenance due to large number of large partition, faster full scans for ware house due to parallel)
  • Global prefixed(OLTP, less partitions,less downtime for maintenance)

Let us test the behavior

Create a tablespace, a range partitioned table with advanced compression and populate data into the table

kIsH@STAX<>create tablespace TBS1 datafile '/apps01/oradata/tbs1.dbf' size 5M autoextend on;

Tablespace created.

kIsH@STAX<>CREATE TABLE "KISH"."RP"
(    "R_ID" NUMBER(30,0),
        "R_NAME" VARCHAR2(30),
        "R_COST" NUMBER(30,0),
        "R_DATE" DATE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBS1"
PARTITION BY RANGE ("R_DATE")
(PARTITION "R_JAN2022"  VALUES LESS THAN (TO_DATE(' 2022-02-01 00:00:00', '
SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 ROW STORE COMPRESS ADVANCED LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBS1" ,
PARTITION "R_FEB2022"  VALUES LESS THAN (TO_DATE(' 2022-03-01 00:00:00' , '
SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
ROW STORE COMPRESS ADVANCED LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBS1",
PARTITION "R_MAR2022"  VALUES LESS THAN (TO_DATE(' 2022-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
ROW STORE COMPRESS ADVANCED LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBS1",
PARTITION "R_APR2022"  VALUES LESS THAN (TO_DATE(' 2022-05-01 00:00:00', '
SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBS1" ) ROW STORE COMPRESS ADVANCED; 16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35   36   37   38   39   40   41

Table created.

kIsH@STAX<>create sequence xid increment by 1 start with 1;

Sequence created.

kIsH@STAX<>INSERT INTO "KISH"."RP"
SELECT xid.nextval,
       dbms_random.string('a','10'),
           floor(dbms_random.value(1,1000000)),
           to_date(trunc(dbms_random.value(to_char(date '2022-01-01','J'),
                                           to_char(date '2022-04-30','J'))),'J')
from dual connect by level <= 1000;     2    3    4    5    6    7

1000 rows created.

kIsH@STAX<>commit;

Commit complete.

Check the segment size if needed for the compressed partitioned table

kIsH@STAX<>col SEGMENT_NAME for a20
kIsH@STAX<>SELECT segment_name,bytes/1048576 MB FROM dba_segments WHERE segment_name='RP';

SEGMENT_NAME                 MB
-------------------- ----------
RP                        .0625
RP                        .0625
RP                        .0625
RP                        .0625

Check the status of the newly created partition. Compression is enabled for all partitions.

kIsH@STAX<>col TABLE_NAME for a20
kIsH@STAX<>SELECT compression,compress_for,table_name from dba_tab_partitions WHERE table_name = 'RP';

COMPRESS COMPRESS_FOR                   TABLE_NAME
-------- ------------------------------ --------------------
ENABLED  ADVANCED                       RP
ENABLED  ADVANCED                       RP
ENABLED  ADVANCED                       RP
ENABLED  ADVANCED                       RP


kIsH@STAX<>set lines 200 pages 1000
kIsH@STAX<>col PARTITION_NAME for a20
kIsH@STAX<>SELECT compression,compress_for,table_name,partition_name from dba_tab_partitions WHERE table_name = 'RP';

COMPRESS COMPRESS_FOR                   TABLE_NAME           PARTITION_NAME
-------- ------------------------------ -------------------- --------------------
ENABLED  ADVANCED                       RP                   R_APR2022
ENABLED  ADVANCED                       RP                   R_MAR2022
ENABLED  ADVANCED                       RP                   R_JAN2022
ENABLED  ADVANCED                       RP                   R_FEB2022

Create a local index with basic compression on the partitioned table. Global index cannot be partitioned because it is global which is centralized to all the partitions.

kIsH@STAX<>CREATE INDEX idx_rp on RP(r_id) LOCAL COMPRESS;

Index created.

All the inherit partitioned indexes are compressed automatically with basic compression.

kIsH@STAX<>SELECT partition_name,index_name,compression FROM dba_ind_partitions WHERE index_name like '%IDX_RP%';

PARTITION_NAME       INDEX_NAME           COMPRESSION
-------------------- -------------------- -------------
R_APR2022            IDX_RP               ENABLED
R_FEB2022            IDX_RP               ENABLED
R_JAN2022            IDX_RP               ENABLED
R_MAR2022            IDX_RP               ENABLED

Add a new partition to the partitioned compressed table

kIsH@STAX<>ALTER TABLE rp ADD PARTITION "R_MAY2022" VALUES LESS THAN (TO_DATE(' 2022-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "TBS1";

Table altered.

Then check the new index which is added with basic compression automatically and marked in bold for new partition

kIsH@STAX<>SELECT dip.partition_name,dip.index_name,dip.compression,di.partitioned
  FROM dba_ind_partitions dip
  INNER JOIN dba_indexes di on dip.index_name = di.index_name
  WHERE dip.index_name = 'IDX_RP'
  ORDER BY index_name;  2    3    4    5

PARTITION_NAME       INDEX_NAME           COMPRESSION   PAR
-------------------- -------------------- ------------- ---
R_APR2022            IDX_RP               ENABLED       YES
R_FEB2022            IDX_RP               ENABLED       YES
R_JAN2022            IDX_RP               ENABLED       YES
R_MAR2022            IDX_RP               ENABLED       YES
R_MAY2022            IDX_RP               ENABLED       YES

So far, basic compression happens automatically for newly added partitioned index, when new partition is added to the table. Now, drop the index with basic compression and add index with advanced compression. If a new partition is added for partitioned table with advanced low compression, the respective index partition is not compressed automatically. It is disabled by default but the table partition is compressed automatically as mentioned earlier.

kIsH@STAX<>DROP INDEX idx_rp;

Index dropped.
kIsH@STAX<>ALTER TABLE rp ROW STORE COMPRESS ADVANCED;

Table altered.

kIsH@STAX<>ALTER TABLE rp MODIFY DEFAULT ATTRIBUTES ROW STORE COMPRESS ADVANCED;

Table altered.

kIsH@STAX<>SELECT def_compression,def_compress_for FROM all_part_tables WHERE table_name='RP';

DEF_COMP DEF_COMPRESS_FOR
-------- ------------------------------
ENABLED  ADVANCED

kIsH@STAX<>CREATE INDEX idx_rp on RP(r_id) LOCAL
(PARTITION R_JAN2022 COMPRESS ADVANCED LOW,
 PARTITION R_FEB2022 COMPRESS ADVANCED LOW,
 PARTITION R_MAR2022 COMPRESS ADVANCED LOW,
 PARTITION R_APR2022 COMPRESS ADVANCED LOW,
 PARTITION R_MAY2022 COMPRESS ADVANCED LOW
);  2    3    4    5    6    7

Index created.

kIsH@STAX<>ALTER TABLE rp ADD PARTITION "R_JUN2022" VALUES LESS THAN (TO_DATE(' 2022-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "TBS1";

Table altered.

kIsH@STAX<>set lines 200 pages 1000
kIsH@STAX<>col TABLE_NAME for a20
kIsH@STAX<>col PARTITION_NAME for a20
kIsH@STAX<>SELECT compression,compress_for,table_name,partition_name from dba_tab_partitions WHERE table_name = 'RP';

COMPRESS COMPRESS_FOR                   TABLE_NAME           PARTITION_NAME
-------- ------------------------------ -------------------- --------------------
ENABLED  ADVANCED                       RP                   R_MAY2022
ENABLED  ADVANCED                       RP                   R_JUN2022
ENABLED  ADVANCED                       RP                   R_APR2022
ENABLED  ADVANCED                       RP                   R_FEB2022
ENABLED  ADVANCED                       RP                   R_JAN2022
ENABLED  ADVANCED                       RP                   R_MAR2022

6 rows selected.

kIsH@STAX<>SELECT dip.partition_name,dip.index_name,dip.compression,di.partitioned
  FROM dba_ind_partitions dip
  INNER JOIN dba_indexes di on dip.index_name = di.index_name
  WHERE dip.index_name = 'IDX_RP'
  ORDER BY index_name;   2    3    4    5

PARTITION_NAME       INDEX_NAME           COMPRESSION   PAR
-------------------- -------------------- ------------- ---
R_JUN2022            IDX_RP               DISABLED      YES
R_MAY2022            IDX_RP               ADVANCED LOW  YES
R_APR2022            IDX_RP               ADVANCED LOW  YES
R_JAN2022            IDX_RP               ADVANCED LOW  YES
R_FEB2022            IDX_RP               ADVANCED LOW  YES
R_MAR2022            IDX_RP               ADVANCED LOW  YES

6 rows selected.

kIsH@STAX<>ALTER INDEX idx_rp REBUILD PARTITION R_JUN2022 COMPRESS ADVANCED LOW;

Index altered.

kIsH@STAX<>col INDEX_NAME for a20
kIsH@STAX<>SELECT dip.partition_name,dip.index_name,dip.compression,di.partitioned
  FROM dba_ind_partitions dip
  INNER JOIN dba_indexes di on dip.index_name = di.index_name
  WHERE dip.index_name = 'IDX_RP'
  ORDER BY index_name;  2    3    4    5

PARTITION_NAME       INDEX_NAME           COMPRESSION   PAR
-------------------- -------------------- ------------- ---
R_MAY2022            IDX_RP               ADVANCED LOW  YES
R_JUN2022            IDX_RP               ADVANCED LOW  YES
R_APR2022            IDX_RP               ADVANCED LOW  YES
R_JAN2022            IDX_RP               ADVANCED LOW  YES
R_FEB2022            IDX_RP               ADVANCED LOW  YES
R_MAR2022            IDX_RP               ADVANCED LOW  YES

6 rows selected.

If the index is GLOBAL, then there are no partitions created.

kIsH@STAX<>CREATE INDEX idx_rp on RP(r_id) GLOBAL COMPRESS;

Index created.

kIsH@STAX<>SELECT partition_name,index_name,compression FROM dba_ind_partitions WHERE index_name like '%IDX_RP%';

no rows selected

kIsH@STAX<>select index_name,partitioned from dba_indexes where index_name='IDX_RP';

INDEX_NAME           PAR
-------------------- ---
IDX_RP               NO

Summary:

  • Local indexes with basic compression are compressed automatically during new partition addition
  • Local indexes with Advanced compression are NOT compressed automatically during new partition addition. DBA need to manually compress with index rebuild.

Leave a Reply