Unique Index Re-design

From: Mohamed Houri <mohamed.houri_at_gmail.com>
Date: Fri, 28 Mar 2014 10:53:57 +0100
Message-ID: <CAJu8R6gSC=fnz60UokHM+_WeTamPzk9UMA9GaS50GGFk9jQOfg_at_mail.gmail.com>

I don't know if this is a good question worth an answer or not; nevertheless I couldn't resist the temptation to ask it.

I have a real life unique two columns composite *index ind_uk (a,b)*having the following picture

select num_rows, distinct_keys, clustering_factor

from all_indexes where index_name = 'IND_UK';

*num_rows, distinct_keys, clustering_factor*

1,350,375 1,350,375 146,386

The data repartition of these two indexed columns are

select a, count(1)

from table_t

group by a

order by count(1) desc;

shows one unique extreme count

*a count(1)*

90996518 67977 -à this is my concern

106628306 8

104585295 8

105558779 8

105243015 8

84407427 8

106183944 7

...

73262355 1

73262392 1

73393305 1

73393309 1

73393325 1

73469367 1

The majority of the remaining records are with count = 1

select

      b

    , count(1)

from table_t

group by b

order by count(1) desc;

*b count(1)*

null                432500  -à this is my concern

13/11/2013 00:00:00 9075

14/11/2013 00:00:00 9030

08/11/2013 00:00:00 8780

15/11/2013 00:00:00 8721

12/11/2013 00:00:00 8060

19/11/2013 00:00:00 7772

22/11/2013 00:00:00 7696

21/11/2013 00:00:00 7618

26/11/2013 00:00:00 7539

...

Etc..

This index when used by the CBO is generating a lot of time consuming *db file sequential read* wait events

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).
*My question finally is*: I want to reengineer this index so that (a) it
will still be unique (b) do not contain a = *90996518* value and (c) do not contains column b having null values.

I created the following index to honor my question

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);

Have you any other suggestions?

Thanks

Mohamed Houri

www.hourim.wordpress.com

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

Received on Fri Mar 28 2014 - 10:53:57 CET