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

From: Mark Bobak <Mark.Bobak_at_proquest.com>
Date: Wed, 7 May 2014 13:50:10 +0000
Message-ID: <CF8FAF5D.5950F%Mark.Bobak_at_ProQuest.com>



You don't mention a version number.

If you're on 11g, you could create 4 new virtual columns: Alter table feed_details (add non_p_feed_date date generated always as CASE WHEN fed_typ!='P' THEN feed_date ELSE null END);

(not sytax checked)
Do the same for the other three columns, then do index as: Create unique index on feed_details(non_p_feed_type,non_p_branch_code,non_p_acc_number) compress n nologging;

Consider the compressibility of the first 'n' columns, and replace n above, with appropriate value.

IMHO, this looks "neater". Also, if you apply predicates to a query, for example, if you know for a fact that you're only interested in rows where fed_typ != 'P', then you can use these columns in your predicates, and the index should be utilized. If you have a situation where the table is quite large, but you're interested only in rows where fed_typ!='P', and that set of rows is relatively small compared to the total size of the table, then this index could be quite advantageous. By only indexes the "interesting" rows, the index is much more cache friendly and efficient. But, those are all only potential side effects of this technique. By creating virtual columns, and a unique index on them, it should serve your purpose.

Hope that helps,

-Mark

From: vijay sehgal <vijaysehgal21_at_gmail.com<mailto:vijaysehgal21_at_gmail.com>> Reply-To: "vijaysehgal21_at_gmail.com<mailto:vijaysehgal21_at_gmail.com>" <vijaysehgal21_at_gmail.com<mailto:vijaysehgal21_at_gmail.com>> Date: Wednesday, May 7, 2014 at 3:52 AM
To: ORACLE-L <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>> Subject: conditional unique index to restrict duplicates of a particular set

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
Received on Wed May 07 2014 - 15:50:10 CEST

Original text of this message