how to compress subpartition on exadata, using 'for query high' and pctfree 10 options?
I used this statment, but I get only ORA-14160:this physical attribute may not be specified for a table subpartition.
alter table table_name move subpartition subpartition_name PCTFREE 10 compress for query high;
Edited by: user837363 on 2012-11-12 05:14
The document is on My Oracle Support
The short version is: you can't specify physical attributes like compression for an individual subpartition. You would need to do it for the entire partition (or even the entire table).
I just noticed that the enhancement request to permit subpartition-level physical storage modifications (https://support.oracle.com/epmos/faces/ui/km/BugDisplay.jspx?id=2210439) was updated two weeks ago to a status of "Suggestion Implemented". So we can probably expect this restriction to be removed in a future software version.
The compression part of the statement does in fact work - it's the PCTFREE part that doesn't.
I will submit a comment to the referenced MOS note, which says that it applies to up to 10.2.0.5 (correct) but still reports that the enhancement is being worked on (incorrect, as it has been implemented).