Re: Testing for Existence of Rows - What's Fastest?

In article <3cefdc73$1_3_at_nopics.sjc>, "contrapositive" says...
>
>
>"Mladen Gogala" <mgogala_at_adelphia.net> wrote in message
>news:acokls$r6lfo$1_at_ID-82084.news.dfncis.de...
>> On Sat, 25 May 2002 13:45:32 -0400, contrapositive wrote:
>>
>> Well, the first thing that comes to mind is a unique index. They are
>> usually pretty good at determining uniqueness. If the property is
>> complicated, try with a function-based index.
>>
>Manipulating indexes really aren't an option, but that wouldn't help me much
>anyway. This uniqueness is only enforced only on particular types of records
>(there's a WHEN clause on the trigger), and furthermore records don't have
>to be unique throughout the table (see the WHERE clause in the original
>posting). I should have mentioned these things up front.
>
>
>

where clause was just ... in original posting.

In any case -- use Mladen's other piece of advice -- a function based index. It can selectively index rows easily and it's the ONLY way to enforce uniqueness without adding TONS of serialization.

If you are "looking" in the table to see if another row exists already -- and two people do this at the same time -- they will not see eachothers rows (multi-versioning, non-blocking reads) and will achieve DUPLICATES. You really need to look at using an index for this.

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp