Re: Creating unique index on huge table.

  • From: Mladen Gogala <gogala.mladen@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Fri, 23 Sep 2016 16:39:39 -0400

The OP has partitioned tables. I think that partitioning is only available on the EE, if my memory serves me right?

On 09/23/2016 11:00 AM, Powell, Mark wrote:

Just a reminder that ONLINE index rebuild is only available with EE, not with Standard, which is a bummer.

*From:* oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf of Tim Gorman <tim@xxxxxxxxx>
*Sent:* Friday, September 23, 2016 2:26:11 AM
*To:* Chitale, Hemant K; vkeylis2009@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
*Subject:* Re: Creating unique index on huge table.
Absolutely correct, thanks for catching that.

I don't recall using the ONLINE clause, but it's been a few years.

On 9/22/16 21:50, Chitale, Hemant K wrote:

I think you missed the REBUILD in the

alter index <index-name> partition <partition-name> parallel <degree> nologging compute statistics

What is scary is that this is a UNIQUE Index.

Hemant K Chitale

*From:*oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Tim Gorman
*Sent:* Friday, September 23, 2016 7:32 AM
*To:* vkeylis2009@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
*Subject:* Re: Creating unique index on huge table.


Several years ago, I accidentally dropped a unique index on a 55TB table, which represented about 12TB of index. There are several on this list who know exactly the circumstances, and one person on this list who was the recipient of the tearful phone call I made when I realized what I had done. :)

High-level description of what worked to rebuild it...

 1. Run "create partitioned index ... unusable" to create the
    partitioned index with all partitions empty.
 2. Create a shell-script to run NN SQL*Plus processes
    simultaneously, where "NN" is a number of your choice, each
    process doing the following...
      * alter index <index-name> partition <partition-name> parallel
        <degree> nologging compute statistics

We ordered the SQL*Plus calls inside the shell-script so that the partitions for the most-recent partitions (i.e. the table was partitioned by a DATE column) were populated first, and then let the builds progress back in time. Depending on the application, you can be doing some or all of the normal activities on the table. Our assumption (which proved correct) was that all DML occurs against the newest partitions, so those were the partitions that needed to be made "usable" first.

This approach won't eliminate downtime or performance problems, but it will likely minimize them.

I hope this makes sense?



On 9/22/16 15:43, Vadim Keylis wrote:

    I am working on adding replication using third party
    tool(dbvisit)  to our databases.

    I have couple 5T highly transactional partition tables.  I need
    to add a partition unique index to these tables without causing
    any outage or incurring performance hit. Will greatly appreciate
    suggestions on the best approach to do it.

    Thanks so much,


This email and any attachments are confidential and may also be privileged. If you are not the intended recipient, please delete all copies and notify the sender immediately. You may wish to refer to the incorporation details of Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at

Mladen Gogala
Oracle DBA
Tel: (347) 321-1217

Other related posts: