Re: Creating unique index on huge table.

Also, if the index is unique, the statistics doesn't really need to be computed immediately. Computing statistics on such a huge data set is very expensive. On the other hand, with unique index, the key distribution is already known, exactly one row per key. What is not known is the clustering factor, which is used for the range scans, but that can be gathered later, using estimate statistics, with block sampling, which is significantly cheaper than doing "compute statistics". Regards

On 09/23/2016 01:41 AM, Jonathan Lewis wrote:
>
> Does the index have to be unique, or can it be non-unique with a
> unique constraint behind it ?
> What you way partitioned index do you mean it has to be locally
> partitioned or could it be globally partitioned.
>
> The problem with a unique index is that you can't create it as an
> unusable index and then rebuild it in steps - the table cannot be
> modified while the unique index is unusable. (And I have to say, what
> makes you think that the 5TB data hasn't got any duplicates ? What are
> you going to do if you do have duplicates ?)
>
> Just in case you've overlooked it - if you want locally partitioned
> unique index (or unique constraint) the unique key must include the
> partition key).
>
> If you want virtually no downtime (but a lot of slow time) you can:
>
> Set the system to skip unusable indexes
> Create a non-unique partitioned index unusable
> Rebuild each partition in turn - perhaps several at once, as described
> by Tim.
> Add a unique constraint constraint in the 'enable novalidate' state
> using the index you've created
> Validate the constraint.
>
> At 5TB you might want to read the following posting I did some time
> ago, because the validation could be massively expensive (unless
> Oracle has changed the code) - so you'll want to do some testing to
> check what method Oracle uses and how you can optimise it:
> https://jonathanlewis.wordpress.com/2012/07/12/pk-problem/
> <https://jonathanlewis.wordpress.com/2012/07/12/pk-problem/>
>
> Note that my example is about a primary key - which includes nulls;
> uniqueness by itself may display some differences.
>
>
>
>
>
>
> Regards
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
> _at_jloracle
> ------------------------------------------------------------------------
> *From:* oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org]
> on behalf of Vadim Keylis [vkeylis2009_at_gmail.com]
> *Sent:* 22 September 2016 22:43
> *To:* oracle-l_at_freelists.org
> *Subject:* Creating unique index on huge table.
>
> 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,
> Vadim

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


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