Re: B-Tree to Partitioned index

From: Connor McDonald <hamcdc_at_yahoo.co.uk>
Date: Thu, 30 Dec 2004 01:07:03 +0000 (GMT)
Message-ID: <20041230010703.86010.qmail@web86903.mail.ukl.yahoo.com>

Some (untried, untested) ideas...

Create the new index online with a trailing additional column in it. This way you get to leave your current index on there until the new one has completed building.
Add a new column "X" to the table that is always null. Then same concept as (1) but using "X" as the additional column. The new index is then "closer" to the original one in terms of size and (theoretically) its impact on execution plans should be reduced.
If your table is partitioned and your new index is going to be locally partitioned, check out DBMS_PCLXUTIL for some nice ways of building the index in a time-efficient manner.

hth
connor

aj wells <awellsdba_at_gmail.com> wrote:

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

Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions" ISBN: 1590592174 web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com

Coming Soon! "Oracle Insight - Tales of the OakTable"

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"

Do you Yahoo!?
Read only the mail you want - Yahoo! Mail SpamGuard. http://promotions.yahoo.com/new_mail

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

Received on Wed Dec 29 2004 - 19:02:19 CST