Oracle Changing Unique Index to Non-Unique Index on Primary Key

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_8016.htm

According to Oracle it does invalidate.

That's interesting (and maybe new in Oracle11?).  I've never seen that happen, but we've only been woking with Oracle11 for about six months, and it is possible that I haven't dropped any indexes in Oracle11.  I certainly have not seen this behavior in earlier versions of Oracle.

This is true for prior versions as well.

I would recommend trying this in a test system to see if dropping an index really does invalidate any objects.

DROP INDEXPurpose

Use the DROP INDEX statement to remove an index or domain index from the database.

When you drop an index, Oracle Database invalidates all objects that depend on the underlying table, including views, packages, package bodies, functions, and procedures.

When you drop a global partitioned index, a range-partitioned index, or a hash-partitioned index, all the index partitions are also dropped. If you drop a composite-partitioned index, all the index partitions and subpartitions are also dropped.

Sounds like not just partitioned.

Ok.

Do you have any problem in re-validating the objects? Sounds like you have to drop and recreate the indexes as mark suggested and oracle says the same.

Yes, that is an issue. This is a large data warehouse.

Sure. Give us more details. Are you trying this on a regular table or part table? Why do you want to do that?

How big your table?

regular but could be partitioned as well. Obviously, to allow duplicates in certain conditions so that jobs don't break within etl  and to tweak the constraints as needed.

Sounds like from what I am reading we should be defining our pk constraints with non-unique indexes so we have more control/flexibility in the future.  

Invalidation due to dropping and recreating an index is not something that should worry you. Oracle recompiles invalid objects for you, on demand, or you can manually recompile using utlrp.sql or exec dbms_utility.compile_schema(). But you won't have to do this when dropping/recreating indexes, as markgeer said, any invalidation is done transparently in this case.

There is no way to drop + create indexes without Oracle using standard procedure, which is to invalidate dependencies which will then be recompiled.

But it doesn't mean you shouldn't do it. Nowhere do the docs say "invalidation of objects is bad", it is just part of Oracle's architecture.

Simple test.

select object_name, status from user_objects where status = 'INVALID';
 
create table mytab(id integer);
 
create index ix_mytab on mytab(id);
 
create or replace procedure testproc as
begin
  update mytab set id = 0;
end;
/
 
create or replace view myview as select * from mytab;
 
drop index ix_mytab;
 
select object_name, status from user_objects where status = 'INVALID';
 
 
-- No invalid objects
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:

Thanks for the info, I do appreciate the effort...but not what the post question asked. It's not that it "worries" us to drop an index, it's just too much overhead and not flexible...the most flexible solution is to create non-unique indexes.

That was the question in this post...not whether or not we should drop an index!

Will share the points anyway!

Thanks
http://dbaforums.org/oracle/index.php?showtopic=4334

The answer was not what I was asking...but it was not inaccurate. Good effort but found optimum solution myself.

>>It's not that it "worries" us to drop an index, it's just too much overhead and not flexible...the most flexible solution is to create non-unique indexes.

The most flexibile solution is to create non-unique indexes?? Solution for what? Create the appropriate index for the column, or let Oracle create the index it needs to support the key or constraint. A unique index is the _correct_ index for many cases.


>>That was the question in this post...not whether or not we should drop an index!

No, I read your question clearly, but it seems you were looking for something else. Your question was if you could replace a unique index with a non-unique index without invalidating objects. The answer is no, because to replace a unique index with a non-unique index requires dropping the unique index and creating a non-unique one. What am I missing?

Your question was about dropping indexes, not dropping constraints while keeping the original index.