re Unique Indexes --- was Re: v$object_usage: anyone have bad experience with it?

I have to ask about
" Unique index can be dropped if it was created with a unique index without specifying a unique constraint on the column." Either you have miss-typed or transposed "constraint" and "index" or you or I have misunderstood them.

 From early versions onwards, and 9i still supports this, it has always been possible to create a Unique Index __without__ defining it as a Constraint. Such an Index is still used to enforce Uniqueness [ie Non-Duplicates]
on Inserts/Updates. In fact, in earlier versions, the actual text of the message for ORA-0001 used to be something different, something like "... duplicate value not allowed ..."

In your database [and I know I do have in a number of my databases], you might have a Unique Index created as an Index, but __not__ defined as a Constraint.

If the Index is not used in Queries, that's just too bad ! Queries are incorrectly
written.
If, however, you drop the Index, you are eliminating all the Index's automatic action in preventing Duplicate values !

Furthermore I believe that the note on FKs actually states that if you have a well-defined application, you do not need exclusive table locks. However, the absence of a Unique Index , even if not placing a table lock, would still require a full scan of the parent table !

Hemant

At 08:53 PM Saturday, zhu chao wrote:
>Hi Lex,
> Actually CPU bottleneck has been solved temporiry . I noticed the
>redundent index problem while I was doing the tuning job on that host.
> Unique index can be dropped if it was created with a unique index
>without specifying a unique constraint on the column.
> And your words about the FK index at 9.2 is no longer accurate, as
>note 223303.1 said. The document IS wrong in this case.
>"This new locking behaviour is an expected behaviour for the Oracle
>code. As only shared locks are involved, it does not prevent DML from
>being issued against either the child or parent tables. It will
>prevent operations that require an exclusive table level lock.
>However, as it generally considered to be bad design to have an
>application implementing exclusive table locks, the impact of the
>change should be minimal."
>
><snip>

>Regards
>Zhu Chao
>www.cnoug.org

Hemant K Chitale
http://web.singnet.com.sg/~hkchital                

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