Home » SQL & PL/SQL » SQL & PL/SQL » Rebuild Partition Indexes (oracle 11gR2)
Rebuild Partition Indexes [message #559666] Thu, 05 July 2012 07:28 Go to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
Hi,

I got from websites that below details

Today I found myself in a situation where I needed to compress an already existing, partitioned index. First I issued an alter table to rebuild and compress the whole index:

SQL> ALTER INDEX MY_INDEX REBUILD COMPRESS;
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a wholeSo next I tried to rebuild compress one of the partitions:

SQL> ALTER INDEX MY_INDEX REBUILD PARTITION PART1 COMPRESS;
ERROR at line 1:
ORA-28659: COMPRESS must be specified at object level first

Turns out that there is no way you can do a rebuild compress on partitioned indexes. I did not realize that until I stumbled across document 312843.1 on Metalink. It says the only way to compress a partitioned index is to drop it and recreate it (through 11.2).


My question is do we have any new feature in Oracle 11gR2 to rebuild partition indexes?

Thanks,
Srinivas

[Updated on: Thu, 05 July 2012 07:35] by Moderator

Report message to a moderator

Re: Rebuild Partition Indexes [message #559668 is a reply to message #559666] Thu, 05 July 2012 07:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SQL> create table t (id integer) partition by range (id) (partition p1 values less than (100),
  2  partition p2 values less than (maxvalue));

Table created.

SQL> create index t_i on t(id) local;

Index created.

SQL> alter index t_i rebuild compress;
alter index t_i rebuild compress
            *
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole


SQL> alter index t_i rebuild partition p1 compress;
alter index t_i rebuild partition p1 compress
*
ERROR at line 1:
ORA-28659: COMPRESS must be specified at object level first


SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 32-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

It seems no.

Regards
Michel
Re: Rebuild Partition Indexes [message #559669 is a reply to message #559668] Thu, 05 July 2012 07:42 Go to previous message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
More specifically http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_1010.htm#i2050526

Quote:
•You cannot rebuild an entire partitioned index. You must rebuild each partition or subpartition, as described for the PARTITION clause

and http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_1010.htm#BABHFFHI

Quote:
If you want to use compression for a partitioned index, then the index must have compression enabled at the index level.

So nothing has changed.

Regards
Michel

[Updated on: Thu, 05 July 2012 07:43]

Report message to a moderator

Previous Topic: specific data from column of details table.
Next Topic: can we create view in onther view
Goto Forum:
  


Current Time: Tue Apr 29 08:56:56 CDT 2025