Re: Unique Index Re-design

From: Mohamed Houri <>
Date: Fri, 28 Mar 2014 11:24:25 +0100
Message-ID: <>


Yes you are right for your first remark.

In a composite index (a,b) you need only to have one not null value (a or b) to see your corresponding couple of value indexed.

As per your second remark, we agreed with the customer to definitely delete those dummy rows. I was asking about a solution in case those dummy records needs to be kept and in that case partitioning might represent an alternative.


2014-03-28 11:19 GMT+01:00 Sayan Sergeevich Malakshinov <>:

> Hi Mohamed,
> > create unique index mho_ind on t4 (case when a = 90996518 then null
> else a end, case when b is not null then b end);
> Strictly speaking, "case when b is not null then b end" will have same
> values as just simple "b"
> And you will need to rewrite predicates(or maybe create view like "create
> view t4 select case when a = 90996518 then null else a end as A, ... from
> old_t4" )
> > When I asked the client what is the particularity of this a value
> (90996518) he answered that this a dummy value used for testing (yes for
> testing
> Am i right that all rows with such value are just dummy rows? In that
> case, i think, we could consider table partitioning with separation dummy
> rows from others and local indexing.
> --
> Best regards,
> Sayan Malakshinov

Bien Respectueusement
Mohamed Houri

Received on Fri Mar 28 2014 - 11:24:25 CET

Original text of this message