Re: B-Tree to Partitioned index

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 much.

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

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Public Appearances - schedule updated Dec 23rd 2004

• Original Message ----- From: "Mercadante, Thomas F" <thomas.mercadante_at_labor.state.ny.us> To: <awellsdba_at_gmail.com>; <oracle-l_at_freelists.org> Sent: Wednesday, December 29, 2004 2:12 PM Subject: RE: B-Tree to Partitioned index

April,

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!

Tom

-----Original Message-----

From: aj wells [mailto:awellsdba_at_gmail.com] Sent: Wednesday, December 29, 2004 9:04 AM To: oracle-l_at_freelists.org
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...

Thanks
aj
--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Wed Dec 29 2004 - 09:07:26 CST