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 .0625Check 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 ENABLEDAdd 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.