Re: Unique Index Re-design
- From: Mohamed Houri <mohamed.houri@xxxxxxxxx>
- To: Sayan Sergeevich Malakshinov <malakshinovss@xxxxxxxxx>
- Date: Fri, 28 Mar 2014 11:24:25 +0100
Sayan
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@xxxxxxxxx>:
>
> 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
Other related posts:
