re Unique Indexes --- was Re: v$object_usage: anyone have bad experience with it?
- From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
- To: zhuchao@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Sat, 05 Feb 2005 21:18:04 +0800
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » re Unique Indexes --- was Re: v$object_usage: anyone have bad experience with it?
