Re: B-Tree to Partitioned index

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 29 Dec 2004 15:12:12 -0000

Isn't that just going to get:
    ORA-nnnnn such column list already indexed

If you want to try something like this, then
you will have to re-order the columns, or
add/remove a column to allow both indexes
to exist at once.

It's possible that going to local partitioning
will allow the partition key columns to be
dropped from the index - if they are in there
at present, and don't add much value - but
even then you may have problems with the
optimizer not liking the new index stats so

You may have to do an online re-definition
of the table in order to be able to build the
indexes you want :(


Jonathan Lewis
The Co-operative Oracle Users' FAQ
Public Appearances - schedule updated Dec 23rd 2004

----- Original Message ----- 
From: "Mercadante, Thomas F" <thomas.mercadante@xxxxxxxxxxxxxxxxx>
To: <awellsdba@xxxxxxxxx>; <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, December 29, 2004 2:12 PM
Subject: RE: B-Tree to Partitioned index


One suggestion is to just create the new partitioned index with a different
name.  When it is complete, drop the old one and rename the new one if you
want it named a certain way.

Look at the ALTER INDEX RENAME command available in Oracle 9i.  If naming it
is not the issue, then just build the new one and drop the old one.

Hope this helps!


-----Original Message-----
From: aj wells [mailto:awellsdba@xxxxxxxxx]
Sent: Wednesday, December 29, 2004 9:04 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: B-Tree to Partitioned index

Is there anyway to move from a "normal" b-tree index to a locally
partitioned index online without having to to a rebuild?

We have fairly large databases (15 TB for the one that we are
concerned with now) and we need to get one of the highest hitting
indexes rebuilt into a partitioned index, but we have no way to do
that elegantly when we are dumping nearly 50 GB an hour into the
database 24 hours a day.

Suggestions?  Docs are not OVERLY helpful here...



Other related posts: