Re: Unique Index Re-design

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.

Thanks
Mohamed

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

>
> 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
> > in PRODUCTION).
>
> 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
> http://orasql.org

-- 
Bien Respectueusement
Mohamed Houri

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