Database Administration (MOSC)

MOSC Banner

changing key compression of a partitioned IOT

edited Feb 3, 2016 4:00AM in Database Administration (MOSC) 4 commentsAnswered

hi folks

We have a situation where a partitioned IOT of 4.5 billion rows has been created without a "compress 1 " clause in the table create statement on 11.2.0.1

As far as I can see, it is not possible to apply key compression to an IOT after creation.

in a discussion in 2012 an answer of "There never was an option to compress an index partition after the IOT was created." was given

I have done an "analyze.... validate structure" on a typically populated partition which returned

OPT_CMPR_COUNT  = 1

OPT_CMPR_PCTSAVE = 14

Although a space saving of 14% is significant, I'm not sure whether this 14% reflects only key columns - as in an ordinary index - or the total space including the non primary key content of the index.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center