Re: Should one include partitioning key column as first column of non-unique local index
From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Wed, 17 Aug 2011 15:49:30 +0200
Message-ID: <9b1v7kF8ngU1_at_mid.individual.net>
On 17.08.2011 11:00, Robert Klemme wrote:
> On 17.08.2011 09:35, vsevolod afanassiev wrote:
>> OK, found Metalink Note 312843.1. The only way to compress partitioned
>> index is to drop and rebuild. Great.
>
> Maybe DBMS_REDEFINITION can help. It's certainly not an easy operation
> though.
> http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_redefi.htm#CBBJJAIF
>
>
> One way could be to copy the table (with partitions), create the
> compressed index on the copy and swap both tables with DBMS_REDEFINITION.
Date: Wed, 17 Aug 2011 15:49:30 +0200
Message-ID: <9b1v7kF8ngU1_at_mid.individual.net>
On 17.08.2011 11:00, Robert Klemme wrote:
> On 17.08.2011 09:35, vsevolod afanassiev wrote:
>> OK, found Metalink Note 312843.1. The only way to compress partitioned
>> index is to drop and rebuild. Great.
>
> Maybe DBMS_REDEFINITION can help. It's certainly not an easy operation
> though.
> http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_redefi.htm#CBBJJAIF
>
>
> One way could be to copy the table (with partitions), create the
> compressed index on the copy and swap both tables with DBMS_REDEFINITION.
Works like a charm: https://gist.github.com/1151539
I split up the script in two because I did not want to fiddle with permissions too much because DBMS_REDEFINITION needs quite a few of them:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#i1006801
So I simply switched to SYS for the redefinition.
Downside is of course that you need at least twice the space temporarily but at least downtime isn't too big with large tables.
Kind regards
robert
-- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/Received on Wed Aug 17 2011 - 08:49:30 CDT