RE: Unique Index Re-design
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "mohamed.houri@xxxxxxxxx" <mohamed.houri@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 28 Mar 2014 10:42:08 +0000
In principle if you hide the 90996518 values you're only going to reduce the
volume of the index by 5% - which doesn't sound like the solution to "a lot of"
single block reads.
Why are you seeing a lot of reads ? What's the nature of the queries (or DML)
that causes them to happen ?
When you say this index is "generating" a lot of single block reads, do you
mean that index blocks are read randomly, or that table blocks identified by
the index are read randomly ?
Looking at the states (8,000 to 9,000 rows per date) I'd guess that inserts (or
updated) for a date may be happening around the same time requiring a constant
volume of random reads of the index to find the blocks that need updating. But
are the queries then: fetch me everything for a date, fetch me everything for
an "a" value, or fetch me an "a" value across a range of dates ? And what
queries do you have that address the nulls in the data column ? Is some of you
db file sequential read the result of index fast full scans where lots of index
blocks are already in memory ?
Based on details supplied so far I'd be considering the two indexes (b,a) for
the uniqueness and (a) for precision when only (a) has been supplied in
predicate (knowing, of course) that there should be no production queries for
a=90996518 - but that's just based on a few guesses about what the system might
be trying to achieve.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Mohamed Houri [mohamed.houri@xxxxxxxxx]
Sent: 28 March 2014 09:53DML)
To: ORACLE-L
Subject: Unique Index Re-design
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.hourim.wordpress.com/>
Other related posts:
