Re: Index compression on Oracle 9.2
- From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
- To: <gogala@xxxxxxxxxxxxx>, <Christian.Antognini@xxxxxxxxxxxx>
- Date: Sun, 5 Feb 2006 23:40:41 +0100
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@xxxxxxxxxxxxx>
To: <Christian.Antognini@xxxxxxxxxxxx>
Cc: <j.velikanovs@xxxxxxxxx>; "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
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 ;))))
--
//www.freelists.org/webpage/oracle-l
Other related posts: