Skip to Main Content
  • Questions

  • Rebuild Index of partition after updating partition of record

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Asim.

Asked: April 08, 2017 - 3:21 pm UTC

Last updated: March 09, 2019 - 2:29 am UTC

Version: abc

Viewed 1000+ times

You Asked

Hi,
I got the huge table with me ,consider it as table1.
And I want to delete old record from it around 2 billion. This table is partition on date using range partition.

So I have updated the it's date field and pulled identified record to one partition and truncated that partition.

So I need to rebuild index of table but rebuild index of large table will consume more time and it will lock the table.

Can you please assist is there any other optimize process to rebuild index or delete record.
Or Can only rebuild​ index of partition.

Thanks for help in advance

and Connor said...

I'm not sure what the rebuild options are on version 'abc' :-)

But on most Oracle versions you can rebuild the index online, and thus not have locking issues.

If you need the index available *constantly*, even during the drop partition operations, you could consider using a syntax such as:

alter table T drop partition P update indexes;

which will drop the partition and keep the indexes in a usable state (at the cost of slower operation and more redo consumption)

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

onmouseover=alert(1)

<b>BOWLER</b>, March 07, 2019 - 10:47 am UTC

onmouseover=alert(1)
<h1>KALI LINUX</h1>
Connor McDonald
March 09, 2019 - 2:29 am UTC

uh huh
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.