Re: conditional unique index to restrict duplicates of a particular set

1. To me sure the performance impact of this index I would do an actual test with some volume with and without the index.
2. I do not see how this index could be used by real predicates, so most probably this index would not be useful for anything else.
3. You may want to consider NOT having this index and use a trigger instead. In other words, have a regular non-unique B-Tree index on acc_number, branch_code, feed_date and feed_type. Then the trigger would validate that values I and E do not duplicate. The business requirement that you describe, in my opinion is better served with a trigger than with the unique index you described. This regular non-unique B-Tree index would most probably be used by other predicates in your application.

Cheers — Carlos

On May 7, 2014, at 3:52 AM, vijay sehgal <vijaysehgal21_at_gmail.com> wrote:

> Dear Experts,
>
> I have a few questions with reference to below scenario:
>
> Table Structure : feed_details( id_pk number,
> acc_number number,
> branch_code number,
> feed_date date,
> feed_type varchar2(1),
> balance number
> );
>
> The feed_type can have three values 'I','E' and 'P'. We have to put a conditional unique constraint on acc_number, branch_code, feed_date and feed_type. The feed_date will have date and time will be always set to midnight.
>
> The constraint is to be put such that duplicates of feed type 'P' should be allowed but duplicates for 'I' and 'E' should not be allowed.
>
> e.g. acc_number|branch_code|feed_type|feed_date
> 101 |10 | I | 07052014
> 102 |10 | E | 07052014
> 101 |10 | I | 07052014 -- should not be allowed
> 102 |10 | E | 07052014 -- should not be allowed
> 103 |10 | P | 07052014
> 103 |10 | P | 07052014
>
> FB unique index as below would allow this to be achieved.
>
> CREATE UNIQUE INDEX idx_feed_details
> ON feed_details(CASE WHEN fed_typ!='P' THEN feed_date ELSE null END,
> CASE WHEN fed_typ!='P' THEN acc_number ELSE null END,
> CASE WHEN fed_typ!='P' THEN branch_code ELSE null END,
> CASE WHEN fed_typ!='P' THEN feed_type ELSE null END);
>
>
> 1) How should we correctly measure the performance hit after his index is created (the inserts would be concurrent from multiple java connections).
> 2) Since the index is only to restrict duplicates, would this index be helpful in some other scenarios of data selection when the predicate is on these columns.
> 3) any other overheads / side effects of this kind of index.
>
> Hope the questions and scenario is clear.
>
> Thanking you all for your time and help.
>
> Warm Regards,
> Vijay Sehgal.
>
>

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