Thanks for the question, Alan.

Asked: October 17, 2023 - 5:50 pm UTC

Last updated: November 06, 2023 - 12:01 am UTC

Version: Oracle 19.18

Viewed 10K+ times! This question is

You Asked

Hi,

We have several multi-billion row tables that need to be partitioned. We also want to try some other things to reduce I/O. Specifically:

- Partition by hash
- Purge old records
- Advanced compression on table and indexes
- Cluster by primary key.

We can get everything to work except for compressing the indexes. Can this be done in the ALTER TABLE statement or does it require separate steps? The process will take several hours per table, on a live production system with ongoing transactions. We would like to be able to do it all in one complete online operation per table.

Here is a simple version of what we are trying:

First, create a single segment table:

CREATE TABLE big AS SELECT * FROM sys.dba_objects;

CREATE INDEX big_idx1 ON big (owner, object_name);

EXECUTE DBMS_STATS.gather_table_stats('', 'BIG');

Second, Set attributes for clustering and compression:

ALTER TABLE big ADD CLUSTERING BY LINEAR ORDER (owner);

ALTER TABLE big ROW STORE COMPRESS ADVANCED;

Third, create the new version of the table:

ALTER TABLE big
      MODIFY
      PARTITION BY HASH (owner) PARTITIONS 8
      INCLUDING ROWS WHERE last_ddl_time >= SYSDATE - (4*365)
      ONLINE
      UPDATE INDEXES (big_idx1 GLOBAL PARTITION BY HASH (owner, object_name) PARTITIONS 8);

The table is partitioned and compressed as expected:

col partition_name format a15
col compression format a12
col compress_for format a12

SELECT partition_name, compression, compress_for
  FROM user_tab_partitions
 WHERE table_name = 'BIG';

PARTITION_NA COMPRESSION  COMPRESS_FOR
------------ ------------ ------------
SYS_P12500   ENABLED      ADVANCED    
SYS_P12501   ENABLED      ADVANCED    
SYS_P12502   ENABLED      ADVANCED    
SYS_P12503   ENABLED      ADVANCED    
SYS_P12504   ENABLED      ADVANCED    
SYS_P12505   ENABLED      ADVANCED    
SYS_P12506   ENABLED      ADVANCED    
SYS_P12507   ENABLED      ADVANCED    

8 rows selected.

The index is partitioned but NOT compressed:

col partition_name format a15
col compression format a12

SELECT partition_name, compression
  FROM user_ind_partitions
 WHERE index_name = 'BIG_IDX1';

PARTITION_NA COMPRESSION 
------------ ------------
SYS_P12508   DISABLED    
SYS_P12509   DISABLED    
SYS_P12510   DISABLED    
SYS_P12511   DISABLED    
SYS_P12512   DISABLED    
SYS_P12513   DISABLED    
SYS_P12514   DISABLED    
SYS_P12515   DISABLED    

8 rows selected.

We have tried everything we could think of to put the COMPRESS ADVANCED HIGH setting into the ALTER TABLE ... UPDATE INDEXES clause but nothing seemed to work. How do we get the indexes to also be compressed and ideally in the same ONLINE ALTER TABLE statement?

Thanks,
Alan

and Connor said...

Yeah that doesn't seem right - I'll ask around internally and report back if I get some better info than below.

Addenda: From the docs:

This clause cannot change the uniqueness property of the index or any other index property.

https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/evolve-nopartition-table.html

Here's a (hacky) workaround - we'll create the compressed index in advance to preserve the compression. Afterwards, drop the uncompressed one


SQL> create table big as select * from sys.dba_objects;

Table created.

SQL> create index big_idx1 on big (owner, object_name);

Index created.

SQL> execute dbms_stats.gather_table_stats('', 'BIG');

PL/SQL procedure successfully completed.

SQL> alter table big add clustering by linear order (owner);

Table altered.

SQL> alter table big row store compress advanced;

Table altered.

SQL> create index tmp_ix on big  (owner, object_name, null) compress 2;

Index created.

