This is just a short heads-up note to those dealing with HCC-enabled tables (so at present this applies only to Exadata customers).
As already outlined in a previous post about compression restrictions tables with HCC enabled do not support dropping columns - DROP COLUMN gets silently converted into SET UNUSED and DROP UNUSED COLUMNS throws an error to be unsupported.
I've recently come across an interesting variation of this restriction. Obviously Oracle treats virtual columns in this case the same: If you drop a virtual column of a HCC-enabled table it doesn't get dropped but is also silently turned into an unused column - which doesn't really make sense to me since dropping it doesn't require any physical modification to the underlying data structures.
Now you might wonder why this could be relevant? Well it can be important for several reasons:
1. All the unused columns whether virtual or not count towards the 1,000 column limit of a table - so frequently adding and dropping virtual columns is a no-brainer with non-HCC tables, but can become relevant with HCC enabled
2. Extended Statistics also use under the covers virtual columns. So if you create and drop extended statistics the same happens - the dropped virtual columns stay there. What is even more annoying - there is an upper limit of number of extensions per table. The limit itself is defined in a quite interesting way (greatest(20, 10% non-virtual columns)), but the problem here is that the dropped extensions count towards this limit, so you can easily end up with a situation where you cannot add any further extended statistics but you can't see any of them in the DBA/ALL/USER_STAT_EXTENSIONS dictionary view. What you can see however in DBA/ALL/USER_TAB_COLS are the remaining dropped virtual hidden columns
Since you can't drop unused columns on HCC-enabled tables there is no easy way around this apart from uncompressing the table/all table partitions, dropping the unused columns and re-compressing - nothing you usually want to/can do with HCC-compressed segments...
Note by the way that this nuisance doesn't affect exchange partition operations. Virtual columns are correctly handled in case of exchange partition operations - which means that only the physical column definitions need to be in sync between the two segments exchanged, but not any virtual columns. You can happily exchange partitions between tables with different number and types of virtual columns.
Subscribe to:
Post Comments (Atom)
Thank you, Randolf, for publishing this interesting bit of research!
ReplyDeleteHi Randolf,
ReplyDeleteInteresting post. Thanks for sharing. There's one other reason why marking columns as UNSED instead of actually dropping them can be problematic. Golden Gate apparently has some real problems replicating tables with UNSED columns. We have had two projects where this issue occurred. In both cases Golden Gate was being used to migrate from a 10G database to Exadata. I'm not really sure how the columns got marked as UNUSED as this seems like a feature that would very rarely be used. (In fact I had never come across it before the two recent issues.) This behavior is not unique to HCC by the way, it behaves the same way with non-HCC compression as well. Give it a try with good old fashion basic compression.
Kerry
Hi Kerry,
ReplyDeleteinteresting point with Golden Gate, didn't know that.
I'm not sure what you mean by "behaves the same way with non-HCC compression", because I couldn't reproduce the problem with virtual columns and basic compression.
With basic compression I can't drop regular columns at all: It raises an error message rather than doing a SET UNUSED under the covers like HCC does.
But I can successfully add and drop virtual columns without having them silently remaining as UNUSED columns... So this seems to be unique to HCC and shows that Oracle is able to handle dropping virtual columns correctly in the case of basic compression.
Randolf
I should have pointed out that the previous comment refers to 11.2.0.1 behaviour, I haven't tested it yet on 11.2.0.2
ReplyDeleteRandolf
Hi Randolf,
ReplyDeleteI sent some system output to you via email from an 11.2.0.2 system.
Kerry
Hi Kerry,
ReplyDeletethanks for sending the script output. I see now where the differences come from - you were saying "basic compression" but actually seemed to have meant "OLTP compression" (which requires the Advanced Compression option by the way).
And yes, you're absolutely right: OLTP compression behaves exactly the same way, and in particular also when dealing with virtual columns / extended statistics.
So thanks for pointing this out - it's something I should add to this post clarifying how OLTP compression actually manages to "drop" columns...
Randolf