1

I am new to oracle database and using 19c version. I need to know if zstd algorithm can be implemented along with oracle advanced compression. I am able to implement zstd algorithm at RMAN level. Is there any way to choose the compression algorithm when using advanced compression in oracle? Thanks in advance!

RMAN COMMAND OUTPUT FOR ENABLING ZSTD:

RMAN> CONFIGURE COMPRESSION ALGORITHM 'ZSTD'
2> ;

new RMAN configuration parameters:
CONFIGURE COMPRESSION ALGORITHM 'ZSTD' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
new RMAN configuration parameters are successfully stored

RMAN> show COMPRESSION ALGORITHM
2> ;

RMAN configuration parameters for database with db_unique_name DB9ZX are:
CONFIGURE COMPRESSION ALGORITHM 'ZSTD' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;

EXPECTING FOR SYNTAX:

SQL<>alter table xtbl row store compress advanced;

Table altered.

SQL<>alter table xtbl row store compress advanced zstd;
alter table xtbl row store compress advanced zstd
                                             *
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option


SQL<>alter table xtbl row store compress zstd advanced;
alter table xtbl row store compress zstd advanced
                                    *
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
Kishan
  • 334
  • 2
  • 16

1 Answers1

1

Compression for RMAN files is significantly different from compression for live tables and rows. You can't choose a specific algorithm like ZSTD for table compression.

The Oracle Database Administrator's Guide has a section on table compression which covers 4 types of compression and when you might prefer each one.

When you use basic table compression, warehouse compression, or archive compression, compression only occurs when data is bulk loaded or array inserted into a table.

Advanced row compression is intended for OLTP applications and compresses data manipulated by any SQL operation.

Warehouse and Archive compression use Hybrid Columnar Compression (which requires additional licensing) - it dynamically chooses different (unspecified) compression algorithms based on the data type, etc. and is optimized for storage, not performance.

Oracle Database Concepts also has a section on table compression that goes into a little bit of detail about advanced row compression, which is optimized for OLTP performance. The relevant part for your question is that Oracle implemented their own simple compression algorithm (just replacing duplicate values with symbol table references). You can't configure your own compression algorithm.

kfinity
  • 8,581
  • 1
  • 13
  • 20
  • Apart from the documented algorithms for advanced compression, there is no option for custom settings for using third party algorithm in advanced compression. Am i right? – Kishan Mar 30 '22 at 15:50
  • Yes, I couldn't find anything like that in the documentation. – kfinity Mar 30 '22 at 15:56