Heute wurde im
OTN Forum die Frage gestellt: woran kann man erkennen, ob die Inhalte einer Partition komprimiert sind? Ich denke, das ist eine ziemlich interessante Frage, und eine, auf die
Jonathan Lewis in einem Kommentar zu einem seiner Artikel zum Thema
compression auf der AllThingsOracle-Seite vor kurzem eine kompakte Antwort gegeben hat:
Q: Is there a way to tell if the data is actually compressed?
A: In effect, no.
You might be able to make a reasonable guess - check the average rows per block (num_rows / blocks), then cross-check with the avg_row_len (which reports the uncompressed row length, even when the rows are compressed). You could also use dbms_rowid to convert rowids to file and block numbers and check the number of rows per block individually - because you might have a table where half the data was compressed and half wasn't.
In principle there is a method, mentioned originally in the 9.2 manuals I think. Column spare1 of table sys.seg$ has two bits set to show whether or not a segment is compressed and whether it holds any compressed data, so you could query seg$ (joined to various other data dictionary tables) to check. The tests are: bitand(spare1, 2048) = 2048 to check if the segment is defined with compression, and bitand(spare1, 4096) = 4096 to check if any of the data is compressed - unfortunately when I did a quick check of bit 4096 in a recent version of Oracle I found that it got set as soon as bit 2048 got set. Even it it worked, of course, all you could learn from a single bit was that some of the data in the table was compressed, you'd still have no idea about how much was compressed.
Auf Anhieb könnte man meinen, dass der Fall viel einfacher läge, denn schließlich enthält dba_tab_partitions eine Spalte COMPRESSION (und eine weiter Spalte COMPRESS_FOR). Aber darin steht nur der für folgende Operationen gültige Status: zum aktuellen Zustand gibt es keine Aussage. Dazu ein Beispiel:
drop table test_part_compress;
create table test_part_compress (
startdate date
, col1 number
, col2 varchar2(100)
)
partition by range (startdate) (
partition p1 values less than (to_date('01.01.2013','dd.mm.yyyy'))
, partition p2 values less than (to_date('01.02.2013','dd.mm.yyyy'))
) tablespace test_ts;
insert into test_part_compress
select to_date('31.12.2012', 'dd.mm.yyyy') + mod(rownum, 2) startdate
, mod(rownum, 10) col1
, lpad('*', 100, '*') col2
from dual
connect by level <= 10000;
exec dbms_stats.gather_table_stats(user, 'test_part_compress')
select partition_name
, num_rows
, blocks
, compression
, compress_for
from dba_tab_partitions
where table_name = 'TEST_PART_COMPRESS';
PARTITION_NAME NUM_ROWS BLOCKS COMPRESS COMPRESS_FOR
------------------------------ ---------- ---------- -------- ------------
P1 5000 84 DISABLED
P2 5000 84 DISABLED
alter table test_part_compress modify partition p1 compress;
exec dbms_stats.gather_table_stats(user, 'test_part_compress')
select partition_name
, num_rows
, blocks
, compression
, compress_for
from dba_tab_partitions
where table_name = 'TEST_PART_COMPRESS';
PARTITION_NAME NUM_ROWS BLOCKS COMPRESS COMPRESS_FOR
------------------------------ ---------- ---------- -------- ------------
P1 5000 84 ENABLED BASIC
P2 5000 84 DISABLED
alter table test_part_compress move partition p1;
exec dbms_stats.gather_table_stats(user, 'test_part_compress')
select partition_name
, num_rows
, blocks
, compression
, compress_for
from dba_tab_partitions
where table_name = 'TEST_PART_COMPRESS';
PARTITION_NAME NUM_ROWS BLOCKS COMPRESS COMPRESS_FOR
------------------------------ ---------- ---------- -------- ------------
P1 5000 8 ENABLED BASIC
P2 5000 84 DISABLED
alter table test_part_compress modify partition p1 nocompress;
select partition_name
, num_rows
, blocks
, compression
, compress_for
from dba_tab_partitions
where table_name = 'TEST_PART_COMPRESS';
PARTITION_NAME NUM_ROWS BLOCKS COMPRESS COMPRESS_FOR
------------------------------ ---------- ---------- -------- ------------
P1 5000 8 DISABLED
P2 5000 84 DISABLED
Die Ergebnisse zeigen, dass die Angabe der COMPRESS oder NOCOMPRESS-Eigenschaft zunächst keine direkte Wirkung auf das vorliegende Segment hat: erst ein folgendes MOVE führt zur physikalischen Reorganisation. Das bedeutet dann auch, dass man im Fall der Partition P1 am Ende des Tests nicht ohne Weiteres bestimmen kann, ob ihr Inhalt komprimiert ist. Zur Bestimmung bleiben also die Vorschläge des Herrn Lewis. Dabei lasse ich die Prüfungen zur Größe außen vor, denn im Test ist der Unterschied zwischen 8 Blocks und 84 Blocks ausreichend deutlich:
select partition_name
, header_file
, header_block
from dba_segments
where segment_name = 'TEST_PART_COMPRESS';
PARTITION_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
P1 5 147272
P2 5 147104
select block#
, blocks
, bitand(spare1, 2048) compression_defined
, bitand(spare1, 4096) compressed
from sys.seg$ t
where file# = 5
and BLOCK# in (147104, 147272)
BLOCK# BLOCKS COMPRESSION_DEFINED COMPRESSED
------ ---------- ------------------- ----------
147104 88 0 0
147272 16 0 4096
Das scheint also exakt die gewünschte Information zu liefern. Eine andere Variante zur Bestimmung nennt Randolf Geist in seinem Beitrag zum OTN-Thread: die Verwendung der Funktion dbms_compression.get_compression_type:
select subobject_name
, data_object_id
from dba_objects
where object_name = 'TEST_PART_COMPRESS'
and subobject_name is not null;
SUBOBJECT_NAME DATA_OBJECT_ID
------------------------------ --------------
P1 101978
P2 101977
select dbms_rowid.rowid_relative_fno(rowid) file_nr
, dbms_compression.get_compression_type(user, 'TEST_PART_COMPRESS', rowid) compression_type
, count(*) cnt
from test_part_compress
group by dbms_rowid.rowid_relative_fno(rowid)
, dbms_compression.get_compression_type(user, 'TEST_PART_COMPRESS', rowid);
OBJECT COMPRESSION_TYPE CNT
------ ---------------- ----------
101978 2 5000
101977 1 5000
Zumindest für den aktuell erreichten Zustand einer komprimierten Partition, deren Status aktuell NOCOMPRESS ist, sind die Tests demnach aussagekräftig: Für P1 wird der COMPRESSION_TYPE = 2 angegeben, der anscheinend für Basic und OLTP compression steht (sagt jedenfalls
Kerry Osborne). Der Vollständigkeit halber hier noch ein paar weitere Schritte in die eingeschlagene Richtung:
-- move nach NOCOMPRESS-Definition
alter table test_part_compress move partition p1;
exec dbms_stats.gather_table_stats(user, 'test_part_compress')
PARTITION_NAME NUM_ROWS BLOCKS COMPRESS COMPRESS_FOR
------------------------------ ---------- ---------- -------- ------------
P1 5000 74 DISABLED
P2 5000 84 DISABLED
-- beide Partitionen liefern jetzt COMPRESSION_TYPE = 1 (keine compression)
OBJECT COMPRESSION_TYPE CNT
------ ---------------- ----------
101979 1 5000
101977 1 5000
BLOCK# BLOCKS COMPRESSION_DEFINED COMPRESSED
------ ---------- ------------------- ----------
147096 80 0 0
147104 88 0 0
-- erneute COMPRESS-Definition
alter table test_part_compress modify partition p1 compress;
exec dbms_stats.gather_table_stats(user, 'test_part_compress')
PARTITION_NAME NUM_ROWS BLOCKS COMPRESS COMPRESS_FOR
------------------------------ ---------- ---------- -------- ------------
P1 5000 74 ENABLED BASIC
P2 5000 84 DISABLED
-- der COMPRESSION_TYPE bleibt 1, was plausibel ist, da die Daten ja tatsächlich nicht komprimiert sind
OBJECT COMPRESSION_TYPE CNT
------ ---------------- ----------
101979 1 5000
101977 1 5000
-- aber seg$ behauptet, dass die Partition jetzt als COMPRESSed definiert ist und ihre Sätze komprimiert sind,
-- was nicht den Tatsachen entspricht
BLOCK# BLOCKS COMPRESSION_DEFINED COMPRESSED
------ ---------- ------------------- ----------
147096 80 2048 4096
147104 88 0 0
Auf Anhieb scheint dbms_compression.get_compression_type im gegebenen Fall also die brauchbarere Information zu liefern - und immerhin gibt es überhaupt Möglichkeiten, die unzureichende Aussage aus dba_tab_partitions zu ergänzen.