SQL> alter table big
  2    modify
  3    partition by hash (owner) partitions 8
  4    including rows where last_ddl_time >= sysdate - (4*365)
  5    online
  6    update indexes (
  7      big_idx1 global partition by hash (owner, object_name) partitions 8 ,
  8      tmp_ix global partition by hash (owner, object_name) partitions 8
  9    );

Table altered.

SQL>
SQL>  col partition_name format a15
SQL>  col compression format a12
SQL>  col compress_for format a12
SQL>
SQL>  select partition_name, compression, compress_for
  2        from user_tab_partitions
  3       where table_name = 'BIG';

PARTITION_NAME  COMPRESSION  COMPRESS_FOR
--------------- ------------ ------------
SYS_P13826      ENABLED      ADVANCED
SYS_P13827      ENABLED      ADVANCED
SYS_P13828      ENABLED      ADVANCED
SYS_P13829      ENABLED      ADVANCED
SYS_P13830      ENABLED      ADVANCED
SYS_P13831      ENABLED      ADVANCED
SYS_P13832      ENABLED      ADVANCED
SYS_P13833      ENABLED      ADVANCED

8 rows selected.

SQL>
SQL>  col partition_name format a15
SQL>  col compression format a12
SQL>
SQL>  select partition_name, compression
  2        from user_ind_partitions
  3       where index_name in ('BIG_IDX1','TMP_IX');

PARTITION_NAME  COMPRESSION
--------------- ------------
SYS_P13834      DISABLED
SYS_P13835      DISABLED
SYS_P13836      DISABLED
SYS_P13837      DISABLED
SYS_P13838      DISABLED
SYS_P13839      DISABLED
SYS_P13840      DISABLED
SYS_P13841      DISABLED
SYS_P13842      ENABLED
SYS_P13843      ENABLED
SYS_P13844      ENABLED
SYS_P13845      ENABLED
SYS_P13846      ENABLED
SYS_P13847      ENABLED
SYS_P13848      ENABLED
SYS_P13849      ENABLED

16 rows selected.

SQL> drop index big_ix;

Index dropped.
SQL>
SQL>
SQL>

Addenda: But also see Rajesh's option in the comments for another nifty workaround

Rating

(10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

another option using "db_index_compression_inheritance" parameter

Rajeshwaran Jeyabal, October 30, 2023 - 1:02 pm UTC

using alter table modify command - we have an option to specify tablespace for index in "update indexes" clause.

so you create an tablespace with "default index compress advanced" and then set the db_index_compression_inheritance to tablespace at session level and perform "alter table modify" command - the index will compressed automatically, by inheriting compression attributes from tablespace level.

https://asktom.oracle.com/pls/apex/asktom.search?tag=advanced-index-compression-high-in-122
https://connor-mcdonald.com/2020/10/02/compression-default-settings/

demo@PDB1> create tablespace ts_demo
  2  datafile 'C:\ORACLE\ORA21C\ORADATA\ORCL\PDB1\ts_demo_01.DBF'
  3  size 10m
  4  autoextend on next 100m
  5  maxsize 1G
  6  default index compress advanced high;

Tablespace created.

demo@PDB1> create table t as select * from all_objects;

Table created.

demo@PDB1> create index t_idx on t(object_id);

Index created.

demo@PDB1> select tablespace_name,compression
  2  from user_indexes
  3  where index_name ='T_IDX';

TABLESPACE_NAME                COMPRESSION
------------------------------ -------------
TS_DATA                        DISABLED

demo@PDB1>
demo@PDB1> alter session set db_index_compression_inheritance = tablespace;

Session altered.

demo@PDB1> alter table t
  2  modify partition by range( created )
  3  interval ( numtoyminterval(1,'month') )
  4  ( partition pJan2023 values less than
  5     (to_date('01-feb-2023','dd-mon-yyyy')) )
  6  update indexes ( t_idx global tablespace ts_demo );

Table altered.

demo@PDB1> select tablespace_name,compression
  2  from user_indexes
  3  where index_name ='T_IDX';

TABLESPACE_NAME                COMPRESSION
------------------------------ -------------
TS_DEMO                        ADVANCED HIGH

demo@PDB1>

What about DBMS_REDEFINITION ?

Alan Rosenthal, October 30, 2023 - 5:42 pm UTC

Hi Connor,

Thanks for the hacky workaround. I was feeling dumb for not being able to find the answer myself. Guess it's not so obvious :)

The other alternative I was pondering was to use DBMS_REDEFINITION. I haven't used this in very long time but it seems like it should work given that I just have to be able to create the target table and indexes in the old-fashioned way?

What do you think of that? Do you think it will work? Will it perform well in ONLINE mode on a live system (at night on the weekend of course)? I believe DBMS_REDEFINITION has that little lock time when it is flipping the original and the interim around. Will that still work ok if the system is still using the table?

Thanks again!
Alan

November 02, 2023 - 4:53 am UTC

archival is an issue with dbms_redef

about DBMS_REDEFINITION.

Rajeshwaran Jeyabal, October 31, 2023 - 7:34 am UTC

yes dbms_redefinition will work.

but why do you think to go that route? since you are in 19c(19.18) why dont you take the above route (new feature introduced in 12.2) - a single DDL statement Vs multiple pl/sql calls

the above ddl can be executed in online mode as well. no downtime.
so write less code and enjoy the new features.

ALTER MODIFY vs DBMS_REDEFINITION ?

Alan Rosenthal, October 31, 2023 - 5:26 pm UTC

Hi Rajeshwaran ,

Thanks for the idea! I like the idea of a single ALTER MODIFY statement to do all the work. On the other hand the DBMS_REDEFINITION seems to give us great flexibility since all we have to do is create the target table, in the usual way, with any characteristics that we want.

What I don't know is:
1) is one method faste than the other?
2) is the flip from the old table to the new table better/faster/less-disruptive with one method compared to the other?

