Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Rebuild index partition with different compression number
465 views
Skip to first unread message
gonzo
Aug 7, 2012, 8:52:30 AM8/7/12
to
Hi, I am working with Oracle 10gR2.
I have a list partitioned table on column "a" and a locally partitioned index with compression enabled:
My_Table (a,b,c,d,.....) Partitions P1 and P2 on column "a" values.
My_Index (b,c,d) non unique, compress 3, partitioned on "a".
According to Index_stats view, the optimal compression would be 1, so I tried to rebuild my index with different compression executing:
alter index My_Index rebuild partition P1 compress 1;
*
ERROR at line 1:
ORA-14010: this physical attribute may not be specified for an index partition
Does anyone have a clue of why I am getting this error?
According to the documentation the above statement should be valid:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_1008.htm#i2050158
I have a list partitioned table on column "a" and a locally partitioned index with compression enabled:
My_Table (a,b,c,d,.....) Partitions P1 and P2 on column "a" values.
My_Index (b,c,d) non unique, compress 3, partitioned on "a".
According to Index_stats view, the optimal compression would be 1, so I tried to rebuild my index with different compression executing:
alter index My_Index rebuild partition P1 compress 1;
*
ERROR at line 1:
ORA-14010: this physical attribute may not be specified for an index partition
Does anyone have a clue of why I am getting this error?
According to the documentation the above statement should be valid:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_1008.htm#i2050158
ddf
Aug 7, 2012, 9:42:31 AM8/7/12
to
"Oracle Database compresses only nonpartitioned indexes that are nonunique or unique indexes of at least two columns."
That is quoted from the link you posted.
David Fitzjarrell
gonzo
Aug 7, 2012, 11:05:46 AM8/7/12
to
Thanks David, I skip that line you highlight.
The partitioned index was originally created using the "COMPRESS" clause, and since it is a non-unique index it is like issuing "COMPRESS 3". No errors were raised at that time.
When I check the status of the index:
select PARTITION_NAME, STATUS, COMPRESSION from user_ind_partitions where index_name='MY_INDEX';
PARTITION_NAME STATUS COMPRESS
------------------------------ -------- --------
P_1 USABLE ENABLED
P_2 USABLE ENABLED
So it says the each index partition has compression enabled.
Is Oracle saying that compression is enabled although nothing is compressed?
Looking at the like "wire diagram" for "alter index rebuild partition" it shows that "compress <integer>" can be used, so I am surprised of having this error.
The partitioned index was originally created using the "COMPRESS" clause, and since it is a non-unique index it is like issuing "COMPRESS 3". No errors were raised at that time.
When I check the status of the index:
select PARTITION_NAME, STATUS, COMPRESSION from user_ind_partitions where index_name='MY_INDEX';
PARTITION_NAME STATUS COMPRESS
------------------------------ -------- --------
P_1 USABLE ENABLED
P_2 USABLE ENABLED
So it says the each index partition has compression enabled.
Is Oracle saying that compression is enabled although nothing is compressed?
Looking at the like "wire diagram" for "alter index rebuild partition" it shows that "compress <integer>" can be used, so I am surprised of having this error.
Jesper Wolf Jespersen
Aug 7, 2012, 11:13:26 AM8/7/12
to
Hello Gonzo.
I dont think the problem is compression but that you are trying to
change just one partition of the index.
I do think you need to rebuild the entire index to change compression.
Just my two cents worth :-)
Greetings from Denmark
Jesper Wolf Jespersen
I dont think the problem is compression but that you are trying to
change just one partition of the index.
I do think you need to rebuild the entire index to change compression.
Just my two cents worth :-)
Greetings from Denmark
Jesper Wolf Jespersen
gonzo
Aug 7, 2012, 11:25:33 AM8/7/12
to
hi Jesper.
Yes, It looks like I need to recreate the index, and this is really a bummer....
If Oracle supports a nice command like:
> analyze index MY_INDEX partition (P1) validate structure;
which populates the INDEX_STATS saying that OPT_CMPR_COUNT is 1.
I would expect that you can to rebuild that partition using the right compression, specially if this looks supported in the "ALTER INDEX wire diagram"....
Yes, It looks like I need to recreate the index, and this is really a bummer....
If Oracle supports a nice command like:
> analyze index MY_INDEX partition (P1) validate structure;
which populates the INDEX_STATS saying that OPT_CMPR_COUNT is 1.
I would expect that you can to rebuild that partition using the right compression, specially if this looks supported in the "ALTER INDEX wire diagram"....
0 new messages