Re: Index compression on Oracle 9.2

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Sun, 5 Feb 2006 23:40:41 +0100
Message-ID: <0b0801c62aa5$32340c00$3c02a8c0@JARAWIN>

Hi,

> I must say that the paper is excellent, ..

Indeed, as other papers from this source.

> .. especially the finding that ..

I encountered some additional "minor issues" connected with compression of tables and indexes; may be some one will find this interesting:

• the AVG_ROW_LEN is the length of the *uncompressed* row, so this value doesn't change after the compression.
• a little bit tricky is the compression on the composite (e.g. range / list) partitioned table if the intention is to insert the data uncompressed and to compress them later on.
• on a table with composite partition is not possible to direct compress the partition or subpartition with an alter table statement

ALTER TABLE t MOVE (sub)partition x compress; -- gives ora-14160 / ora-14257

• the workaround is to flag the whole table as compressed and move the subpartition without compress.

alter table t compress;

alter table t move subpartition x; -- this subpartition will be compressed

• the bottom line is that on a table with composite partitioning you may control the compression on the subpartition level (e.g. current part of the table is not compressed; history is compressed) but the COMPRESSION column in dba_tab_subpartition doesn't say anything about the actual status of the compression of the subpartition.
• until 10g (not sure exact what release; it works in 10r2) it is not possible to define a local compressed index on a composite partitioned table (ORA-08113)

Regards,

Jaromir D.B. Nemec
----- Original Message -----
From: "Mladen Gogala" <gogala_at_sbcglobal.net> To: <Christian.Antognini_at_trivadis.com>
Cc: <j.velikanovs_at_gmail.com>; "ORACLE-L" <oracle-l_at_freelists.org> Sent: Friday, February 03, 2006 2:14 PM
Subject: Re: Index compression on Oracle 9.2

On 02/03/2006 07:18:51 AM, Christian Antognini wrote:
> Jurijs
>
> >BTW: If some body have got results of that kind of testing, please do
> >not hesitate to share them with the list ;))))
>

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Feb 05 2006 - 16:40:41 CST