Thanks again,
Alan

DBMS_REDEFINITION Doesn't Delete Rows ?

Alan Rosenthal, October 31, 2023 - 11:09 pm UTC

Hi,

I think I just hit a rock and a hard place - while partitioning, compressing, and clustering this table - I also need to purge old rows. Unfortunately, it looks like DBMS_REDEFINITION doesn't have a delete rows option or including rows option like ALTER TABLE MODIFY does.

If that is correct then it is a show stopper for me using DBMS_REDEFINITION for this process. Doing the compressed index tablespace idea that Rajeshwaran suggested sudden looks Golden.

Alan

DBMS_REDEFINITION Doesn't Delete Rows

Rajeshwaran Jeyabal, November 01, 2023 - 2:09 am UTC

yes that is correct, using dbms_redefinition, we dont have an option to purge the old records - all we can do is copy the entire set from Original table to Interim table.

My solution

Alan Rosenthal, November 02, 2023 - 10:11 pm UTC

THANKS !!! Connor and Rajeshwaran,

So after much playing around, this is the solution I finally came up with FWIW:

Create a tablespace with the index compress attribute.

CREATE TABLESPACE compressed_index_ts
    DATAFILE '/u01/oradata/compressed_index_ts_1.dbf' SIZE 64M AUTOEXTEND ON NEXT 64M MAXSIZE 31G , 
             '/u02/oradata/compressed_index_ts_2.dbf' SIZE 64M AUTOEXTEND ON NEXT 64M MAXSIZE 31G
    DEFAULT INDEX COMPRESS ADVANCED HIGH;

Create a regular table with 3 indexes:

CREATE TABLE big
AS
    SELECT *
      FROM sys.dba_objects
     WHERE object_id IS NOT NULL;

CREATE UNIQUE INDEX big_pk ON big(object_id);

ALTER TABLE big ADD CONSTRAINT big_pk PRIMARY KEY(object_id) USING INDEX big_pk;

CREATE INDEX big_idx1 ON big(object_type);

CREATE INDEX big_idx2 ON big(owner, object_name);

EXECUTE dbms_stats.gather_table_stats('', 'BIG');

Set attributes for the index rebuild:

ALTER TABLE big ADD CLUSTERING BY LINEAR ORDER (owner, object_name);
ALTER TABLE big ROW STORE COMPRESS ADVANCED;

ALTER SESSION SET db_index_compression_inheritance =  tablespace;

Move the non-partitioned indexes into the compressed tablespace:
<code>ALTER INDEX big_pk REBUILD TABLESPACE compressed_index_ts ONLINE NOLOGGING PARALLEL 8;
ALTER INDEX big_pk LOGGING PARALLEL 1;

ALTER INDEX big_idx1 REBUILD TABLESPACE compressed_index_ts ONLINE PARALLEL 8;
ALTER INDEX big_idx1 LOGGING PARALLEL 1;

ALTER INDEX big_idx2 REBUILD TABLESPACE compressed_index_ts ONLINE PARALLEL 8;
ALTER INDEX big_idx2 LOGGING PARALLEL 1;

Partition the tablespace and the indexes

ALTER TABLE big
    MODIFY
        PARTITION BY HASH(owner) PARTITIONS 8
        INCLUDING ROWS WHERE SUBSTR(object_name, 1, 1) NOT LIKE 'G%'
        ONLINE
        UPDATE INDEXES(big_pk GLOBAL PARTITION BY HASH(object_id)            PARTITIONS 8,
                       big_idx1 GLOBAL PARTITION BY HASH(object_type)        PARTITIONS 8,
                       big_idx2 GLOBAL PARTITION BY HASH(owner, object_name) PARTITIONS 8);

And the indexes are all partitioned, compressed, and in their new tablespace:

SET PAGESIZE 100
COL index_Name FORMAT a10
COL tablepace_name FORMAT a20
COL compression FORMAT a20

  SELECT index_name, tablespace_name, compression
    FROM user_ind_partitions
   WHERE index_name LIKE 'BIG%'
ORDER BY index_name;

INDEX_NAME TABLESPACE_NAME                COMPRESSION         
---------- ------------------------------ --------------------
BIG_IDX1   COMPRESSED_INDEX_TS            ADVANCED HIGH       
BIG_IDX1   COMPRESSED_INDEX_TS            ADVANCED HIGH       
BIG_IDX1   COMPRESSED_INDEX_TS            ADVANCED HIGH       
BIG_IDX1   COMPRESSED_INDEX_TS            ADVANCED HIGH       
BIG_IDX1   COMPRESSED_INDEX_TS            ADVANCED HIGH       
BIG_IDX1   COMPRESSED_INDEX_TS            ADVANCED HIGH       
BIG_IDX1   COMPRESSED_INDEX_TS            ADVANCED HIGH       
BIG_IDX1   COMPRESSED_INDEX_TS            ADVANCED HIGH       
BIG_IDX2   COMPRESSED_INDEX_TS            ADVANCED HIGH       
BIG_IDX2   COMPRESSED_INDEX_TS            ADVANCED HIGH       
BIG_IDX2   COMPRESSED_INDEX_TS            ADVANCED HIGH       
BIG_IDX2   COMPRESSED_INDEX_TS            ADVANCED HIGH       
BIG_IDX2   COMPRESSED_INDEX_TS            ADVANCED HIGH       
BIG_IDX2   COMPRESSED_INDEX_TS            ADVANCED HIGH       
BIG_IDX2   COMPRESSED_INDEX_TS            ADVANCED HIGH       
BIG_IDX2   COMPRESSED_INDEX_TS            ADVANCED HIGH       
BIG_PK     COMPRESSED_INDEX_TS            ADVANCED HIGH       
BIG_PK     COMPRESSED_INDEX_TS            ADVANCED HIGH       
BIG_PK     COMPRESSED_INDEX_TS            ADVANCED HIGH       
BIG_PK     COMPRESSED_INDEX_TS            ADVANCED HIGH       
BIG_PK     COMPRESSED_INDEX_TS            ADVANCED HIGH       
BIG_PK     COMPRESSED_INDEX_TS            ADVANCED HIGH       
BIG_PK     COMPRESSED_INDEX_TS            ADVANCED HIGH       
BIG_PK     COMPRESSED_INDEX_TS            ADVANCED HIGH       

24 rows selected.

November 03, 2023 - 4:47 pm UTC

Glad we could help

reg :: my solution.

Rajeshwaran Jeyabal, November 04, 2023 - 1:01 pm UTC

BTW : once you have this alter session in place

ALTER SESSION SET db_index_compression_inheritance =  tablespace;

