Thanks for the question, Ibrahim.
Asked: September 02, 2025 - 12:47 pm UTC
Last updated: September 02, 2025 - 3:50 pm UTC
Version: 19.0
Viewed 1000+ times
You Asked
Hi tom,
I have a table with compression, also have partitions and subpartitions. You can see The create DDL on the below. As you can see all of my objects are compressed or nocompressed. Bu i can't see this information on the all_tables table. Compression ad compress_for is turning null. Why i can see the table is compressed on the DDL. What is the point on this issue.
CREATE TABLE EFSSALES1
(
sale_id NUMBER,
sale_date DATE,
region VARCHAR2(10),
amount NUMBER
)
COMPRESS BASIC
PARTITION BY RANGE (sale_date)
SUBPARTITION BY HASH (region)
(
PARTITION sales_2024 VALUES LESS THAN (TO_DATE('01-JAN-2025','DD-MON-YYYY'))
(
SUBPARTITION sp_east1 COMPRESS FOR OLTP,
SUBPARTITION sp_west1 NOCOMPRESS
),
PARTITION sales_2025 VALUES LESS THAN (TO_DATE('01-JAN-2026','DD-MON-YYYY'))
(
SUBPARTITION sp_east2 COMPRESS FOR OLTP,
SUBPARTITION sp_west2 COMPRESS FOR OLTP
)
);
SELECT compression, COMPRESS_FOR FROM all_tables WHERE table_name = 'EFSSALES1' AND owner='COPYCATLIVE'
and Chris said...
As this is set at the (sub)partition level, it makes little sense to give a compression state at the table level.
You can view this information in the *_tab_[sub]partitions views:
select partition_name, compression, compress_for
from user_tab_partitions
where table_name = 'EFSSALES1';
PARTITION_NAME COMPRESSION COMPRESS_FOR
SALES_2024 ENABLED BASIC
SALES_2025 ENABLED BASIC
select partition_name, subpartition_name, compression, compress_for
from user_tab_subpartitions
where table_name = 'EFSSALES1';
PARTITION_NAME SUBPARTITION_NAME COMPRESSION COMPRESS_FOR
SALES_2024 SP_EAST1 ENABLED ADVANCED
SALES_2024 SP_WEST1 DISABLED <null>
SALES_2025 SP_EAST2 ENABLED ADVANCED
SALES_2025 SP_WEST2 ENABLED ADVANCED