Re: exchange partition and transport tablespace with unique constraints

Funny, there's a note in one of the Oracle doco's saying 'you are advised not to use unique indexes'. I can't remember where this was (it related to something I was looking up in 8.1.6). And the particular paragraph that contained this gem didn't elaborate as to why they weren't to be used..... but I remember being quite pleased to discover that statement there, because I've been telling people for years to avoid unique indexes like the plague, at least so far as constraint enforcement is concerned.

There's no need for such indexes to be unique, as of 8.0 onwards. And there is no disadvantage in them being non-unique: the optimizer knows that a non-unique index enforcing unique constraints must have unique contents, and treats it as such. There is zero performance difference between them, in other words. Yet non-uniques have the distinct advantage of not doing a disappearing act every time you try a quick 'disable contraint'.

Conclusion (mine, and Oracle's it would seem): welcome to the world of non-unique indexes.

Regards
HJR

--
-----------------------------------------------
Resources for Oracle : http://www.hjrdba.com
===============================

"John Summers" <john.summers_at_medtronic.com> wrote in message
news:5d76b757.0204051644.3c7a466d_at_posting.google.com...


> Here's the situation. We have a large database (150G) with key tables

> that are range partitioned by month (with tablespaces for each table

> partition) . The goal is to have a rolling year online and be able to

> restore any old partition when needed. The other goal is to have the

> database online  24 X 7... or as close to that as we can.

>  The plan is to exchange the partitions for a given month with

> non-partitioned "holder" tables. Then, export (ala transport) the

> exchanged partitioned tablespaces. We can then backup the meta file

> and db files containing the partitioned tablespaces and reverse this

> process and get the partition back online. Or, before you comment

> about all the tablespace and partition names that will pile up, we can

> drop the partition and set up a new database for handling the

> archives.

>   This is all well and good... until you consider the constraints,

> especially the primary key and unique constraints.  You can set up

> your tables so that you can exchange all you want with all the

> constraints you like... but you're never going to be able to export a

> transportable tablespace that have FK, PK, or unique constraints. I'm

> not sure how all this pointing works... but, Oracle knows they are

> pointing to something that is outside the transportable tablespaces

> (not self contained).

>   This is fine, you say, disable the constraints, exchange, and

> transport, voila.. re-enable and you're fine.  The catch is... I've

> got some big tables, so disabling/re-enabling constraints that have

> indexes supporting them is not possible. So, the only solution I see

> is to create non-unique indexes for all of my primary keys and unique

> constraints. This lets me disable without losing the index and then I

> must do an "enable novalidate" and throw in a "rely" when dealing with

> all of the constraints on these tables.

>


>   The conclusion: For large partitioned tables that "archive"

> partitions... unique indexes cannot be used.

>


>  Am I missing something here? Is there another way? I had always

> thought unique indexes were useful. Am I stuck in a non-unique

> world.... aaaahhh!

>


> john.