you dont need all these stuffs

ALTER INDEX big_pk REBUILD TABLESPACE compressed_index_ts ONLINE NOLOGGING PARALLEL 8;
ALTER INDEX big_pk LOGGING PARALLEL 1;

ALTER INDEX big_idx1 REBUILD TABLESPACE compressed_index_ts ONLINE PARALLEL 8;
ALTER INDEX big_idx1 LOGGING PARALLEL 1;

ALTER INDEX big_idx2 REBUILD TABLESPACE compressed_index_ts ONLINE PARALLEL 8;
ALTER INDEX big_idx2 LOGGING PARALLEL 1;

instead add the tablespace to update indexes clause, like this

alter table big modify 
partition by hash(owner) partitions 8
including rows where object_name not like 'g%'
online
update indexes(big_pk global partition by hash(object_id)  partitions 8 tablespace compressed_index_ts,
      big_idx1 global partition by hash(object_type) partitions 8 tablespace compressed_index_ts,
      big_idx2 global partition by hash(owner, object_name) partitions 8 tablespace compressed_index_ts);

that should work.

demo@PDB1> create table t as select * from all_objects;

Table created.

demo@PDB1> alter table t
  2  add constraint t_idx1
  3  primary key(object_id);

Table altered.

demo@PDB1> create index t_idx2 on t(owner,object_type);

Index created.

demo@PDB1>
demo@PDB1> select index_name,tablespace_name,compression
  2  from user_indexes
  3  where index_name like 'T_IDX%'
  4  and table_name ='T';

INDEX_NAME      TABLESPACE_NAME COMPRESSION
--------------- --------------- -------------
T_IDX1          TS_DATA         DISABLED
T_IDX2          TS_DATA         DISABLED

demo@PDB1> alter session set db_index_compression_inheritance = tablespace;

Session altered.

demo@PDB1>
demo@PDB1> alter table t modify
  2  partition by hash(owner) partitions 8
  3  update indexes(t_idx1 global partition by hash(object_id)  partitions 8 tablespace ts_demo ,
  4     t_idx2 global tablespace ts_demo );

Table altered.

demo@PDB1> select partition_name,tablespace_name,compression
  2  from user_ind_partitions
  3  where index_name ='T_IDX1';

PARTITION_NAME  TABLESPACE_NAME COMPRESSION
--------------- --------------- -------------
SYS_P12223      TS_DEMO         ADVANCED HIGH
SYS_P12224      TS_DEMO         ADVANCED HIGH
SYS_P12225      TS_DEMO         ADVANCED HIGH
SYS_P12226      TS_DEMO         ADVANCED HIGH
SYS_P12227      TS_DEMO         ADVANCED HIGH
SYS_P12228      TS_DEMO         ADVANCED HIGH
SYS_P12229      TS_DEMO         ADVANCED HIGH
SYS_P12230      TS_DEMO         ADVANCED HIGH

8 rows selected.

demo@PDB1>
demo@PDB1> select tablespace_name,compression
  2  from user_indexes
  3  where index_name = 'T_IDX2'
  4  and table_name ='T';

TABLESPACE_NAME COMPRESSION
--------------- -------------
TS_DEMO         ADVANCED HIGH

demo@PDB1>

November 06, 2023 - 12:01 am UTC

nice input

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

another option using "db_index_compression_inheritance" parameter

What about DBMS_REDEFINITION ?

about DBMS_REDEFINITION.

ALTER MODIFY vs DBMS_REDEFINITION ?

DBMS_REDEFINITION Doesn't Delete Rows ?

DBMS_REDEFINITION Doesn't Delete Rows

My solution

reg :: my solution.

More to Explore

VLDB

VLDB

Altering a table to be partitioned and compressed

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

another option using "db_index_compression_inheritance" parameter

What about DBMS_REDEFINITION ?

about DBMS_REDEFINITION.

ALTER MODIFY vs DBMS_REDEFINITION ?

DBMS_REDEFINITION Doesn't Delete Rows ?

DBMS_REDEFINITION Doesn't Delete Rows

My solution

reg :: my solution.

More to Explore

VLDB

VLDB